row level security across multiple columns

[gregory.sharrow@usa.net]

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_ke y 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_ke y
)
)
GO
insert into generic_fact values(1,1,10,2 0,45)
insert into generic_fact values(2,1,10,2 1,90)
insert into generic_fact values(3,1,11,2 2,17)
insert into generic_fact values(4,2,10,2 0,32)
insert into generic_fact values(5,2,13,2 5,6)
insert into generic_fact values(6,2,12,2 4,56)
insert into generic_fact values(7,3,11,3 4,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_operat or varchar (20) NOT NULL ,
CONSTRAINT PK_security_cro ss_reference PRIMARY KEY
(
cross_ref_key
)
)
GO

insert into security_cross_ reference values(1,1,'pro duct',10,'or')
insert into security_cross_ reference values(2,1,'cus tomer',21,'or')
insert into security_cross_ reference values(3,2,'cus tomer',20,'and' )
insert into security_cross_ reference values(4,3,'sal esrep',3,'and')
insert into security_cross_ reference values(5,4,'sal esrep',1,'and')
insert into security_cross_ reference values(6,4,'cus tomer',22,'and' )
insert into security_cross_ reference values(7,4,'pro duct',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_ke y values of 1,2,and
4)

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

User 3 can see sales for salesrep 3
(generic_fact table rows that have a generic_fact_ke y 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_ke y value of 3)


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

[Erland Sommarskog]

Re: row level security across multiple columns

(gregory.sharro w@usa.net) writes:[color=blue]
> 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).[/color]

First all, a general caveat. While row-level security can be implemented
with adding conditions to queries, this is not a waterproof method. A
skilled user can still be able to wrestle out information from such
a view by running queries with "funny" WHERE clauses, from which he
can draw conclusions from query plans, error messages from conversion
errors etc. The dangers here should not be exagerrated, because it is
by no means an easy exercise, and more or less requires access to the
database through Query Analyzer.
[color=blue]
> 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.[/color]

The problem with the security_cross_ reference table is that you have
geared it up to construct a query in dynamic SQL - which most certainly
would give better performance that what I will present below, despite
what Celko says in the post you quote. But dynamic SQL was out for you
anyway. You will need one column for each key in the fact table. And
rather having this and/or column, I've added groupcode column, taken
directly from the skill_code column in Celko's post:

CREATE TABLE security_cross_ ref (
cross_ref_key int NOT NULL ,
user_key int NOT NULL ,
salesrep int NULL,
product int NULL,
customer int NULL,
groupcode char(1) NOT NULL,
CONSTRAINT PK_security_cro ss_ref PRIMARY KEY(cross_ref_k ey),
CONSTRAINT U_security_cros s_ref UNIQUE
(user_key, salesrep, product, customer, groupcode),
CONSTRAINT ckt_excactly_on e CHECK (
CASE WHEN salesrep IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN product IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN customer IS NOT NULL THEN 1 ELSE 0 END = 1)
)

The table constraint ensure that exactly one column of salesrep,
customer and product is non-NULL. Maybe this constraint could be
relaxed, so that you could have a row with salesrep = 1 and product = 8,
matching rows in the fact table that has both conditions. In such case,
the groupcode would not be needed. I kept it in, since I suspect that
what you presented here is a bit simplified.

Anyway, here is a complete repro that shows how to fill up the
cross-reference table and how to query the generic fact table:

CREATE TABLE generic_fact (
generic_fact_ke y 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_ke y
)
)
GO
insert into generic_fact values(1,1,10,2 0,45)
insert into generic_fact values(2,1,10,2 1,90)
insert into generic_fact values(3,1,11,2 2,17)
insert into generic_fact values(4,2,10,2 0,32)
insert into generic_fact values(5,2,13,2 5,6)
insert into generic_fact values(6,2,12,2 4,56)
insert into generic_fact values(7,3,11,3 4,75)


CREATE TABLE security_cross_ ref (
cross_ref_key int NOT NULL ,
user_key int NOT NULL ,
salesrep int NULL,
product int NULL,
customer int NULL,
groupcode char(1) NOT NULL,
CONSTRAINT PK_security_cro ss_ref PRIMARY KEY(cross_ref_k ey),
CONSTRAINT U_security_cros s_ref UNIQUE
(user_key, salesrep, product, customer, groupcode),
CONSTRAINT ckt_excactly_on e CHECK (
CASE WHEN salesrep IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN product IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN customer IS NOT NULL THEN 1 ELSE 0 END = 1)
)
GO

insert security_cross_ ref (cross_ref_key, user_key, product, groupcode)
values(1, 1, 10, 'A')
insert security_cross_ ref (cross_ref_key, user_key, customer, groupcode)
values(2, 1, 21, 'B')
insert security_cross_ ref (cross_ref_key, user_key, customer, groupcode)
values(3, 2, 20, 'A')
insert security_cross_ ref (cross_ref_key, user_key, salesrep, groupcode)
values(4, 3, 3, 'A')
insert security_cross_ ref (cross_ref_key, user_key, salesrep, groupcode)
values(5, 4, 1, 'A')
insert security_cross_ ref (cross_ref_key, user_key, customer, groupcode)
values(6, 4, 22, 'A')
insert security_cross_ ref (cross_ref_key, user_key, product, groupcode)
values(7, 4, 11, 'A')

go
DECLARE @userid int
SELECT @userid = 1
SELECT g.*
FROM generic_fact g
JOIN (SELECT DISTINCT g.product_key, g.customer_key, g.salesrep_key
FROM generic_fact g
JOIN security_cross_ ref s ON g.product_key = s.product OR
g.customer_key = s.customer OR
g.salesrep_key = s.salesrep
WHERE s.user_key = @userid
GROUP BY g.product_key, g.customer_key, g.salesrep_key,
s.groupcode
HAVING COUNT(*) >= (SELECT COUNT(*)
FROM security_cross_ ref s2
WHERE s2.user_key = @userid
AND s2.groupcode = s.groupcode)) AS sec
ON g.product_key = sec.product_key
AND g.salesrep_key = sec.salesrep_ke y
AND g.customer_key = sec.customer_ke y
go
drop table security_cross_ ref
drop table generic_fact

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[gregory.sharrow@usa.net]

Re: row level security across multiple columns

Erland,

Thank you for the response it was helpful. After reviewing my post I
see I should have presented at least one more example (or maybe I am
not completely understanding your reply).

In your version of the security_cross_ ref table, how do you enter
multiple products, salesreps, or customers for a single user?

So let say I have a user 5 who has access to
products in (10,11)
or
customers in (20,21,22)
or
salesreps in (1)

How would I populate the cross ref table?


Because in reality my situation is

A user could have access to

0 to many products

and/or

0 to many customers

and/or

0 to many salesreps

[Erland Sommarskog]

Re: row level security across multiple columns

(gregory.sharro w@usa.net) writes:[color=blue]
> So let say I have a user 5 who has access to
> products in (10,11)
> or
> customers in (20,21,22)
> or
> salesreps in (1)
>
> How would I populate the cross ref table?[/color]

Do you mean that this have the rights to see a row as long as any
of these conditions are fulfilled? Then you would enter six rows,
all with a different groupcode. The group codes are there when you have
AND conditions like "User may only see the combination of salesrep = 2
and customer = 29".


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp