The GRANT Table Access Privileges authorization statement gives one or more users or groups the privilege of performing selected actions on a specified table, view, function, procedure or table procedure.
To issue a GRANT statement for a table privilege, you must own the table, view, function, procedure, or table procedure, hold the corresponding grantable privilege on the table, view, procedure or table procedure, or hold the DBADMIN privilege on the database that contains the table, view, function, procedure, or table procedure.
►►─── GRANT ─┬─ ACCESS ─────────┬────────────────────────────────────────────► │ ┌────── , ─────┐ │ └─▼─┬─ DELETE ─┬─┴─┘ ├─ INSERT ─┤ ├─ SELECT ─┤ └─ UPDATE ─┘ ►─── ON table ─┬─ table-name ─────────────────────────────────────────────┬──► └┬───────────────┬──────────────────── function-identifier ┘ └─ schema-name.─┘ ┌─────────────── , ──────────────┐ ►─── TO ─▼─┬─ PUBLIC ───────────────────┬─┴──────────────────────────────────► └─ authorization-identifier ─┘ ►─┬─────────────────────┬────────────────────────────────────────────────────►◄ └─ WITH GRANT OPTION ─┘
Gives the DELETE, INSERT, SELECT, and UPDATE privileges on the table, view, function, procedure or table procedure identified in the ON parameter to the users or groups identified in the TO parameter.
The ACCESS parameter is a CA IDMS extension of the SQL standard.
Gives the DELETE privilege on the table, view, or table procedure identified in the ON parameter to the users or groups identified in the TO parameter.
The DELETE privilege on a table, view, or table procedure allows you to delete rows from the table or view.
Gives the INSERT privilege on the table, view, or table procedure identified in the ON parameter to the users or groups identified in the TO parameter.
The INSERT privilege on a table, view, or table procedure allows you to insert rows into the table or view.
Gives the SELECT privilege on the table, view, function, procedure or table procedure identified in the ON parameter to the users or groups identified in the TO parameter.
The SELECT privilege on a table, view, function, procedure or table procedure allows you to:
Gives the UPDATE privilege on the table, view, or table procedure identified in the ON parameter to the users or groups identified in the TO parameter.
The UPDATE privilege on a table, view, or table procedure allows you to modify data in the table or through the view.
Identifies the table, view, procedure or table procedure to which the table access privileges apply.
If table-name does not include schema name qualifier, the schema name qualifier defaults to the current schema in effect for your SQL session.
The optional keyword TABLE is a CA IDMS extension of the SQL standard. See Expansion of Table-name for expanded table-name syntax.
Optional qualifier of the function-identifier. If not specified the schema name qualifier defaults to the current schema in effect for your SQL session.
Identifies the function to which the access privilege applies.
Identifies the users to whom you are giving table access 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 privileges on the named table, view, procedure or table procedure to the users identified in the TO parameter. The owner of the resource, a holder of the applicable DBADMIN privilege, or a holder of SYSADMIN privilege can specify WITH GRANT OPTION.
A privilege granted with the WITH GRANT OPTION is called a grantable privilege.
Multiple Tables and Views in One GRANT Statement
You can grant privileges on multiple tables, views, functions, procedures and table procedures 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 table, view, function, procedure or table procedure identifier in table-name or function-identifier. A table, view, function, procedure or table procedure identifier with an asterisk represents all the tables, views, and table procedures whose identifiers match the pattern established by the identifier with the asterisk.
For example, the table, view, function, procedure or table procedure identifier EST* in a GRANT statement represents all tables, views, procedures and table procedures whose identifiers start with EST in the specified or current schema.
Duration of Privileges
Users hold privileges granted on a table, view, function, procedure or table procedure until the privileges are explicitly taken away by means of the REVOKE statement.
The ACCESS Keyword
When you use the ACCESS keyword with a GRANT statement, you grant a set of access privileges on a table, view, function, procedure or table procedure to one or more users or groups.
When you use the ACCESS keyword with a REVOKE statement, you revoke any access privileges that have been previously granted on the table, view, function, procedure or table procedure from the specified users or groups.
This means that if you GRANT SELECT privilege on a table, you can revoke the privilege with a REVOKE SELECT statement or a REVOKE ACCESS statement. Using REVOKE ACCESS is an efficient technique when you intend to revoke all access privileges on a table from a user or group, whether the privileges were granted singly or as a set.
Similarly, you can GRANT ACCESS 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 table access privilege.
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 no longer holds the privilege.
Granting Selected Privileges on a Table
The following GRANT statement gives the SELECT and UPDATE privileges on the EMPLOYEE table to users KRP, SAE, and PGD:
grant select, update on employee to krp, sae, pgd;
|
Copyright © 2014 CA.
All rights reserved.
|
|