August 17, 2010 at 11:16 pm
Hi All,
Our database has implemented row level security using USER_NAME() function in all the views. We have about 1400 views in total and lot of stored procedures that depend on these views.
I have previously posted in the forum related to SQL security and Users that we have a total of 6000 users in sysusers table and am looking for a better solution.
http://www.sqlservercentral.com/Forums/Topic968668-359-1.aspx?Update=1
To Compound the problem all the stored procedures are calling the views and the rows are displayed depending on who is logged in. I have an example code.
Any Suggestions on how these views can be rewritten.
Here is a sample of the problem that i am describing.
[Code]
CREATE TABLE #User
(
Name VARCHAR(255)
)
INSERT INTO #User
SELECT
'User1'
UNION ALL
SELECT
'User2'
CREATE USER User1 WITHOUT LOGIN
CREATE USER User2 WITHOUT LOGIN
CREATE TABLE #UserCompanyAccess
(
UserName VARCHAR(255),
Company VARCHAR(255)
)
INSERT INTO #UserCompanyAccess
SELECT
'User1',
'Company1'
UNION ALL
SELECT
'User1',
'Company2'
UNION ALL
SELECT
'User2',
'Company1'
exec as user = 'User1'
select #UserCompanyAccess.* from #UserCompanyAccess
JOIN #User
ON #User.Name = #UserCompanyAccess.UserName
AND #User.Name = USER_NAME()
revert
exec as user = 'User2'
select #UserCompanyAccess.* from #UserCompanyAccess
JOIN #User
ON #User.Name = #UserCompanyAccess.UserName
AND #User.Name = USER_NAME()
revert
DROP USER User1
DROP USER User2
DROP TABLE #User
DROP TABLE #UserCompanyAccess
[/Code]
August 18, 2010 at 3:41 pm
I have been thinking for a solution for similar issues (we've got an auditing in place that uses suser_sid() which raises the same issues for updates, inserts and deletes performed by processes acting on behalve of a user but not on a specific login for each user) and came up with this idea. I have not been able to verify it, nor test it in any real system, but I would like your feedback and maybe it is an idea for you too. The idea is that on every connection a session must be started that verifies the user credentials and only if it finds them valid, stores that user's authorisation with the session's id. The session id is stored in the connection's context_info, for all views and stored procedures to retrieve it. As long as no valid session has been started, no valid session id will be in the context_info and the views will return no information at all.
Here's an example bit of code of what I have in mind. Please tell what you think of it and if possible give me ideas on how to improve on it.
create table dbo.[Sessions] (
SessionID varbinary(128) not null,
constraint PKSessions primary key clustered (SessionID)
);
create table dbo.Authorized_Columns (
SessionID varbinary(128) not null,
LeftChar char(1) not null,
constraint FK_Authorized_Columns_Sessions foreign key (SessionID) references dbo.[Sessions](SessionID) on delete cascade not for replication
);
alter procedure dbo.spLogin
@UserName varchar(256),
@Password varchar(256)
as
begin
set nocount on;
declare @SessionID varbinary(128);
begin tran trnLogin;
begin try
-- TODO: generate some 'random' session number.
if context_info() is null
set context_info 0x1235678;
-- TODO: provide means to verify the credentials passed in.
if nullif('admin', @UserName) is null and nullif('password', @Password) is null
begin
insert dbo.Sessions( SessionID)
select context_info()
where not exists (
select top 1 *
from dbo.[Sessions]
where SessionID = @SessionID
);
-- TODO: Do whatever you need to retrieve what rights the found user has
-- access to and store the results in your tables in such a format that
-- the views can easily use it to do their row filtering.
insert dbo.Authorized_Columns( SessionID, LeftChar)
select context_info(), t.LeftChar
from (
select 'A' as LeftChar
union all select 'D'
) t;
end
else
begin
delete dbo.[Sessions]
where SessionID = context_info();
end
commit tran trnLogin;
end try
begin catch
if xact_state() <> 0
rollback tran;
end catch
end
go
create view dbo.vTest
as
select c.name
from sys.columns c
where left(c.name,1) in (
select leftChar
from dbo.Authorized_Columns
where SessionID = Context_info()
)
go
To see the result you can use this:
exec splogin 'test', 'test';
select * from dbo.vTest;
exec splogin 'admin', 'password';
select * from dbo.vTest;
which returns in my test database:name
--------------------------------------------------------------------------------------------------------------------------------
(0 row(s) affected)
name
--------------------------------------------------------------------------------------------------------------------------------
addr
algorithm
audit_spec_id
auid
datasize
dataspace
dbfragid
dbfragid
dbfragid
dbfragid
dbfragid
dbfragid
dbfragid
defval
defval
depclass
depid
depid
depid
depsubid
depsubid
deriv
dflt
dflt
dflt
dfltsch
diagid
diagid
diffbaseguid
diffbaselsn
diffbaseseclsn
diffbasetime
dlgerr
dlgid
dlgopened
dlgtimer
droplsn
(37 row(s) affected)
August 18, 2010 at 5:41 pm
I will test the suggestions posted by you and let you know what I think (Currently I am held up with some strict deadlines).
Just wondering if anyone else ran into similar problem and found a working solution.
August 19, 2010 at 12:47 am
Rozema thanks for the pointers.
There is another article in sqlservercentral that talks about setting contexts and capturing the loggedinusers credentials (Mainly used for triggers). Hope this might help you.
http://www.sqlservercentral.com/articles/Security/easyauditingasharedaccount/1953/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply