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

2 Answers 2

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

CREATE TABLESPACE dwtblspc LOGGING DATAFILE 'D:\oraclexe\app\oracle\oradata\XE\DWTBLSPC.DBF' SIZE 300M AUTOEXTEND ON NEXT 1048K MAXSIZE UNLIMITED;

CREATE USER prateek IDENTIFIED by prateek DEFAULT TABLESPACE dwtblspc ACCOUNT UNLOCK;

GRANT unlimited tablespace to prateek ;

CREATE ROLE prateek_role;

GRANT CREATE SESSION,ALTER SESSION,CREATE TABLE,CREATE CLUSTER,CREATE SYNONYM,CREATE VIEW,CREATE SEQUENCE,
CREATE DATABASE LINK,CREATE PROCEDURE,CREATE TRIGGER,CREATE TYPE,CREATE OPERATOR,CREATE INDEXTYPE,BACKUP ANY TABLE,
SELECT ANY TABLE,CREATE ANY TABLE,CREATE ANY INDEX,ALTER ANY INDEX,ALTER ANY INDEXTYPE,DROP ANY INDEX,DROP ANY INDEXTYPE to prateek_role;

GRANT prateek_role to prateek ;

Hope it help.

share|improve this answer
1  
The OP's post was clearly tagged with MySQL - so your answer is not very useful ;-). –  Vérace Sep 3 at 16:23
    
if you are conceptually clear then whatever the persistence doesn't matter so I think it can give a clear guide for anyone other than syntax. –  Sai prateek Sep 3 at 16:29
    
This answer does not help because 1) there are no roles in MySQL, 2) many of the grants you have in your answer do not exist in the mysql grant tables, 3) there are no types in MySQL, 4) no sequences in MySQL, 5) no database links, 6) no operators, 7) no clusters, 8) no synonyms, 9) no backup grants, 10) There are separate GRANTs for 4 levels of access. They are not united in MySQL as your are showing from SQL Server, 11) Grants are not tablespace-dependent, 12) no concept of ANY TABLE and ANY INDEX as a separate grant, etc. –  RolandoMySQLDBA Sep 3 at 16:49
    
@Rolando I don't think this is SQL Server specific, it is Oracle specific. Doesn't change anything, just clarifying. :-) Anyway, it is an attempt to answer, it's just wrong. –  Aaron Bertrand Sep 3 at 16:53
    
@AaronBertrand OMG you're right, it is Oracle. it's in the path. –  RolandoMySQLDBA Sep 3 at 17:33

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.