Revoke Grant Oracle Privileges

You can grant and revoke privileges for different database objects in Oracle. We will first look at how to grant and remove table privileges, and then how to grant and remove privileges to functions and procedures in Oracle.

Grant Oracle Privileges for tables

You can give users different privileges to the tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or others.

Syntax to provide table privileges in Oracle/PLSQL

GRANT privileges ON object TO user;
privileges

Privileges for appointment. This can be any of the following values:

• object – the name of the database object to which you grant privileges. If you are granting privileges to a table, this must be the name of the table.
• user – the name of the user to whom the privilege will be granted.

Let’s look at some examples of giving table privileges in Oracle

For example, if you want to grant SELECT, INSERT, UPDATE and DELETE privileges to a table named suppliers for a user named trizor, you need to run the following GRANT sentence:

GRANT SELECT, INSERT, UPDATE, DELETE ON TO trizor;

You can also use the keyword ALL to specify that all permissions must be given to a user named trizor.

For example:

GRANT ALL ON TO trizor;

If you want to give your table only SELECT access for all users, you will give privileges with the public keyword.

For example:

GRANT SELECT ON TO public;

Revoke Privileges for tables

Once you have granted the privileges, you may have to cancel all or some of these privileges. To do this, you can run the revoke command. You can override any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER INDEX, or ALL.

Syntax for revoke privileges for a table in Oracle/PLSQL:

REVOKE privileges ON object FROM user;
privileges

Privileges for cancellation. This can be any of the following values:

• object – The name of the database object for which the privileges are revoked. If the privileges for a table were canceled, that would be the name of the table.
• user – The name of the user for whom the privileges are to be cancelled.

Consider some examples of how to override table privileges in Oracle/PLSQL.

For example, if you want to undo DELETE privileges for a table named suppliers for a user named anzor, then follow the following REVOKE sentence:

REVOKE DELETE ON FROM anzor;

If you want to override all table privileges for a user named anzor, you can use ALL keyword as follows:

REVOKE ALL ON FROM anzor;

If you have granted privileges to all users on the suppliers table and it is necessary to cancel these privileges, you can run the following REVOKE offer:

REVOKE ALL ON FROM public;

Grant Privileges on functions/procedures

When working with functions and procedures, you can give users the ability to perform these functions and procedures.

Syntax to grant EXECUTE privilege to functions/procedures in Oracle/PLSQL:

GRANT EXECUTE ON object TO user;
EXECUTE

Ability to compile a function/procedure. Possibility to execute the function/procedure directly.

• object – The name of the database object to which you grant privileges. If you grant EXECUTE privilege to a function or procedure, that would be the name of the function or procedure.
• user – The name of the user who will be granted EXECUTE privileges.

Consider some examples of how to grant EXECUTE privileges to functions or procedures in Oracle/PLSQL.

For example, if you have a function named Get_Value and you want to grant EXECUTE access to the user trizor, you would do the following GRANT sentence:

GRANT EXECUTE ON Get_Value TO trizor;

If you want to allow all users to run Get_Value, follow the GRANT sentence below:

GRANT EXECUTE ON Get_Value TO public;

Revoke Privileges on functions/procedures

Once you have granted EXECUTE privileges to a function or procedure, you may need to cancel those privileges for the user. To do so, you can run the REVOKE command.

Syntax for removing privileges to functions or procedures in Oracle/PLSQL:

REVOKE EXECUTE ON object FROM user;
EXECUTE

Ability to compile a function/procedure. Ability to directly execute the function/procedure.

• object – The name of the database object to which you cancel privileges. If you cancel the EXECUTE privilege of a function or procedure, that would be the name of the function or procedure.
• user – The name of the user who will be granted an EXECUTE privilege if you cancel it.

Let’s look at some examples of how to override EXECUTE privileges on functions or procedures in Oracle/PLSQL.

If you want to override EXECUTE privileges on a function named Get_Value for a user named maximus, follow the following REVOKE sentence:

REVOKE execute ON Get_Value FROM maximus;

If you have provided EXECUTE privileges to all users for a function called Get_Value and you want to override these EXECUTE privileges then execute the following REVOKE sentence:

REVOKE EXECUTE ON Get_Value FROM Public;

Grant, Revoke and Grant Table View to other user in Oracle