July 26, 2007 at 8:55 am
We currently run about 30 databases on SQL Server 2000. Each database has close to 200 security views for providing CRUD access with row and cell level security to underlying tables based on your database role. Until now, we've been making ad-hoc updates to sysusers, setting the uid to the identity of the person's record in our database. This allows our security views to be very fast, since we can then use user_id() in the where clauses of our security views. Here's a simplistic example:
select first_name, last_name, ssn, from Person where person_pk = user_id()
union
select first_name, last_name, null as ssn from Person where person_pk <> user_id()
A lot of our views are more complicated than that, doing row and cell level security based on who you are in relation to the data you're trying to access.
I was hoping I'd be able to get away with a mass search and replace on user_id() to a new UDF that did a query, something like:
create function dbo.Person_ID (@username nvarchar(256))
returns int
with schemabinding
as
begin
declare @id
select @id = person_pk from dbo.Person where username = @username
return @is
end
SQL Server tells me that this function is deterministic by using
select objectpropertyex(object_id('Person_ID'), 'IsDeterministic')
However, if I run a query on a table with about 30k records using user_id() it takes a fraction of a second, but using Person_ID(user_name()) it takes about 8 seconds. There are some tables that are much larger than that and some views that make multiple calls to user_id(), so these would just end up being unacceptably slow.
So I've been exploring other options and all have left me back at square one.
1) Store the user's ID in CONTEXT_INFO. This hasn't worked out because I can't set it in an AUDIT_LOGIN event notification or in a UDF. Also I'm concerned about the way ADO.NET does a lot of disconnect/reconnect between queries and having CONTEXT_INFO get lost. Getting/Setting CONTEXT_INFO is sort of messy too. I don't like having to do multiple cast and converts every time.
2) Perform additional joins to the Person table and have a column with database_principal_id or something. This would require updating all 200 views. This is a last resort. Some views make several references to user_id and having more joins would make them messier and slower.
3) Update person.person_pk with cascade update to their database_principal_id. I can think of several reasons why this is a bad idea. I don't see it as an option.
I'm really at a loss here. Given enough time I can usually figure out a clever solution to a problem, especially one that would otherwise involve a lot of manual work, but this is just beyond me. I'm hoping there's a good answer to this problem, such as a query hint or something with that UDF to make SQL Server execute it in a deterministic fashion.
Any help or pointing in the right direction will be so greatly appreciated.
July 26, 2007 at 9:03 am
ok this is not meant as a flame, just trying to see if I understand.
You are worried about security but you have been making direct updates to the table that pretty much is the gate keeper for security on your system?
July 26, 2007 at 9:19 am
It hasn't been a problem, thought I've never really been a fan of doing this. Performance was the main drive behind this. Obviously we're not going to be doing it anymore though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply