Menu
How do I view the grants (access rights) for a given user in MySQL?
749
To determine which users have direct grant access to a table we'll use the DBA_TAB_PRIVS view: SELECT * FROM DBA_TAB_PRIVS; You can check the official documentation for more information about the columns returned from this query, but the critical columns are: GRANTEE is the name of the user with granted access.
Information about account privileges is stored in the grant tables in the mysql system database. For a description of the structure and contents of these tables, see Section 6.2. 3, “Grant Tables”.
The WITH GRANT OPTION clause gives the user the ability to give to other users any privileges the user has at the specified privilege level. To grant the GRANT OPTION privilege to an account without otherwise changing its privileges, do this: GRANT USAGE ON *.
The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources.
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").
120
answered Oct 19 '22 10:10
mysql> show grants for 'user'@'host'
You could try this:
SELECT GRANTEE, PRIVILEGE_TYPE FROM information_schema.user_privileges;
SELECT User,Host,Db FROM mysql.db;
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.
23
If you're already running a web server with PHP then phpMyAdmin is a fairly friendly administrative tool.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
