Re: HELP: Row Level Security

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

>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?

I explored this situation (or one closely related to it) a while ago. I'm doing this from memory but it should work under Oracle 7:

Assuming you have a readily identifiable criteria for selection you can create a separate view for each subset you want to create.

The example here is to give certain users access only to customers residing in Nebraska (NE).

user MASTER owns table CUSTOMER, so as MASTER you do:

    CREATE OR REPLACE VIEW CUSTOMER_NE AS SELECT * FROM CUSTOMER     WHERE STATE = 'NE'; (This is arguably bad design, because the view should probably NOT use '*' to select all columns. If you add a column to CUSTOMER, it doesn't automatically change the view to pick up those extra columns. However, rerunning the CREATE statement will refresh the view and pick them up without requiring regrants on the view, so I do tend to use this technique.)

Now, if you want USER1 and USER2 to only have access to this subset, but to otherwise treat it as if it was the full CUSTOMER table, you can grant them access to CUSTOMER_NE, then create a synonym for each user:

    GRANT SELECT ON CUSTOMER_NE TO USER1, USER2;     CREATE SYNONYM USER1.CUSTOMER FOR MASTER.CUSTOMER_NE;     CREATE SYNONYM USER2.CUSTOMER FOR MASTER.CUSTOMER_NE; USER1 and USER2 can now access CUSTOMER_NE under the name CUSTOMER, and this will work transparently for tools such as Forms. (I don't know how much of a performance impact this technique has, but it would obviously be highly desirable to have an index for the STATE column.)

I haven't tried this under ODBC, but assuming users have separate accounts, I don't know any reason why it wouldn't work there too.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)