I had to make a new database today on one of my testing servers at home and for the 75th time, I had to look up the syntax for adding a new user to all the grant tables. So I decided to make a handy cheat sheet for myself here:
From the shell, create the database and then get into MySQL's monitor:
$ mysqladmin -u root -p create db_name;
$ mysql -u root -p mysql
Then when in the monitor do the following:
INSERT INTO host
(host,db,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('localhost','db_name','Y', 'Y', 'Y', 'Y', 'Y', 'Y');
INSERT INTO user
(host,user,password)
VALUES ('localhost','username',password('passwd'));
INSERT INTO db
(host,db,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
VALUES ('localhost','db_name','username','Y','Y','Y','Y','Y','Y');
FLUSH PRIVILEGES;
There: local priviledges for "username" on a handy, easy-to-find page fit for cut-n-paste.
Or... instead of those three database commands:
grant all privileges on db_name.* to username@localhost identified by 'password';
Or something really close to that.. I always get it wrong the first time for some reason. :P
Ah, but I hardly ever want to grant all permissions to a user. Most often the "user" is a program, which often has to be readable by the web server's user. More often than not, I only grant insert and select to a user. I also restrict based on host; more often than not (by a wide margin) only users at localhost can connect. I like seeing the 'Y' and 'N' all over the insert statement.
You are right, though. I could however do this:
GRANT SELECT,INSERT,UPDATE
ON dbname.*
TO username@localhost
IDENTIFIED BY 'passwd';
That would get me where I wanted to be. I started off doing it the old fashioned way and the habit stuck I guess.
Posted by wee at November 19, 2002 10:16 PM