Re: Q: Oracle7 & Row level security...

From: David Sidwell <dasidwel_at_us.oracle.com>
Date: 1995/05/18
Message-ID: <dasidwel-1805951358520001_at_dasidwel-mac.us.oracle.com>#1/1

In article <D8BnH1.Fsx_at_lanier.com>, dvick_at_lanier.com (Don Vick) wrote:

> 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

Row-level security in Oracle7 can be achieved in many ways:

o        using combination of database triggers (does not meet SELECT criteria).
o        using views to restrict access, and synonyms to make these transparent.
o        building logic/security into application front-end or middleware
          (this approach may provide required functionality but with
little or no
            assurance of security).

If you require a highly secure solution to row-level access control then the best implementation would be to use Trusted Oracle7 on an MLS operating system. This product provides the functionality of Oracle7 with mandatory row-level access control. It has also been successfully evaluated to the B1 evaluation class under the TCSEC evaluation criteria, and at E3 assurance level under the European ITSEC security evaluation scheme. Received on Thu May 18 1995 - 00:00:00 CEST