Re: Users vs. Schemas

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 27 Jun 2007 16:56:04 -0700
Message-ID: <1182988564.281324@bubbleator.drizzle.com>

Brian Peasland wrote:

> Matthias Hoys wrote:
>> "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message 
>> news:1182967511.040233.50050_at_k79g2000hse.googlegroups.com...
>>> On Jun 27, 11:14 am, HansF <fuzzy.greybe..._at_gmail.com> wrote:

>>>> On Jun 27, 7:58 am, kilik3..._at_gmail.com wrote:
>>>>
>>>>> I'm an oracle novice and I'm a little confused by Users vs. Schemas.
>>>>> I had thought that User == Schema.
>>>>> However, while using the schema browser feature in TOAD GUI I noticed
>>>>> that under the 'Schemas' node there is an entry for 'PUBLIC' for which
>>>>> there is no corresponding entry under the 'Users' node.
>>>>> Every other Schema/User has an entry in both.
>>>>> Why is PUBLIC different?
>>>>> Dose User == Schema OR does User != Schema?
>>>> Yes, there is a lot of confusion around this.
>>>>
>>>> I look at it this way:
>>>>
>>>> - A schema is a namespace for the definitions of objects.
>>>> - A user is an identifier to which resources may be granted.
>>>>
>>>> One resource that may be attached to an Oracle User is the schema
>>>> namespace. In which case the schema namespace and the Oracle Userid
>>>> have the same identifier.
>>>>
>>>> Since it is so commonly required (and partly due to history), when an
>>>> Oracle User is created the associated schema namespace is always
>>>> created.
>>>>
>>>> However, as you have seen, the schema does not necessarily imply
>>>> user.
>>>>
>>>> If you take this one large step further, you will find that PUBLIC is
>>>> a special case ... it is neither a USER nor a ROLE, but rather a
>>>> [url=http://www.petefinnigan.com/weblog/archives/00000060.htm]'USER
>>>> GROUP'[/url].
>>>>
>>>> /Hans
>>>> --
>>>> Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
>>>> *** Feel free to correct me when I'm wrong!
>>>> *** Top posting [replies] guarantees I won't respond.

>>> Pete makes an interesting argument but Oracle has published a paper in
>>> May of 2007 which may also be of interest to those with access to
>>> metalink:
>>>
>>> PUBLIC : Is it a User, a Role, a User Group, a Privilege ?
>>> Document  234551.1
>>>
>>> The ID is created with the command: "create role public".
>>>
>>> My argument was that is is a schema.
>>>
>>> HTH -- Mark D Powell --
>>>
>>>
>>
>>  I think of a schema as every user who owns objects, so you can have 
>> users with no corresponding schema. But PUBLIC is the exception on the 
>> rule :-)
>>
>>
>>
> 
> I always thought of the schema as the collections of objects owned by a 
> particular user. And some users have no objects.
> 
> 
> Cheers,
> Brian

I think it can be defined rather clearly by comparing what happens when you issue:

CREATE USER
and
CREATE SCHEMA One creates a user with quotas and privileges ... the other creates objects owned by a user.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Jun 27 2007 - 18:56:04 CDT