Databases Reference
In-Depth Information
Revoking Privileges
You can selectively revoke privileges with the
REVOKE
statement, which essentially has
the same syntax as
GRANT
. Consider a simple example, in which we remove the
SELECT
privilege from the user
'partmusic'@'localhost'
for the
time
column in the
track
table
in the
music
database. Here's the statement, which we've run when logged in as
'root'@'localhost'
:
mysql>
REVOKE SELECT (time) ON music.track FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.06 sec)
The format of
REVOKE
is straightforward when you understand
GRANT
. Following the
keyword
REVOKE
is one or more comma-separated privileges, and these are optionally
followed by column names, comma-separated in braces; this is the same as
GRANT
. The
ON
keyword has the same function as in
GRANT
and is followed by a database and table
name, both of which can be wildcards. The
FROM
keyword is followed by the user and
host from which the privileges are to be revoked, and the host can include wildcards.
Removing privileges using the basic syntax is laborious, since it requires that you re-
move the privileges in the same way they are granted. For example, to remove all priv-
ileges of
'partmusic'@'localhost'
, you would use the following steps:
mysql>
REVOKE SELECT (track_id) ON music.track FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>
REVOKE ALL PRIVILEGES ON music.artist FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>
REVOKE ALL PRIVILEGES ON music.album FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
You can remove all database-, table-, and column-level privileges of a user at once using
one of the following two methods. If you're using a version of MySQL earlier than 4.1.2,
use:
mysql>
REVOKE ALL PRIVILEGES FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql>
REVOKE GRANT OPTION FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
From MySQL version 4.1.2 onward, you can combine these into a single statement:
mysql>
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'partmusic'@'localhost';
Query OK, 0 rows affected (0.00 sec)
You could get a similar result using:
mysql>
REVOKE ALL PRIVILEGES ON *.* FROM 'allmusic'@'localhost';
but this would not revoke any
GRANT
OPTION
privileges that the user might have. To limit
the revocation to the
music
database, you would write
music.*
rather than
*.*
.
