Syntax for Securing Database Resources › GRANT Table Access Privileges › GRANT Table Access Privileges Usage

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;