Grant and Revoke Statements

The GRANT and REVOKE statements allow a user to control access to objects (Tables, Views, Sequences, Procedures, etc.) in their schema. The Grant command grants authorization for a subject (another user or group) to perform some action (SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX) on an object (Table, View, stored procedure, sequence or synonym).

The actions are defined as follows:

  • SELECT - allows a subject to select rows from the object.
  • INSERT - allows a subject to insert rows into the object.
  • UPDATE - allows a subject to update rows in the object.
  • DELETE - allows a subject to delete rows from the object.
  • ALTER - allows a subject to alter the object. For example, add a column or change a constraint.
  • INDEX - allows a subject to create an index on the object.
  • EXECUTE - allows a subject to execute a stored procedure or trigger.
In addition to objects such as tables, the SELECT and UPDATE actions may also be granted on individual columns in a table or view.

The general syntax for the GRANT statement is: 

      GRANT <ACTION1>, <ACTION2>, ...
      ON    tablename
      TO    subject;
For example, assume user ALICE wishes to allow another user BOB to view the rows in the employee table. ALICE would execute the following GRANT statement: 
      GRANT SELECT
      ON    employee
      TO    BOB;
At this point, user BOB may now issue SQL SELECT statements on the table ALICE.employee. For example, user BOB may execute: 
      SELECT * FROM ALICE.employee;

      FNAME    MI LNAME          SSN
      -------- -- -------- ---------
      JOHN     B  SMITH    123456789
      FRANKLIN T  WONG     333445555
      ALICIA   J  ZELAYA   999887777
      JENNIFER S  WALLACE  987654321
      RAMESH   K  NARAYAN  666884444
      JOYCE    A  ENGLISH  453453453
      AHMAD    V  JABBAR   987987987
      JAMES    E  BORG     888665555
      etc.
The REVOKE statement reverses the authorization by removing privileges from a subject (user). The syntax for REVOKE is: 
      REVOKE <ACTION>
      ON
      <OBJECT>
      FROM   <SUBJECT>
For example, to revoke Bob's privileges to read the employee table, Alice might execute: 
      REVOKE SELECT
      ON     employee
      FROM   BOB;
The current authorizations in effect can be viewed by selecting from the USER_TAB_PRIVS view. In the following example, the columns are first formatted (more examples of this are given in a later section), and then the privileges for the user (table owner) ALICE are displayed. 
      COLUMN grantee    FORMAT A10
      COLUMN grantor    FORMAT A10
      COLUMN owner      FORMAT A10
      COLUMN table_name FORMAT A10
      COLUMN privilege  FORMAT A10
      SELECT * FROM USER_TAB_PRIVS ;

      GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE  GRA
      ---------- ---------- ---------- ---------- ---------- ---
      BOB        ALICE       EMPLOYEE  ALICE      SELECT     NO
A quick way to generate a list of GRANT statements for every table in your schema is to run a query on the catalog that forms the GRANT statements: 
      SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON '
      || table_name ||  ' TO username;'
      FROM   cat
      WHERE  table_type = 'TABLE';In the above example, username is 
the name of the user you would like to grant access to.
The result of this query is something like the following: 
      GRANT SELECT, INSERT, UPDATE, DELETE ON DEPARTMENT TO username;
      GRANT SELECT, INSERT, UPDATE, DELETE ON DEPENDENT TO username;
      GRANT SELECT, INSERT, UPDATE, DELETE ON DEPT_LOCATIONS TO username;
      GRANT SELECT, INSERT, UPDATE, DELETE ON EMPLOYEE TO username;
      GRANT SELECT, INSERT, UPDATE, DELETE ON PROJECT TO username;
      GRANT SELECT, INSERT, UPDATE, DELETE ON WORKS_ON TO username;This 
output can then be copied and pasted back in to put the grant statements into 
effect.

Exercise 5: GRANT and REVOKE

GRANT SELECT privileges to another member of your group. Have them query your STUDENTS table.

THen REVOKE the SELECT privilege from the STUDENTS table. Have your group member try to query the table after you have revoked access and see what happens.

Synonyms

In many cases, a schema is created under a single username but other users must have access to the tables, sequences and stored procedures. One possibility is to code all queries and applications to specifically access these database objects by providing a schema name. For example; 
      SELECT * FROM alice.employee;This query selects all of the 
columns and rows from the employee table in user alice's schema.
One problem with this method is that if the tables move to another user's schema, all of the references will need to change.

An alternative is to use Synonyms to provide a pointer to the schema and database objects. A Synonym is like a pointer in that is has a name that is recognized in the local schema that, when addressed, will resolve to the schema.object name in another user's schema.

Synonyms are created with the CREATE SYNONYM command: 

      CREATE SYNONYM <SYNONYM_NAME> FOR <SCHEMA>.<OBJECT>;
One can create synonyms for tables, views, sequences and stored procedures.

For example, if Bob wishes to have access to Alice's employee table, first, Alice would need to GRANT access to her table using the GRANT command, and then Bob would create a synonym using: 

      CREATE SYNONYM employee FOR alice.employee;
Now Bob can execute the following query: 
      SELECT * FROM employee;The above query will return all columns 
and rows from the employee table in Alice's schema.
If the tables are moved to another schema such as Abe's schema, then only the synonyms need to be dropped and recreated. All applications will run the same.

To generate a list of CREATE SYNONYM statements, use the following type of query: 

      SELECT 'CREATE SYNONYM ' || table_name ||
      ' FOR schema.' || table_name || ' ;'
      FROM   cat
      WHERE  table_type = 'TABLE';Where schema is the username 
containing the tables. This results in the following output that can be pasted 
back into SQL*Plus by each user to create the set of synonyms:
      CREATE SYNONYM DEPARTMENT FOR schema.DEPARTMENT ;
      CREATE SYNONYM DEPENDENT FOR schema.DEPENDENT ;
      CREATE SYNONYM DEPT_LOCATIONS FOR schema.DEPT_LOCATIONS ;
      CREATE SYNONYM EMPLOYEE FOR schema.EMPLOYEE ;
      CREATE SYNONYM PROJECT FOR schema.PROJECT ;
      CREATE SYNONYM STUDENTS FOR schema.STUDENTS ;
      CREATE SYNONYM WORKS_ON FOR schema.WORKS_ON ;
In this section, the SQL commands for creating, altering and deleting tables, views and sequences, and granting and revoking access to database objects have been introduced. A typical database may have a dozen or more related tables with several columns each. To facilitate the creation and deletion of a large number of tables, the CREATE statements can be placed into a file and executed using the SQL*Plus START command.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.