Account with GRANT statements.

[Morgoth]

Windows 2000, IIS 5.0, New to MySQL

After reading how to create an account in the manual files, I am even more lost, and angry then I was when I started.

This is all very confusing and it seems very complicated. I don't understand at all why it has to be so large like this. this seems like alot of work for something so small of a job.

I am also very confused with the syntax used, it's all uncommon to me.


I am to creat an account using GRANT, and I see this:


shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;


I am not sure what account type I should allow use hosted users to have. I don't want to give them admin because of obvious reasons, now do I want to have them access databases without a password and on the local host. I assume a hosted user would use monty becuase they need a password, am I right?

I also need to know what application I proform this in. In my Command promt?

Now to create this user, I want all scripts ot be ran on the server computer (no access from the outside, if this is what it means) and for the user to have to use an account name and password.

Now I see this:

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]

I don't understand the syntax very well, and it does explain it to me, but not exactly. By creating a table, am I lead to believe I have ot create a table for the user when he requests it?
In ASP I use a MS access database, and I create it by hand off the server, how does the user create a database with tables and everything else himself online? I assume it is possible.

I need this server running with pretty much basic database usages and some if any requirements for me to touch (including creating the user account which I am tring to figure out how to do now.)

Is their anything else I need to know that might require me to look into? I don't want this server to slow down my computer or effect anyother applications from running. As I said before I am new to MySQL and it's all very strange.

Sorry for my ignorance

[bcarl314]

Well, I'm not exactly sure what you want to accomplish here. The grant option basically creates a username and password that you will use to connect to a database.

Let's say you're using php.
to connect to a DB you're code would look something like this...

$linkID = mysql_connect("localhost","myUserName", "myPassword");
mysql_select_db("myDBName", $linkID);

Here you're telling php to try to use mySQL with the userName of "myUserName" and a password of "myPassword".

Then your telling it to use the DB "myDBName".

Now to execute, php will suppliy mySQL with the username and password above, so you need to create an account with that info. To do that you go to mySQL and type:

GRANT USAGE ON myDBName.* TO myUserName@localhost IDENTIFIED BY myPassword;

This will give the user "myUserName" access rigths only to the myDBName Database. The user will not be able to add/remove any additional users, but should have full Access to the DB to select, insert, and update information. (Not sure about altering the table structure though)

If you change the above to:
GRANT USAGE ON *.* TO myUserName@localhost IDENTIFIED BY myPassword;

You will give "myUserName" usage rights to every DB on the system. (The difference being *.* vs. myDBName.*

IF you want to restrict access further (Ie only alow a user to "SELECT" from a DB, you'd use

GRANT SELECT ON myDBName.* TO myUserName@localhost IDENTIFIED BY myPassword;

I hope I remember all of this correctly. :eek:, but I checked the docs and it looks right. I haven't set up users in a while so Don't hold me to it! ;)
Hope this helps!

[Morgoth]

Thank you again bcarl314, you have helped once more!

And by "go to mySQL and type" you mean my command prompt?

I will test this when I get back from school.

[Morgoth]

Ok well, I still have some problems.
I don't know if the account was created becuase I don't know what program I insert...

GRANT USAGE ON myDBName.* TO myUserName@localhost IDENTIFIED BY myPassword;

...into.


And also, this creates the account, do I have to create the database?
And If I have to create the database, do the users create their own tables, or what?


Once I get accounts created I think I can get started on acually using the program.