Posted by gregory.sharrow on 11/05/05 03:21

I need to secure a datawarehouse table at the row level based on 1 to
many keys on that table. A user should only see the rows they have
access to. I need to be able to figure out which rows they have access
to using a single sql statement and it cannot be dynamic SQL or a
stored procedure (this is a limitation based on the reporting tool we
use).

The conditions can be any combination of "and" and "or" operators. I
have seen posts (and actually have three of his books) by Joe celko
describing disjunctive canonical form. If not familiar please click
the link below.

http://groups.google.com/group/comp.databases.theory/browse_frm/thread/667793f549fe7e7f/4db6abcfa6209d2f?lnk=st&q=disjunctive+canonical&rnum=6&hl=en#4db6abcfa6209d2f


A rules table in disjunctive canonical form looks like it might
potentially be a solution, but I can't figure out how to use it across
multiple keys.

Here ia an example of what I am trying to do including current table
structures and sample data. I cannot really change the generic_fact
table schema, but the security cross reference table can be modified or
aditional tables added.

CREATE TABLE generic_fact (
generic_fact_key int NOT NULL ,
salesrep_key int NOT NULL ,
product_key int NOT NULL ,
customer_key int NOT NULL ,
sales_amount decimal(18, 0) NOT NULL ,
CONSTRAINT PK_generic_fact PRIMARY KEY
( generic_fact_key
)
)
GO
insert into generic_fact values(1,1,10,20,45)
insert into generic_fact values(2,1,10,21,90)
insert into generic_fact values(3,1,11,22,17)
insert into generic_fact values(4,2,10,20,32)
insert into generic_fact values(5,2,13,25,6)
insert into generic_fact values(6,2,12,24,56)
insert into generic_fact values(7,3,11,34,75)


CREATE TABLE security_cross_reference (
cross_ref_key int NOT NULL ,
user_key int NOT NULL ,
security_type varchar (50) NOT NULL ,
security_value int NOT NULL ,
security_operator varchar (20) NOT NULL ,
CONSTRAINT PK_security_cross_reference PRIMARY KEY
(
cross_ref_key
)
)
GO

insert into security_cross_reference values(1,1,'product',10,'or')
insert into security_cross_reference values(2,1,'customer',21,'or')
insert into security_cross_reference values(3,2,'customer',20,'and')
insert into security_cross_reference values(4,3,'salesrep',3,'and')
insert into security_cross_reference values(5,4,'salesrep',1,'and')
insert into security_cross_reference values(6,4,'customer',22,'and')
insert into security_cross_reference values(7,4,'product',11,'and')






So based on the data in the security cross reference table

User 1 can see sales for customer 21 or product 10
(generic_fact table rows that have generic_fact_key values of 1,2,and
4)

User 2 can see sales for customer 20
(generic_fact table rows that have generic_fact_key values of 1 and 4)

User 3 can see sales for salesrep 3
(generic_fact table rows that have a generic_fact_key value of 7)

User 4 can see sales for salesrep 1 and product 11 and customer 22
(generic_fact table rows that have a generic_fact_key value of 3)


Does anyone have any ideas on the best way to do this?

[Back to original message]