GRANT

Invocation for GRANT

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

If the authorization mechanism was not activated when the Db2 subsystem was installed, an error condition occurs.

Authorization for GRANT

To grant a privilege P, the privilege set must include one of the following:

• The privilege P WITH GRANT OPTION
• Ownership of the object on which P is a privilege
• SECADM authority
  Note: If installation parameter SEPARATE SECURITY is NO, SYSADM authority has implicit SECADM authority.
• ACCESSCTRL authority

  The presence of ACCESSCTRL authority in the privilege set allows the granting of all authorities except:

  • System DBADM
  • CREATE_SECURE_OBJECT privilege
  • DATAACCESS
  • ACCESSCTRL
    Note: If installation parameter SEPARATE SECURITY is NO, SYSCTRL authority has implicit ACCESSCTRL authority that allows the granting of all privileges except:

    • DBADM on databases
    • DELETE, INSERT, SELECT, and UPDATE on user tables or views
    • EXECUTE on plans, packages, functions, or stored procedures
    • PACKADM on collections
    • SYSADM authority
    • USAGE on distinct types, JARs, and sequences
    • READ, WRITE on global variables
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
  The Installation SYSOPR authority with the current SQLID set to SYSINSTL allows the granting of the following privileges:

  • All database, table, and table space privileges on objects in database DSNDB04 and system databases DSNRGFDB, DSNRLST, DSNOPTDB, DSNMDCDB, DSNADMDB, DSNATPDB, SN5JSDB, DSNMQDB, SYSIBMTA, SYSIBMTS, and DSNXSR
  • The USE privilege on buffer pools and storage groups
  • All privileges on plans that begin with 'DSN'
  • All privileges on packages whose collection-ID and package-name begin with 'DSN'
  • The EXECUTE privilege on system-defined routines.
  

To grant the CREATE_SECURE_OBJECT system privilege, the privileges that are held by the authorization ID of the statement must include SECADM authority.

Except for views, the GRANT option for privileges on a table is also inherent in DBADM authority for its database, provided DBADM authority was acquired with the GRANT option. See CREATE VIEW for a description of the rules that apply to views.

If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. The owner can be a role. If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. However, if the process is running in a trusted context that is defined with the ROLE AS OBJECT OWNER CLAUSE, the privilege set is the privileges that are held by the role in effect.

Description for GRANT

authorization-specification

Specifies one or more privileges for the class of privilege. The same privilege must not be specified more than once.

TO

Specifies to what authorization IDs the privileges are granted.

authorization-name,...

Lists one or more authorization IDs.

ROLE role-name

Lists one or more role names. Each name must identify a role that exists at the current server.

The value of the CURRENT RULES special register determines whether you can use the ID or role of the GRANT statement itself (to grant privileges to yourself). When CURRENT RULES is:

DB2

You cannot use the ID or role of the GRANT statement.

STD

You can use the ID or role of the GRANT statement.

PUBLIC

Grants the privileges to all users at the current server, including database requesters using DRDA access.

CREATE_SECURE_OBJECT must not be granted to PUBLIC.

ACCESSCTRL, DATAACCESS and system DBADM authorities cannot be granted to PUBLIC.

WITH GRANT OPTION

Allows the named users to grant the privileges to others. Granting an administrative authority with this option allows the user to specifically grant any privilege belonging to that authority. If you omit WITH GRANT OPTION, the named users cannot grant the privileges to others unless they have that authority from some other source.

GRANT authority cannot be passed to PUBLIC. When WITH GRANT OPTION is used with PUBLIC, a warning is issued, and the named privileges are granted, but without GRANT authority.

If you grant the CREATE_SECURE_OBJECT system privilege, the WITH GRANT OPTION clause is ignored because the CREATE_SECURE_OBJECT system privilege cannot be granted to others.

GRANT ACCESSCTRL, DATAACCESS and system DBADM authorities cannot be passed to others. If WITH GRANT OPTION is used when granting these authorities, a warning is issued and the named authorities are granted, but without GRANT authority.

Notes for GRANT

A grant is the granting of a specific privilege by a specific grantor to a specific grantee. The grantor for a given GRANT statement is the authorization ID for the privilege set; that is, the SQL authorization ID of the process or a role, or the authorization ID of the owner of the plan or package. Grant statements that are made in a trusted context that is defined with the ROLE AS OBJECT OWNER clause result in the grantor being the role that is in effect. If the statement is prepared dynamically, the grantor is the role that is associated with the ID that is running the statement. If the statement is embedded in an application program that was bound in a trusted context that was defined with the ROLE AS OBJECT OWNER clause the owner of the plan or package is a role which is the grantor. If the ROLE AS OBJECT OWNER clause is not specified for the trusted context, the grantor is the authorization ID of the process.

The grantee, as recorded in the catalog, is an authorization ID or PUBLIC. Any instances of PUBLIC* in the catalog mean the same as PUBLIC.

Duplicate grants from the same grantor are not recorded in the catalog. Otherwise, the result of executing a GRANT statement is recorded as one or more grants in the current server's catalog.

If more than one privilege or authorization-name is specified after the TO keyword and one of the grants is in error, execution of the statement is stopped and no grants are made. The status of the privilege or privileges granted is recorded in the catalog for each authorization-name.

Different grantors can grant the same privilege to a single grantee. The grantee retains that privilege as long as one or more of those grants are recorded in the catalog. Privileges that imply other privileges are also termed authorities. Grants are removed from the catalog by executing SQL REVOKE statements.

Whenever a grant is made for a database, distinct type, package, plan, schema, stored procedure, table, trigger, user-defined function, view, or USE privilege for an object that does not exist, an SQL return code is issued and the grant is not made.