Home arrow MySQL arrow Page 4 - MySQL User Account Management

12.2.3.2 The REVOKE Statement - MySQL

Last week, we began our discussion of MySQL database security. This week, we continue that discussion with user account management. The second of several parts, this article is excerpted from chapter 12 of the MySQL 5.0 Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. MySQL User Account Management
  2. 12.2.2 The Grant Tables
  3. 12.2.3 Granting and Revoking Privileges
  4. 12.2.3.2 The REVOKE Statement
  5. 12.2.4 Changing Account Passwords
  6. 12.2.5 Specifying Resource Limits
By: Sams Publishing
Rating: starstarstarstarstar / 20
July 20, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: