Syntax to revoke privileges on specific tables and views

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 ] ;

Parameters

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.

The syntax for the privilege item is shown below:

{ SELECT | INSERT | DELETE | INDEX
| UPDATE [ ( column , column , ... ) ]
| REFERENCES [ ( column , column , ... ) ]} ;

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.

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

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.

Examples

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.

For more information on SQL database user authentication and REVOKE statement, see OpenEdge Getting Started: Identity Management.