Creating and Using New Users | 301

specifies where the columns t
rack-id and time are located. Note that you can’t grant all privileges at the column level you must specifically list them.
Before you experiment with your new user, let’s summarize what you’ve done. You’ve created anew user, partmusic
, set this user’s password to
the_password
, and allowed access to the database server from only the machine on which the server is installed,
the localhost
. The partmusic user has access to the music database and has all privileges for the album and artist tables. In addition, this user can run
SELECT
statements that retrieve values from the track_id and time columns in the track table.
Let’s test what our new user can do. Start the monitor as the new user by supplying the appropriate user and password parameters mysql --user=partmusic --password=the_password
and connect to the music database:
mysql> USE music;
Now, check what tables this user can access:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| track |
+-----------------+
3 rows inset sec)
You can see three of the four tables in the database, but since you (the partmusic user)
don’t have privileges for the played table, you can’t see it. You do have privileges to do anything you want to the album and artist tables, so try this out:
mysql> INSERT INTO artist VALUES (7, "The Jimi Hendrix Experience");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT album_name FROM album WHERE album_id=4;
+------------------------------------------+
| album_name |
+------------------------------------------+
| Retro - New Order / Bobby Gillespie LIVE |
+------------------------------------------+
1 row inset sec)
Feel free to test your access to these tables further you’ll be able to insert, delete, update,
and retrieve all data.
Now, let’s test our limited access to the track table. First, we’ll try to retrieve the values in all columns:
mysql> SELECT * FROM track;
ERROR 1143 (42000): select command denied to user:
'partmusic'@'localhost' for column 'track_name' in table 'track'

Download 4.24 Mb.

Share with your friends: