REVOKE statement

Revokes system and object-level privileges from users and roles.

Syntax

Revoke system privileges

REVOKE [ { EXERCISE | ADMIN } OPTION FOR ] privilege 
FROM grantee, ...

grantee : 
{ system-role | userid }

Revoke object-level privileges

REVOKE object-level-privilege[,...]
ON [ owner.]table-or-view
FROM userid[,...]

object-level-privilege : 
ALL [ PRIVILEGES ] 
| ALTER 
| DELETE 
| INSERT 
| LOAD 
| REFERENCES [ ( column-name[,...] ) ] 
| SELECT [ ( column-name[,...] ) ] 
| TRUNCATE 
| UPDATE [ ( column-name[,...] ) ]

Revoke CONNECT, INTEGRATED LOGIN, and KERBEROS LOGIN

REVOKE capability FROM userid[,...]

capability : 
CONNECT
| INTEGRATED LOGIN
| KERBEROS LOGIN

Revoke EXECUTE on a procedure

REVOKE EXECUTE
ON [ owner.]procedure-name[,...]
FROM userid[,...]

Revoke USAGE on a sequence

REVOKE USAGE ON SEQUENCE sequence-name[,...]
FROM userid[,...]

Parameters

{ EXERCISE | ADMIN } OPTION FOR clause
Specify the ADMIN OPTION FOR clause to revoke administration rights for the privilege, but leave exercise rights. Specify the EXERCISE OPTION FOR clause to revoke exercise rights for the privilege, but leave administration rights. If the clause is not specified, both rights are revoked.
REVOKE CONNECT
REVOKE CONNECT removes a user ID from a database, and also destroys any objects (tables, views, procedures, and so on) owned by that user. However, it is recommended that you use the DROP USER statement to remove users instead of the REVOKE CONNECT statement. System privileges granted by the user remain in effect; however, object-level privileges granted by the user are revoked.

You cannot execute a REVOKE CONNECT statement on a user if the user being dropped owns a table referenced by a view owned by another user.

When you are connected to the utility database, executing REVOKE CONNECT FROM DBA disables future connections to the utility database. No future connections can be made to the utility database unless you use a connection that existed before the REVOKE CONNECT was executed, or restart the database server.
REVOKE USAGE ON SEQUENCE
Specify this syntax to remove the privilege to evaluate the current or next value in a sequence.

Remarks

If a privilege that is being revoked was not granted to grantee, then the statement does nothing, and does not return an error.

REVOKE fails with an error if, as a consequence of executing the statement, the number of administrators for the system privilege being revoked would fall below the required minimum as set by the min_role_admins database option.

When you revoke an object-level privilege for a user who also had administrative rights for that privilege, then everyone who that user granted the privilege to also has their privilege revoked, as well as anyone that the grantees granted it to, and so on.

If you are revoking connection-related privileges from a user, the user must not be connected to the database.

When revoking a system privilege from the UPGRADE ROLE system privilege after an upgrade, you must use the special internal representation SYS_UPGRADE_ROLE_ROLE. For example, REVOKE privilege-name FROM SYS_UPGRADE_ROLE_ROLE;).

The REVOKE syntax related to authorities, permissions, and groups used in pre-16.0 versions of the software is still supported but deprecated.

Privileges

You must have administration rights for the system privileges that you are revoking.

If you are revoking object-level privileges, you must have one of the following:

Ownership of the object
Administrative rights on the object-level privilege for that object
MANAGE ANY OBJECT PRIVILEGE system privilege

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
REVOKE capability is not part of the standard. REVOKE object-level-privilege and REVOKE EXECUTE are Core Features of the ANSI/ISO SQL Standard. With REVOKE ALL (revoking all object-level privileges), the PRIVILEGES keyword is optional, while in the Standard it is mandatory.

REVOKE USAGE ON SEQUENCE is part of optional ANSI/ISO SQL Language Feature T176, "Sequence generator support".

Example

This example prevents user Dave from updating the Employees table.

REVOKE UPDATE ON GROUPO.Employees FROM Dave;

This example prevents a fictitious user-extended role called Finance from executing the procedure ShowCustomers.

REVOKE EXECUTE ON ShowCustomers FROM Finance;

This example drops user FranW from the database. This syntax is deprecated; consider using the DROP USER statement instead.

REVOKE CONNECT FROM FranW;

This example revokes database login privilege from a fictitious Kerberos user, pchin.

REVOKE KERBEROS LOGIN
FROM "pchin@MYREALM.COM";