11.2.13 Operation example of using viewed tables

This subsection describes how to define viewed tables and how to grant access privileges for when the following operation conditions are satisfied.

■ Operation conditions

A customer table (CUSTOMERS) that stores customer data is defined so that HADB users are allowed to refer to customer data.
HADB users who refer to customer data will not be able to refer data stored in the customer name column (CUSTOMER_NAME) in the customer table.
The table name (CUSTOMERS) of the customer table will not be opened to the HADB users who refer to customer data.

■ Tables defined in this operation example

Customer table (CUSTOMERS)

This base table stores the actual customer data.
Viewed table for the customer table (V1_CUSTOMERS)

V1_CUSTOMERS is a viewed table that depends on the underlying table CUSTOMERS. The customer name column (CUSTOMER_NAME) is deleted from the customer table, and the resulting table is defined as a viewed table.
Viewed table that depends on the viewed table (underlying table) of the customer table (V2_CUSTOMERS)

V2_CUSTOMERS is a viewed table that depends on the underlying table V1_CUSTOMERS.

HADB users who refer to customer data are allowed to access V2_CUSTOMERS.

■ HADB users in this operation example

HADB user ADBUSER01: Owner of customer table CUSTOMERS and viewed table V1_CUSTOMERS
HADB user ADBUSER02: Owner of viewed table V2_CUSTOMERS
HADB user ADBUSER03: HADB user who refers to customer data
HADB user ADBUSER04: HADB user who refers to customer data

■ Operation relationship diagram

To satisfy the preceding operation conditions, the following procedure defines the customer table and viewed tables, and grants the access privileges for individual tables to HADB users.

Procedure:

ADBUSER01 defines the customer table.

CREATE TABLE "ADBUSER01"."CUSTOMERS"("CUSTOMER_ID" INTEGER,
                                     "CUSTOMER_NAME" CHAR(100),
                                     "REGISTERED_DATE" TIMESTAMP)
             IN "DBAREA01"

ADBUSER01 imports customer data into the customer table.

Execute the adbimport command to import customer data into the customer table.
ADBUSER01 defines viewed table V1_CUSTOMERS for the customer table.
```
CREATE VIEW "ADBUSER01"."V1_CUSTOMERS"
    AS SELECT "CUSTOMER_ID","REGISTERED_DATE" FROM "ADBUSER01"."CUSTOMERS"
```
The CUSTOMER_NAME column is deleted from the customer table, and the resulting table is defined as viewed table V1_CUSTOMERS.
ADBUSER01 grants the SELECT privilege (with grant option) for viewed table V1_CUSTOMERS to ADBUSER02.
```
GRANT SELECT ON "ADBUSER01"."V1_CUSTOMERS" TO "ADBUSER02" WITH GRANT OPTION
```
The SELECT privilege (with grant option) for viewed table V1_CUSTOMERS is granted to ADBUSER02.
Note
- WITH GRANT OPTION is specified to grant to ADBUSER02 the grant option for the SELECT privilege for viewed table V1_CUSTOMERS.
- The SELECT privilege for viewed table V1_CUSTOMERS is granted to ADBUSER02 so that, in step 5, ADBUSER02 can define viewed table V2_CUSTOMERS that depends on viewed table V1_CUSTOMERS (as the underlying table).
- Only the SELECT privilege is granted in this example, but you can also grant other types of access privileges as necessary.
ADBUSER02 defines viewed table V2_CUSTOMERS.
```
CREATE VIEW "ADBUSER02"."V2_CUSTOMERS"
    AS SELECT * FROM "ADBUSER01"."V1_CUSTOMERS"
```
Viewed table V2_CUSTOMERS that depends on viewed table V1_CUSTOMERS (as the underlying table) is defined.
Note
- In the CREATE VIEW statement, * is specified for SELECT in the query expression. However, viewed table V2_CUSTOMERS is defined with the CUSTOMER_NAME column deleted because that table depends on the underlying table V1_CUSTOMERS, which also has its CUSTOMER_NAME column deleted.
- By specifying viewed table V1_CUSTOMERS as the underlying table for viewed table V2_CUSTOMERS, the name of the customer table (CUSTOMERS) that stores actual customer data can be hidden from users.
ADBUSER02 grants the SELECT privilege for viewed table V2_CUSTOMERS to ADBUSER03 and ADBUSER04.
```
GRANT SELECT ON "ADBUSER02"."V2_CUSTOMERS" TO "ADBUSER03","ADBUSER04"
```
ADBUSER03 and ADBUSER04 can now refer to customer data (in viewed table V2_CUSTOMERS) because the SELECT privilege for viewed table V2_CUSTOMERS has been granted.

Note

ADBUSER03 and ADBUSER04 are HADB users who only refer to customer data. Therefore, the grant option for the SELECT privilege for viewed table V2_CUSTOMERS is not granted. Thus, in the preceding GRANT statement, WITH GRANT OPTION is not specified.

If HADB user ADBUSER05 who refers to customer data is added while the preceding operation continues, ADBUSER02 needs to grant the SELECT privilege for viewed table V2_CUSTOMERS to ADBUSER05. Then, ADBUSER05 is allowed to refer to customer data.

Note

In this example, only ADBUSER01 can update, add, or delete customer data in the customer table.
ADBUSER02 performs management of the HADB users who refer to customer data (that is, management of access privileges for viewed table V2_CUSTOMERS).
Users from ADBUSER02 to ADBUSER05 can only refer to customer data. The CUSTOMER_NAME column cannot be referred.

To Page Top