Databases Reference
In-Depth Information
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 a new 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 in set (0.00 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 in set (0.00 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'
