How do I view the grants (access rights) for a given user in MySQL?
A:
If you're already running a web server with PHP then phpMyAdmin is a fairly friendly administrative tool.
Kevin ORourke
2008-09-24 15:03:08
+1
A:
You might want to check out mk-show-grants from Maatkit, which will output the current set of grants for all users in a canonical form, making version control or replication more straightforward.
Jon Topper
2008-09-24 17:53:53
+5
A:
An alternative method for recent versions of MySQL is:
select * from information_schema.user_privileges where grantee like "'user'%";
The possible advantage with this format is the increased flexibility to check "user's" grants from any host (assuming consistent user names) or to check for specific privileges with additional conditions (eg, privilege_type = 'delete').
This version is probably better suited to use within a script while the "show grants" syntax is better for interactive sessions (more "human readable").
igelkott
2008-09-24 20:04:14
+1 : This allows checking against tables and particular privileges as well, which is useful in some cases (rather than just a textual description as returned by SHOW GRANTS).
Legooolas
2010-03-24 11:43:11
This is also very helpful for debugging improperly created grants. It can be difficult to get what you need from SHOW GRANTS if your granted for the wrong user or host;
Bradley Harris
2010-08-03 18:39:13
A:
Can i use syntax like "Show grants on db_name.user_name for user;" to check, for example, INSERT-privileges for current user? Whot's alternative way? Create transaction to insert and Rollback? (i think, it's bad way)
NiCketT
2010-10-26 03:27:49