November 22, 2010 at 2:04 pm
Has anyone found an efficient method of implementing Row Level security on a SQL relational database?
I'll explain a little bit of history so you can see the context.
- I have a reasonably sized DW running on SQL 2008 R2.
- I also have a series of AS databases over the top of the DW.
- Within MSAS I have implemented row level security on some of the dimensions (well member level security actually).
- The goal is to have a consistent security model at a low level, regardless of which database a user connects to.
Based on my research, I can't see anything within SQL that natively supports this kind of thing. Most people I read about have used some auxiliary tables to store the security rights of a user, and then restrict access by joining to these tables. The dimension tables can be wrapped in views or table functions so that the security logic only has to be implemented on one place.
I have experimented with this model and it works after a fashion. I choose to look at tables functions as it gives me the flexibility to implement a "Security Group" concept rather than a "one size fits all" model. For example, in one context a user should see all Suppliers, but in another they should be restricted to a subset.
The problem with this solution is that the performance drops dramatically when querying larger datasets or with larger dimensions. I can improve performance by dropping back to simpler views, but I loose the advantage of the Security Group model.
So has anyone found an elegant method of doing this, or alternatively have you decided to implement this security in a higher layer.
Please feel free to comment, all thoughts are appreciated.
P.
November 22, 2010 at 2:08 pm
How static is the security? Do you have lots of groups, or just a few? Does the group-level security change frequently, or almost never, or somewhere in between? Different solutions work better for different requirements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 2:19 pm
The security is reasonably static. New hires get added to the appropriate AD groups, so the security I designed works off either Windows User accounts or groups. Preference is to add them to AD groups and have the AD Group mentioned in the SQL security layer.
The Security Groups are few at this point but I can see them growing. There isn't a one-to-one relationship between a Security Group and an AD Group.
November 22, 2010 at 2:24 pm
Is the security hierarchical? As in, do people in Group A get to see their own data, and that of Group B and Group C, while Group B can see theirs and Group C's, but not Group A's, and Group C can only see their own data? Or is it isolated, so Group A can only see Group A data, Group B can only see Group B data, and so on? Or are there overlaps, but not strictly hierarchical ones, like Group A can see all Group A data, some Group B data, and some Group C data, and so on for each group?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 2:26 pm
No it is not hierarchical, and yes a person/ADGroup can be a member of more than one Security Group.
November 22, 2010 at 2:27 pm
Right, but do the groups overlap on what data they can see?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 2:29 pm
Yes the Security Groups do overlap which data they can see.
November 22, 2010 at 2:39 pm
You can't just split up rows by group, you have to indicate what groups can view what what rows, and that's essentially a many-to-many join. Those can be efficient, but it's going to take some work, and it will hit performance at least a bit.
Or you'll need to somehow manage it in the data access or security layer, outside the database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 22, 2010 at 2:43 pm
Okay, thanks for your comments.
Has anyone else tried something like this? I'm keen to hear of your experiences.
November 22, 2010 at 2:55 pm
Peter Jenks (11/22/2010)
Okay, thanks for your comments.Has anyone else tried something like this? I'm keen to hear of your experiences.
Not necessarily a helpful answer, but one none-the-less... Yes, I've tried it. No, it didn't turn out as well as one would hope.
A lot of this depends on how you are going to control the security model. Assuming you have some kind of selector (user name, group name, whatever), this leads to a many to many table for *each* datatable that is considered secured.
What do I mean? If you are limiting at the account level only, then only the account level gets secured, and a person can see anything underlying. If you also secure at the invoice level (only certain people can see invoices of higher than, say, $10,000, as theft deterrence), now you've got two layers of security interlaced. It gets more and more complex, with more and more lookups, the deeper you go.
This really bit me with HIPAA, trying to do it 'perfectly'.
I ended up with a non-joined model that was built off bitflags which the application cached at IIS. This stored things such as specific settings that we were securing against. We would lookup a login's bitflag array, and that would be passed as a filter for each table involved in the procedure. Pretty? No. Intuitive? No. We got a solid volume of speed out of it though.
For a more explicit example, and to explain the annoying complexity:
Say we have an employee's table. We added a field called SecurityBitMask BIGINT. There are a few security components here. Let's say division: 'HR', 'Engineers', 'Executive', 'Administrative'. Each of these get a UNIQUE FLAG if we're going to mask this way, and have very specific ways of approaching it. So, 1/2/4/8 are used for our divisions. We add in some others, say, 'region'. Four more regions, 16/32/64/128. All's well and good right?
Now we add division 'Maintenance'. Another flag. and another. and another.
It was a specific fix for a specific location. I wouldn't recommend it for any other case, but it's one way, if your securable options are small and mostly unmutable.
Maybe that can help you. A lot of your solution will revolve around a few things. Mutability of securable items selectivity, hierarchal vs. any to any security, how many methods you intend to do row level security via, and is the security hierarchal after the first layer.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2010 at 3:27 pm
Hi Craig, thanks for your answer.
I can see how you have done this.
Perhaps a better term for what I have called "Security Groups" is "Security Area or Domain".
We could set up 3 groups :Finance, Sales, Production. When writing reports that touch Finance data, then they should implement what ever security is set up for a user within that context.
For example Joe is a Production Manager. When running a Production report he needs to have access to all members of the Location dimension. However he also has access to some Finance reports. When running those he needs to be limited to only certain Locations.
Here is some sample code to my current solution so you can see how it works at the moment. Just change DOMAIN\JOE to your current Windows Domain\User and it should work for you. My current solution is a bit more complex than this, but you can get the idea of what I am trying to do.
if object_id('dbo.SecurityData') is not null drop table dbo.SecurityData
if object_id('dbo.SecurityGroup') is not null drop table dbo.SecurityGroup
if object_id('dbo.Location') is not null drop table dbo.Location
go
CREATE TABLE dbo.SecurityGroup (
[SecurityGroupCode] [varchar](20) not null primary key,
[SecurityGroupDescription] [varchar](max) NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.SecurityData(
[TableName] [varchar](100) NULL,
[SecurityGroupCode] [varchar](20) NULL,
[UserName] [varchar](100) NULL,
[TableKey] [int] NULL,
) ON [PRIMARY]
go
ALTER TABLE SecurityData WITH CHECK ADD CONSTRAINT [FK_SecurityData_SecurityGroup]
FOREIGN KEY([SecurityGroupCode])
REFERENCES SecurityGroup ([SecurityGroupCode])
GO
CREATE TABLE dbo.Location
(LocationID int not null,
LocationName varchar(200))
go
set nocount on
-- Add some locations
insert into Location (LocationID, LocationName) values (1,'Location 1')
insert into Location (LocationID, LocationName) values (2,'Location 2')
insert into Location (LocationID, LocationName) values (3,'Location 3')
insert into Location (LocationID, LocationName) values (4,'Location 4')
insert into Location (LocationID, LocationName) values (5,'Location 5')
insert into Location (LocationID, LocationName) values (6,'Location 6')
insert into Location (LocationID, LocationName) values (7,'Location 7')
insert into Location (LocationID, LocationName) values (8,'Location 8')
insert into Location (LocationID, LocationName) values (9,'Location 9')
insert into Location (LocationID, LocationName) values (10,'Location 10')
-- Create 3 Security Groups
insert into SecurityGroup([SecurityGroupCode],[SecurityGroupDescription]) values ('FIN', 'Finance Security Group')
insert into SecurityGroup([SecurityGroupCode],[SecurityGroupDescription]) values ('SAL', 'Sales Security Group')
insert into SecurityGroup([SecurityGroupCode],[SecurityGroupDescription]) values ('PRD', 'Produciton Security Group')
-- Add Security for Joe so he can see 3 Locations in the Finance Security Group
insert into SecurityData values ('Location', 'FIN', 'DOMAIN\JOE', 1)
insert into SecurityData values ('Location', 'FIN', 'DOMAIN\JOE', 2)
insert into SecurityData values ('Location', 'FIN', 'DOMAIN\JOE', 3)
-- Add Security for Joe so he can see all Locations in the Production Security Group
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 1)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 2)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 3)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 4)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 5)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 6)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 7)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 8)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE', 9)
insert into SecurityData values ('Location', 'PRD', 'DOMAIN\JOE',10)
go
if object_id('dbo.fn_Location') is not null drop function dbo.fn_Location
go
create function dbo.fn_Location (@SecurityGroup varchar(20)='') returns table as
return(
SELECT
l.*
FROM Location l
inner join (select TableKey from SecurityData where SecurityGroupCode = @SecurityGroup and TableName = 'Location' and (UserName = system_user or is_member(UserName)=1) group by TableKey) lex on l.LocationID = lex.TableKey
)
go
SELECT * FROM dbo.fn_Location('FIN') -- Returns 3 rows
SELECT * FROM dbo.fn_Location('PRD') -- Returns 10 Rows
SELECT * FROM dbo.fn_Location('SAL') -- Returns nothing
Cheers
P.
November 22, 2010 at 4:09 pm
What is your difference between Finance 1 and Production 1, or is that overlap on purpose?
In your current model, his restriction on Finance is reduced to 3 records, but his Production overlaps that and adds 7 more. This means that in this security model, he'll see everything.
Unless, of course, you're passing in the FIN/PRD/etc components yourself based on where the call comes from. Then it works.
This isn't a bad design, but it's going to depend on how much hierarchy you want in the control. This also really isn't 'rowlevel' security, but it is a form of relational security. For example, while I can't see everything hiding under location 12, I can see everything that belongs to Location 4 while in PRD. So really, I'm controlling this via a relation, or hierarchy. Sorry, not trying to get symantic, but it presents a much easier approach.
It's the chaining this down through the hierarchy/relations and having yet more limiters at sublevels that I usually understand to be considered 'rowlevel' security, if not each row having its security settable outright.
Your design is probably the best you can hope for from what I'm reading in this circumstance. The only other thing I *might* do is test it against using the table itself instead of a function to see how the optimizer behaves against indexing with and without the iTVF in the way.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2010 at 4:20 pm
Thanks Craig
The over lap was intentional. The main point is that the same user sees different rows when in different contexts (Finance or Production).
Yes the Table Function does cause the query plan to get a lot more complicated, hence the problem. I went from a view to a TVF because I wanted to ensure that external interface (ie replacing the tablename in some query with the name of the TVF) was a close as possible. I also needed to pass in the Security Group. The other thing I have been doing in the TVF is checking to see if the user has an "All Rows" setting. All of this is something that, when implemented in a view, causes the query plan to panic, and go a bit weak at the knees.
The main point of this thread was to see if the wider commiunity had come up with something better or more elegant. I'm sure this isn't unique requriement, and frankly I'm surprised MS hasn't provided something "row level" within the server framework.
Thanks for your comments.
Cheers,
P.
November 22, 2010 at 4:32 pm
Peter Jenks (11/22/2010)
The main point of this thread was to see if the wider commiunity had come up with something better or more elegant. I'm sure this isn't unique requriement, and frankly I'm surprised MS hasn't provided something "row level" within the server framework.
Off this comment, you got me a bit curious, so I went poking around a bit. What I consider row-level and the rest of the world apparently are slightly disagreed, but yeah, I see where they're coming from. I just think a little differently, I guess. :hehe:
The age old standard of 'make a view' still exists, and there's been some interesting bits out there in regards to manipulation in that method. There's also row-label security, and a decent white paper on the implementation. But nope, nothing remotely 'new'. At least not that anyone's advertising.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply