Databases Reference
In-Depth Information
As expected, MySQL complains that you don't have privileges to retrieve values from
the columns other than
track_id
and
time
; note that MySQL stops on its first error and
doesn't report all the columns you can't access. If you now try to retrieve values for
columns you can access, it works as expected:
mysql>
SELECT time FROM TRACK LIMIT 3;
+------+
| time |
+------+
| 8.10 |
| 5.27 |
| 8.66 |
+------+
3 rows in set (0.00 sec)
Notice that, unlike databases and tables, you can see the details of all columns in a table
even if you don't have access to them:
mysql>
DESCRIBE track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec
Privileges
So far, we've shown you how to add new users and grant privileges for databases, tables,
and columns. In this section, we discuss the privileges in more detail and explain which
ones are used at the global, database, table, and column level. Then we discuss how
the different privilege levels interact.
You can see a list of all available privileges by running the
SHOW PRIVILEGES
command
in the MySQL monitor; Table 9-1 lists some of the more important of these. Each row
shows a privilege, followed by a description, and then a list of the four levels at which
the privilege can be granted. For example, the second row shows the
ALTER
privilege
that controls whether the
ALTER TABLE
statement can be used, and shows that it can be
controlled at the global, database, and table levels:
Global level
You can use
ON *.*
in a
GRANT
statement to grant a user a particular privilege across
all databases on the server.
Database level
You can use, for example,
music.*
to grant a privilege for one or more databases.
