Geeks With Blogs
MightyZot

I worked on a project for PaperWise a few months ago where I needed to restrict the results of a SQL view based upon the user executing queries against that view.  To do that, I needed some way to tie the query being executed on the view to the user executing it.  The problem is that subsequent queries in PaperWise don’t necessarily identify the user executing them.

PaperWise uses standard SQL Server authentication and not Windows NT authentication, which means that we have a Users table in our database and we query against that to authenticate users.  In my case, I wanted to create a copy of a table containing HR data and restrict the data returned to only information pertaining to the employee doing the query.

After authentication, PaperWise inserts a record into a table called CurrentUsers to track licensing and sessions.  So, to restrict the HR data, I created a column in the CurrentUsers table called MACAddress.  I then created a trigger for insertions into that table that retrieves the MAC address of the user based upon the process ID of the current connection.  Here’s what that trigger looks like:

CREATE TRIGGER PwInsertSession
ON CurrentUsers AFTER INSERT
AS

DECLARE @SessionID as int

SELECT @SessionID=iSessionID FROM inserted
IF @SessionID <> –1
BEGIN

DECLARE @Address as varchar(20)
SELECT @Address=net_address FROM master.dbo.sysprocesses
WHERE spid=@@SPID

UPDATE CurrentUsers SET MACAddress=@Address
WHERE iSessionID=@SessionID

END

The trigger uses @@SPID to get the spid of the SQL Server process executing the query.  Next, it looks up the MAC address in the master database’s sysprocesses table.  And, finally, it inserts that MAC address into the MACAddress field of the CurrentUsers table, so that I can reference it in subsequent queries.

Once the trigger was in place, I created a view called “HR_Employee_View.”  This is the view that employees will have rights to see.  The HR table is restricted such that only HR personnel can see it.  Here’s what the new view looks like:

CREATE VIEW [dbo].[HR_Employee_View]
AS

SELECT DocumentID, HR.UserID, ImageData, CreateDate, LastModifiedDate,
           MetaData1, MetaData2, MetaData3, MetaData4, MetaData5
FROM HR
WHERE Status=’Active’
           AND HR.UserID =
              (SELECT DISTINCT Users.UserID FROM Users
               INNER JOIN CurrentUsers ON (Users.UserID = CurrentUsers.UserID 
                        AND MACAddress = 
                                (SELECT net_address
                                 FROM master.dbo.sysprocesses
                                 WHERE spid = @@SPID)))

 

So, the results of the trigger and the view working together is that the content of the HR_EmployeeView view (which looks like a table to the user) is restricted based upon the user executing the query.

I should mention that I have taken the liberty of simplifying the SQL in this example for both brevity and to protect the intellectual property of PaperWise.  (There are no MetaData1, MetaData2, etc, fields in the database.  I just included those to make the example make sense.)

Posted on Thursday, June 11, 2009 11:56 PM SQL Server | Back to top


Comments on this post: Restricting a View Based on User

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © MightyZot | Powered by: GeeksWithBlogs.net