The syntax to revoke privileges on specific tables and views is shown below:

Syntax

REVOKE [ GRANT OPTION FOR ]
 {privilege[, privilege] , ...| ALL [ PRIVILEGES ]}
   ON table_name  
   FROM {user_identifier [ , user_identifier] , ...| PUBLIC }
     [ RESTRICT | CASCADE ] 
   GRANTED BY ANY_USER;

Parameters

GRANT OPTION FOR

Revokes the GRANT option for the privilege from the specified users. The actual privilege itself is not revoked. If specified with RESTRICT, and the privilege is passed on to other users, the REVOKE statement fails and generates an error. Otherwise, GRANT OPTION FOR implicitly revokes any privilege the user might have provided to other users.

privilege
The syntax for the privilege item is shown below:
{ SELECT | INSERT | DELETE | INDEX 
  | UPDATE [ ( column , column , ... ) ]
   | REFERENCES [ ( column , column , ... ) ]} ;
privilege [ , privilege] , ...| ALL [ PRIVILEGES ]

List of privileges to be revoked. Refer to the description in the GRANT statement. Revoking RESOURCE and DBA privileges can only be done by the administrator or a user with DBA privileges.

If more than one user grants access to the same table to a user, then all the grantors must perform a revoke for the user to lose access to the table.

Using the keyword ALL revokes all the privileges granted on the table or view.

FROM PUBLIC

Revokes the specified privileges on the table or view from any user with access to the system.

RESTRICT | CASCADE

Prompts SQL to check to see if the privilege being revoked was passed on to other users. If the original privilege included the clause WITH GRANT OPTION, then the REVOKE statement fails and generates an error. If the privilege was not passed on, the REVOKE statement succeeds.

If the REVOKE statement specifies CASCADE, revoking the access privileges from a user also revokes the privileges from all users who received the privilege from that user.

If the REVOKE statement does not specify either RESTRICT nor CASCADE, the behavior follows only CASCADE.

GRANTED BY ANY_USER

A DBA can use this phrase to revoke all or specific access privileges to a table for a specified user, even if the user was the creator of the table. This phrase is only applicable to users with DBA privileges.

Examples

Revoking privileges

In the following example, REVOKE is used on INSERT and DELETE privileges:

REVOKE INSERT ON customer FROM dbuser1;
REVOKE DELETE ON cust_view FROM dbuser2;

If the username specified in a GRANT DBA or GRANT RESOURCE operation does not already exist in the authorization tables, the GRANT statement creates a row in the SYSDBAUTH system table for the new username. This row is not deleted by a subsequent REVOKE operation.

Revoking privileges from a user associated with the domain of multi-tenant table

In the following example, a DBA revokes privileges from dbuser1 associated with domuser1 domain of a multi-tenant table:

REVOKE INSERT ON customer FROM dbuser1@domuser1;
REVOKE DELETE ON cust_view FROM dbuser2@domuser1;

The user names dbuser1 and dbuser2 must be associated with the domain name domuser1 in the multi-tenant table.

Revoking privileges for a specified user who has been granted privileges by ANY_USER.

In this example, all privileges granted by any_user on PUB.EMPLOYEE table are revoked from bsmith:

REVOKE ALL on PUB.EMPLOYEE FROM 'bsmith' GRANTED BY ANY_USER;

In this example, only SELECT and INSERT privileges granted by any_user on PUB.EMPLOYEE table are revoked from bsmith:

REVOKE SELECT, INSERT on PUB.EMPLOYEE FROM 'bsmith' GRANTED BY ANY_USER;

For more information on SQL database user authentication and REVOKE statement, see Learn about Identity Management.