2.7.6 Access privileges for viewed tables : Hitachi Advanced Database Setup and Operation Guide

■ Access privileges for defined viewed tables

Which access privileges an HADB user has for the viewed tables they define depends on the access privileges they have for the underlying table. For example, suppose an HADB user defines a viewed table based on an underlying table for which they have SELECT privilege and INSERT privilege. In this case, the HADB user who defined the viewed table will have SELECT privilege and INSERT privilege for that viewed table.

If the viewed table has more than one underlying table, the access privileges the HADB user has for the viewed table are the access privileges he or she has in common for all of the underlying tables.

Example:

Suppose that HADB user A has the following access privileges for the following tables:

Table X.T1: SELECT privilege, INSERT privilege, and DELETE privilege
Table X.T2: SELECT privilege, INSERT privilege, and UPDATE privilege
Table X.T3: SELECT privilege, UPDATE privilege, and DELETE privilege

If HADB user A defines a viewed table A.V1 whose underlying tables are table X.T1 and table X.T2, HADB user A will have SELECT privilege and INSERT privilege for table A.V1.

If HADB user A defines a viewed table A.V2 whose underlying tables are tables X.T1, X.T2, and X.T3, HADB user A will only have SELECT privilege for table A.V2.

Note: If you want to have, for example, UPDATE privilege for a viewed table you define, you must have UPDATE privilege for all of its underlying tables.

For details about the rules that determine the access privileges for defined viewed tables, see Rules in Specification format and rules for the CREATE VIEW statement in the manual HADB SQL Reference.

■ Granting access privileges for viewed tables

To grant access privileges for a viewed table to another HADB user, you must have access privileges with a grant option for the underlying tables.

Example:

Suppose that HADB user A has the following access privileges for the following tables:

Table X.T1: SELECT privilege (with grant option), INSERT privilege (with grant option), and UPDATE privilege
Table X.T2: SELECT privilege (with grant option), INSERT privilege, and DELETE privilege (with grant option)

If HADB user A defines a viewed table A.V1 whose underlying table is table X.T1, HADB user A will have SELECT privilege (with grant option), INSERT privilege (with grant option), and UPDATE privilege for table A.V1. Because HADB user A has grant options for the SELECT privilege and INSERT privilege, he or she can grant SELECT privilege and INSERT privilege for viewed table A.V1 to other HADB users.

If HADB user A defines a viewed table A.V2 whose underlying tables are table X.T1 and table X.T2, HADB user A will have SELECT privilege (with grant option) and INSERT privilege for table A.V2. Because HADB user A has a grant option for the SELECT privilege, he or she can grant SELECT privilege for viewed table A.V2 to other HADB users.

■ Propagation of access privileges for viewed tables

When new access privileges are granted in relation to an underlying table, those access privileges are also newly granted for the viewed tables that depend on that underlying table. This is called the propagation of access privileges.

Example:: Suppose that HADB user A has defined viewed table A.V1 whose underlying table is X.T1, and viewed table A.V2 whose underlying table is A.V1. In this case, if HADB user A is then granted INSERT privilege for table X.T1, HADB user A will be automatically granted INSERT privileges for viewed tables A.V1 and A.V2.

Propagation of access privileges also takes place when access privileges for the underlying table are revoked.

Example:: Suppose that HADB user A has defined viewed table A.V1 whose underlying table is X.T1, and viewed table A.V2 whose underlying table is A.V1. In this case, if the INSERT privilege HADB user A has for table X.T1 is revoked, the INSERT privileges HADB user A has for viewed tables A.V1 and A.V2 are also revoked.

For details about the rules that govern the propagation of access privileges, see Rules in Granting access privileges in the manual HADB SQL Reference.

■ Effect on viewed tables when SELECT privilege is revoked for tables

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

Example:: Suppose that HADB user A has defined viewed table A.V1 whose underlying table is X.T1, and viewed table A.V2 whose underlying table is A.V1. In this case, if the SELECT privilege HADB user A has for table X.T1 is revoked, the viewed tables A.V1 and A.V2 that depend on table X.T1 are invalidated.

If the SELECT privilege with the grant option is revoked, or the grant option of the SELECT privilege is revoked, any SELECT privileges granted using that grant option are also revoked. This might also result in viewed tables being invalidated.

Example:: Suppose that the grant option HADB user A has for the SELECT privilege for table X.T1 is revoked. If HADB user A has used this grant option to grant SELECT privilege for table X.T1 to HADB user B, the SELECT privilege granted to HADB user B for table X.T1 is also revoked. If HADB user B has defined a viewed table B.V1 with table X.T1 as its underlying table, the viewed table B.V1 will be invalidated.