Restricted Session

(Tested on an Oracle 9 database.)

You can use the RESTRICTED SESSION facility to control logins to the database. It can be used to prevent normal users from logging into a database while a DBA is carrying out maintenance. There are two ways to do this:

You can do it when you open the database. In the example below, an instance is started twice, once in restricted mode. You can tell if the instance is in restricted mode by querying V$INSTANCE:

SQL> startup open

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL>

Or you can do it while the database is running:

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL>

Enabling restricted session stops new logins except by users with the RESTRICTED SESSION privilege. Existing connections are not terminated (although you can kill them yourself if necessary). In the next example, RESTRICTED SESSION is enabled. Andrew keeps the session he had beforehand but cannot start a new one:

SQL> alter system enable restricted session;

System altered.

SQL> select username from v$session

  2  where username is not null;

USERNAME

------------------------------

SYS

ANDREW

SQL>conn andrew/reid

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

SQL>

The DBA role has the RESTRICTED SESSION privilege:

SQL> select grantee from dba_sys_privs

  2  where privilege = 'RESTRICTED SESSION';

GRANTEE

------------------------------

DBA

SQL>

I’m not sure why you would want to do this but you can grant the privilege directly to a user. This allows him to login even when the database is in restricted mode:

SQL> conn andrew/reid

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

SQL> grant restricted session to andrew;

Grant succeeded.

SQL> conn andrew/reid

Connected.

SQL>

RESTRICTED SESSION is an instance level setting so it disappears when you bounce the database (unless you reopen it with the RESTRICT clause as shown above):

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL>