GRANT REVOKE PRIVILEGES

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 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.

Object is 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.

user is 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 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.

Object is 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 is 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 means the following:

Privilege : EXECUTE
Description : Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
 

Object is 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.

user is 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;

Privilege : EXECUTE
Description : 
EXECUTE Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
 
Object is 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.

user is 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;

Startup and Shutdown stages

Startup and Shutdown stages

Before starting the instance we need to set instance name to be opened at os prompt
$export ORACLE_SID=<sid>

Sid = instance name

SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                   788368 bytes
Variable Size             145750128 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.

When Oracle is trying to open your database, it goes through three distinct stages, and each of these is listed in the startup output listed previously. These stages are:
The Startup (nomount) Stage
During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured
* Startup (nomount)
There are some types of Oracle recovery operations that require the database to be in nomount stage.
SQL> startup nomount

The Mount Stage

When the startup command enters the mount stage, it opens and reads the control fil
SQL> startup mount
If you have already started the database instance with the startup nomount command, you might change it from the nomount to mount startup stage using the alter database command:
SQL> alter database mount;
The Open Oracle startup Stage
To open the database, you can just use the startup command as seen in this example
SQL> startup
If the database is mounted, you can open it with the alter database open command as seen in this example:
SQL> alter database open;

Shutting Down a Database
To shut down a database and instance, you must first connect as SYSOPER or SYSDBA.

·         Shutting Down with the NORMAL Clause
·         Shutting Down with the IMMEDIATE Clause
·         Shutting Down with the TRANSACTIONAL Clause
·         Shutting Down with the ABORT Clause


Shutting Down with the NORMAL Clause
To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:

SHUTDOWN NORMAL
The NORMAL clause is optional, because this is the default shutdown method if no clause is provided.
Normal database shutdown proceeds with the following conditions:
·         No new connections are allowed after the statement is issued.
·         Before the database is shut down, the database waits for all currently connected users to disconnect from the database.
The next startup of the database will not require any instance recovery procedures.


Shutting Down with the IMMEDIATE Clause
Immediate database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
·         Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.
The next startup of the database will not require any instance recovery procedures.
Shutting Down with the TRANSACTIONAL Clause
When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:

SHUTDOWN TRANSACTIONAL
Transactional database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         After all transactions have completed, any client still connected to the instance is disconnected.
·         At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.
The next startup of the database will not require any instance recovery procedures.
Shutting Down with the ABORT Clause
You can shut down a database instantaneously by aborting the database instance.
When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:
SHUTDOWN ABORT
An aborted database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         Current client SQL statements being processed by Oracle Database are immediately terminated.
·         Uncommitted transactions are not rolled back.
·         Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users