Lesson 34 : GRANT and REVOKE

Grant and Revoke

Grant  statement  is used  to  grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.

Revoke is used to take back the granted privileges   on tables, views, sequences, synonyms, procedures to other users or roles.

Here are the set  of  object level privileges available,

SELECT -    To query the table, ie) to run a select statement .

INSERT   -  To  insert new rows to the table, ie) to use  insert statement.

UPDATE  -  To update rows in the tabl using update statement.

DELETE   -   To delete rows from the table using delete statement.

REFERENCES   - To create a constraint that refers to the table.

ALTER   -   To   make changes in table definition using alter table statement.

INDEX   -  To create an index on  tables with the create index statement.

EXECUTE  -  To execute the function/procedure.

ALL [PRIVILEGES]  - To grant all the privileges for the object that you received from another schema/user.

Syntax :

GRANT <privileges>  ON  <object name>  TO  <user/schema or Role or PUBLIC >    [WITH GRANT OPTION]

Where,

WITH GRANT OPTION  is to enable the grantee to grant the object privileges to other users and roles.

PUBLIC  -  Specify PUBLIC to grant the privileges to all users.

Example :

SQL> grant select,update on emp to scott;

Grant succeeded.

SQL> grant  index on emp to appadmin;

Grant succeeded.

SQL> grant  execute  on tax_calc_proc to scott;

Grant succeeded.

SQL> grant  ALL on emp to appadmin WITH GRANT OPTION ;

Grant succeeded.

To give SELECT priviege to all users in the database

SQL> grant  SELECT on emp to PUBLIC  ;

Grant succeeded.

REVOKE

- To take back the granted privileages  from other user or role or public

Syntax :

REVOKE <privileges>  ON  <object name>  FROM  <user/schema or Role or PUBLIC >

Examples :

SQL> revoke select,update on emp from scott;

Revoke succeeded.

SQL> revoke  index on emp from appadmin;

Revoke succeeded.

SQL> revoke  execute  on tax_calc_proc from scott;

Revoke succeeded.

SQL> revoke  ALL on emp from appadmin WITH revoke OPTION ;

Revoke succeeded.

SQL> revoke  SELECT on emp from PUBLIC  ;

Revoke succeeded.

 Important tables on view of privileges,

 USER_TAB_PRIVS_MADE  -  Privileges are granted by you to other users.

USER_TAB_PRIVS_RECD -  Privileges are granted to you by other users

USER_ROLE_PRIVS - Privileges are granted to roles

USER_COL_PRIVS_MADE -  Column level privileges are granted by you to other users

USER_COL_PRIVS_RECD - Column level privileges are granted to you by other users/