Re: Users vs. Schemas

On Jun 27, 7:56 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Brian Peasland wrote:
> > Matthias Hoys wrote:
> >> "Mark D Powell" <Mark.Pow..._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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

For explaining the differences between a USER and a SCHEMA to a developer looking at the CREATE USER and CREATE SCHEMA command results is a good idea.

But is there a consensus of how best to describe PUBLIC?

HTH -- Mark D Powell -- Received on Thu Jun 28 2007 - 08:58:31 CDT