Revoking privileges

The REVOKE statement can be used for a wide variety of purposes. It can revoke a single user's access to a single column or it can revoke the public's privilege to access an entire database.

Privileges are revoked in the same manner in which they are granted—database-wide or table-specific.

The syntax for using the REVOKE statement to revoke database-wide privileges is:

REVOKE { RESOURCE , DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT}
FROM {user_identifier[ , user_identifier] , ...}
[ RESTRICT | CASCADE ] [ GRANTED BY ANY_USER ];

Identifies a username. For a tenant user, you must mention the fully qualified user name, username@domain_name, to revoke access from a user.

Note: Only the user who granted an audit-related privilege can revoke it. CASCADE is not supported for the revocation of audit-related privileges.

The syntax for using the REVOKE statement to revoke table-specific privileges is:

REVOKE [ GRANT OPTION FOR ]{privilege[, privilege], ...
| ALL[ PRIVILEGES ]} ON table_nameFROM {username[,username], ...
| PUBLIC }[RESTRICT | CASCADE];
where privilege is:
{ EXECUTE | SELECT | INSERT | DELETE | INDEX |UPDATE [(COLUMN, COLUMN, ...)]
| REFERENCES [(COLUMN, COLUMN, ...)]};

Examples: REVOKE statement

The REVOKE statement can be used to remit the privileges previously granted to 'GPS', as shown in the following example.

REVOKE UPDATE
ON Item (ItemNum, ItemName, CatDescription)
FROM "GPS";

The following example demonstrates how 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;

In the above example, the user names dbuser1 and dbuser2 must be associated with the domain name domuser1 in the multi-tenant table.

If the REVOKE statement specifies RESTRICT, SQL checks to see if the privilege being revoked was passed on to other users. This is possible only if the original privilege included the WITH GRANT OPTION clause. If so, 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 specifies neither RESTRICT nor CASCADE, the behavior is the same as for CASCADE.

For detailed information on the REVOKE statement, see OpenEdge Data Management: SQL Reference.