Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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

share|improve this answer
    
Thanks, the problem is that when user A creates a new schema/table, he has no privileges at all on the entities that he just created. I'd like him or her to have all privileges instead with no intervention from the root user (me). Is this possible? –  Gevorg Nov 6 '12 at 19:06
    
so you have a user who has permissions in schema a. but is creating objects in schema b? Or does your user only have permissions to a specific set of objects? –  Lumpy Nov 6 '12 at 19:40
    
I'd like a user to be able to create schemas. Once a user create a schema I'd like him or her to have automatically all privileges on that schema. This way, I can let new users play around and be fairly autonomous without risking to compromise existing data –  Gevorg Nov 6 '12 at 22:16
1  
What you are asking is standard protocol for Oracle because when you create a user in Oracle, a schema is automatically generated in the default tablespace or the tablespace of your choice. MySQL does not follow that paradigm. In MySQL, a user with all privileges should be responsible for user creation and subsequent credential assignments. –  RolandoMySQLDBA Nov 6 '12 at 22:37
    
A mysql user can use GRANT commands to assign privileges to other MySQL users, but can only assign as many privileges as the user owns. –  RolandoMySQLDBA Nov 6 '12 at 22:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.