Re: how to verify current_schema?

  • From: Mladen Gogala <mgogala@xxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 12 May 2004 23:24:09 -0400

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: