GRANT

Use the GRANT command to grant privileges to a user or group. The system adds these privileges to whatever privileges the user or group already has.

To grant privileges to only a few columns, create a view that contains those columns and grant privileges to that view.

Use the \dp command to obtain information about privileges on existing objects.

Syntax

Syntax for granting an object privilege:
GRANT <object_privilege>[,<object_privilege>…] ON <object>[,{<object> | <object_type>}…]
[TYPE <object_type>] TO {PUBLIC | GROUP <group> | <username> } [ WITH GRANT OPTION ]
Syntax for granting an administration privilege:
GRANT <admin_privilege>[,<admin_privilege>…] [ IN <scope> ] 
  TO { PUBLIC | GROUP <group> | <username> } [ WITH GRANT OPTION ]

The optional TYPE clause is only applicable for DATABASE, SCHEMA, USER and GROUP object types.

Inputs

The GRANT command takes the following inputs:
Table 1. GRANT inputs
Input Description
<object_privilege> One of the following object privileges:
  • ALL
  • ABORT
  • ALTER
  • DELETE
  • DROP
  • EXECUTE
  • EXECUTE AS
  • GENSTATS
  • GROOM
  • INSERT
  • LABEL ACCESS
  • LABEL RESTRICT
  • LABEL EXPAND
  • LIST
  • SELECT
  • TRUNCATE
  • UNFENCE
  • UPDATE
<object> The name of the object or object class for which the object privilege is to be granted. The name can be a qualified with the schema name or the database and schema names:
[[<database>.]<schema>.]<name>
The default database and schema are the current database and schema. If the privilege is to apply to all databases or schemas, specify ALL in place of the database or schema name.
The object can be of, or the object class can be, one of the following types:
  • AGGREGATE
  • DATABASE
  • EXTERNAL TABLE
  • FUNCTION
  • GROUP
  • MANAGEMENT TABLE
  • MANAGEMENT VIEW
  • PROCEDURE
  • SCHEMA
  • SEQUENCE
  • SYNONYM
  • SYSTEM TABLE
  • SYSTEM VIEW
  • TABLE
  • USER
  • VIEW

TABLE refers to user tables only, not system tables or management tables. To grant privileges on system tables, specify the SYSTEM TABLE object.

TYPE Specify this parameter when the type of specified objects is or might be ambiguous. It can be one of the types listed for <object>.
admin_privilege One the following administration privileges:
  • ALL ADMIN
  • BACKUP
  • [CREATE] AGGREGATE
  • [CREATE] DATABASE
  • [CREATE] EXTERNAL TABLE
  • [CREATE] FUNCTION
  • [CREATE] GROUP
  • [CREATE] LIBRARY
  • [CREATE] MATERIALIZED VIEW
  • [CREATE] PROCEDURE
  • [CREATE] SCHEDULER RULE
  • [CREATE] SEQUENCE
  • [CREATE] SYNONYM
  • [CREATE] TABLE
  • [CREATE] TEMP TABLE
  • [CREATE] USER
  • [CREATE] VIEW
  • [MANAGE] HARDWARE
  • [MANAGE] SECURITY
  • [MANAGE] SYSTEM
  • RESTORE
<scope> The scope of the administrative privileges:
<schema_name>
A specific schema in the current database
<database_name>.<schema_name>
A specific database and schema.
<database_name>.ALL
All schemas in a database.
ALL.ALL
All schemas in all databases.
PUBLIC The privileges are to be granted for the Public group. The Public group is a group of which all users are members, including users whose accounts are created later.
<groupname> The user group for which the privileges are to be granted.
<username> The user for whom the privileges are to be granted.
WITH GRANT In addition to granting the privileges, allows the user to grant the privileges to other users.

Output

The GRANT command has the following output
Table 2. GRANT output
Output Description
GRANT The command was successful.

Description

The GRANT command has the following features:
Privileges required
You must be the admin user, the owner of the user account or group to which you are granting privileges, or your account must have List and Alter privileges for the user or group, or for the User or Group object class.
Common tasks
Use the GRANT command to give specific object or administrator permissions to one or more users or groups of users. The system adds the permissions that you grant to whatever permissions the user or group already has.

Usage

The following provides sample usage.
  • Grant the Insert privilege to all users for the table films:
    MYDB.SCH1(USER)=> GRANT INSERT ON films TO PUBLIC;