Re: users and schemas and who can use which

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 25 Aug 2005 07:46:03 -0500
Message-ID: <uu0hef271.fsf@rcn.com>

On Wed, 24 Aug 2005, spamcontrol_at_iion.com wrote:
>
> "brightspot" <brightspot5_at_hotmail.com> wrote in message
> news:1124909186.523871.75550_at_g14g2000cwa.googlegroups.com...

>> Sybrand, sorry to be vague.  Is there a system table in oracle that
>> contains the users and the schemas they are allowed to access?  For
>> example, JoeUser can use (select, insert, delete, etc) schema1,
>> schema2 and schema3.  MaryUser can use schema1 and schema4.  I can
>> get information about tables inside a schema, but I need this
>> information about the schema itself.
>>

>
> To answer your question more precisely, users aren't granted access to
> schemas. Note, don't make the mistake of comparing Oracle to SQL
> Server, thinking that a SQL Server database is equivalent to a schema.
> It doen't work that way.
>
> User's are granted access to objects, not to schemas. To get a better
> handle of what schemas are, consider them users created using CREATE
> USER user IDENTIFIED BY xxxxxx;
>
> However, you can see the schema in which an object was created by
> inspecting the OWNER column of the USER_TAB_PRIVS view.
>
> Type: describe user_tab_privs
> at the SQL> prompt to see a definition of this view.
>
> Hope this helps.

The hard thing to solve about what the OP is saying is that there are different ways privileges to tables can be granted. The privilege can be granted directly. This can be seen in DBA_TAB_PRIVS. But, the privs can be granted to a role, which can also be seen in DBA_TAB_PRIVS but then you have to go to other tables to get what roles are granted to the user in guestion. Then, the priv can be granted to a role which that role can then be granted to another role, which can then be granted to another ... which can then be granted to a user. CONNECT BY anyone?

Its not a simple thing to solve in Oracle.

-- 
Galen deForest Boyer

Received on Thu Aug 25 2005 - 07:46:03 CDT