setsun_at_gmail.com wrote:
> On Aug 17, 9:19 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>> On Aug 17, 9:41 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
>>
>>
>>
>>
>>
>>> set..._at_gmail.com wrote:
>>>> Hello Oracle Experts!
>>>> Unfortunately, I am given the task of designing database for a web
>>>> based application. Basically my Oracle DB would consist of several
>>>> schema's with identical tables something like the following
>>>> SCHEMA_A
>>>> TBL_ONE
>>>> TBL_TWO
>>>> SCHEMA_B
>>>> TBL_ONE
>>>> TBL_TWO
>>>> SCHEMA_C
>>>> TBL_ONE
>>>> TBL_TWO
>>>> and there would be another data retrieval schema consisting of Stored
>>>> Procs, Functions, Views etc that would act upon one of the above
>>>> schemas based on user selection.
>>>> SCHEMA_RETRIEVE
>>>> PROC_GET_FROM_TBL_ONE
>>>> PROC_GET_FROM_TBL_TWO
>>>> My question is: Is it possible for me to provide the schema name as an
>>>> input parameter to the procedures so that the procedure resolves the
>>>> schema and the table names at runtime?
>>>> i.e, is it possible to write something like the following inside the
>>>> procedure?
>>>> SELECT COUNT(1) INTO tbl_count_one FROM <SCHEMA_NAME>.TBL_ONE ?
>>>> I am aware of the INVOKER RIGHTS concept in Oracle. But I would want
>>>> to abstain from it as I would not want to mainitain multiple
>>>> connection states in my web application.
>>>> I am unsure if similar queries have been posted before. I would
>>>> appreciate if you could suggest a solution or point me to articles
>>>> suggesting solution to my problem.
>>>> Thank you very much in advance
>>>> Arun
>>> Instead of these different schemas, why not implement a Virtual Private
>>> Database (VPD)? You'll have less to manage. Each user will only see
>>> their own data. And you appplication development will probably be much
>>> easier.
>>> HTH,
>>> Brian
>>> --
>>> ===================================================================
>>> Brian Peasland
>>> d...@nospam.peasland.nethttp://www.peasland.net
>>> Remove the "nospam." from the email address to email me.
>>> "I can give it to you cheap, quick, and good.
>>> Now pick two out of the three" - Unknown
>>> --
>>> Posted via a free Usenet account fromhttp://www.teranews.com-Hide quoted text -
>>> - Show quoted text -
>> If I read this thread correctly VPD isn't an option as the web
>> application has the ability to process data across schemas, something
>> VPD won't provide. I would tend to agree with the suggestion to add
>> an OWNER or SCHEMA column to the table design to create a 'master'
>> table (instead of three or more identical schemas) to identify whose
>> data is represented, thus the 'owner' could be passed to the stored
>> procedure and only the relevant data would be returned.
>>
>> David Fitzjarrell- Hide quoted text -
>>
>> - Show quoted text -
>
> David et al
>
> Thanks for the inputs.
> I had thought about David/Shakespear's option to add a column to
> 'master' table (for each identical table in all schema) and
> identifying records by passing the schema name. But since this
> application pertains to holding financial data for a Bank catering to
> different geogrophical zones, and any screwup in inserting the schema
> name to the master table would result in inconsitency ( and I would be
> fired! )...
>
> Looking at the options I have, I think I need to design based on
> INVOKER RIGHTS model,
>
>
> Thank you very much.
> Arun
>
>
>
>
>
Then I would once again suggest the VPD. If adding a column to a master
table could solve your issue, but you are worried about others accessing
the wrong data, then the VPD could certainly fit the bill! This way, you
guarantee that only those who can access certain rows of data in the
table get the access to those rows.
Cheers,
Brian
--
===================================================================
Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
--
Posted via a free Usenet account from http://www.teranews.com
Received on Sun Aug 19 2007 - 22:34:35 CDT
