Friday, February 02, 2007

USER != SCHEMA

Most of us tend to bandy around the terms USER and SCHEMA is if they were synonyms, but in Oracle they are different objects. USER is the account name, SCHEMA is the set of objects owned by that user. True, Oracle creates the SCHEMA object as part of the CREATE USER statement and the SCHEMA has the same name as the USER but it is quite easy to demonstrate that they are different things.

SQL> conn b/b
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> grant select on my_tab to a
2 /

Grant succeeded.

SQL> conn a/a
Connected.
SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80404 ACTOR
80414 ACTOR_NT
45707 AP
45708 AP_PK
52765 ASSIGNMENT
49747 A_ID
52768 A_OBJTYP

7 rows selected.

SQL> alter session set current_schema=b
2 /

Session altered.

SQL> select * from my_tab
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
80116 BIG_PK
80114 BIG_TABLE
45709 BP
45710 BP_PK

SQL> select username, schemaname
2 from v$session
3 where sid in (select sid from v$mystat)
4 /
USERNAME SCHEMANAME
--------- -----------
A B

SQL>

The important thing to remember about alter session set current_schema is that it only changes the default schema. It does not change the privileges we have on that schema and it does not change the results when we issues queries that depend upon username, for instance against the USER_ views.

SQL> conn u2/u2
Connected.
SQL> select table_name from user_tables
2 /
TABLE_NAME
------------------------------
T1
T2
T3

SQL> select * from t1
2 /
COL1 COL2
---------- ----------
1 BBBBBBBBBB

SQL> grant select on t1 to u1
2 /

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select table_name from user_tables
2 /
TABLE_NAME
------------------------------
T1
T2

SQL> select * from t1
2 /
COL1 COL2
---------- ----------
1 AAAAAAAAAA

SQL> alter session set current_schema=U2
2 /

Session altered.

SQL> select * from t1
2 /
COL1 COL2
---------- ----------
1 BBBBBBBBBB

SQL> select * from t2
2 /
select * from t2
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from u1.t2
2 /

no rows selected

SQL> select table_name from user_tables
2 /
TABLE_NAME
------------------------------
T1
T2

SQL>

Of course, the confusion stems partly from the fact that there is a one-to-one correspondence between USER and SCHEMA, and a user's schema shares its name. But the fact that people who ought to know better use them interchangeably (me included) doesn't help matters.

Update


If you found this interesting you might also want to read a piece I have published on the CREATE SCHEMA statement.

12 comments:

Tim... said...

I must admit I'm completely guilty of this. In SQL Server, DB2 and mySQL, the distinction is really pronounced, but in Oracle they are so closely related that you can be forgiven for thinking thy are one and the same.

Cheers

Tim...

Anonymous said...

Hi Andrew

Interesting post. I talked about CREATE SCHEMA quite a while back on my blog. I have just written a post about the same linking here with a simple test of the CREATE SCHEMA command ad a link to the original blog entry. Its at http://www.petefinnigan.com/weblog/archives/00001000.htm - I just realised its also my 1000th blog post!

cheers Andrew

Pete

Anonymous said...

Great post, I admit to use the terms like synonyms but your example explain it very well.

Question
If user and schema are different objects(you mention that in your post), if we want to see user properties/description we must query *user view, how about schema, is there a view to do the same?

I did a quick search in Oracle documentation but everything mentions that schema is a collections of objects.
Also there is a statement CREATE SCHEMA but looks like a different thing.
This relation schema-user is somehow confusing for someone who comes from another database as SQL server,mySQL,etc and starts working with oracle.

Again, great example.

Anonymous said...

Actually, the distinction is clear when you start talking about enterprise users and enterprise roles in OID. You can create an enterprise user without a schema and give him a default schema in the database to connect to.

Best regards,
Slavik

APC said...

>> You can create an enterprise user
>> without a schema

Actually what you can do is create a user with an empty schema ;)

Cheers, APC

Anonymous said...

Actually what you can do is create a user with an empty schema ;)

Hi All,

I'm a total ora newbie - forgive me please. If I create a new user in the oem, is an empty schema automatically created? If so, I am not seeing it under the Schemas node in oem.

What is the 'best' way to create a new user and schema where the schema is manipulable in the oem?

Thanks - joe

Anonymous said...

All,

I answered my own question...
If a schema has no objects, it is not(by design) visible in the 9i oem.

Thanks - joe

Unknown said...

Thanks a lot

Rejishal said...

Thanks Andrew

ChaNani said...

Well explained dude.. U cleared it with a simple and easy example.

Unknown said...

Very Good Explaination...it cleared all my doubts on User Vs Schema.

Rafael Paz said...

Great! Thanks!