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}
(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.
- 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.
-
- 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.
- 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).
-
-
(3) Privileges required at execution
A user who runs the REVOKE statement that revokes an access privilege must have the following privileges:
-
CONNECT privilege
-
The schema definition privilege or the access privilege with the grant option
(4) Rules
-
You can revoke only access privileges that you granted.
-
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.
-
You cannot revoke your own access privileges to objects that you own.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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
-
(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.
-