Re: users and schemas and who can use which

Comments in-line.

brightspot wrote:
> Hi,
>
> 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.

Unless you are being paid by the hour to reinvent the wheel this is just plain dumb. This is not how Oracle works. I would suggest you read the Oracle concepts books.

> 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.
>
> Or do we have to set up our own user-schema table and do the query that
> way?
>
> TIA
> Chris

Sorry to be harsh here but you have absolutely no business trying to write an application against a database for which you apparently have zero knowledge of its concepts, architecture, capabilities, and security model.

Stop writing code.
Push yourself back from the keyboard.
Take a deep breath.
Go to http://tahiti.oracle.com and look up the Concepts books Also purchase Tom Kyte's book "Effective Oracle by Design"

Then, having learned something about Oracle, fix your design.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)