Data seem sometimes to have their own life and will, and they refuse to behave as we wish.
Then, you need a firm hand to tame the wild data and turn them into quiet and obeying pets.

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

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.

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.

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.

Now,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!

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!!

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 -

GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'newedge'@'localhost' IDENTIFIED BY 'test' WITH GRANT OPTION; Login using newedge GRANT SELECT ON test.* TO 'new1'@'localhost'; It's still giving "You are not allowed to create a user with GRANT"!

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...