Re: HELP: Row Level Security

In article <1994Jul13.001325.12976_at_scammell.ecos.tne.oz.au>

           pcm_at_scammell.ecos.tne.oz.au "Peter" writes:

> Newsgroups: comp.databases.oracle
> Path: hayford.demon.co.uk!demon!pipex!howland.reston.ans.net!agate!
> ihnp4.ucsd.edu!munnari.oz.au!yarrina.connect.com.au!harbinger.cc.monash.edu.au!
> bunyip.cc.uq.oz.au!qus102!scammell!pcm
> From: pcm_at_scammell.ecos.tne.oz.au (Peter)
> Subject: HELP: Row Level Security
> Message-ID: <1994Jul13.001325.12976_at_scammell.ecos.tne.oz.au>
> Organization: Starfleet Academy
> X-Newsreader: TIN [version 1.2 PL2]
> Date: Wed, 13 Jul 1994 00:13:25 GMT
> Lines: 22
>
>
> 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 |
> +-----------------------------------------------------------------------+
>

You might try creating a VIEW, for example, "Create view as select fld1, fld2 from table_name where fld3 = expression". Then give users access to the view only. Do not give users access to the underlying table. When the user selects data from the view, they will only see a subset of the data from the base table. Stored procedures should not be necessary since access to the view can be controlled through the Grant statement. Procedures should only be required to provide update privileges to users indirectly (ie, privileges are only available via the procedure, and not through ad-hoc SqlPlus queries).

-- 
Alan Muster