grant

Description

Assigns authorizations to users.

Syntax

To grant authorization to access database objects:

grant {all [privileges]| permission_list} 
on {table_name | view_name} | stored_procedure_name}
to {public | name_list} with grant option]

To grant authorization to create database objects:

grant {all [privileges] | command_list}
 to {public | name_list}

Any valid object in the catalog can be substituted for table_name or view_name.

Parameters

all: when used to assign authorization to access database objects (first syntax format), all specifies that all privileges applicable to the specified object are granted or revoked.

permission_list: is a list of authorizations granted.

command_list: is a list of commands granted.

table_name: is the name of a table in the database.

view_name: is the name of a view in the current database. Only one view can be listed for each grant command.

stored_procedure: is the name of a stored procedure in the database.

public: is all users of the “public” group, which includes all users of the system.

name_list: is a list of users’ database names or group names or both, separated by commas.

with grant option: allows the users specified in name_list to grant the privileges specified by permission_list to other users.

Examples

Example 1

grant insert, delete
 on titles
 to mary, sales

Example 2

grant update
 on titles (price, advance)
 to public

Example 3

grant create database, create table
 to mary, john

Example 4

grant all on titles
 to public

Example 5

grant all
 to public

Example 6

grant update on authors
 to mary
 with grant option

Example 7

grant select, update on titles(price)
 to bob
 with grant option

Usage

DB2 does not allow you to grant authorization to a stored procedure.
You can substitute the word from for to in the grant syntax.
You can only grant or revoke authorizations on objects in the current database.
Role_name, which allows you to grant authorizations to all users who have been granted a specific role, is not supported. However, if you include it in the command, an error does not occur.