Information on Database in restricted mode.

When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.
SQL> STARTUP RESTRICT;

— shows database is in restricted mode
12:49:30 @> select logins from v$instance;

LOGINS
———-
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;

Grant succeeded.

To revoke access restricted mode access
SQL> REVOKE RESTRICTED SESSION FROM scott;

How to find the users that have restricted session access:
— find users who have been granted through the role (displays 1 level)
SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ — find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

GRANTEE GRANTED
———— ————————————-
SCOTT User (Direct)
SYS DBA (Role)
SYSTEM DBA (Role)

To change from restricted mode to unrestricted/active mode, which can switch without restarting the database.

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
ALLOWED

Also database can be placed in restricted mode by running the following DDL without restarting the database.

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
RESTRICTED

Note: There seems to be an issue when disabling restricted mode in 10.2.0.1, you can try to shutdown from the same session where restricted session was disable was executed from.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction – commit or rollback first

You can work around this by executing shutdown from a new session or executing commit/rollback from the session where restricted session was disabled.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s