On 8/29/06, Jared Still <jkstill@xxxxxxxxx> wrote:
10:27:48 SQL>alter system enable restricted session;
I just learned something interesting about restricted session on 10g.
Here's how it works on 9i.
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning option JServer Release 9.2.0.7.0 - Production
SQL> create user scott identified by tiger;
User created.
SQL> grant dba to scott;
Grant succeeded.
SQL> alter system enable restricted session;
System altered.
SQL> connect scott/tiger@dv10 Connected. SQL> SQL> revoke dba from scott;
Revoke succeeded.
SQL> connect scott/tiger@dv10 ERROR: ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE. SQL>
Here's what happens on 10g:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, Oracle Label Security and Data Mining options
SQL> create user scott identified by tiger;
User created.
SQL> grant dba to scott;
Grant succeeded.
SQL> alter system enable restricted session;
System altered.
SQL> connect scott/tiger@dv11 ERROR: ORA-12526: TNS:listener: all appropriate instances are in restricted mode
Warning: You are no longer connected to ORACLE. SQL> connect scott/tiger Connected. SQL>
Restricted mode in 10g prevents connections via sqlnet. I also tried it with "sqlplus user/password@dv11 as sysdba" - same error.
I have not been able to find anything to explain this behavior, though the error message suggests that it has to do with RAC.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
