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;
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 (See ORA-01031.ora-code.com): 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 > 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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
