3.17.1 Revoking user privileges, schema operation privileges, and audit privileges

(1) Specification format

REVOKE-statement::=REVOKE privilege[,privilege]...
                 FROM authorization-identifier[,authorization-identifier]... [drop-behavior]
 
  privilege::={user-privilege|schema-operation-privilege|audit-privilege}
    user-privilege::={DBA|CONNECT}
    schema-operation-privilege::=SCHEMA
    audit-privilege::={AUDIT ADMIN|AUDIT VIEWER}
 
  drop-behavior::={CASCADE|RESTRICT}

To Page Top

(2) Explanation of specification format

privilege[,privilege]...

privilege::={user-privilege|schema-operation-privilege|audit-privilege}

Specifies the privilege to be revoked. You cannot specify the same privilege more than once.

user-privilege ::= {DBA | CONNECT}

Specify this to revoke user privileges.

• DBA

  Specify this to revoke the DBA privilege.

• CONNECT

  Specify this to revoke the CONNECT privilege.

schema-operation-privilege ::= SCHEMA

Specify this to revoke schema operation privileges.

• SCHEMA

  Specify this to revoke the schema definition privilege.

audit-privilege ::= {AUDIT ADMIN|AUDIT VIEWER}

Specify this to revoke an audit privilege (audit admin privilege or audit viewer privilege).

• AUDIT ADMIN

  Specify this to revoke the audit admin privilege.

• AUDIT VIEWER

  Specify this to revoke the audit viewer privilege.

FROM authorization-identifier[,authorization-identifier]...

Specifies the authorization identifiers of the HADB user(s) whose privileges are to be revoked. A maximum of 128 authorization identifiers can be specified.

Note the following rules for specifying an authorization identifier:

• If you want to use lowercase letters, enclose the authorization identifier in double quotation marks ("). When not enclosed in double quotation marks, lowercase letters will be treated as uppercase.

  Example: REVOKE DBA FROM adbuser01

  In this case, the authorization identifier is treated as ADBUSER01.

• Because an authorization identifier is specified as a name, we recommend that you enclose it in double quotation marks (").

For details about the rules for specifying an authorization identifier, see 6.1.4 Specifying names.

• drop-behavior

drop-behavior ::= {CASCADE | RESTRICT}

This specification only applies when revoking the schema definition privilege.

Specify whether to revoke the schema definition privilege if the HADB user who wants to revoke the schema definition privilege owns the schema. The following table describes the possible specifications for drop-behavior.

Specification of drop-behavior	Description	Handling of viewed tables in other schemata	Handling of foreign keys in other schemata
If drop-behavior is omitted	The schema definition privilege is revoked even if the targeted HADB user owns a schema. At this time, the schema owned by the targeted HADB user is also deleted.	If viewed tables defined in other schemata depend on the tables that will be deleted by the REVOKE statement, those dependent viewed tables are invalidated.	If the tables that will be deleted by the REVOKE statement are referenced by foreign keys defined in other schemata, those foreign keys are also deleted.
If CASCADE is specified		If viewed tables defined in other schemata depend on the tables that will be deleted by the REVOKE statement, those dependent viewed tables are also deleted.
If RESTRICT is specified	If the targeted HADB user owns the schema, the REVOKE statement results in an error.	The viewed tables in other schemata are not affected because the REVOKE statement results in an error.	The foreign keys in other schemata are not affected because the REVOKE statement results in an error.

To Page Top

(3) Privileges required at execution

• To execute a REVOKE statement that revokes a user privilege or schema operation privilege:

  The DBA privilege and the CONNECT privilege are required.

• To execute a REVOKE statement that revokes an audit privilege:

  The audit admin privilege and the CONNECT privilege are required.

To Page Top

(4) Rules

1. You cannot revoke the CONNECT privilege of the HADB user whose authorization identifier is currently connected to the HADB server.

2. You cannot revoke the DBA and CONNECT privileges that have been granted to yourself. You can revoke the schema definition privilege that has been granted to yourself.

3. If an error occurs in the execution of the REVOKE statement when multiple authorization identifiers are specified, the operation is cancelled for all of the targeted HADB users.

4. You cannot revoke the CONNECT privilege and the schema definition privilege of an HADB user who has an audit privilege.

5. An HADB user having the audit admin privilege can revoke the following privileges:

   • Other HADB users' audit admin privilege or audit viewer privilege

   • The HADB user's own audit admin privilege or audit viewer privilege

6. Audit privileges can be revoked if the audit trail facility is enabled.

   However, as an HADB user, you can revoke the audit admin privilege even when the audit trail facility is disabled if all of the following conditions are met:

   • There are no HADB users who have the audit viewer privilege.

   • You are the only HADB user who has the audit admin privilege.

7. If the audit trail facility is enabled and there is only one HADB user having both the audit admin privilege and the CONNECT privilege, the HADB user's audit admin privilege cannot be revoked.

To Page Top

(5) Examples

Example 1

Revoke the DBA privilege, CONNECT privilege, and schema definition privilege of HADB user ADBUSER01.

REVOKE DBA,CONNECT,SCHEMA FROM "ADBUSER01" CASCADE

Example 2

Revoke the CONNECT privilege and the schema definition privilege of HADB users ADBUSER02 and ADBUSER03. However, if HADB user ADBUSER02 or ADBUSER03 owns a schema, make the REVOKE statement result in an error.

REVOKE CONNECT,SCHEMA FROM "ADBUSER02","ADBUSER03" RESTRICT

For example, assume that ADBUSER02 owns a schema and ADBUSER03 does not. In this case, if the preceding REVOKE statement is executed, the processing of the REVOKE statement for both ADBUSER02 and ADBUSER03 results in an error.

Example 3

Revoke the audit admin privilege of HADB user ADBAUDITADMIN.

REVOKE AUDIT ADMIN FROM "ADBAUDITADMIN"

Example 4

Revoke the audit viewer privilege of HADB user ADBAUDITOR.

REVOKE AUDIT VIEWER FROM "ADBAUDITOR"

To Page Top