GRANT (schema privileges and authorities) statement
This form of the GRANT statement grants privileges and authorities on a schema.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
- The WITH GRANT OPTION for each identified privilege on schema-name
- ACCESSCTRL authority on schema-name
- ACCESSCTRL or SECADM authority
- Only an authorization ID with ACCESSCTRL or SECADM can grant the following privileges on schema
names starting with SYS:
- SELECTIN privilege on SYSCAT, SYSFUN, SYSSTAT or any schema names starting with SYSIBM (SQLSTATE 42501).
- SELECTIN, CREATEIN and DROPIN privileges on SYSPROC, SYSPUBLIC or SYSTOOLS schemas. Granting CREATEIN privilege allows the user to create a public alias. Granting DROPIN privilege allows the user to drop any public alias.
- No user can grant any other privileges or authorities on schema names starting with SYS (SQLSTATE 42501).
- Only a user with SECADM or database ACCESSCTRL authority can grant schema ACCESSCTRL authority.
- No schema authorities (SCHEMAADM, ACCESSCTRL, DATAACCESS, and LOAD) can be granted to PUBLIC directly or indirectly
Syntax
Description
- ALL or ALL PRIVILEGES
- Grants all of the following schema privileges on the
schema that is named in the ON clause:
- ALTERIN
- CREATEIN
- DELETEIN
- DROPIN
- EXECUTEIN
- INSERTIN
- SELECTIN
- UPDATEIN
If ALL is not specified, one or more of the keywords in the list of privileges must be specified.
- ACCESSCTRL
- Grants the access control authority on the schema. The schema ACCESSCTRL authority allows the
holder to:
- Grant and revoke the following privileges:
- READ, WRITE on global variables defined in the schema
- BIND privileges on packages defined in the schema
- CONTROL privileges on packages and modules defined in the schema
- USAGE privilege on XSR objects defined in the schema
- Grant and revoke all schema level privileges and authorities except for schema ACCESSCTRL itself.
- Grant and revoke the following privileges:
- ALTERIN
- Grants the privilege to alter or comment on all objects in the schema. The owner of an explicitly created schema automatically receives ALTERIN privilege.
- CREATEIN
- Grants the privilege to create objects in the schema. Other authorities or privileges required to create the object (such as CREATETAB) are still required. The owner of an explicitly created schema automatically receives CREATEIN privilege. An implicitly created schema has CREATEIN privilege automatically granted to PUBLIC.
- DATAACCESS
- Grants the authority to access data in the schema. The schema DATAACCESS authority allows the
holder to do the following:
- Select, insert, update, delete, and load data from tables or views defined in the schema
- Execute any package defined in the schema
- Execute any routine, except audit routines, defined in the schema
The schema DATAACCESS authority cannot be granted to PUBLIC (SQLSTATE 42508).
- DELETEIN
-
Grants the privilege to delete data in the table objects in the schema. The owner of the schema (explicitly or implicitly created) does not automatically receive DELETEIN privilege.
- DROPIN
- Grants the privilege to drop all objects in the schema. The owner of an explicitly created schema automatically receives DROPIN privilege.
- EXECUTEIN
- Grants the privilege to execute all existing and future user-defined functions, methods, procedures, packages, or modules defined in the schema. The owner of the schema (explicitly or implicitly created) does not automatically receive EXECUTEIN privilege.
- INSERTIN
-
Grants the privilege to insert rows and to run the IMPORT utility on all existing and future tables or views defined in the schema. The owner of the schema (explicitly or implicitly created) does not automatically receive INSERTIN privilege.
- LOAD
- Grants the authority to load in this schema. This authority gives a user the right to use the
LOAD utility in this schema. SCHEMAADM has this authority by default. However, if a user only has
schema LOAD authority (not schema DATAACCESS), the user is also required to have table-level
privileges. In addition to schema LOAD privilege, the user is required to have:
- INSERT privilege on the table or INSERTIN privilege on the schema of the table for LOAD with mode INSERT, TERMINATE (to terminate a previous LOAD INSERT), or RESTART (to restart a previous LOAD INSERT)
- INSERT and DELETE privilege on the table or INSERTIN and DELETEIN privilege on the schema of the table for LOAD with mode REPLACE, TERMINATE (to terminate a previous LOAD REPLACE), or RESTART (to restart a previous LOAD REPLACE)
- INSERT privilege on the exception table or INSERTIN privilege on the schema of the exception table, if such a table is used as part of LOAD
- SCHEMAADM
-
Grants the schema administrator authority. A schema administrator holds nearly all privileges on nearly all objects in the schema. The only exceptions are those privileges that are part of the access control, and schema data access.
SCHEMAADM authority cannot be granted to PUBLIC (SQLSTATE 42508).
- SELECTIN
- Grants the privilege to select from all existing and future tables or views defined in the schema. The owner of the schema (explicitly or implicitly created) does not automatically receive SELECTIN privilege.
- UPDATEIN
- Grants the privilege to use the UPDATE statement on all existing and future tables or updatable views defined in the schema. The owner of the schema (explicitly or implicitly created) does not automatically receive UPDATEIN privilege.
- LOGICAL BACKUP AND RESTORE OF RCAC PROTECTED DATA
- Grants users the privilege to allow a schema-level db_backup and db_restore scripts to access RCAC protected data.
- ON
-
- SCHEMA schema-name
- Specifies the name of the schema on which the authorities are to be granted. Authorities cannot be granted on any schema beginning with the SYS prefix (SQLSTATE 42501).
- CURRENT SCHEMA
- Specifies that the authorities will be granted on the schema described by the DB2® special register CURRENT SCHEMA. Authorities cannot be granted on any schema beginning with the SYS prefix (SQLSTATE 42501).
- TO
- Specifies to whom the privileges are granted.
- USER
- Specifies that the authorization-name identifies a user.
- GROUP
- Specifies that the authorization-name identifies a group name.
- ROLE
- Specifies that the authorization-name identifies a role name. The role name must exist at the current server (SQLSTATE 42704).
- Lists the authorization IDs of one or more users, groups, or roles.
The list of authorization IDs cannot include the authorization ID of the user issuing the statement (SQLSTATE 42502).
- PUBLIC
- Grants the privileges to a set of users (authorization IDs). For more information, see
Authorization, privileges and object ownership
.
- WITH GRANT OPTION
- Allows the specified authorization-names to GRANT the privileges to others.
Rules
- For each authorization-name specified, if neither
USER, GROUP, nor ROLE is specified, then:
- If the security plug-in in effect for the instance cannot determine the status of the authorization-name, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined as ROLE in the database, and as either GROUP or USER according to the security plug-in in effect, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according to the security plug-in in effect as both USER and GROUP, an error is returned (SQLSTATE 56092).
- If the authorization-name is defined according to the security plug-in in effect as USER only, or if it is undefined, USER is assumed.
- If the authorization-name is defined according to the security plug-in in effect as GROUP only, GROUP is assumed.
- If the authorization-name is defined in the database as ROLE only, ROLE is assumed.
- In general, the GRANT statement will process the granting of privileges that the authorization ID of the statement is allowed to grant, returning a warning (SQLSTATE 01007) if one or more privileges was not granted. If no privileges were granted, an error is returned (SQLSTATE 42501). (If the package used for processing the statement was precompiled with LANGLEVEL set to SQL92E for MIA, a warning is returned (SQLSTATE 01007), unless the grantor has no privileges on the object of the grant operation.)
Notes
- Grant on SYSPUBLIC: You can grant privileges on the reserved schema SYSPUBLIC. Granting CREATEIN privilege allows you to create a public alias, and granting DROPIN privilege allows you to drop a public alias. Granting SELECTIN allows you to select from tables defined in the schema.
- The
following authorities cannot be granted to the special group PUBLIC:
- SCHEMAADM on the schema
- ACCESSCTRL on the schema
- DATAACCESS on the schema
- LOAD on the schema
- Role role-name is granted directly to PUBLIC if the following statement has been
issued:
GRANT ROLE role-name TO PUBLIC
- Role role-name is granted indirectly to PUBLIC if the following statements have been
issued:
GRANT ROLE role-name TO ROLE role-name2
GRANT ROLE role-name2 TO PUBLIC
- Privileges granted to a group: An authority that is granted to a group is not used
for authorization checking on:
- Static DML statements in a package
- A base table while processing a CREATE VIEW statement
- A base table while processing a CREATE TABLE statement for a materialized query table
- Create SQL routine
- Create trigger
- WITH GRANT OPTION is ignored when granting schema authorities (SCHEMAADM, ACCESSCTRL, DATAACCESS, LOAD)
Examples
- Example 1: Grant user JSINGLETON to the ability to create
objects in schema CORPDATA.
GRANT CREATEIN ON SCHEMA CORPDATA TO JSINGLETON
- Example 2: Grant user IHAKES the ability to create and
drop objects in schema CORPDATA.
GRANT CREATEIN, DROPIN ON SCHEMA CORPDATA TO IHAKES