Managing Users and Privileges - Learning MySQL - page 301

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| music |
| test |
+----------+
2 rows in set (0.00 sec)
A user who doesn't have any privileges for a database can't see or use that database
(the exception to this is a user who has the global SHOW DATABASE privilege we discuss
later).
Let's try to create a new database:
mysql> CREATE DATABASE some_new_database;
ERROR 1044 (42000): Access denied for user 'allmusic'@'localhost' to database
'some_new_database'
We can't; when we were logged in as the MySQL root user, we never granted the
allmusic user the privilege to create new databases.
Let's create a second new user who can access only the artist table in the music database
( music.artist ). Quit the monitor (or start the monitor from another terminal or com-
mand prompt window) and connect again as the root user. Then, create this new user:
mysql> GRANT ALL ON music.artist TO 'partmusic'@'localhost'
-> IDENTIFIED BY ' the_password ';
Query OK, 0 rows affected (0.01 sec)
We've specified the artist table in the music database by using music.artist . If you
want to provide access to more than one table (but not all tables) in a database (or
tables in different databases), you have to type several GRANT statements. For example,
to add access to the album table to our newly created user, type:
mysql> GRANT ALL ON music.album TO 'partmusic'@'localhost';
Query OK, 0 rows affected (0.01 sec)
Since we're reusing the username and location 'partmusic'@'localhost' , there's no
need to provide a password in this second statement; the password was set when the
user was first created, and it isn't changed by the second statement.
You can also allow a user to access only specific columns in a table. For example, you
can allow the partmusic user to have only read ( SELECT ) access to the title and time
columns of the track table:
mysql> GRANT SELECT (track_id, time) ON music.track TO 'partmusic'@'localhost';
Query OK, 0 rows affected (0.01 sec)
The syntax is different from the previous examples: instead of specifying ALL privileges,
we've specified only SELECT , and we've listed the columns to which the privilege applies
— track_id and time —in parentheses after it. The remainder of the statement follows
the same syntax as the previous examples, including the music.track component that