GRANT (privilege)

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.

This statement has the following format:

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

Specifies the privilege, which must be valid for the object_type.

Valid privileges are described under the section Object Privileges.

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

Controls who can register for and raise specific database events.

Controls who can retrieve values from individual database sequences.

The object_type 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_type is CURRENT INSTALLATION, object_name must be omitted.

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_type. For example, if object_type is TABLE, object_name must be the name of an existing table or view.

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:

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.

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

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.