Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Accessing tables on multiple schema

Re: Accessing tables on multiple schema

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 17 Aug 2007 16:15:01 +0200
Message-ID: <46c5ad6f$0$235$e4fe514c@news.xs4all.nl>

<setsun_at_gmail.com> schreef in bericht
news:1187359445.199159.189920_at_i38g2000prf.googlegroups.com...
> 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
>
>
>

Sorry, I thought you mentioned a Oracle DBA, but you said Oracle DB..

Do you have the possibility to change the design? Maybe adding a 'schema' column to the tables and using one table could help (so put all data in one table and 'mark' the rows with the owner). But you might have to change check-constraints, unique keys etc. Is this application for serving multiple users with a 'private' database in one instance? Maybe you should look at Virtual Private Databases....

Still I wonder what is the rationale behind all this....

Shakespeare Received on Fri Aug 17 2007 - 09:15:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US