Learning Mysql

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 remove the privileges in the same way they are granted. For example, to remove all privileges 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 aversion 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

Download 4.24 Mb.

Share with your friends: