Hitachi

Hitachi Advanced Database Setup and Operation Guide

2.7.5 Access privileges

This section describes the privileges (access privileges) that control access to schema objects.

Organization of this subsection

(1) About access privileges

Access privileges are privileges required to access the following schema objects:

Before you can use SQL statements to search the data in a schema object or use commands to manipulate a schema object, you must have access privileges for that schema object.

You will automatically have access privileges for schema objects that you (the HADB user with the authorization identifier currently connected to the HADB server) own. This means that each user can access the schema objects he or she owns.

Ordinarily, you cannot access schema objects owned by other HADB users because you will not have access privileges for them. To access a schema object owned by another HADB user, you must be granted access privileges by an HADB user who already has access privileges for that schema object.

The following figure shows an example of accessing a schema object owned by another HADB user:

Figure 2‒25: Example of accessing schema objects owned by another HADB user

[Figure]

Explanation

HADB user A owns table A.T1 and table A.T2. HADB user B owns table B.T3 and table B.T4.

Because HADB user A as owner of table A.T1 and table A.T2 has access privileges for those tables, he or she is able to access table A.T1 and table A.T2. However, HADB user A does not have access privileges for tables B.T3 and B.T4 which HADB user B owns. Therefore, HADB user A cannot access table B.T3 or table B.T4. To gain access to tables B.T3 and B.T4, HADB user A must be granted access privileges for tables B.T3 and B.T4 by HADB user B.

Note

An HADB user can only access schema objects for which they have access privileges.

To Page Top

(2) Types of access privileges

The following table lists the types of access privileges that are available in HADB. The operations you can perform with respect to a schema object depend on the types of access privileges you have.

Table 2‒7: List of access privileges

No.

Access privilege type

Explanation

Schema object to be referenced

1

SELECT privilege

The SELECT privilege is the privilege that an HADB user must have to reference the data of a schema object.

To execute the SELECT or CREATE VIEW statement, you need the SELECT privilege for the relevant schema object.

  • Base table

  • Viewed table

2

INSERT privilege

The INSERT privilege is the privilege that an HADB user must have to insert data into a schema object.

To execute the INSERT statement, you need the INSERT privilege for the relevant schema object.

  • Base table

  • Viewed table

3

UPDATE privilege

The UPDATE privilege is the privilege that an HADB user must have to modify the data of a schema object.

To execute the UPDATE statement, you need the UPDATE privilege for the relevant schema object.

  • Base table

  • Viewed table

4

DELETE privilege

The DELETE privilege is the privilege that an HADB user must have to delete the data of a schema object.

To execute the DELETE statement, you need the DELETE privilege for the relevant schema object.

  • Base table

  • Viewed table

5

TRUNCATE privilege

The TRUNCATE privilege is the privilege that an HADB user must have to delete all of a schema object's data in a single operation.

To execute the TRUNCATE TABLE or PURGE CHUNK statement, you need the TRUNCATE privilege for the relevant schema object.

Base table

6

REFERENCES privilege

The REFERENCES privilege is the privilege that an HADB user must have to define a referential constraint (foreign key) in the CREATE TABLE statement.

To define a foreign key, you need the REFERENCES privilege to the referenced table (the table in which the primary key is defined).

Base table

7

IMPORT TABLE privilege

The IMPORT TABLE privilege is the privilege that an HADB user must have to import data into a schema object.

To execute the adbimport command, you need the IMPORT TABLE privilege for the relevant schema object.

Base table

8

REBUILD INDEX privilege

The REBUILD INDEX privilege is the privilege that an HADB user must have to rebuild the index of a schema object.

To execute the adbidxrebuild command, you need the REBUILD INDEX privilege for the relevant schema object.

Base table

9

GET COSTINFO privilege

The GET COSTINFO privilege is the privilege that an HADB user must have to collect cost information from a schema object and from the index of that schema object, or to delete the collected cost information.

To execute the adbgetcst command, you need the GET COSTINFO privilege for the relevant schema object.

Base table

10

EXPORT TABLE privilege

The EXPORT TABLE privilege is the privilege that an HADB user must have to export the data of a schema object.

To execute the adbexport command, you need the EXPORT TABLE privilege for the relevant schema object.

  • Base table

  • Viewed table

11

MERGE CHUNK privilege

The MERGE CHUNK privilege is the privilege that an HADB user must have to merge multiple chunks that were created for a schema object into a single chunk.

To execute the adbmergechunk command, you need the MERGE CHUNK privilege for the relevant schema object.

Base table

12

CHANGE CHUNK COMMENT privilege

The CHANGE CHUNK COMMENT privilege is the privilege that an HADB user must have to enter a comment for a chunk defined in a schema object, or to modify or delete the comment.

To execute the adbchgchunkcomment command, you need the CHANGE CHUNK COMMENT privilege for the relevant schema object.

Base table

13

CHANGE CHUNK STATUS privilege

The CHANGE CHUNK STAUS privilege is the privilege that an HADB user must have to change the status of a chunk defined in a schema object.

To execute the adbchgchunkstatus command, you need the CHANGE CHUNK STAUS privilege for the relevant schema object.

Base table

14

ARCHIVE CHUNK privilege

ARCHIVE CHUNK privilege is the privilege that an HADB user must have to archive chunks.

To execute the adbarchivechunk command, you need the ARCHIVE CHUNK privilege for the relevant schema object.

Base table

15

UNARCHIVE CHUNK privilege

UNARCHIVE CHUNK privilege is the privilege that an HADB user must have to unarchive chunks.

To execute the adbunarchivechunk command, you need the UNARCHIVE CHUNK privilege for the relevant schema object.

Base table
Note

  • The owner of a base table (the HADB user who defined the base table) has all the access privileges listed in the preceding table for the base table they own.

  • The owner of a viewed table (the HADB user who defined the viewed table) does not necessarily have all the access privileges listed in the preceding table for the viewed table they own. Which types of access privileges the owner of a viewed table has is determined by the access privileges they have for the underlying table. For details, see 2.7.6 Access privileges for viewed tables.

To Page Top

(3) Granting access privileges

To grant access privileges for a schema object to another HADB user, you must have a grant option for access privileges for that schema object. A grant option for access privileges is a privilege that allows an HADB user to grant access privileges for a schema object to another HADB user.

The access privileges owners of base tables have for the base tables they own automatically come with grant options. Although the owners of viewed tables have access privileges for the viewed tables they own, whether they also have grant options depends on whether they have access privileges with the grant options for the underlying table. For details, see 2.7.6 Access privileges for viewed tables.

Important

When granting access privileges, you can select whether to grant access privileges only, or grant access privileges together with the related grant option.

The following figure shows an example of granting access privileges for a schema object:

Figure 2‒26: Example of granting access privileges for a schema object

[Figure]

Explanation

  • HADB user A

    The owner of the schema object (table A.T1). HADB user A has access privileges with a grant option for table A.T1.

  • HADB user B

    An HADB user who was granted access privileges with a grant option for table A.T1 by HADB user A. HADB user B can access table A.T1, and can also grant access privileges for table A.T1 to other HADB users.

  • HADB user C

    An HADB user who was granted access privileges with a grant option for table A.T1 by HADB user B. HADB user C can access table A.T1, and can also grant access privileges for table A.T1 to other HADB users.

  • HADB user D

    An HADB user who was granted access privileges without a grant option for table A.T1 by HADB user C. HADB user D can access table A.T1. However, because HADB user D does not have a grant option for table A.T1, HADB user D cannot grant access privileges for table A.T1 to other HADB users.

  • HADB user E

    An HADB user who was granted access privileges without a grant option for table A.T1 by HADB user C. HADB user E can access table A.T1. However, because HADB user E does not have a grant option for table A.T1, HADB user E cannot grant access privileges for table A.T1 to other HADB users.

To grant access privileges to another HADB user, you execute a GRANT statement. To grant access privileges with a grant option, you execute the GRANT statement with the WITH GRANT OPTION option specified.

Important

In the preceding example, the access privileges HADB users B to E have for table A.T1 are dependent privileges.

■ About dependent privileges

Access privileges granted to an HADB user by another HADB user are called dependent privileges.

Example:

[Figure]

  • The access privileges HADB user A has for table A.T1 shall be called P1.

  • The access privileges HADB user B has for table A.T1 shall be called P2.

  • The access privileges HADB user C has for table A.T1 shall be called P3.

  • The access privileges HADB user D has for table A.T1 shall be called P4.

  • The access privileges HADB user E has for table A.T1 shall be called P5.

In this scenario, the access privileges P2 to P5 are dependent privileges of the access privileges P1.

The dependent privileges of the access privileges P2 are P3 to P5.

The dependent privileges of the access privileges P3 are P4 and P5.

The access privileges P4 and P5 do not have any dependent privileges.

The dependent privileges you have are affected when the access privileges of the HADB user who granted you those privileges are revoked. This includes situations where the access privileges of an HADB user higher in the chain are revoked. For example, suppose that the access privileges P2 are revoked. In this case, access privileges P3 to P5 which are its dependent privileges are also revoked. If the access privileges P3 are revoked, the access privileges P4 and P5 which are its dependent privileges are also revoked.

■ HADB users to whom access privileges cannot be granted

You cannot grant access privileges to the following HADB users, even if you have a grant option for those access privileges:

  • The HADB user who granted you the access privileges with the grant option

  • An HADB user who is part of the chain that granted the preceding user the applicable access privileges with the grant option

  • Yourself (you cannot grant yourself access privileges you have already been granted)

Example:

[Figure]

To Page Top

(4) Revoking access privileges

You can revoke the access privileges you have granted. You cannot revoke the access privileges granted by another HADB user.

Important

  • If there are any dependent privileges of the access privileges you revoke, those dependent privileges will also be revoked.

  • If you revoke access privileges with the grant option, the grant option is revoked along with the access privileges.

The following figure shows an example of revoking access privileges for a schema object:

Figure 2‒27: Example of revoking access privileges for a schema object

[Figure]

Explanation

  1. Revoking the access privileges of HADB user B for table A.T1

    Only HADB user A can revoke these access privileges.

    When you revoke the access privileges of HADB user B for table A.T1 , the access privileges of HADB users C, D, and E for table A.T1 are also revoked because they are dependent privileges.

  2. Revoking the access privileges of HADB user C for table A.T1

    Only HADB user B can revoke these access privileges.

    When you revoke the access privileges of HADB user C for table A.T1 , the access privileges of HADB users D and E for table A.T1 are also revoked because they are dependent privileges.

  3. Revoking the access privileges of HADB user D for table A.T1

    Only HADB user C can revoke these access privileges. The access privileges of HADB user D for table A.T1 have no dependent privileges.

  4. Revoking the access privileges of HADB user E for table A.T1

    Only HADB user C can revoke these access privileges. The access privileges of HADB user E for table A.T1 have no dependent privileges.

To revoke access privileges, you execute a REVOKE statement.

Note

  • You cannot revoke the access privileges the owner of a schema object (HADB user A in the preceding example) has for that schema object (table A.T1 in the preceding example).

  • In the preceding example, HADB user A cannot directly revoke the access privileges of HADB user C for table A.T1 because those access privileges were granted to HADB user C by another user. However, if HADB user A were to revoke the access privileges or grant option granted to HADB user B for table A.T1, the access privileges of HADB user C for table A.T1 would also be revoked. Because the access privileges of HADB user C for table A.T1 are dependent privileges of the access privileges of HADB user B for table A.T1, both can be revoked at once.

■ Revoking only the grant option of access privileges

You can revoke only the grant option of access privileges. When you revoke only the grant option, access privileges granted using that grant option and any dependent privileges are revoked.

Example:

[Figure]

Explanation

When HADB user A revokes the grant option for the access privileges of HADB user B for table A.T1, the access privileges of HADB user C for table A.T1 are also revoked.

The access privileges of HADB users D and E for table A.T1 are also revoked because they are dependent privileges of the access privileges HADB user C has for table A.T1.

■ When access privileges are granted for the same schema object by multiple HADB users

Suppose that as shown by the following example, HADB user B and HADB user C both grant access privileges for table A.T1 to HADB user D. In this case, HADB user D will have access privileges for table A.T1 granted by HADB user B, and access privileges for table A.T1 granted by HADB user C.

Example:

[Figure]

For example, even if the access privileges for table A.T1 that HADB user B granted to HADB user D were revoked, HADB user D would still have the access privileges for table A.T1 granted by HADB user C. This is because an HADB user can only revoke the access privileges he or she has granted. Because HADB user D still has access privileges for table A.T1, HADB user D can continue to access table A.T1.

To Page Top

(5) Access privileges required to access multiple schema objects

When an HADB user executes an SQL statement, and multiple schema objects need to be accessed to process that SQL statement, the HADB user need access privileges to all of the schema objects in question.

An example follows in which the targeted schema objects are base tables.

Execution example of an SQL statement that accesses multiple base tables
SELECT * FROM "ADBUSER02"."T1", "ADBUSER02"."T2", "ADBUSER02"."T3"
Explanation

To retrieve base tables T1, T2, and T3, which are owned by another HADB user (ADBUSER02), the HADB user (ADBUSER01) needs an access privilege (SELECT privilege) to all three of those base tables. If the HADB user (ADBUSER01) does not have this access privilege (SELECT privilege) to base table T3, the above SELECT statement cannot be executed.

This requirement applies to the SELECT privilege as well as to other access privileges, such as the INSERT, UPDATE, and DELETE privilege.

To Page Top

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