SQL Reference Guide > SQL Reference Guide > SQL Statements > GRANT (privilege)
Was this helpful?
GRANT (privilege)
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
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.
The GRANT (privilege) 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];
privilege
Specifies the privilege, which must be valid for the object_type.
Valid privileges are described in Object Privileges.
object_type
Specifies the type of object on which you are granting privileges. Object_type must be one of the following:
TABLE
(Default) Controls access to individual tables or views.
DATABASE
Controls access to database resources.
PROCEDURE
Controls who can execute individual database procedures.
DBEVENT
Controls who can register for and raise specific database events.
SEQUENCE
Controls who can retrieve values from individual database sequences.
CURRENT INSTALLATION
Grants the specified privilege on the current installation.
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.
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_type. For example, if object_type 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.
Last modified date: 11/09/2022