Re: Q: Oracle7 & Row level security...
Date: 1995/05/09
Message-ID: <D8BnH1.Fsx_at_lanier.com>#1/1
In article <3ono2j$4tv_at_ttis.thomtech.com>,
Dave H <dheissner_at_thomtech.com> wrote:
>Hi,
>I am curious to know what approaches people are using to implement
>row level security on an Oracle7 table without spliting the table
>into separte tables with the same structure.
>
We are experimenting with a table to be used by admin scripts to look up passwords for remote logins. We have a table and a view, named similar to the Oracle dictionary views:
The table:
pdltst sql>desc dba_passwords
Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) USERNAME VARCHAR2(30) DATABASE NOT NULL VARCHAR2(30) PASSWORD VARCHAR2(30)
The view:
pdltst sql>desc user_passwords
Name Null? Type ------------------------------- -------- ---- OWNER NOT NULL VARCHAR2(30) USERNAME VARCHAR2(30) DATABASE NOT NULL VARCHAR2(30) PASSWORD VARCHAR2(30)
pdltst sql>select text from dba_views where view_name='USER_PASSWORDS';
TEXT
select "OWNER","USERNAME","DATABASE","PASSWORD" from dba_passwords where owner=user with check option
The last line of the view definition is the key. 'Owner is user' means that each user sees only those rows that he inserted, i.e., the owner matches his userid. 'With check option' means that no user can insert a row that he could not subsequently see. I.e., USER_A can only insert rows with 'USER_A' in the OWNER field.
We grant select, insert, update, delete on user_passwords to public, so every user can insert rows and see only his own rows. To the user it appears that he has a private table.
I'll be interested in any comments on this scheme, as well as hearing what other people are doing.
Donald E. Vick (dvick_at_lanier.com, dvick_at_crl.com) Voice: (404) 493-2194 Fax: (404) 493-2399 Received on Tue May 09 1995 - 00:00:00 CEST