Re: [Q] create second schema under a account

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 30 Dec 2004 16:15:01 +1100
Message-ID: <41d38ed7$0$5126$afc38c87@news.optusnet.com.au>

Serge Rielau wrote:

> Howard J. Rogers wrote:
> 

>> aaa wrote:
>>
>>> we have ORACLE 9.2.0.6 on UNIX server. I know ORACLE support
>>> multiple schemas
>>> under a user account.
>>
>>
>>
>> You know wrongly, I am afraid. Since a user more or less *is* a schema
>> (and vice versa), then what you say you know to be the case cannot
>> possibly be true.
>>
>>> Does any know how to do following and give me a sample:
>>>
>>> 1. create second schema under a user
>>
>>
>>
>> Impossible, taking what you ask for at face value.
>>
>>> 2. create table or view under second schema
>>
>>
>>
>> This is do-able. Any user, provided they have been granted an 'ANY'
>> privilege (for example, 'create any table') can exercise that
>> privilege in anybody else's schema. So for example, I could do this:
>>
>> connect scott/tiger
>> create table elmer.employees (col1 char(4));
>>
>> ...and the EMPLOYEES table has just been created in ELMER's schema.
>> The table belongs to Elmer, and Elmer is forever afterwards considered
>> its owner. But it was actually created by someone else. Is that what
>> you are after?
>>
>> If not, then you're going to be disappointed! :-)
>>
>> Regards
>> HJR

> 
> Given that user management is part of Oracle, isn't it possible 
> (feasable) to create users which have no real life equivalent for the 
> sole purpose of organizing objects? Say to hold all objects which belong 
> to a certain package...

Of course. I don't know if they have an official name, but I call them "application schemas": a user account that is created initially locked simply for the purposes of owning objects, and no-one can actually log on as that user. The converse, of course, also happens all the time: real-life users who don't actually own or create any objects of their own, but merely read or modify the contents of other people's objects. So, yes, you can have a schema that has no real human being attached, and real human beings that have no schema attached.

But that misses the essential point as far as the OP is concerned: that you cannot have an Oracle user account which is not *potentially* a schema owner, and neither can you have an Oracle schema without first creating an Oracle user account.

Rather than confuse the OP with talk about "real life equivalents", let's just get the plain message across first: a user=schema.

Regards
HJR Received on Wed Dec 29 2004 - 23:15:01 CST