USE music;and pressing Enter. Try running a simple query:mysql> SELECT * FROM album

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| music |
| test |
+----------+
2 rows inset sec)
A user who doesn’t have any privileges fora 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 anew database:
mysql> CREATE DATABASE some_new_database;
ERROR 1044 (42000): Access denied for user 'allmusic'@'localhost' to database 'some_new_database'
We cant 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 command 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 indifferent 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

Download 4.24 Mb.

Share with your friends: