24.4.6 Table access privilege setup

The owner of a table uses the GRANT statement to grant access privileges to other users or roles.

Organization of this subsection
(1) Granting the table access privilege to a user
(2) Granting table access privileges to a role
(3) A user moves to a different role (when granting access privilege for a table to a different role)

(1) Granting the table access privilege to a user

Example
Grant SELECT privilege for a table (authorization-identifier.table-identifier is USR02.T01) to a user (authorization identifieris USR03):

GRANT SELECT ON USR02.T01 TO USR03

Note
If a role name is the same as a user ID and access privileges were granted to both names, it may not be possible to distinguish between the names when acquiring table access privileges information. For details, see 24.5.2 Acquiring table access privileges information.

(2) Granting table access privileges to a role

Table access is granted to roles that use filters.

Example
Grant SELECT privilege for a table (authorization-identifier.table-identifier is USER02.T01) to a role (role name is GRP01):

GRANT SELECT ON USR02.T01 TO GROUP GRP01

Remarks
When the following SQL is executed, the SELECT privilege, INSERT privilege, or UPDATE privilege for a table (T01) is granted to a user (USR01) who belongs to a role (GRP01).

GRANT SELECT ON T01 TO PUBLIC
GRANT INSERT ON T01 TO GROUP GRP01
GRANT UPDATE ON T01 TO USR01

Note
  • When access privilege for a table is granted to a role, users belonging to that role cannot create a view table for that table.
  • Access privilege for a table can be granted to a user or role that is not registered in the Directory Server. However, the individual user or a user belonging to the role cannot access the table.

(3) A user moves to a different role (when granting access privilege for a table to a different role)

When a user moves to a different role, it is not necessary to use the GRANT statement or REVOKE statement to change access privilege for a table. For example, when a user (USR01) moves from the Finance Department to General Administration, simply change the department name in the user's user information registered in Directory Server and execute the pdgrprfl command. USR01 will then use the table access privileges granted to the General Administration group and will no longer be able to use the table access privileges granted to the Finance Department group.

If table access privileges have been granted directly to the user, it is necessary to reevaluate those table access privileges (i.e., it may be necessary to revoke some table access privileges).