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

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 Table owner's access privileges 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 table	Table owner's access privileges	Can provide access privileges?
   Base table	All access privileges	Yes
   Read-only view table defined from a base table that belongs to the user#1	SELECT privileges	Yes
   Updatable view table defined from a base table that belongs to the user#2	All access privileges	Yes
   Read-only view table defined from a base table that belongs to another user#1, #3	SELECT privileges	No
   Updatable view table defined from a base table or that belongs to another user#2, #3	All access privileges the user has for base tables	No

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.

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

• DISTINCT, set function, literal, or arithmetic operation is specified in the SELECT clause.
• The same base table column is specified in the SELECT clause more than once.
• Tables are joined.
• GROUP BY clause is specified.
• HAVING clause is specified.
• READ ONLY clause is specified.

#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.