GRANT Table Access Privileges

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.
idmscu19
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.
This article describes the following information:
2
2
Authorization
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.
Syntax
  ►►─── GRANT  ─┬─ ACCESS ─────────┬────────────────────────────────────────────►               │ ┌────── , ─────┐ │               └─▼─┬─ DELETE ─┬─┴─┘                   ├─ INSERT ─┤                   ├─ SELECT ─┤                   └─ UPDATE ─┘  ►─── ON table ─┬─ 
table-name
 ─────────────────────────────────────────────┬──►                 └┬───────────────┬──────────────────── 
function-identifier
 ┘                  └─ 
schema-name.
─┘           ┌─────────────── , ──────────────┐  ►─── TO ─▼─┬─ PUBLIC ───────────────────┬─┴──────────────────────────────────►             └─ 
authorization-identifier
 ─┘  ►─┬─────────────────────┬────────────────────────────────────────────────────►◄    └─ WITH GRANT OPTION ─┘  
Parameters
  • ACCESS
    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.
  • DELETE
    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.
  • INSERT
    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.
  • SELECT
    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:
    • Retrieve data from the table, view, function, procedure or table procedure
    • Name the table, view, function, procedure or table procedure in a subquery
  • UPDATE
    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.
  • ON table table-name
    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.
    • schema-name
      Optional qualifier of the function-identifier. If not specified the schema name qualifier defaults to the current schema in effect for your SQL session.
    • function-identifier
      Identifies the function to which the access privilege applies.
  • TO
    Identifies the users to whom you are giving table access 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 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.
Usage
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 Table Access Privileges 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.
Example
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;
More Information