Restrict 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

sojha@testdb> 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.