Learning Mysql

how connections are verified without careful management, you can accidentally allow more privileges than you planned, or allow connections by users you didn’t want to grant access to. Fortunately, there area few tools available to help you explore access privileges.
The simplest method to check the privileges of a user is to use the SHOW GRANTS
state- ment. You can execute this statement to check the privileges of other users only if you have access to the mysql database however, you can always check your own privileges.
If you want to experiment, it’s best to login now as the root user or another user with sufficient global privileges. We explain the role of the mysql database in privilege management later in Managing Privileges with SQL.”
After logging into the monitor, you can check the current user’s privileges with:
mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ONTO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row inset sec)
MySQL reports the privileges as one or more
GRANT
statements. Not surprisingly,
'root'@'localhost'
has all privileges, including GRANT OPTION
Now let’s check the privileges of the user 'selina'@'localhost'
we created earlier in this chapter:
mysql> SHOW GRANTS FOR 'selina'@'localhost';
+-----------------------------------------------------------------------------+
| Grants for selina@localhost |
+-----------------------------------------------------------------------------+
| GRANT USAGE ONTO 'selina'@'localhost' |
| GRANT ALL PRIVILEGES ON music TO 'selina'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+
2 rows inset sec)
This syntax works on MySQL versions later than 4.1.2. The first
GRANT
statement is a default privilege that creates the user with no privileges (yes,
USAGE
implies no privileges. The second statement gives all privileges for the music database.
Let’s also check the user 'partmusic'@'localhost'
:
mysql> SHOW GRANTS FOR 'partmusic'@'localhost';
+-----------------------------------------------------------------------------+
| Grants for partmusic@localhost |
+-----------------------------------------------------------------------------+
| GRANT USAGE ONTO 'partmusic'@'localhost' IDENTIFIED BY |
| PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |
| GRANT ALL PRIVILEGES ON `music`.`album` TO 'partmusic'@'localhost' |
| GRANT ALL PRIVILEGES ON `music`.`artist` TO 'partmusic'@'localhost' |
| GRANT SELECT (track_id, time) ON `music`.`track` TO 'partmusic'@'localhost' |
+-----------------------------------------------------------------------------+
4 rows inset sec)

Download 4.24 Mb.

Share with your friends: