Hitachi

Hitachi Advanced Database SQL Reference

3.17.2 Revoking access privileges

Revoke access privileges that were granted to an HADB user.

Organization of this subsection

(1) Specification format

REVOKE-statement ::= REVOKE [GRANT OPTION FOR] access-privilege ON object-name
                 FROM privilege-grantee [drop-behavior]
 
  access-privilege ::= {ALL [PRIVILEGES]|operation[,operation]...}
    operation ::= {SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES
              |IMPORT TABLE|REBUILD INDEX|GET COSTINFO|EXPORT TABLE
              |MERGE CHUNK|CHANGE CHUNK COMMENT|CHANGE CHUNK STATUS
              |ARCHIVE CHUNK|UNARCHIVE CHUNK}
 
  object-name ::= {[TABLE] table-name|ALL TABLES}
 
  privilege-grantee ::= {authorization-identifier[,authorization-identifier]...|PUBLIC}
 
  drop-behavior ::= {CASCADE|RESTRICT}

To Page Top

(2) Explanation of specification format

● GRANT OPTION FOR

Specify this keyword to revoke only the grant option of an access privilege. If the REVOKE statement is run with this keyword specified, the access privilege itself is not revoked. Only the grant option of the access privilege is revoked.

Example:

REVOKE GRANT OPTION FOR SELECT ON "X"."T1" FROM "ADBUSER01"

If the preceding REVOKE statement is run, HADB user ADBUSER01 has only the grant option of the SELECT privilege for table X.T1 revoked. HADB user ADBUSER01 does not have the SELECT privilege for table X.T1 revoked.

[Figure] access-privilege
access-privilege ::= {ALL [PRIVILEGES] | operation[,operation]...}

Specifies the type of access privilege to be revoked.

ALL [PRIVILEGES]:

Specify this to revoke all access privileges.

Important

The privileges that you can revoke by running the REVOKE statement with ALL PRIVILEGES specified are only the privileges that you granted. The access privileges granted by other HADB users are not revoked.

Example:

Assume that HADB user ADBUSER01 has the following access privileges for table X.T1:

  • SELECT and UPDATE privileges granted by HADB user ADBUSER02

  • INSERT and DELETE privileges granted by HADB user ADBUSER03

If HADB user ADBUSER02 runs the following REVOKE statement, only the SELECT and UPDATE privileges are revoked:

REVOKE ALL PRIVILEGES ON "X"."T1" FROM "ADBUSER01"
Note

If you run the REVOKE statement with ALL PRIVILEGES specified when you have no access privilege with the grant option for the target object, the statement will result in an error.

operation[,operation]...:
operation ::= {SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES
           | IMPORT TABLE | REBUILD INDEX | GET COSTINFO | EXPORT TABLE
           | MERGE CHUNK | CHANGE CHUNK COMMENT | CHANGE CHUNK STATUS
           | ARCHIVE CHUNK | UNARCHIVE CHUNK}

Specifies the type of access privilege to be revoked. You cannot specify the same operation more than once.

  • SELECT

    Specify this to revoke the SELECT privilege.

  • INSERT

    Specify this to revoke the INSERT privilege.

  • UPDATE

    Specify this to revoke the UPDATE privilege.

  • DELETE

    Specify this to revoke the DELETE privilege.

  • TRUNCATE

    Specify this to revoke the TRUNCATE privilege.

  • REFERENCES

    Specify this to revoke the REFERENCES privilege.

  • IMPORT TABLE

    Specify this to revoke the IMPORT TABLE privilege.

  • REBUILD INDEX

    Specify this to revoke the REBUILD INDEX privilege.

  • GET COSTINFO

    Specify this to revoke the GET COSTINFO privilege.

  • EXPORT TABLE

    Specify this to revoke the EXPORT TABLE privilege.

  • MERGE CHUNK

    Specify this to revoke the MERGE CHUNK privilege.

  • CHANGE CHUNK COMMENT

    Specify this to revoke the CHANGE CHUNK COMMENT privilege.

  • CHANGE CHUNK STATUS

    Specify this to revoke the CHANGE CHUNK STATUS privilege.

  • ARCHIVE CHUNK

    Specify this to revoke the ARCHIVE CHUNK privilege.

  • UNARCHIVE CHUNK

    Specify this to revoke the UNARCHIVE CHUNK privilege.

[Figure] ON object-name
object-name ::= {[TABLE] table-name | ALL TABLES}

Specifies the object to which access privileges are to be revoked.

Here, object refers to a schema object.

[TABLE] table-name:

Revoke access privileges to the table specified here. For rules on specifying a table name, see (2) Table name specification format in 6.1.5 Qualifying a name.

Note that you cannot specify the table name of a viewed table that has been invalidated.

ALL TABLES:

Revoke access privileges to all the base tables in the schema of the executing user. In this context, executing user means the HADB user executing the REVOKE statement.

If the HADB user executing the REVOKE statement has not defined a schema, or no base tables are defined in the schema, the REVOKE statement terminates normally without revoking any access privileges.

[Figure] FROM privilege-grantee
privilege-grantee ::= {authorization-identifier[,authorization-identifier]...| PUBLIC}

Specifies the HADB users whose access privileges are to be revoked.

authorization-identifier[,authorization-identifier]...:

Specifies the authorization identifiers of the HADB user(s) whose access 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.

  • 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.

PUBLIC:

Specify this to revoke access privileges that were authorized by the GRANT statement with PUBLIC specified.

Example:

REVOKE SELECT,IMPORT TABLE ON "T1" FROM PUBLIC

Executing the above REVOKE statement revokes the following access privileges that were authorized by a GRANT statement with PUBLIC specified:

  • The SELECT privilege on table T1

  • The IMPORT TABLE privilege on table T1

Note

The PUBLIC keyword can be thought of as an implicit, system-generated user who represents all HADB users.

• drop-behavior
drop-behavior ::= {CASCADE | RESTRICT}

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

  • When the SELECT or REFERENCES privilege is to be revoked

  • When an access privilege for which dependent privileges exist is to be revoked

If specification of the drop behavior is omitted, the system assumes that CASCADE is specified.

CASCADE:

Specify this keyword if you want to revoke an access privilege even when any of the following conditions are met:

  • There is a viewed table that was defined using the SELECT privilege to be revoked.

    In this case, the viewed table is invalidated. The viewed tables that depend on the viewed table to be invalidated are also invalidated.

  • There is a referential constraint that was defined using the REFERENCES privilege to be revoked.

    In this case, the referential constraint is deleted.

  • There are dependent privileges for the access privilege to be revoked (including when only the grant option is to be revoked).

    In this case, the dependent privileges are revoked. If there are viewed tables or referential constraints that use the dependent privileges, the viewed tables are invalidated and the referential constraints are deleted.

RESTRICT:

Specify this keyword if you want the REVOKE statement to result in an error in any of the following cases:

  • There is a viewed table that was defined using the SELECT privilege to be revoked.

  • There is a referential constraint that was defined using the REFERENCES privilege to be revoked.

  • There are dependent privileges for the access privilege to be revoked (including when only the grant option is to be revoked).

To Page Top

(3) Privileges required at execution

A user who runs the REVOKE statement that revokes an access privilege must have the following privileges:

To Page Top

(4) Rules

  1. You can revoke only access privileges that you granted.

  2. If you attempt to revoke an access privilege by running the REVOKE statement when you do not have the access privilege with the grant option, the statement will result in an error.

  3. You cannot revoke your own access privileges to objects that you own.

  4. If an error occurs during execution of the REVOKE statement that you ran by specifying multiple authorization identifiers as the privilege grantee, the revoking of privileges for all HADB users will become invalid.

  5. If an access privilege for an underlying table is revoked, the access privilege for the viewed tables that depend on the underlying table is also revoked. (Consequently, propagation of access privileges occurs.)

    For example, assume that HADB user A has defined viewed table A.V1 by using table X.T1 as the underlying table, and viewed table A.V2 by using viewed table A.V1 as the underlying table. In this case, if the INSERT privilege for table X.T1 is revoked, the INSERT privileges for viewed tables A.V1 and A.V2 are also revoked.

    For invalidated viewed tables, however, the revoking of access privileges for those viewed tables is not propagated.

  6. If the SELECT privilege for an underlying table is revoked, all viewed tables that depend on the underlying table are invalidated.

    Example:

    Assume that HADB user A has the SELECT privilege for table X.T1, and has defined viewed table A.V1 by using table X.T1 as the underlying table. Also assume that the user has defined viewed table A.V2 by using viewed table A.V1 as the underlying table, and viewed table A.V3 by using viewed table A.V2 as the underlying table.

    If the SELECT privilege for table X.T1 is revoked, viewed tables A.V1, A.V2, and A.V3, which depend on table X.T1, are invalidated.

  7. If the REFERENCES privilege for a table is revoked, the referential constraints defined by using the REFERENCES privilege are deleted. For example, if the REFERENCES privilege for table X.T1 owned by HADB user A is revoked, the referential constraint that HADB user A defined by using table X.T1 as the referenced table is deleted.

  8. The following describes the rules for revoking access privileges when an access privilege for the same table is granted by multiple HADB users or permitted with the PUBLIC specification. Note that the following description is an example for the SELECT privilege.

    Example:

    Assume that the following SQL statements are run:

    GRANT SELECT ON "ADBUSER01"."T1" TO "ADBUSER03"     ...1  <= Run by HADB user ADBUSER01
GRANT SELECT ON "ADBUSER01"."T1" TO "ADBUSER03"     ...2  <= Run by HADB user ADBUSER02
GRANT SELECT ON "ADBUSER01"."T1" TO PUBLIC          ...3  <= Run by HADB user ADBUSER01
REVOKE SELECT ON "ADBUSER01"."T1" FROM "ADBUSER03"  ...4  <= Run by HADB user ADBUSER01
REVOKE SELECT ON "ADBUSER01"."T1" FROM "ADBUSER03"  ...5  <= Run by HADB user ADBUSER02
REVOKE SELECT ON "ADBUSER01"."T1" FROM PUBLIC       ...6  <= Run by HADB user ADBUSER01

    [Explanation]

    • In steps 1 to 3, the GRANT statements are run to grant (or permit) HADB user ADBUSER03 the SELECT privilege for table ADBUSER01.T1 (table T1, hereafter).

    • When the REVOKE statement in step 4 is run, only the SELECT privilege granted in step 1 is revoked. The SELECT privilege granted in step 2 and the SELECT privilege permitted in step 3 are not revoked.

    • Then, when the REVOKE statement is run in step 5, only the SELECT privilege granted in step 2 is revoked. The SELECT privilege permitted in step 3 is not revoked.

    • Then, when the REVOKE statement is run in step 6, the SELECT privilege permitted in step 3 is revoked.

      At this time, all SELECT privileges for table T1 are revoked. Therefore, if HADB user ADBUSER03 has defined a viewed table by using table T1 as the underlying table, the viewed table is invalidated at this time.

    Note that the rules for revoking the SELECT privilege described in the preceding example also apply to the revoking of the REFERENCES privilege. Therefore, the referential constraints are deleted when all REFERENCES privileges for table T1 are revoked.

  9. If you change the access privileges of an HADB user who is currently connected to the HADB server, the changed access privileges take effect at the following time:

    • The next time the HADB user executes a transaction

To Page Top

(5) Examples

Example 1

Revoke the SELECT and INSERT privileges on table T1 of HADB user ADBUSER01.

REVOKE SELECT,INSERT ON "T1" FROM "ADBUSER01"

If ADBUSER01 has defined a viewed table by using table T1 as the underlying table, the viewed table is invalidated when the preceding REVOKE statement is run. The viewed tables that depend on the viewed table to be invalidated are also invalidated.

Example 2

Revoke all the access privileges of HADB users ADBUSER02 and ADBUSER03 to table T1.

REVOKE ALL PRIVILEGES ON "T1" FROM "ADBUSER02","ADBUSER03" RESTRICT

Because RESTRICT is specified, while ADBUSER02 or ADBUSER03 is performing any of the following operations, the REVOKE statement results in an error:

  • Defined a viewed table by using table T1 as the underlying table

  • Defined a referential constraint whose referenced table is T1

Example 3

In the following example, HADB user ADBUSER01 has only the grant option of the SELECT privilege for table X.T1 revoked.

REVOKE GRANT OPTION FOR SELECT ON "X"."T1" FROM "ADBUSER01"

When the preceding REVOKE statement is run, HADB user ADBUSER01 does not have the SELECT privilege for table X.T1 revoked. Therefore, even if ADBUSER01 has defined a viewed table by using table X.T1 as the underlying table, the viewed table is not invalidated. However, in cases such as the following, the viewed tables defined by HADB users other than ADBUSER01 are invalidated.

  • If ADBUSER01 has granted the SELECT privilege for table X.T1 to another HADB user (ADBUSER02, for example), ADBUSER02 has the SELECT privilege for table X.T1 revoked. Therefore, if ADBUSER02 has defined viewed table ADBUSER02.V1 by using table X.T1 as the underlying table, viewed table ADBUSER02.V1 is invalidated.

  • Assume that ADBUSER01 has defined viewed table ADBUSER01.V1 by using table X.T1 as the underlying table. If the SELECT privilege for viewed table ADBUSER01.V1 has been granted to another HADB user (ADBUSER02, for example), ADBUSER02 has the SELECT privilege for viewed table ADBUSER01.V1 revoked. Therefore, if ADBUSER02 has defined viewed table ADBUSER02.V2 by using viewed table ADBUSER01.V1 as the underlying table, viewed table ADBUSER02.V2 is invalidated.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.