Re: HELP: Row Level Security

From: Olli P|yry <poyryol1_at_news.ougf.fi>
Date: 13 Jul 1994 17:31:57 +0300
Message-ID: <300tstINN88b_at_news.ougf.fi>

You can use pseudocolumn USER to dynamically restrict the user to appropriate rows.
Here ekepr.pr_oikeus_pr and ekepr.pr_oikeus_yr are tables containing definitions on who is allowed to see what (which projects or companies as a whole).

CREATE VIEW PA_PR_TAP (

 YNO                             ,

 ... rows deleted
 PVM
) AS SELECT

 YRITYS                          ,
 ...   rows deleted
 PVM           

FROM kpito.PA_PROJTAP
WHERE
( ( yritys, kohde_1 ) IN

    ( SELECT yritysnro, proj

      FROM ekepr.pr_oikeus_pr
      WHERE ora_user = user

    )
OR yritys IN

    ( SELECT yritysnro

      FROM ekepr.pr_oikeus_yr
      WHERE ora_user = user

    )
)

I know that this query might not be the fastest around, but it shows the right records.

---

Olli Poyry poyryol1_at_news.ougf.fi
Essaim Oy
Finland

---

Peter (pcm_at_scammell.ecos.tne.oz.au) wrote:

: Hello,
 

: I wish to restrict users to accessing certain rows in a
: table without using Trusted Oracle. Users would be
: accessing the table through ODBC. Does anyone have
: any ideas on how I might do this? Could I somehow use
: stored procedures containing SQL to restrict access and
: only provide the procedure SELECT access to the tables
: and only allow users access to the procedure?
 

: Any help would be REALLY appreciated!! :)
 

: Thanks, Peter.
 

: +-----------------------------------------------------------------------+
: | Peter C. Murray: I hear the apes howl sadly in dark mountains, |
: | The blue river runs swiftly through the night. |
: | class Disclaimer : public StdDisclaimer { /* It was all my fault */ };|
: | |
: | Information Technology Group, Telecom (Telstra) Australia |
: +-----------------------------------------------------------------------+
Received on Wed Jul 13 1994 - 16:31:57 CEST