Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
REVOKE statementUse the REVOKE statement to cancel access privileges or roles that are held by users, by roles, or by PUBLIC, or to cancel user security labels or exemptions from the rules of security policies.
Table-Level Privileges
When to Use REVOKE Before GRANT
Replacing PUBLIC with Specified Users

Replacing PUBLIC with Specified Users

If a table owner grants a privilege to PUBLIC, the owner cannot revoke the same privilege from any specific user. For example, assume PUBLIC has default Select privileges on your customer table. Suppose that you issue the following statement in an attempt to exclude ted from accessing your table:

REVOKE ALL ON customer FROM ted;

This statement results in ISAM error message 111, No record found, because the system catalog tables (syscolauth or systabauth) contain no table-level privilege entry for a user named ted. This REVOKE operation does not prevent ted from keeping all the table-level privileges given to PUBLIC on the customer table.

To restrict table-level privileges, first revoke the privileges with the PUBLIC keyword, then re-grant them to some appropriate list of users and roles. The following statements revoke the Index and Alter privileges from all users for the customer table, and then grant these privileges specifically to user mary:

REVOKE INDEX, ALTER ON customer FROM PUBLIC; 
GRANT INDEX, ALTER ON customer TO mary;