Wednesday, March 09, 2011

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>

No comments: