SHOW GRANTS FOR
The SHOW GRANTS FOR <user> statement, which I refer to simply as SHOW GRANTS, enables you to find out quickly the privileges that a given user has on a server — and the statements you would have to issue if you were to give that user those same privileges.
For example, one task I am confronted with frequently is giving a user access to a new database system as new servers are implemented. The user almost always needs the same access as they have on the existing system. Using the SHOW GRANTS statement, I can quickly find out their current privilege level and recreate it on the new server. The SHOW GRANTS statement can be a great timesaver (even if some IT pros still tell the user that the change takes at least 48 hours and use the extra time to play video games).
Figure 9-7 shows the grants for a few different users in my test database. As you can see, the dbuser account has privileges only from the localhost — and only on the inventory database. In addition, an error is shown in Figure 9-7 as well. This error occurs because the given user doesn't have privileges to connect from other hosts. To correct the error, I simply specify the host from which the user has privileges.
For more information on the SHOW STATUS and SHOW VARIABLES statements, see Appendix A or in Chapter 10.
192.168.1.75 - PuTTY
Type 'help;1 or 1\h1 for help. Type mysql> show grants for suehring;
Nc1 to clear the buffer.
Grants for suehring@%
GRANT AIL PRIVILEGES ON *.* TO 'suehring'@'%' WITH GRANT OPTION
1 row in set (0.00 sec) mysql> show grants for dbuser;
ERROR 1141: There is no such grant defined for user 'dbuser' on host mysql> show grants for dbuser®localhost;
Grants for dbuser®localhost
- GRANT USAGE ON * * TO 'dbuser'@'localhost' IDENTIFIED BY PASSWORD '7d67497627a 4539b' |
- GRANT SELECT, INSERT, UPDATE, DELETE ON inventory* TO 'dbuser'@'localhost'
Figure 9-7: The SHOW GRANTS statement is quite useful in determining what privileges a given user has on a database.
Post a comment