Re: Users vs. Schemas

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 -- Received on Wed Jun 27 2007 - 13:05:11 CDT