3.16.2 Granting access privileges

Grant access privileges to an HADB user.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples

(1) Specification format

GRANT-statement ::= GRANT access-privilege ON object-name TO privilege-grantee [WITH GRANT OPTION]
 
  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}

To Page Top

(2) Explanation of specification format

access-privilege

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

Specify the type of access privilege to be granted.

ALL [PRIVILEGES]:

Specify this to grant all access privileges.

Note that the access privileges that are granted if this clause is specified are all the access privileges that are supported at the time when the GRANT statement is run. If other access privileges are additionally supported as a result of version upgrade after the GRANT statement is run, those access privileges will not be granted automatically.

Important: If you run the GRANT statement with ALL PRIVILEGES specified when you have only some access privileges with the grant option, you cannot grant all types of access privileges. In this case, only the access privileges with the grant option are granted to the privilege grantee. For example, when you have the grant option for the INSERT privilege only, if you run the GRANT statement with ALL PRIVILEGES specified, only the INSERT privilege is granted to the privilege grantee.

Note: If you specify ALL PRIVILEGES when you have no access privilege with the grant option for the target object, the GRANT statement results 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}

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

SELECT

Specify this to grant the SELECT privilege to an HADB user.
INSERT

Specify this to grant the INSERT privilege to an HADB user.
UPDATE

Specify this to grant the UPDATE privilege to an HADB user.
DELETE

Specify this to grant the DELETE privilege to an HADB user.
TRUNCATE

Specify this to grant the TRUNCATE privilege to an HADB user.
REFERENCES

Specify this to grant the REFERENCES privilege to an HADB user.
IMPORT TABLE

Specify this to grant the IMPORT TABLE privilege to an HADB user.
REBUILD INDEX

Specify this to grant the REBUILD INDEX privilege to an HADB user.
GET COSTINFO

Specify this to grant the GET COSTINFO privilege to an HADB user.
EXPORT TABLE

Specify this to grant the EXPORT TABLE privilege to an HADB user.
MERGE CHUNK

Specify this to grant the MERGE CHUNK privilege to an HADB user.
CHANGE CHUNKCOMMENT

Specify this to grant the CHANGE CHUNK COMMENT privilege to an HADB user.
CHANGE CHUNKSTATUS

Specify this to grant the CHANGE CHUNK STATUS privilege to an HADB user.
ARCHIVE CHUNK

Specify this to grant the ARCHIVE CHUNK privilege to an HADB user.
UNARCHIVE CHUNK

Specify this to grant the UNARCHIVE CHUNK privilege to an HADB user.

ON object-name

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

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

Here, object refers to a schema object.

[TABLE] table-name:

Grant 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:

Grant 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 GRANT statement.

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

Note: Specifying ALL TABLES grants access privileges to all the base tables owned by the executing user at the time of the GRANT statement. This does not include new base tables defined after the GRANT statement is executed (access privileges will not be granted for these newly-defined base tables).

TO privilege-grantee

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

Specifies the HADB users to grant access privileges to.

authorization-identifier[,authorization-identifier]:

Specifies the authorization identifiers of the HADB users who are to be granted access privileges. 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 if you want to authorize access privileges to the specified object for all HADB users. In this context, all HADB users includes HADB users created after execution of the GRANT statement in which PUBLIC is specified.

Example:

GRANT SELECT,IMPORT TABLE ON "T1" TO PUBLIC

Executing the GRANT statement above authorizes the following access privileges for all HADB users:

The SELECT privilege on table T1
The IMPORT TABLE privilege on table T1

For a user who runs the GRANT statement to specify PUBLIC, he or she must own the object for which the access privileges are to be authorized.

Note

The PUBLIC keyword can be thought of as an implicit, system-generated user who represents all HADB users.
In this context, authorizing access privileges means authorizing access or operations that use access privileges.

● WITH GRANT OPTION

Specify this option when you grant an access privilege with the grant option to the privilege grantee.

Note that a user who runs the GRANT statement must have the relevant access privilege with the grant option.

Example:

GRANT SELECT ON "X"."T1" TO "ADBUSER01" WITH GRANT OPTION

When you run the preceding GRANT statement, the SELECT privilege for table X.T1 is granted to ADBUSER01 with the grant option. An HADB user who runs the GRANT statement must have the SELECT privilege with the grant option for table X.T1.

To Page Top

(3) Privileges required at execution

A user who runs the GRANT statement that grants an access privilege must have the following privileges:

The CONNECT privilege
The schema definition privilege or the access privilege with the grant option

To Page Top

(4) Rules

For you to grant an access privilege to other HADB users, you must have the grant option for that access privilege.
You cannot grant yourself access privileges to objects that you own.
Even if you have the grant option for an access privilege, you cannot grant the access privilege to the following HADB users:
- HADB user who granted you an access privilege with the grant option
- Any HADB users in the chain of granting the access privilege with the grant option up to the preceding HADB user
- Yourself (you cannot grant yourself an access privilege that has been granted to you)
Example:
For you to grant another HADB user an access privilege for a viewed table, you must have an access privilege for all underlying tables of the viewed table with the grant option.
If a new access privilege for an underlying table is granted, the access privilege for the viewed tables that depend on the underlying table is also granted. (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 granted to HADB user A, the INSERT privileges for viewed tables A.V1 and A.V2 are also granted to HADB user A.
Note
- Propagation of access privileges can occur for only viewed tables that are defined by an HADB user who is granted the access privilege.
- If viewed tables are invalidated, no propagation of an access privilege occurs for the viewed tables.
Important

Be careful when a viewed table has multiple underlying tables. In this case, if a new access privilege for an underlying table is granted, the access privilege for the viewed table can be changed only when the access privilege meets the conditions applied to the viewed table.

Example:

Assume that HADB user A has the SELECT privileges for tables X.T1 and X.T2, and has defined viewed table A.V1 by using tables X.T1 and X.T2 as the underlying tables. In this case, even if the UPDATE privilege for table X.T1 is granted, the UPDATE privilege for viewed table A.V1 is not granted. Unless the UPDATE privileges for both tables X.T1 and X.T2 are granted, the UPDATE privilege for viewed table A.V1 is not granted. As shown earlier, even if the access privilege for only one underlying table is granted, the access privilege for the viewed table is not changed unless the conditions for access privileges applied to the viewed table are met.
To revoke only the grant option from an access privilege granted to another HADB user with the grant option, run the REVOKE statement with GRANT OPTION FOR specified. From an HADB user who is granted an access privilege with the grant option, you cannot revoke the grant option by running the GRANT statement without specifying WITH GRANT OPTION to regrant the same access privilege.
If you specify more than one authorization identifier for a privilege grantee and an error occurs in the execution of the GRANT statement, the granting of privileges to all of the specified HADB users is invalidated.
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

Grant the SELECT and INSERT privileges on table T1 to HADB user ADBUSER01.

GRANT SELECT,INSERT ON "T1" TO "ADBUSER01"

Example 2

Grant all access privileges on table T1 to HADB users ADBUSER02 and ADBUSER03.

GRANT ALL PRIVILEGES ON "T1" TO "ADBUSER02","ADBUSER03"

Example 3

In this example, the SELECT privilege for table X.T1 is granted with the grant option to HADB user ADBUSER04.

GRANT SELECT ON "X"."T1" TO "ADBUSER04" WITH GRANT OPTION

To Page Top