Re: was (RE: SGADEF file?) is How to best detect database health

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

Other related posts: