Using Grant/Revoke Privileges in Oracle

This article is written about how to provide and revoke privileges in Oracle with syntax and examples.

Description

You can GRANT and REVOKE privileges on a number of database objects in Oracle. We’ll first seem to be at how to supply and revoke privileges on tables and then how to supply and revoke privileges on functions and techniques in Oracle.

Grant Privileges on Table

You can furnish customers a variety of privileges to tables. These privileges can be any aggregate 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 The privileges to assign. It can be any of the following values: Privilege Description SELECT Ability to function SELECT statements on the table. INSERT Ability to function INSERT statements on the table. UPDATE Ability to operate UPDATE statements on the table. DELETE Ability to function DELETE statements on the table. REFERENCES Ability to create a constraint that refers to the table. ALTER Ability to operate ALTER TABLE statements to alternate the table definition. INDEX Ability to create an index on the desk with the create index statement. ALL All privileges on table. object The title 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. user The identify of the person that will be granted these privileges.

Example

Let’s seem to be at some examples of how to provide privileges on tables in Oracle.

For example, if you desired to supply SELECT, INSERT, UPDATE, and DELETE privileges on a desk referred to as suppliers to a consumer name smithj, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;

You can additionally use the ALL keyword to indicate that you desire ALL permissions to be granted for a consumer named smithj. For example:

GRANT ALL ON suppliers TO smithj;

If you wanted to furnish only SELECT get entry to on your table to all users, you may want to provide 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 additionally need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any aggregate of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for revoking privileges on a desk in Oracle is:

REVOKE privileges ON object FROM user;

privileges The privileges to revoke. It can be any of the following values: Privilege Description SELECT Ability to operate SELECT statements on the table. INSERT Ability to perform INSERT statements on the table. UPDATE Ability to function UPDATE statements on the table. DELETE Ability to operate DELETE statements on the table. REFERENCES Ability to create a constraint that refers to the table. ALTER Ability to function ALTER TABLE statements to trade the desk definition. INDEX Ability to create an index on the desk with the create index statement. ALL All privileges on table. object 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. user 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 desired to revoke DELETE privileges on a table referred to as suppliers from a person named anderson, you would run the following REVOKE statement:

REVOKE DELETE ON suppliers FROM anderson;

If you desired to revoke ALL privileges on a table for a user named anderson, you ought to use the ALL key-word as follows:

REVOKE ALL ON suppliers FROM anderson;

If you had granted ALL privileges to public (all users) on the suppliers table and you desired to revoke these privileges, you ought to run the following REVOKE statement:

REVOKE ALL ON suppliers FROM public;

Grant Privileges on Functions/Procedures

When dealing with features and procedures, you can supply users the ability to EXECUTE these features and procedures.

Syntax

The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:

GRANT EXECUTE ON object TO user;

EXECUTE The potential to collect the function/procedure. The potential to execute the function/procedure directly. object The title 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 characteristic title or the method name. user The identify of the consumer that will be granted the EXECUTE privileges.

Example

Let’s appear at some examples of how to grant EXECUTE privileges on a characteristic or procedure in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the person named smithj, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO smithj;

If you desired 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 feature 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 system in Oracle is:

REVOKE EXECUTE ON object FROM user;

EXECUTE The ability to bring together the function/procedure. The capability to execute the function/procedure directly. object 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 identify or the system name. user The title of the person that will be revoked the EXECUTE privileges.

Example

Let’s look at some examples of how to revoke EXECUTE privileges on a feature or manner in Oracle.

If you wanted to revoke EXECUTE privileges on a feature referred to as Find_Value from a person 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 feature known as Find_Value and you desired to revoke these EXECUTE privileges, you may want to run the following REVOKE statement:

REVOKE EXECUTE ON Find_Value FROM public;