Revoke a role from a user or role

You can use the REVOKE statement to revoke a role from a user or another role.

Currently, you can revoke one role from multiple users or roles, revoke multiple roles from one user or role, or revoke multiple roles from multiple users or roles.

Prerequisites

To revoke a role, the current user must have the role to be revoked and the ADMIN OPTION privilege. For more information about how to view the roles of the current user or role, see View roles.

Considerations

When you execute the REVOKE statement to revoke a role from a user, the change does not take effect immediately for the currently logged-in user.

Examples

Revoke a role from a user

• Revoke the employee role from the test1 user.

  REVOKE employee FROM test1;

• Revoke the employee role from the test1 and test2 users.

  REVOKE employee FROM test1,test2 IGNORE UNKNOWN USER;

  In this example, the IGNORE UNKNOWN USER clause is used to check whether the users to be revoked exist. If you do not specify the IGNORE UNKNOWN USER clause, an error is returned if either the test1 or test2 user does not exist. If you specify the IGNORE UNKNOWN USER clause, a warning is returned.

  Regardless of whether you specify the IGNORE UNKNOWN USER clause, the operation fails if either the test1 or test2 user does not exist.

• Revoke the employee and developer roles from the test1 user.

  REVOKE IF EXISTS employee,developer FROM test1 IGNORE UNKNOWN USER;

  In this example, the IF EXISTS clause is used to check whether the roles to be revoked exist. If you do not specify the IF EXISTS clause, an error is returned if either the employee or developer role does not exist. If you specify the IF NOT EXISTS clause, a warning is returned.

  Regardless of whether you specify the IF EXISTS clause, the operation fails if either the employee or developer role does not exist.

Revoke a role from a role

• Revoke the employee role from the role1 role.

  REVOKE employee FROM role1;

• Revoke the employee role from the role1 and role2 roles.

  REVOKE IF EXISTS employee FROM role1,role2;

References

• Revoke privileges from a role

• For more information about the REVOKE statement, see REVOKE.