Re: users and schemas and who can use which

On 24 Aug 2005 10:41:17 -0700, "brightspot" <brightspot5_at_hotmail.com> wrote:

>I have a java program that prompts the user for his/her login name. I
>want to use the login name to find out which of the many schemas in my
>database he/she is allowed to see and present that list in a drop down
>menu.
>
>Is there any way in the Oracle system tables to set this up using roles
>or privileges or profiles (or anything else) and then querying the
>system tables to check to see who can see what? We are doing this in
>SQL Server (using system tables) now, and I was wondering if there is a
>similar mechanism in Oracle.

 Privileges aren't granted on a schema level, they're per object. Having said that, first stop could be ALL_USERS:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2114.htm#sthref1545

 You can then look into ALL_OBJECTS, ALL_TABLES, ALL_TAB_PRIVS, etc. for more detail.

 The "ALL_" data dictionary views show the objects you have some privileges on. "USER_" shows those you own, and "DBA_" shows all objects (and you often won't have access to that one anyway).

 They're all listed here in the REFERENCE manual:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

 The Concepts guide has a chapter on the data dictionary:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref77 http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/datadict.htm#g6891

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool