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

On 8/29/06, Jared Still <jkstill_at_gmail.com> 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_at_dv10
Connected.
SQL>
SQL> revoke dba from scott;

Revoke succeeded.

SQL> connect scott/tiger_at_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_at_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_at_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

--
http://www.freelists.org/webpage/oracle-l