Mysql – How to automatically grant all privileges to a schema created by a user in thesql

MySQLpermissionsusers

Someone new joined the company and I created an account for her in mysql using the following:

CREATE USER 'username' IDENTIFIED BY 'password'

She needs to create her own schema and so used:

GRANT CREATE ON *.* TO username;

The problem is that now she is able to create new schemas and tables but she cannot do anything with it. I'd like her not to have access to existing schemas/tables but be able to create her own db and do anything with it. Is this possible?

Best Answer

To grant all privs on database mydb to username

GRANT ALL PRIVILEGES ON mydb.* TO username;

To grant privs on a table mydb.mytable to username

GRANT ALL PRIVILEGES ON mydb.mytable TO username;

I suggest you drop the user first to clear up any server-wide privileges.

DROP USER username;
CREATE USER username;
GRANT ALL PRIVILEGES ON mydb.* TO username;

Why drop the user first? When you first create a user called username and login, username has no privs. Just run SHOW GRANTS; to see it. It should say something like GRANT USAGE ON *.* TO username. At that moment, username can only login. That's your chance to create privileges.

If you run

GRANT ALL PRIVILEGES ON *.* TO username;

username will have global database privileges (all databases)

If you run

GRANT ALL PRIVILEGES ON mydb.* TO username;

username will have database-wide privileges on the mydb database only.

If you run

GRANT ALL PRIVILEGES ON mydb.mytable TO username;

username will have table privileges on the mydb.mytable only.

You cannot mix them. If you do you accidently slip username privileges that username should not have.

If you want to see what privileges exist at the three levels run the the following queries

SELECT table_name,column_name
FROM information_schema.columns
WHERE table_schema='mysql'
AND table_name IN ('user','db','tables_priv')
AND column_name LIKE '%\_priv'
ORDER BY table_name,column_name;

Here are privs for MySQL 5.5

mysql> SELECT table_name,column_name
    -> FROM information_schema.columns
    -> WHERE table_schema='mysql'
    -> AND table_name IN ('user','db','tables_priv')
    -> AND column_name LIKE '%\_priv'
    -> ORDER BY table_name,column_name;
+-------------+------------------------+
| table_name  | column_name            |
+-------------+------------------------+
| db          | Alter_priv             |
| db          | Alter_routine_priv     |
| db          | Create_priv            |
| db          | Create_routine_priv    |
| db          | Create_tmp_table_priv  |
| db          | Create_view_priv       |
| db          | Delete_priv            |
| db          | Drop_priv              |
| db          | Event_priv             |
| db          | Execute_priv           |
| db          | Grant_priv             |
| db          | Index_priv             |
| db          | Insert_priv            |
| db          | Lock_tables_priv       |
| db          | References_priv        |
| db          | Select_priv            |
| db          | Show_view_priv         |
| db          | Trigger_priv           |
| db          | Update_priv            |
| tables_priv | Column_priv            |
| tables_priv | Table_priv             |
| user        | Alter_priv             |
| user        | Alter_routine_priv     |
| user        | Create_priv            |
| user        | Create_routine_priv    |
| user        | Create_tablespace_priv |
| user        | Create_tmp_table_priv  |
| user        | Create_user_priv       |
| user        | Create_view_priv       |
| user        | Delete_priv            |
| user        | Drop_priv              |
| user        | Event_priv             |
| user        | Execute_priv           |
| user        | File_priv              |
| user        | Grant_priv             |
| user        | Index_priv             |
| user        | Insert_priv            |
| user        | Lock_tables_priv       |
| user        | Process_priv           |
| user        | References_priv        |
| user        | Reload_priv            |
| user        | Repl_client_priv       |
| user        | Repl_slave_priv        |
| user        | Select_priv            |
| user        | Show_db_priv           |
| user        | Show_view_priv         |
| user        | Shutdown_priv          |
| user        | Super_priv             |
| user        | Trigger_priv           |
| user        | Update_priv            |
+-------------+------------------------+
50 rows in set (0.01 sec)

mysql>

Run the query to see the privs for 5.1 and 5.0

Related Question