GRANT Table Access Privileges

Gives one or more users or groups the privilege of accessing a specified table-like object in a specified way.
idms
Gives one or more users or groups the privilege of accessing a specified table-like object in a specified way.
GRANT Table Access Privileges Authorization
To grant table-like object access privileges, one of the following must be true:
  • You hold the corresponding grantable privilege on the table-like object (you can grant the privilege, but you cannot specify WITH GRANT OPTION).
  • You own the table-like object.
  • You hold DBADMIN privilege on the database that contains the table-like object data.
  • You hold SYSADMIN privilege.
GRANT Table Access Privileges Syntax
►►─── GRANT ──┬─ ACCESS ─────────┬────────────────────────────────────────────►               │ ┌────── , ─────┐ │               └─▼─┬─ DELETE ─┬─┴─┘                   ├─ INSERT ─┤                   ├─ SELECT ─┤                   └─ UPDATE ─┘    ►─── ON table 
table-name
 ────────────────────────────────────────────────────►             ┌─────────────── , ──────────────┐  ►─── TO ─▼─┬─ PUBLIC ───────────────────┬─┴──────────────────────────────────►             └─ 
authorization-identifier
 ─┘    ►─┬─────────────────────┬────────────────────────────────────────────────────►◄    └─ WITH GRANT OPTION ─┘
GRANT Table Access Privileges Parameters
 
  • ACCESS
    Gives the DELETE, INSERT, SELECT, and UPDATE privileges on the table-like object identified in the ON parameter to the users or groups identified in the TO parameter.
  • DELETE
    Gives the DELETE privilege on the table-like object identified in the ON parameter to the users or groups identified in the TO parameter.
    The DELETE privilege on a table-like object allows a user to delete rows from the table or view.
  • INSERT
    Gives the INSERT privilege on the table-like object identified in the ON parameter to the users or groups identified in the TO parameter.
    The INSERT privilege on a table-like object allows a user to insert rows into the table or view.
  • SELECT
    Gives the SELECT privilege on the table-like object identified in the ON parameter to the users or groups identified in the TO parameter.
    The SELECT privilege on a table-like object allows a user to the following:
    • Retrieve data from the table-like object.
    • Name the table-like object in a subquery.
    • Define a view derived from the table-like object.
  • UPDATE
    Gives the UPDATE privilege on the table-like object identified in the ON parameter to the users or groups identified in the TO parameter.
    The UPDATE privilege on a table-like object allows a user to modify data in the table-like object.
  • ON table table-name
    Specifies the table-like object to which the table access privileges apply.
    Expanded syntax for
    table-name
    is presented in Notes on Security Statement Syntax.
    You can wildcard any identifier when you grant access 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 or groups to whom you are giving table access privileges.
  • PUBLIC
    Specifies all users.
  • authorization-identifier
    Identifies a user or group.
    Expanded syntax for
    authorization-identifier
    is presented in section Notes on Security Statement Syntax.
  • WITH GRANT OPTION
    Gives the privilege of granting the specified access privileges on the named table or view to the users or groups identified in the TO parameter.
    A privilege granted with the WITH GRANT OPTION is called a grantable privilege.
GRANT Table Access Privileges Usage
Verifying Privileges Granted with Wildcards
For table access privileges, all matching wildcarded grants are used. If SELECT privilege has been granted on HR.EMP* and HR.EMPV*, then users or groups receiving either the HR.EMP* or the HR.EMPV* grant are authorized to select from EMPVU_SALARY.
This differs from the use of wildcards for all other types of resources or privileges, where only the closest matching wildcarded grant of privilege is used to verify the user's authorization.
The ACCESS Keyword
When you use the ACCESS keyword with a GRANT statement, you grant a set of access privileges on a table-like object 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-like object from the specified users or groups.
This means that if you GRANT SELECT privilege on a table, you can revoke the privilege with either 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.
Granting Selected Privileges on a Table
The following GRANT statement gives the SELECT and UPDATE privileges on the EMPLOYEE table associated with the current schema to users KRP, SAE, and PGD:
grant select, update    on employee    to krp, sae, pgd;
GRANT Table Access Privileges More Information
For more information on revoking table access privileges, see REVOKE Table Access Privileges.