Revoke privileges

After you grant privileges, you can revoke them from a user.

Prerequisites

• The current user must have the privileges to be revoked (for example, test1 must have the SELECT privilege on table t1 to revoke the SELECT privilege on t1 from test2) and the GRANT OPTION privilege.

• When you revoke the ALL PRIVILEGES and GRANT OPTION privileges, the current user must have the global GRANT OPTION privilege or the UPDATE and DELETE privileges on the privilege table.

Considerations

• When you revoke multiple privileges from a user, separate the privilege types with commas (,).

• When you revoke privileges from multiple users, separate the usernames with commas (,).

• The revocation operation does not cascade. For example, if user test1 grants certain privileges to user test2, revoking privileges from test1 does not also revoke the corresponding privileges from test2.

Examples

• Revoke global privileges

  Revoke the INSERT and SELECT privileges on all tables in all databases from user test.

  REVOKE SELECT, INSERT ON *.* FROM 'test';

• Revoke database-level privileges

  Revoke the INSERT and SELECT privileges on all tables in database db1 from user test.

  REVOKE SELECT, INSERT ON db1 FROM 'test';

• Revoke table-level privileges

  Revoke the INSERT and SELECT privileges on table tb1_name in database db1 from user test.

  REVOKE SELECT, INSERT ON db1.tb1_name FROM 'test';

• Revoke column privileges in a table

  Revoke the SELECT privilege on column c1 and the INSERT privileges on columns c1 and c2 in table tb1_name in database db1 from user test.

  REVOKE SELECT(c1), INSERT (c1, c2) ON db1.tb1_name FROM test;

For more information about the REVOKE statement, see REVOKE.