Re: Accessing tables on multiple schema

Shakespeare,

Thanks for your reply. Well, my initial though was to implement the invoker rights concepts in the data retrieval schema which would fit the bill perfectly from a database design perspective.

In such a design, set up users for each schema as follows

1. USER_RETRIEVE for SCHEMA_RETRIEVE
2. USER_A for SCHEMA_A
3. USER_B for SCHEMA_B
4. USER_C for SCHEMA_C

When the user log on to DB as USER_A, he can execute SPs on SCHEMA_RETRIEVE which would look up SCHEMA_A and look up SCHEMA_B when logged on as SCHEMA_B etc.

But in our web application, it is possible that a single user can query the date in any of the schema mentioned above. In that case, if he were to switch between the schema, database connection management would be an issue if the connections were not properly released.

Keeping this in mind, I checked with my colleague ( who is a Web application developer and NOT a DBA) who suggested that it would be good to pass the schema names from the application ( base on user selection ) and resolve them within the Stored Proc. ( He must have had a tough time in managing DB connections from the application ! )

I tried searching for a solution and all I could see is to do use dynamic SQLs inside stored procedures and functions.. and that seems to be much more messy.

Thank you very much for the quick response. Arun Received on Fri Aug 17 2007 - 09:04:05 CDT