GRANT SQL Definition Privileges

Gives one or more users or groups the privilege of performing definition functions on a specified access module, schema, table, function, procedure, table procedure, or view.
idms
Gives one or more users or groups the privilege of performing definition functions on a specified access module, schema, table, function, procedure, table procedure, or view.
GRANT SQL Definition Privileges Authorization
To grant definition privileges on SQL-defined database resources, one of the following must be true:
  • You hold the corresponding grantable privilege on the resource (you can grant the privilege, but you cannot specify WITH GRANT OPTION).
  • You own the resource.
  • You hold DBADMIN on the dictionary containing the definition.
  • You hold SYSADMIN privilege.
GRANT SQL Definition Privileges Syntax
►►─── GRANT ─┬─ DEFINE ─────────────┬─────────────────────────────────────────►              │ ┌─────── , ────────┐ │              └─▼─┬─ ALTER ──────┬─┴─┘                  ├─ CREATE ─────┤                  ├─ DISPLAY ────┤                  ├─ DROP ───────┤                  └─ REFERENCES ─┘    ►─── ON ─┬─ ACCESS MODULE ─┬────────────────┬─ 
access-module-name
 ─┬─────────►           │                 └─ 
schema-name
. ─┘                      │           ├─ SCHEMA 
schema-name
 ────────────────────────────────────┤           └─ table 
table-name
 ──────────────────────────────────────┘             ┌─────────────── , ──────────────┐  ►─── TO ─▼─┬─ PUBLIC ───────────────────┬─┴──────────────────────────────────►             └─ 
authorization-identifier
 ─┘    ►─┬─────────────────────┬────────────────────────────────────────────────────►◄    └─ WITH GRANT OPTION ─┘
GRANT SQL Definition Privileges Parameters
 
  • DEFINE
    Gives the ALTER, CREATE, DISPLAY, and DROP privileges, as applicable, on the resource identified in the ON parameter to the users or groups identified in the TO parameter.
  • ALTER
    Gives the ALTER privilege on the resource identified in the ON parameter to the users or groups identified in the TO parameter.
    The ALTER privilege on a resource allows a user to modify the definition of the resource.
  • CREATE
    Gives the CREATE privilege on the resource identified in the ON parameter to the users or groups identified in the TO parameter.
    The CREATE privilege on a resource allows a user to define the resource.
  • DISPLAY
    Gives the DISPLAY privilege on the resource identified in the ON parameter to the users or groups identified in the TO parameter.
    The DISPLAY privilege allows the user to issue a DISPLAY RESOURCE statement on the named resource. The grantable DISPLAY privilege allows a user to issue a DISPLAY PRIVILEGES statement on the named resource.
    The DISPLAY privilege on
    access-module-name
    also allows a user to execute the EXPLAIN statement on the access module.
  • DROP
    Gives the DROP privilege on the resource identified in the ON parameter to the users or groups identified in the TO parameter.
    The DROP privilege on a resource allows a user to delete the definition of the resource.
  • REFERENCES
    Gives the REFERENCES privilege on the table identified in the ON parameter to the users or groups identified in the TO parameter.
    The REFERENCES privilege on a table allows a user to define referential constraints in which the named table is the referenced table.
    The REFERENCES privilege applies only to tables. It does not apply to functions, procedures, table procedures, views, access modules, or schemas.
  • ON
    Specifies the resource to which the definition privileges apply.
  • ACCESS MODULE
    Specifies that the privileges apply to any version of
    access-module-name
    in the associated schema.
  • access-module-name
    Identifies the access module.
    You can wildcard
    access-module-name
    . If you specify
    schema-name
    , the wildcard character is valid after the period following
    schema-name
    .
    For more information on wildcarding, see Using a Wildcard.
  • SCHEMA
    schema-name
    Identifies an SQL schema.
    You can wildcard
    schema-name
    in the SCHEMA parameter.
    For more information on wildcarding, see Using a Wildcard.
  • table table-name
    Identifies a table-like object
    Expanded syntax for
    table-name
    is presented in Notes on Security Statement Syntax.
    You can wildcard any identifier when you grant definition privileges on
    table-name
    . If you specify
    schema-name
    in
    table-name
    , the wildcard character is valid after the period following
    schema-name
    .
    For more information on wildcarding, see Using a Wildcard.
    If you do not specify
    schema-name
    , it defaults to the current schema in effect for your session.
  • TO
    Specifies the users to whom you are giving the definition privileges.
  • PUBLIC
    Specifies all users.
  • authorization-identifier
    Identifies a user or group.
    Expanded syntax for
    authorization-identifier
    is presented in Notes on Security Statement Syntax.
  • WITH GRANT OPTION
    Gives the privilege of granting the specified definition privileges on the named resource to the users or groups identified in the TO parameter.
    A privilege granted with the WITH GRANT OPTION is called a grantable privilege.
GRANT SQL Definition Privileges Usage
Wildcarding Table-Name
For table definition privileges, only the closest matching wildcarded grant is used. If CREATE privilege has been granted on HR.EMP* and HR.EMPV*, then only the grant on HR.EMPV* is used to verify the privilege to create HR.EMPVU_SALARY.
The DEFINE Keyword
When you use the DEFINE keyword with a GRANT statement, you grant a set of definition privileges to one or more users or groups.
When you use the DEFINE keyword with a REVOKE statement, you revoke all of the privileges in the set that have been previously granted to the specified users or groups.
This means that if you GRANT CREATE privilege on a resource, you can revoke the privilege with either a REVOKE CREATE statement or a REVOKE DEFINE statement. Using REVOKE DEFINE is an efficient technique when you intend to revoke all definition privileges from a user or group, whether the privileges were granted singly or as a set.
Similarly, you can GRANT DEFINE on a resource to a user and then REVOKE DROP on the resource from the same user as a way to grant all but one definition privilege.
Granting Privileges on a Schema
The following GRANT statement gives the ALTER, CREATE, DISPLAY, and DROP privileges on all schemas that begin with 'DSF' to user DSF. The statement also gives user DSF the privilege of granting the same privileges to other users.
grant define    on schema dsf*    to dsf    with grant option;
GRANT SQL Definition Privileges More Information
For more information on revoking SQL definition privileges, see REVOKE SQL Definition Privileges.