Databases Reference
In-Depth Information
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 are a 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 log in now as the
root
user or another user with
sufficient global privileges. We explain the role of the
mysql
database in privilege man-
agement later in “Managing Privileges with SQL.”
After logging in to the monitor, you can check the current user's privileges with:
mysql>
SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 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 ON *.* TO 'selina'@'localhost' |
| GRANT ALL PRIVILEGES ON `music`.* TO 'selina'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 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 privi-
leges!). 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 ON *.* TO '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 in set (0.00 sec)
