Re: Creating Users

[Javier Rodriguez Paiva]

Hi Jeff,

GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;

would be useful in case you want to create another root account (with all privileges for everything, even creating new users).

I think what you are looking for is something more like:

GRANT ALL PRIVILEGES ON *.* TO user@'%' IDENTIFIED BY 'password';

That way the user won't have the privileges for GRANT and REVOKE.
However, he would still be able to access mysql database and could use DELETE's or UPDATE's to modify the user table (deleting users and changing passwords perhaps). I'm not really sure on how to avoid that, since mysql (and even information_schema) is a database as any other one, and if you give a user permissions to edit all databases, it could edit mysql as well.

The only solution I can think right now would be creating a user just for creating databases, and then having the root account granting him privileges for the db he/she just created. It would go like this:

GRANT CREATE ON *.* TO 'db_creator'@'%' IDENTIFIED BY 'password';

and, when db_creator creates a new database (let's say... new_db); the root account would have to grant him/her privileges like:

GRANT INSERT, SELECT, UPDATE, DELETE ON new_db.* TO 'db_creator'@'%' IDENTIFIED BY 'password';

I know its not the smartest way, so if you or anyone else come up with any ideas, it would be really interested in reading them.