Grant Option Clause

To enable an authorization ID to grant a privilege to another authorization ID, specify the with grant option clause. The owner of an object can grant any privilege to any authorization ID (or to public). The authorization ID to whom the privilege is granted with grant option can grant only the specified privilege. Any authorization ID can grant privileges that were granted to public with grant option to any other authorization ID.

The grant option cannot be specified for database privileges.

For example, if user, tony, creates a table called mytable and issues the following statement:

grant select on tony.mytable to laura
with grant option;

User, laura, can select data from tony.mytable and can authorize user evan to select data from tony.mytable by issuing the following statement:

grant select on tony.mytable to evan;

Because user laura did not specify the with grant option clause, user evan cannot authorize another user to select data from tony.mytable. User laura can grant select privilege, but cannot grant, for example, insert privilege. If user tony revokes select permission from user laura (using the revoke statement), user tony must specify how the DBMS must handle any dependent privileges that user laura has issued.

The choices are:

• revoke...cascade - Revokes all dependent privileges. In the preceding example, select permission is revoked from user evan.
• revoke...restrict - Do not revoke specified privileges if there are dependent privileges. In the preceding example, select privileges are not revoked from user laura because her grant to user evan depends on the privileges she received from user tony.

For more details, see "Ensuring Access Security" in the Database Administrator Guide.

Embedded Usage

In an embedded Grant (privilege) statement, the with clause can be specified using a host string variable (with :hostvar).

Permissions

Database privileges are not enforced if the user has the security privilege or is the DBA of the current database. The grant statement can be executed by a user who is either the owner of the target object, or has been granted permission (using WITH GRANT option) to use the statement on the specific target object by another user.

Locking

Granting privileges on a table takes an exclusive lock on that table. Granting privileges on the database as a whole locks pages in the iidbpriv catalog of the iidbdb.

Related Statements

Create Dbevent

Create Group

Create Procedure

Create Table

Create User

Create View

Grant (role)

Revoke

Examples: Grant (privilege)

The following are Grant (privilege) statement examples:

1. Grant select and update privileges on the salary table to the group, acct_clerk.

   grant select, update on table salary
       to group acct_clerk;

2. Grant update privileges on the columns, empname and empaddress, in the employee table to the users, joank and gerryr.

   grant update(empname, empaddress)
       on table employee
       to joank, gerryr;

3. Grant permission to the public to execute the monthly_report procedure.

   grant execute on procedure monthly_report
       to public;

4. Define a query_row_limit privilege of 100 rows on the new_accts database for users in the group, new_emp.

   grant query_row_limit 100 on database new_accts
       to group new_emp;

5. Grant unlimited rows to the role identifier, update_emp, which allows unlimited rows to be returned to any application that is associated with the role identifier, update_emp.

   grant noquery_row_limit on database new_acct
       to role update_emp;

6. Enable the inventory_monitor role to register for and raise the stock_low database event.

   grant register, raise on dbevent stock_low
       to role inventory_monitor

7. Enable any employee in accounting to change columns containing salary information.

   grant update on employee.salary, employee.socsec
       to group accounting;

8. Enable the accounting manager, rickr, complete access to salary information and to grant permissions to other user.

   grant all on employee to rickr with grant option;

9. Enable any user to create a table constraint that references the employee roster.

   grant references on emp_roster to public;