GRANT Definition Privileges

The GRANT Definition Privileges authorization statement gives one or more users the privilege of performing selected actions on a specified access module, schema, table, view, procedure or table procedure. It is also a CA IDMS extension of the SQL standard.
idmscu19
The GRANT Definition Privileges authorization statement gives one or more users the privilege of performing selected actions on a specified access module, schema, table, view, procedure or table procedure. It is also a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
To issue a GRANT statement for a definition privilege, you must own the resource, hold grantable privilege on the resource, or hold DBADMIN privilege on the dictionary containing the definition.
Syntax
  ►►─── GRANT ─┬─ DEFINE ─────────────┬─────────────────────────────────────────►              │ ┌─────── , ────────┐ │              └─▼─┬─ ALTER ──────┬─┴─┘                  ├─ CREATE ─────┤                  ├─ DISPLAY ────┤                  ├─ DROP ───────┤                  └─ REFERENCES ─┘    ►─── ON ─┬─ ACCESS MODULE ─┬────────────────┬─ 
access-module-name
 ─┬─────────►           │                 └─ 
schema-name
. ─┘                      │           ├─ SCHEMA 
schema-name
 ────────────────────────────────────┤           ├─ table 
table-name
 ──────────────────────────────────────┤           └─────────────────┬────────────────┬─ 
function-identifier
 ┘                             └─ 
schema-name
. ─┘             ┌─────────────── , ──────────────┐  ►─── TO ─▼─┬─ PUBLIC ───────────────────┬─┴──────────────────────────────────►             └─ 
authorization-identifier
 ─┘  ►─┬─────────────────────┬────────────────────────────────────────────────────►◄    └─ WITH GRANT OPTION ─┘  
Parameters
  • DEFINE
    Gives the ALTER, CREATE, DISPLAY, DROP, and REFERENCES 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 resource identified in the ON parameter to the users or groups identified in the TO parameter.
    The ALTER privilege on a resource allows you 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 you 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 on an access module allows you 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 you to delete the definition of the resource.
  • REFERENCES
    Gives the REFERENCES privilege on the resource 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 table is the referenced table.
  • ON
    Specifies the resource to which the definition privileges apply.
  • ACCESS MODULE
    access-module-name
    Specifies that the privileges apply to any version of
    access-module-name
    in the associated schema.
    • schema-name
      Identifies the schema associated with
      access-module-name
      .
      If you do not specify
      schema-name
      , it defaults to the current schema in effect for your SQL session.
  • SCHEMA
    schema-name
    Identifies an SQL schema.
  • table table-name
    Identifies a table, view, procedure or table procedure.
    If
    table-name
    does not include schema name qualifier, the schema name qualifier defaults to the current schema in effect for your SQL session.
  • schema-name
    Specifies the schema with which the function identified by function-identifier is associated. If schema-name is not specified, the schema defaults to the current schema in effect for your SQL session.
  • function-identifier
    Identifies the function.
  • TO
    Identifies the users to whom you are giving the definition privileges.
  • PUBLIC
    Specifies all users.
  • authorization-identifier
    Identifies a user or group. For expanded
    authorization-identifier
    syntax, see Expansion of Authorization-identifier.
  • WITH GRANT OPTION
    Gives the privilege of granting the specified definition privileges on the named resource to the users identified in the TO parameter. Only the owner of the resource or a user holding the DBADMIN privilege can specify WITH GRANT OPTION.
    A privilege granted with the WITH GRANT OPTION is called a grantable privilege.
Usage
Multiple Entities in One GRANT Statement
You can grant privileges on multiple entities of the same type in a single GRANT statement by using an asterisk (*) as a wildcard character. A wildcard character represents one or more characters omitted from a string.
If used, the asterisk must be the last character in the resource name. The asterisk can replace all or part of:
  • Access-module-name
  • Schema-name
    on the SCHEMA parameter
  • Table-identifier
    ,
    view-identifier
    ,
    procedure-identifier
    or
    table-procedure-identifier
    in
    table-name
  • Function-identifier
A resource name with an asterisk represents all the entities of the same type whose names match the pattern established by the name with the asterisk. For example, the access module name ACC* in a GRANT statement represents all access modules whose names start with ACC in the specified or current schema.
The DEFINE Keyword
When you use the DEFINE keyword with a GRANT statement, you grant a set of definition privileges on a resource to one or more users or groups.
When you use the DEFINE keyword with a REVOKE statement, you revoke any definition privileges that have been previously granted on the resource from the specified users or groups.
This means that if you GRANT CREATE privilege on a table, you can revoke the privilege with a REVOKE SELECT statement or a REVOKE DEFINE statement. Using REVOKE DEFINE is an efficient technique when you intend to revoke all definition privileges on a table from a user or group, whether the privileges were granted singly or as a set.
Similarly, you can GRANT DEFINE on a table to a user and then REVOKE DELETE on the table from the same user as a way to grant all but one definition privilege.
Duration of Privileges
Users hold privileges granted on a resource until the privileges are explicitly taken away by means of the REVOKE Definition Privileges statement.
Privileges Granted to Groups
When you grant a privilege to a group, each user in the group holds the privilege. If you subsequently add a user to the group, that user also holds the privilege. If you drop a user from the group, that user longer hold the privilege.
Example
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;
More Information