Revoking a system privilege (SQL)

Revoke a system privilege from a user, user-extended role, or user-defined role.

Prerequisites

You must have administrative rights for the system privilege.

You cannot revoke privileges from the SYS role. You can revoke privileges from other system roles, provided that these privileges are not the default privileges for the system role.

Context and remarks

If a user has been granted exercise and administrative rights for a privilege, you can revoke the administration rights, or the exercise and administrative rights. You cannot revoke the exercise right (only), and leave the administrative right. To do this, revoke the system privilege entirely, and then regrant the system privilege with administrative rights only.

You cannot revoke system privileges from a compatibility role. However, you can migrate a compatibility role to a user-defined role, and then revoke privileges from the user-defined role.

Task

Connect to the database.
Execute a REVOKE statement similar to the following:
REVOKE privilege FROM grantee;

Results

The system privilege and/or the administrative right is revoked from the user or role.

Example

Suppose you have a user DaveW, who has been granted the CREATE ANY TABLE system privilege, with administrative rights. This example revokes the CREATE ANY TABLE system privilege from user DaveW:

REVOKE CREATE ANY TABLE FROM DaveW;

This example revokes the administrative rights (only) for the CREATE ANY TABLE system privilege from user DaveW:

REVOKE ADMIN OPTION FOR CREATE ANY TABLE FROM DaveW;

Discuss this page in DocCommentXchange.

Revoking a system privilege (SQL)

Task

See also

Example