| Difference between Schema and User [message #59800] |
Tue, 23 December 2003 19:15  |
scott
Messages: 73
Registered: September 1999
|
Member |
|
|
Hi All,
This is really confusing.
Oracle Docs say that
A user is created and a corresponding schema is created.Username and Schema are often used interchangeably.
Here is what I see in my Oracle Enterprise Manager.
Just to have some data I choose the HR schema during database creation. Under the Schema tree I see the following
HR
OUTLN
PUBLIC
SYSTEM
SYS
.......
.......
I went ahead and created a user called Scott_007 with the following script
CREATE USER "SCOTT_007" PROFILE "DEFAULT"
IDENTIFIED BY "tiger" DEFAULT TABLESPACE "USERS"
ACCOUNT UNLOCK;
GRANT "CONNECT" TO "SCOTT_007";
I refreshed the OEM view and found user SCOTT_007 under the Users and not under SCHEMA.
Can someone explain the flaw in my thinking. I was hoping to see SCOTT_007 under schemas and the user having his own tables, stored procedures, etc
What is SCHEMA HR and User Scott_007 differing in.
Thanks
Scott
|
|
|
|
| Re: Difference between Schema and User [message #59801 is a reply to message #59800] |
Tue, 23 December 2003 20:07  |
scott
Messages: 73
Registered: September 1999
|
Member |
|
|
I got it working,
I had to create a single table and it showed up under the Schema. I have a quick question though. If a company has 1000 users will this not create 1000 schemas which would be an aweful waste of space. Is there something like SQL Server where in you create a User and give him access to a database ?
Before I invite your ire for bringing SQL Server into picture I would like you all to know that my harted towards any Windozeeeee product is as intense as yours.
Thanks
Scott
|
|
|
|
| Re: Difference between Schema and User [message #59803 is a reply to message #59801] |
Tue, 23 December 2003 21:34 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
See here and here for a quick explanation of the difference between a schema and a user.
To give users access to another users' schema, you would need to give the necessary privileges to these users. Here's a little more explanation. Basically it comes down to this:
- you have app_owner, the owner of all the application objects (tables, views, procedures,...)
- you create a public synonym for each object you want to make accessible to other users (that way you don't have to prefix each call to those objects with the owners' name).
- you connect either as the app_owner or a DBA user and grant the necessary privileges to the users/roles. Most likely, you want to make use of roles because they are easier to maintain.
Now, as for the remark that 1000 schemas are a waste of space: no. You create a user (which is more or less the same as a schema), but the space for his objects (in tablespace x or y) is not yet allocated. At this moment, before objects are created, you have only defined where you want to store them.
Here are a few helpful links:
http://otn.oracle.com (Oracles' Technology Network, technical website of Oracle Corp.)
http://docs.oracle.com (refers directly to the documentation part of otn)
http://tahiti.oracle.com (an alternative documentation source of Oracle, I find it faster to retrieve SQL or PL/SQL info)
These sites need a (spam)free otn account.
Check also http://asktom.oracle.com (Tom Kyte answers most questions in a crystal clear way).
BTW: you can search the board, a lot of questions have already been answered before.
Regards,
MHE
[Updated on: Thu, 02 February 2006 02:44] Report message to a moderator |
|
|
|
| Re: Difference between Schema and User [message #59809 is a reply to message #59800] |
Wed, 24 December 2003 05:03 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
dont worry about what OEM tells you..its just another tool. Basically for Schemas, its lists out users with atleast one object(ie where count(*) from dba_objects >=1) and for User list,it just lists out everyone in dba_users.
When a new user is created , an empty schema is associated with that and that user 'owns' his schema.
Schema is nothing but a collection of database objects associated with a particular database user.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
