By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

row level security across multiple columns

P: n/a
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....b6abcfa6209d2f
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?

Nov 5 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
(gr*************@usa.net) writes:
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).
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.
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.


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_cross_ref PRIMARY KEY(cross_ref_key),
CONSTRAINT U_security_cross_ref UNIQUE
(user_key, salesrep, product, customer, groupcode),
CONSTRAINT ckt_excactly_one 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_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_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_cross_ref PRIMARY KEY(cross_ref_key),
CONSTRAINT U_security_cross_ref UNIQUE
(user_key, salesrep, product, customer, groupcode),
CONSTRAINT ckt_excactly_one 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_key
AND g.customer_key = sec.customer_key
go
drop table security_cross_ref
drop table generic_fact

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 5 '05 #2

P: n/a
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

Nov 8 '05 #3

P: n/a
(gr*************@usa.net) writes:
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?


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 8 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.