3.24.3 Revoking role usage privileges

(1) Specification format

REVOKE-statement ::= REVOKE role-name [,role-name]...
                       FROM authorization-identifier [,authorization-identifier]...
                       [drop-behavior]
 
  drop-behavior ::= {CASCADE | RESTRICT}

To Page Top

(2) Explanation of specification format

● role-name [,role-name]...

Specifies the role name. The role usage privilege of the specified role is revoked.

Note the following rules:

• A maximum of 128 role names can be specified.

• For rules on specifying role names, see 6.1.4 Specifying names.

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

Specifies the authorization identifier to revoke role usage privileges from.

Note the following rules:

• Specify the authorization identifier of an HADB user (user identifier) as the authorization identifier.

• A maximum of 128 authorization identifiers can be specified.

• For rules on specifying authorization identifiers, see 6.1.4 Specifying names.

● drop-behavior

drop-behavior ::= {CASCADE | RESTRICT}

This specification takes effect only in either of the following cases:

• When the revocation of role usage privileges causes SELECT privileges granted to HADB users to be revoked.

• When the revocation of role usage privileges causes REFERENCES privileges granted to HADB users to be revoked.

If the drop behavior is omitted, CASCADE is assumed.

CASCADE:

Specify this when revoking role usage privileges even if either of the following conditions is met:

• Viewed tables defined by using the SELECT privileges to be revoked exist.

  In this case, the corresponding viewed tables will be invalidated. Additionally, viewed tables dependent on the invalidated viewed tables will also be invalidated.

• Referential constraints defined by using the REFERENCES privileges to be revoked exist.

  In this case, the corresponding referential constraints will be deleted.

RESTRICT:

Specify this to cause the REVOKE statement to result in an error if either of the following conditions is met:

• Viewed tables defined by using the SELECT privileges to be revoked exist.

• Referential constraints defined by using the REFERENCES privileges to be revoked exist.

To Page Top

(3) Privileges required at execution

To execute a REVOKE statement to revoke role usage privileges, DBA privileges and CONNECT privileges are required.

To Page Top

(4) Rules

1. You can revoke only the role usage privileges that you have granted.

2. You cannot revoke role usage privileges that you own.

3. If multiple role names or multiple authorization identifiers are specified in the REVOKE statement and an error occurs during its execution, the revocation of privileges from all authorization identifiers is invalidated. Role usage privileges will not be revoked from only some of the authorization identifiers.

4. If the revocation of role usage privileges changes the access privileges of an HADB user who is currently connected to the HADB server, the changed access privileges take effect at the following timing:

   • The next time the HADB user executes a transaction

To Page Top

(5) Examples

Example

Revoke the role usage privilege of role ROLE01 granted to HADB user ADBUSER01.

REVOKE "ROLE01" FROM "ADBUSER01"

To Page Top