GRANT All Table Privileges

Gives one or more users all definition and access privileges on a specified table-like object.
idms
Gives one or more users all definition and access privileges on a specified table-like object.
GRANT All Table Privileges Authorization
To grant all table privileges, one of the following must be true:
  • You hold all privileges on the table-like object as grantable privileges. (you can grant the privileges, but you cannot specify WITH GRANT OPTION)
  • You own the table-like object.
  • You hold DBADMIN privilege the application dictionary where the table-like object is defined and on the database that contains the table-like object data.
  • You hold SYSADMIN privilege.
GRANT All Table Privileges Syntax
►►─── GRANT ALL PRIVILEGES ───────────────────────────────────────────────────►    ►─── ON table 
table-name
 ────────────────────────────────────────────────────►             ┌─────────────── , ───────────────┐  ►─── TO ─▼─┬─ PUBLIC ────────────────────┬─┴─────────────────────────────────►             └─ 
authorization-identifier
 ──┘    ►─┬──────────────────────┬───────────────────────────────────────────────────►◄    └─ WITH GRANT OPTION ──┘
GRANT All Table Privileges Parameters
 
  • ALL PRIVILEGES
    Gives the DELETE, INSERT, SELECT, UPDATE, ALTER, CREATE, DROP, and REFERENCES privileges on the table-like object, as applicable, identified in the ON parameter to the users or groups identified in the TO parameter.
  • ON table table-name
    Identifies the table-like object to which the table privileges apply.
    Expanded syntax for
    table-name
    is presented in Notes on Security Statement Syntax.
    You can wildcard the
    identifier-components
    of
    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 privileges.
  • PUBLIC
    Specifies all users.
  • authorization-identifier
    Identifies a user or group.
    Expanded syntax for
    authorization-identifier
    is presented in Using a Wildcard.
  • WITH GRANT OPTION
    Gives the privilege of granting the all table privileges on
    table-name
    to the users or groups 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.
GRANT All Table Privileges Usage
Verification of Wildcarded Grants
When you grant all table privileges, you grant a combination of table access and table definition privileges. If you wildcard
table-name
, the verification at runtime of the user's access privilege is handled differently from verification of the user's definition privilege:
  • For definition privileges, only the closest matching wildcarded grant is used. If CREATE privilege has been granted on HR.EMP* and HR.EMPV*, then only the grant on HR.EMPV* is used to verify the privilege to create HR.EMPVU_SALARY.
  • For 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.
Granting All Privileges to All Users
The following GRANT statement gives all users all privileges on all table-like objects in the TEST schema:
grant all privileges    on test.*    to public;
GRANT All Table Privileges More Information