Grant (privilege)

Valid in: SQL, ESQL

The Grant (privilege) statement grants privileges on the database as a whole or on individual tables, views, sequences or procedures. It controls access to database objects, roles, and DBMS resources. Details about using the grant statement with role objects is described in Grant (role).

To remove privileges, use the revoke statement. To determine the privileges in effect for a session, use the dbmsinfo function. In some cases granting a privilege imposes a restriction, and revoking the privilege removes the restriction. For example, grant nocreate_table prevents the user from creating tables.

Note: The grant statement is the ISO/ANSI-compliant method for controlling access to database objects and resources.

To display granted database privileges, select data from the iidbprivileges system catalog. For details about system catalogs, see the Database Administrator Guide.

Syntax

The Grant (privilege) statement has the following format:

[EXEC SQL] GRANT ALL [PRIVILEGES] | privilege {, privilege}
              [ON [object_descriptor] [schema.]object_name
{, [schema.]object_name}]
              TO PUBLIC | [auth_type] auth_id {, auth_id} [WITH GRANT OPTION];

• privilege

  Specifies the type of privilege, as described in Types of Privileges.

• object_descriptor

  Specifies the type of object on which you are granting privileges. Object_descriptor must be one of the following:

  • TABLE
  • PROCEDURE
  • DATABASE
  • DBEVENT
  • SEQUENCE
  • CURRENT INSTALLATION

    Default: TABLE

    The object_descriptor must agree with the privilege being granted (for example, execute privilege cannot be granted on a table).

    Privileges cannot be defined for more than one type of object in a single grant statement. If object_descriptor is current installation, object_name must be omitted.

• object_name

  Specifies the name of the table, view, procedure, database event, sequence or database for which the privilege is being defined. The object must correspond to the object_descriptor. For example, if object_descriptor is table, object_name must be the name of an existing table or view.

• auth_type

  Specifies the type of authorization to which you are granting privileges. A grant statement cannot contain more than one auth_type. Valid auth_types are:

  • USER
  • GROUP
  • ROLE

    The auth_ids specified in the statement must agree with the specified auth_type. For example, if you specify auth_type as GROUP, all auth_ids listed in the statement must be group identifiers.

    Default: USER

• PUBLIC

  Grants a privilege to all users. The auth_type parameter can be omitted.

• auth_id

  Specifies the name of the users, groups, or roles to which you are granting privileges, or PUBLIC. Both PUBLIC and a list of auth_ids can be specified in the same grant statement. If the privilege is subsequently revoked from PUBLIC, the individual privileges still exist.