Re: Oracle & Views

Yes. If I understand you correctly, you're looking at something like this:

User1 has privledges to access some tables in some fashion (not too vague, I hope). User2 has a different set of privledges.

They both need limited access to the ACCOUNT table (in a third schema?)

Yes, views would be one way of limiting access. You set up the views to limit access based on the ranges (in a WHERE or HAVING clause) and assign one view to User1 and the other to User2.

The application could treat the views as if they were actual tables for selecting the data; in fact, there is no reason for the app or the user to even know they are reading from a view rather than a table.

I'm guessing that you are using multiple Oracle users and giving them access to some centralized schema. If so, you can set up your views as something like ACCOUNT_USER1, ACCOUNT_USER2 then point your synonyms to these views instead of the base table(ACCOUNT).

Does that help?

-Dean

On Sat, 27 Mar 1999 11:19:31 +0800, SHANX <nasanker_at_netvigator.com> wrote:

>This is to take the views concept a little bit further.
>Im not experienced in intricacies of the Oracle database,
>being more of an implementer of applications.
>
>However, there is a general business problem for which Im not
>sure whether views are the correct ( and efficient) solution.
>
>Lets say you have a table of accounts ( and balances)
>which have to be allocated by ranges to different users to handle
>and administer. The applicatio program which handles the accounting
>busines functions does not directly support this kind of restriction
>or allocation. Lets say that it always reads the ACCOUNT table.
>
>So if user1 is to be restricted to see and operate only accounts within
>a range ( call it ACCOUNT1) and user2 only allowed for ACCOUNT2
>( ACCOUNT1, ACCOUNT2 being proper subsets of ACCOUNT)
>can we create views called ACCOUNT specific to the USER1
>with the range restriction,
>( the ACOUNT table still exists as the application expects it)
>and expect the desired behavior?
>
>What i mean is that when USER1 logs onto the application
>and the application executes SELECTS on ACCOUNT,
>only accounts within range ACCOUNT1 are selected since
>the DBA has created a "synonym" ( is that the correct Oraclespeak?)
>called ACCOUNT specific to USER1 and pointed it to ACCOUNT1 which has
>the restriction of range.
>
>
>Or is there some difficulty with this?
>Do note that application code cannot really be changed due to
>it being a readymade package etc etc
>
>I would appreciate inputs on this.
>TIA
>
>Shanx
>
>
>
>
>
>
>
>
>
>
>dean.reynolds_at_home.net wrote:
>>
>> Keep in mind that a VIEW is nothing more than a SQL statement. Their
>> main purpose is to make querying the data easier.
>>
>> Instead of having to type
>>
>> select a.one,b.two,c.three,d.four from qwerty a, uiop b,
>> asdf c, ghjkl d
>> where a.two-b.two and c.three<> d.six
>>
>> You save that as a view and from then on type
>>
>> select * from my_view
>>
>> DON'T let people trick you into thinking that views offer better
>> performance. If you design and use them right, they can. More often
>> than not, they are poorly designed or people forget that they are
>> VIEWS and not TABLES. I had someone create several views comprising
>> several joins. By the time they got thru with it, it took over nine
>> minutes to return 106 rows!
>>
>> Unless your comfortable with SQL tuning, I would use views to make
>> queries easier, not better performing.
>>
>> Oh, the people who say views will perform faster because they are
>> preparsed queries always seem to forget that views are usually used in
>> ad-hoc queries that have to be parsed to see if they can be integrated
>> with the view query!
>>
>> -Dean
>>
>> On Wed, 24 Mar 1999 10:47:58 -0800, Vitaliy Mogilevskiy
>> <vit100gain_at_earthlink.net> wrote:
>>
>> >Views are GOOD, they are stored select's on the server side
>> >Use them !
>> >
>> >Vitaliy Mogilevskiy
>> >
>> >
>> >Andrew Thomas wrote:
>> >
>> >> In the couple of books that I have, I'm having trouble locating much useful
>> >> information about views. I'm wondering how much resource a view would take.
>> >> Does it just create a 'filter' for the data? I have a table of 1.7 million
>> >> rows and I'm wondering what affect it will have on the system if I create
>> >> 200 views? Is there a better way to do this?
>> >>
>> >> Please respond via email to athomas_at_iminformation.com if at all possible.
>> >>
>> >> Thanks,
>> >> -Andrew Thomas-
>> >
>> >
Received on Fri Mar 26 1999 - 23:08:15 CST