Re: how to verify current_schema?

On 05/12/2004 09:10:26 PM, Stephane Faroult wrote:
> Sorry to have to say that, but you need to give a look to your beloved
> manuals yourself. There is a difference between the current user, who
> determines what to you are allowed to do (although that in some old
> Oracle 7 version ... but I am erring) and the current schema, ie
> basically who is the default owner of the objects you reference when you
> don't specify this owner name explicitly. By default, the current schema
> IS the current user. But afterwards they can live independent lives.
> Jacques' was the correct answer.

Ryan asked for CURRENT USER and that is what I gave him. As for the schema, it can be changed. Current user, on the other hand, cannot be changed. If I do the following:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options  

SQL> alter session set current_schema=scott;  

Session altered.  

SQL> select sys_context('USERENV','SESSION_USER') from dual;  

SYS_CONTEXT('USERENV','SESSION_USER')

--------------------------------------------------------------------------------OPS$MGOGALA
 

SQL> select sys_context('USERENV','SESSION_SCHEMA') from dual;  

SYS_CONTEXT('USERENV','SESSION_SCHEMA')

--------------------------------------------------------------------------------SCOTT
 

SQL> I have OPS$MGOGALA for current user and and SCOTT for current schema. Let's see how it works:
SQL> select username from v$session where type='USER';  

USERNAME

OPS$MGOGALA   SQL> V$SESSION reports a session by OPS$MGOGALA. Let's see about privilege level:  

SQL> alter system set timed_statistics=true;  

System altered.  

SQL>
So, I can do "ALTER SYSTEM". Let's see what happens if I connect as SCOTT:  

SQL>
SQL> connect scott/tiger
Connected.
SQL> alter system set timed_statistics=true;  alter system set timed_statistics=true
*
ERROR at line 1:
ORA-01031: insufficient privileges    

Obviously, SCOTT cannot do "ALTER SYSTEM". That means that, even with my schema altered, I still have the same privileges as I had when I was not disguised as SCOTT.

SQL> connect /
Connected.
SQL> desc emp
ERROR:
ORA-04043: object emp does not exist    

SQL> alter session set current_schema=scott;  

Session altered.  

SQL> desc emp

 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------- EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 

SQL> The above means that when I change the current schema, the new schema is used for name resolution. All other characteristics of the session are inherited from the username that I originally connected as. That means that the username is much more important characteristics then the schema. As Ryan asked for the USERNAME and as I think that the USERNAME is more important then the schema, I gave him username. There is no correct or wrong answer to the question that Ryan asked, because the question itself was rather dubious and the only correct answer would be 42. If you ask me whether I know about the possibility of changing schema, I do, ever since the forementioned V7.

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------