GRANT and REVOKE Privileges in Oracle SQL

Oracle PLSQL allows us to GRANT and REVOKE privileges to/from an Oracle user. Privileges can be termed permissions given to a user so that he/she can interact or is able to modify the database.

In Oracle PLSQL terms:

  • Allowing or giving permission to a user is called as GRANT
  • Disallowing or taking away permission from a user is called REVOKE

The privileges can be a combination of any of the following statements or commands:

Privilege Name Description
Select  Oracle user is allowed to query the database
INSERT Oracle user can insert new records in database table
UPDATE Oracle user can update records in database table
DELETE Oracle user can delete records in database table
REFERENCES Oracle user can create constraints that refers to a database table
ALTER Oracle user can change the definition of table using ALTER statement
INDEX Oracle user can create index on a database table using CREATE INDEX statement

GRANT Privilege to USER

Oracle PLSQL syntax to GRANT a Privilege to a user is:

GRANT PRIVILEGES ON OBJECT TO USER;


Example: Granting Privileges to a USER.

Suppose we want to grant SELECT, INSERT, UPDATE and DELETE privileges to ‘SCOTT’ user on employee table, then we have to execute the following statement;

GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO SCOTT;

The above statement will GRANT SELECT, INSERT, UPDATE and DELETE privileges to SCOTT user on employee table.


Example: Using ALL keyword for granting privileges to a user.

We can also use the ALL Keyword to grant all the privileges to a user.
e.g.

GRANT ALL ON employee TO SCOTT;

The above statement will grant all the privileges to SCOTT user on employee table.


Example: Grant privilege to all users

Oracle PLSQL allows us to grant privileges to all users at once using PUBLIC keyword
e.g.

GRANT SELECT ON employee TO PUBLIC;

Revoking Privileges from User:

Oracle PLSQL syntax to REVOKE Privilege from a user is:

REVOKE PRIVILEGES ON OBJECT FROM USER;

Example: Revoking Privileges from a USER.

Suppose we want to revoke SELECT, INSERT, UPDATE and DELETE privileges from ‘SCOTT’ user on employee table, then we have to execute the following statement;

REVOKE SELECT, INSERT, UPDATE, DELETE ON employee
FROM SCOTT;

Example: Using ALL keyword for revoking privileges to a user.

We can also use the ALL Keyword to revoke all the privileges from a user.
e.g.

REVOKE ALL ON employee FROM SCOTT;

The above statement will revoke all the privileges from SCOTT user on employee table.


Example: REVOKE privilege from all users

Oracle PLSQL allows us to revoke privileges from all users at once using PUBLIC keyword
e.g.

REVOKE SELECT ON employee FROM PUBLIC;

Granting Privileges to Oracle PLSQL Procedures and Functions

Oracle PLSQL allows us to GRANT PRIVILEGES to procedures and functions.

These privileges allow a user to execute a procedure or function.

Privilege Name Description
EXECUTE Oracle user can compile and execute the procedure or function

Oracle PLSQL syntax to GRANT EXECUTE privilege to a user on a function or procedure is:

GRANT EXECUTE ON object_name TO USER;

Example of GRANT EXECUTE privilege to SCOTT on ‘create_new_employee_proc’ procedure

GRANT EXECUTE ON create_new_employee_proc TO SCOTT;

Example to GRANT execute privilege to all users on ‘create_new_employee_proc’ using PUBLIC Keyword

GRANT EXECUTE ON create_new_employee_proc TO PUBLIC;

Revoking Privileges from Oracle PLSQL Procedures and Functions

Oracle PLSQL allows us to revoke privileges, on procedures and functions, which we have granted to users earlier using the REVOKE command.

Oracle PLSQL syntax to REVOKE privilege is:

REVOKE EXECUTE ON object_name FROM USER;

Example: REVOKE execute privilege from SCOTT on create_new_employee_proc

REVOKE EXECUTE ON create_new_employee_proc FROM SCOTT;

Example: Using PUBLIC keyword to revoke execute privilege from all users in create_new_employee_proc

REVOKE EXECUTE on create_new_employee FROM PUBLIC;

Tagged , , , . Bookmark the permalink.