2.2.3 Granting CONNECT and access privileges to users who access tables (database)

Organization of this subsection
(1) Granting CONNECT privileges
(2) Granting access privileges

(1) Granting CONNECT privileges

Executor: HiRDB administrator or user who manages user privileges (user with DBA privilege)

The GRANT statement, which is a definition SQL statement, is used to grant the CONNECT privilege to users who will access database tables.

Example
Grant the CONNECT privilege to a user (authorization identifier USER003, password HIRDB003) who will access tables:

 GRANT CONNECT TO USER003 IDENTIFIED BY HIRDB003

(2) Granting access privileges

Executor: Table owner
The GRANT statement, which is a definition SQL statement, is used by a table's owner to grant access privileges to users who will access the table.
Example 1
Grant only the SELECT privilege for a table (authorization-identifier.table-identifier USER002.T001) to a user (authorization identifier USER004) who is to be permitted only to make retrievals from the table:

 GRANT SELECT ON USER002.T001 TO USER004

Example 2
Grant the SELECT and UPDATE privileges for a table (authorization-identifier.table-identifier USER002.T001) to a user (authorization identifier USER005) who is to be permitted to retrieve and update the table:

 GRANT SELECT,UPDATE ON USER002.T001 TO USER005

Example 3
Grant the SELECT, UPDATE, and INSERT privileges for a table (authorization-identifier.table-identifier USER002.T001) to a user (authorization identifier USER006) who is to be permitted to retrieve, update, add to, and delete the table:

 GRANT ALL ON USER002.T001 TO USER006

Notes
  1. A table owner can grant to another user only the access privileges to that table that the owner has been granted. Table 2-3 lists the access privileges granted to a table owner.
  2. A table owner cannot grant to another user access privileges to a view table that was defined from a table that belongs to a different user.

    Table 2-3 Table owner's access privileges

    Type of tableTable owner's access privilegesCan provide access privileges?
    Base tableAll access privilegesYes
    Foreign tableAll access privilegesYes
    Read-only view table defined from a base table or foreign table that belongs to the user1SELECT privilegesYes
    Updatable view table defined from a base table or foreign table that belongs to the user2All access privilegesYes
    Read-only view table defined from a base table or foreign table that belongs to another user1,3SELECT privilegesNo
    Updatable view table defined from a base table or foreign table that belongs to another user2,3All access privileges the user has for base tables or foreign tablesNo
Legend:
Yes: The user can grant and revoke the access privileges of other users.
No: The user cannot grant or revoke the access privileges of other users.
Note
You can grant to other users only those access privileges to a foreign table that you have for the base table of that foreign table (table at a foreign server). If you attempt to grant access privileges you do not have, an error results.

1 A read-only view table is a view table for which any of the following information is specified in the view definition:

2 An updatable view table is a view table to which the read-only attribute is not assigned.

3 To define a view table from a table that belongs to another user, you must have SELECT privileges for that table.