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.
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.
►►─── 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 ─┘
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.
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.
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.
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.
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.
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.
Specifies the resource to which the definition privileges apply.
Specifies that the privileges apply to any version of access-module-name in the associated schema.
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.
Identifies an SQL schema.
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.
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.
Identifies the function.
Identifies the users to whom you are giving the definition privileges.
Specifies all users.
Identifies a user or group. For expanded authorization-identifier syntax, see Expansion of Authorization-identifier.
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.
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:
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 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.
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;
|
Copyright © 2014 CA.
All rights reserved.
|
|