Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie: Schemas ?
It is definitely sensible and possible to have common schema owners.
The purpose of the schema entails security and logical partitioning.
Firstly, the administrator can create several users (schemas) and
grant each different priviledges. Oracle security is quite customizable.
Secondly, suppose you had two different subjects within one database,
say Finance and Service. It would probably be advisable to keep these
in two different schemas so that applications developed around these
can be separate.
As far as synonyms are concerned, suppose I am user dev with table
table1 that I own. You are user test. You need to view data in table1
so I wish to grant you read priviledges on table1. I use "grant select
on table1 to test" statement. Then when you want to see all of the
data in table1, you have to use "select * from dev.table1". But
if there is also a synonym created -
"create public synonym table1 for dev.table1"
then you only need to use "select * from table1". You don't have to
prefix the table name with the owner. This is where the synonyms fit
with your question. Hope this helps.
Don wrote:
>
> I understand that each user that can access a Oracle db has a schema.
>
> Does that means that we could end up with several thousand schemas!
>
> Is it sensible / possible to have a common schema owner?
>
> Does each developer create objects with the "common schema owner", or
> do they user their own "developer schema owner" objects? If so, how
> is the object "moved" to the "common schema owner"? Where do synonyms
> fit in here?
>
> I may be bringing some old luggage with me, from the AS/400 world,
> where no matter the "creator", the object owner we have defaulting to
> "pgmr" - no matter who makes it. Then the user community as a "group"
> has access to these "pgmr owned" objects - with most object rights
> except create / delete / erase or clear.
>
> Thx for clearing the fog a bit.
>
> Don
> dondealy (at) teleport {dot} com