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