Wednesday, June 22, 2011

Less known facts about MySQL user grants

Reading MySQL security: inconsistencies I remembered a few related experiments that I did several years ago when I was studying for the MySQL certification. The first fact that came to mind is about the clause "WITH GRANT OPTION", which can only be given on the full set of options, not on a single grant. For example
GRANT INSERT,DELETE,UPDATE on world.* to myuser identified by 'mypass';
GRANT SELECT on world.* to myuser identified by 'mypass' WITH GRANT OPTION;
show grants for myuser\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'
*************************** 2. row ***************************
Grants for myuser@%: GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'myuser'@'%' WITH GRANT OPTION
If you are surprised about the "WITH GRANT OPTION" clause applying to all grants instead of only applying to the SELECT, you forgot to consider how the grants are stored. All the grants for a given user (and a user is the combination of a name and a provenience) are stored in a single record in the mysql.user table. The GRANT OPTION is a column in that record. It is either set or not. You can't assign this option for only one attribute in the same record. It's either all the flagged grants or nothing. If you want to assign the "with grant option" on a single column, you must change either the provenience or the name of the user (thus opening another record). But also this addition may not be enough to reach your goal, as you can see in the next section. The other fact that came to mind about the "WITH GRANT OPTION" clause is that, in the examples given, it is ineffective. I dare say illusory. Let's start. As root user, we create this user:
root> grant all on granted.* to grantee identified by 'happyuser' with grant option;
The granted database exists, and now we have an user that can modify it, and, we think, delegate some functions to someone else.
grantee> grant select on granted.* to delegated identified by 'happy';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
grantee> create user delegated;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
Right. I can't create a new user, but only transfer my superpowers to someone else. I will ask root to create the user, and then I will give it another try.
root>  create user delegated;
Query OK, 0 rows affected (0.00 sec)

grantee> grant select on granted.* to delegated identified by 'happy';
ERROR 1044 (42000): Access denied for user 'grantee'@'%' to database 'mysql'
Ouch! Since the grant tables are in the 'mysql' database, I don't have access. I will ask root to give me access to the mysql 'user' and 'db' tables.
root>  grant insert on mysql.user to grantee ;
Query OK, 0 rows affected (0.00 sec)
root>  grant insert on mysql.db to grantee ;
Query OK, 0 rows affected (0.00 sec)

grantee> grant select on granted.* to delegated identified by 'happy';
ERROR 1044 (42000): Access denied for user 'grantee'@'%' to database 'mysql'
Not good. I tried then to get SELECT,INSERT,UPDATE,DELETE for all the grant tables inside 'mysql'. Still, I could not exercise my grant options. Finally, the only solution was to get privilegs on the whole mysql database.
root> grant insert,select,delete,update on mysql.* to grantee;
Query OK, 0 rows affected (0.00 sec)

grantee> grant select on granted.* to delegated identified by 'happy';
Query OK, 0 rows affected (0.00 sec)
At last, I can grant something to someone.
But wait! Now that I can modify the 'mysql' database ...perhaps I could ...
grantee> update user set Select_priv ='Y',
 Insert_priv ='Y', Update_priv ='Y', Delete_priv ='Y',
 Create_priv ='Y', Drop_priv ='Y', Reload_priv ='Y',
 Shutdown_priv ='Y', Process_priv ='Y', File_priv ='Y',
 Grant_priv ='Y', References_priv ='Y', Index_priv ='Y',
 Alter_priv ='Y', Show_db_priv ='Y', Super_priv ='Y',
 Create_tmp_table_priv ='Y', Lock_tables_priv ='Y', Execute_priv ='Y',
 Repl_slave_priv ='Y', Repl_client_priv ='Y', Create_view_priv ='Y',
 Show_view_priv ='Y', Create_routine_priv ='Y', Alter_routine_priv ='Y',
 Create_user_priv ='Y', Event_priv ='Y', Trigger_priv ='Y',
 Create_tablespace_priv ='Y' where user = 'grantee';
This does not enhance my current grants, because I don't have the SUPER privilege (yet), but I can wait until the server restarts or until someone issues a 'flush privileges'. An then I will have full access to the server. Obviously, this situation is not what the DBA had in mind when the user 'grantee' was created.

Update The habit of always seeing the password set as integral part of the GRANT command has made me err on judgment.
As noted in one of the comments, the "grantee" user could have granted privileges to "delegated" without assigning a password. In this case,"grantee" does not need separate grants to the mysql database, which were apparently needed only if you wanted to set the password with the GRANT command.
All the above post is a miscalculation. The additional grants are not needed, provided that you don't include a password clause in your GRANT command.


Shlomi Noach said...

Absolutely crazy.
And... bent.

Fortunately, who wants to provide WITH GRANT OPTION? I never had the need.

Anonymous said...

Let us add there is a NO_AUTOCREATE_USER sql-mode to consider as well.

Peter Laursen

Anonymous said...


What about
"grant select on granted.* to delegated" ?

Giuseppe Maxia said...

You're right!
See my update

Kedar said...


First I Enjoyed Reading... Thanks Shlomi to start :)

DOC: "The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess."
So GRANT OPTION works as said - 'show grants' resulted displaying:
"Grants for myuser@%: GRANT SELECT, INSERT, UPDATE, DELETE ON `world`.* TO 'myuser'@'%' WITH GRANT OPTION"!

I do not find it surprising or incosistent initially but the steps there after for sure left me feel the oddity :)

In a way granting "GRANT OPTION" doesn't make sense for a restricted user!
As you explained providing INSERT / UPDATE PRIVILEGES on mysql.* database may turn worse; we can instead apply CREATE USER + RELOAD PRIVILEGES, will not allow the user to update his privileges and cross the root!

So, GRANT + CREATE USER + RELOAD PRIVILEGES => "grantee" can create "delegated" + flush privileges & other flush-* syntaxes too!!!

** BUT ** "RELOAD PRIVILEGE" brings power of: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload.
So, the solution I can think of having a "FLUSH PRIVILEGES PRIVILEGES" !! :)

About your "update mysql ... " query for grantee where you said "someone issues a flush privileges"; I was thinking if there is anyway for super user to receive grant changes as a confirmation!??
"CHANGE:: User grantee gets all privileges on *.* - Do you want to FLUSH PRIVILEGE?"

Can we admins do it on our own??? May be comparing current privileges in memory vs mysql.user table!!

There are tables in (Ver 5.1+) mysql.user (MYISAM) & information_schema.users_privilegs (MEMORY) .

select distinct grantee from information_schema.user_privileges order by grantee;
select concat('\'',user,'\'','@','\'',host,'\'') grantee from mysql.user order by grantee;

Now if we assign a new grant, I expected mysql.user table to hold values while information_schema.user_privileges table to be updated after we issue FLUSH PRIVILEGES and thus we can get the difference easily!!
** BUT ** it doesn't work like that. And hence we cannot verify the changes in privilege system as far as I know!!

About the update, I'm not able to grant even without password -

Login using newedge
GRANT SELECT ON test.* TO 'new1'@'localhost';
It's still giving "You are not allowed to create a user with GRANT"!


CRM tools said...

I am Very thank full the owner of this blog. Because of this blog is very informative for me.. And I ask u some thing You make more this type blog where we can get more knowledge.
Thanks you very hard work...