Re: Row Level Security

Rob, This works just fine after making a few syntax changes. Attached is a script I created and ran on Informix 7.1. You will also need to revoke all privileges on base_table and grant the privileges on base_view ---------------SQL script starts here --------------------------- create table access_table (id int, granted_user varchar(30)); create table base_table (id int , description varchar(80)); create view base_viewas select * from base_table where exists (select 'x' from access_table where access_table.granted_user=user and access_table.id=base_table.id) with check option; insert into access_table values ( 1, "lester" ); insert into access_table values ( 2, "rob" ); insert into base_table values ( 1,"Test Data 1" ); insert into base_table values ( 1,"Test Data 1" ); insert into base_table values ( 2,"Test Data 2" ); insert into base_table values ( 2,"Test Data 2" ); select count(*) from base_table; -- returns 4 select count(*) from base_view; -- returns 2 ---------------SQL script ends here --------------------------- > Robert Lippmann asked: > I'm trying to implement a simple row level user based security scheme using > views. I have the following schema: > > create table access_table (id number(9), granted_user varchar2(30)); > create table base_table (id number (9), description varchar2(80)); See syntax changes above to number(9) and varchar2 > create or replace view base_view > as select * > from base_table > where exists (select 'x' > from access_table > where access_table.granted_user=user > and access_table.id=base_table.id) > with check option; > > Thus, I can grant only the view to another user, and grant and revoke access > on each row by simply inserting or deleting a row into access_table. > > This works on Oracle (7.1.6) because there is no join in the where clause. > > I was wondering if this behavior is standard across other databases, or am I > exploiting functionality specific to Oracle. Regards - Lester ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # # Visit our Web page: http://www.access.digex.net/~lester # # Washington Area Informix User Group: http://www.access.digex.net/~waiug # #############################################################################