Oracle Plsql Grant Revoke Privileges
Oracle / PLSQL: Grant/Revoke Privileges
This Oracle tutorial explains how to grant and revoke privileges in Oracle with syntax and examples.
Description
You can GRANT and REVOKE privileges on various database objects in Oracle. We'll first look at how to grant and revoke privileges on tables and then how to grant and revoke privileges on functions and procedures in Oracle.
Grant Privileges on Table
You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax
The syntax for granting privileges on a table in Oracle is:
GRANT privileges ON object TO user;- privileges
- object
- user
The privileges to assign. It can be any of the following values:
| Privilege | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table. |
| INSERT | Ability to perform INSERT statements on the table. |
| UPDATE | Ability to perform UPDATE statements on the table. |
| DELETE | Ability to perform DELETE statements on the table. |
| REFERENCES | Ability to create a constraint that refers to the table. |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition. |
| INDEX | Ability to create an index on the table with the create index statement. |
| ALL | All privileges on table. |
The name of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name.
The name of the user that will be granted these privileges.
Example
Let's look at some examples of how to grant privileges on tables in Oracle.
For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a user name smithj, you would run the following GRANT statement:
GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named smithj. For example:
GRANT ALL ON suppliers TO smithj;If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword. For example:
GRANT SELECT ON suppliers TO public;Revoke Privileges on Table
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax
The syntax for revoking privileges on a table in Oracle is:
REVOKE privileges ON object FROM user;- privileges
- object
- user
The privileges to revoke. It can be any of the following values:
| Privilege | Description |
|---|---|
| SELECT | Ability to perform SELECT statements on the table. |
| INSERT | Ability to perform INSERT statements on the table. |
| UPDATE | Ability to perform UPDATE statements on the table. |
| DELETE | Ability to perform DELETE statements on the table. |
| REFERENCES | Ability to create a constraint that refers to the table. |
| ALTER | Ability to perform ALTER TABLE statements to change the table definition. |
| INDEX | Ability to create an index on the table with the create index statement. |
| ALL | All privileges on table. |
The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.
The name of the user that will have these privileges revoked.
Example
Let's look at some examples of how to revoke privileges on tables in Oracle.
For example, if you wanted to revoke DELETE privileges on a table called suppliers from a user named anderson, you would run the following REVOKE statement:
REVOKE DELETE ON suppliers FROM anderson;If you wanted to revoke ALL privileges on a table for a user named anderson, you could use the ALL keyword as follows:
REVOKE ALL ON suppliers FROM anderson;If you had granted ALL privileges to public (all users) on the suppliers table and you wanted to revoke these privileges, you could run the following REVOKE statement:
REVOKE ALL ON suppliers FROM public;Grant Privileges on Functions/Procedures
When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures.
Syntax
The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:
GRANT EXECUTE ON object TO user;- EXECUTE
- object
- user
The ability to compile the function/procedure. The ability to execute the function/procedure directly.
The name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.
The name of the user that will be granted the EXECUTE privileges.
Example
Let's look at some examples of how to grant EXECUTE privileges on a function or procedure in Oracle.
For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the user named smithj, you would run the following GRANT statement:
GRANT EXECUTE ON Find_Value TO smithj;If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:
GRANT EXECUTE ON Find_Value TO public;Revoke Privileges on Functions/Procedures
Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user. To do this, you can execute a REVOKE command.
Syntax
The syntax for the revoking privileges on a function or procedure in Oracle is:
REVOKE EXECUTE ON object FROM user;- EXECUTE
- object
- user
The ability to compile the function/procedure. The ability to execute the function/procedure directly.
The name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.
The name of the user that will be revoked the EXECUTE privileges.
Example
Let's look at some examples of how to revoke EXECUTE privileges on a function or procedure in Oracle.
If you wanted to revoke EXECUTE privileges on a function called Find_Value from a user named anderson, you would run the following REVOKE statement:
REVOKE execute ON Find_Value FROM anderson;If you had granted EXECUTE privileges to public (all users) on the function called Find_Value and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:
REVOKE EXECUTE ON Find_Value FROM public;