|
|
Posted by Erland Sommarskog on 11/05/05 17:43
(gregory.sharrow@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|