MySQL User Account Management - 12.2.3.2 The REVOKE Statement
(Page 4 of 6 )
Use the REVOKE statement to revoke privileges from an account. Its syntax has the following sections:
The keyword REVOKE followed by the list of privileges to be revoked
An ON clause indicating the level at which privileges are to be revoked
A FROM clause that specifies the account name
Suppose that jim on the local host has SELECT, DELETE, INSERT, and UPDATE privileges on the world database, but you want to change the account so that he has SELECT access only. To do this, revoke those privileges that allow him to make changes:
REVOKE DELETE, INSERT, UPDATE ON world.* FROM
'jim'@'localhost';
To revoke the GRANT OPTION privilege from an account that has it, you must revoke it in a separate statement. For example, if jill has the ability to grant her privileges for the world database to other users, you can revoke that ability as follows:
REVOKE GRANT OPTION ON world.* FROM
'jill'@'localhost';
If you use REVOKE to remove all the privileges enabled by a record in the db, tables_priv, or columns_priv tables, REVOKE removes the record entirely. However, REVOKE does not remove an account's user table record, even if you revoke all privileges for the account. It's necessary to use DELETE to remove a user record. A later example demonstrates this.
To determine what REVOKE statements are needed to revoke an account's privileges, SHOW GRANTS might be helpful. Consider again the output from SHOW GRANTS for the jen@localhost account:
mysql> SHOW GRANTS FOR 'jen'@'myhost.example.com';
+-------------------------------------------------+
| Grants for jen@myhost.example.com |
+-------------------------------------------------+
| GRANT FILE ON *.* TO 'jen'@'myhost.example.com' |
| GRANT SELECT ON ´mydb´.* TO |
| 'jen'@'myhost.example.com' |
| GRANT UPDATE ON ´test´.´mytable´ TO |
| 'jen'@'myhost.example.com' |
+-------------------------------------------------+
This output indicates that the account has global, database-level, and table-level privileges. To remove these privileges, convert those GRANT statements to the following corresponding REVOKE statements. The privilege names, privilege levels, and account name must be the same as displayed by SHOW GRANTS:
mysql> REVOKE FILE ON *.* FROM
'jen'@'myhost.example.com';
mysql> REVOKE SELECT ON mydb.* FROM
'jen'@'myhost.example.com';
mysql> REVOKE UPDATE ON test.mytable FROM
'jen'@'myhost.example.com';
After issuing the REVOKE statements, SHOW GRANTS produces this result:
mysql> SHOW GRANTS FOR 'jen'@'myhost.example.com';
+-------------------------------------------------+
| jen@myhost.example.com; |
+-------------------------------------------------+
| Grants for jen@myhost.example.com |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'jen'@'myhost.example.com'|
+-------------------------------------------------+
This means that the account no longer has any privileges, although it does still exist and thus can be used to connect to the server. (In other words, the user table still contains a record for the account, but all the global privileges listed in the record are disabled.) To remove the last trace of the account, use a DELETE statement to remove the user table record, and then tell the server to reload the grant tables:
mysql> USE mysql;
mysql> DELETE FROM user WHERE User = 'jen'
AND Host = 'myhost.example.com';
mysql> FLUSH PRIVILEGES;
After that, the account no longer exists and cannot be used to connect to the server.
12.2.3.3 When Privilege Changes Take Effect
The effects of changes to the grant tables apply to existing client connections as follows:
Table and column privilege changes apply to all statements issued after the changes are made.
Database privilege changes apply with the next USE statement.
Changes to global privileges and passwords do not apply to connected clients. They apply the next time a client attempts to connect.
Next: 12.2.4 Changing Account Passwords >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 18 
