January 15, 2007 at 1:55 am
Scenario:
I’ve a scenario in which I want to get the list of employees for a user as per the security defined. For security purposes, we’ve the following tables…
· secMappingTypes ( Master table to hold all the available data mapping types). We’ve a predefined set of mappings(for ease I’ve listed only a few). The mapping types are linked to employees.
o DIVISION
o LOCATION
o GRADE
· secUserMasterMappings ( Mappings associated with a single user).
· mstEmployees ( Employee datastore).
Application uses SQL Server 2005 as data storage medium.
DDL for Scenario:
CREATE TABLE [dbo].[secMappingTypes]
(
[MappingTypeID] [tinyint] NOT NULL,
[MappingTypeName] [varchar](20) NOT NULL,
CONSTRAINT [PK_secMappingTypes_MappingTypeID] PRIMARY KEY CLUSTERED
(
[MappingTypeID] ASC
)
)
CREATE TABLE [dbo].[secUserMasterMappings]
(
[UserID] [int] NOT NULL,
[MappingTypeID] [tinyint] NOT NULL,
[MappingID] [smallint] NOT NULL,
CONSTRAINT [PK_secUserMasterMappings] PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[MappingTypeID] ASC,
[MappingID] ASC
)
)
CREATE TABLE [dbo].[mstEmployees]
(
[EmployeeID] [int] NOT NULL,
[DivisionID] [int] NOT NULL,
[LocationID] [int] NOT NULL,
[GradeID] [int] NOT NULL,
CONSTRAINT [PK_mstEmployees_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)
)
Populate some data
--[dbo].[secMappingTypes]
--To make things simpler, I’d only used a few of the mapping types avl..
INSERT INTO [dbo].[secMappingTypes]( MappingTypeID, MappingTypeName )
SELECT 1, ‘DIVISION’
UNION ALL
SELECT 2, ‘LOCATION’
UNION ALL
SELECT 3, ‘GRADE’
--[dbo].[secUserMasterMappings]
INSERT INTO [dbo].[secMappingTypes]( UserID, MappingTypeID, MappingID )
SELECT 1, 1, 1
UNION ALL
SELECT 1, 2, 2
UNION ALL
SELECT 2, 2, 1
UNION ALL
SELECT 3, 3, 1
--[dbo].[mstEmployees]
--secUserMasterMappings(MappingID) is related to mstEmployees(DivisionID,
--LocationID, GradeID)
INSERT INTO [dbo].[mstEmployees]( EmployeeID, DivisionID, LocationID, GradeID )
SELECT 1, 1, 1, 2
UNION ALL
SELECT 2, 1, 2, 1
UNION ALL
SELECT 3, 1, 2, 1
UNION ALL
SELECT 4, 1, 1, 1
UNION ALL
SELECT 5, 1, 1, 1
Queries:
How can I get a list of employees for a specific user say user 2? I know I can easily achieve this through Stored Procedures in 2000/2005 but I’m failing to do through user defined function in SQL 2000/2005. Since I’ve not explored much of the T-SQL features in 2005, I suppose there must be some sort of solution to this?
Outputs:
Let’s say we’ve built a function named “GetEmployeesByUserID( UserID )”… Then the outputs will be
SELECT EmployeeID FROM dbo.GetEmployeesByUserID( 2 )
EmployeeID
1
4
5
SELECT EmployeeID FROM dbo.GetEmployeesByUserID( 1 )
EmployeeID
2
3
SELECT EmployeeID FROM dbo.GetEmployeesByUserID( 3 )
EmployeeID
2
3
4
5
--Ramesh
January 15, 2007 at 4:18 am
I had a couple puzzles with your script (you have the wrong table in your second append query). Also, I didn't understand your result for user 1. Since all of the employees are in division 1, shouldn't user 1 return all of the employees?
-- =============================================
-- Create inline function (IF)
-- =============================================
CREATE FUNCTION dbo.GetEmployeesByUserID
(@userid int)
RETURNS TABLE
AS
RETURN SELECT e.EmployeeID from mstEmployees e
where exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 1
and e.DivisionID = s.MappingID)
or
exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 2
and e.LocationID = s.MappingID)
or
exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 3
and e.GradeID = s.MappingID)
GO
Russel Loski, MCSE Business Intelligence, Data Platform
January 15, 2007 at 4:52 am
Thanks for the catch...
Let me explain what actually each table means..
[dbo].[secUserMasterMappings]
For User 1
SELECT 1, 1, 1
--Which means User 1 has the access of all the employees who are in DIVISION 1
SELECT 1, 2, 2
--Which means User 1 has the access of all the employees who are in LOCATION 2
The effective permissions for USER 1 will be..
all the employees who are in DIVISION 1 AND lives in LOCATION 2
..same access logic will be applicable to USERS 2 & 3
--Ramesh
January 15, 2007 at 5:44 am
The following is clumsy, but it works:
CREATE FUNCTION dbo.GetEmployeesByUserID
(@userid int)
RETURNS TABLE
AS
RETURN SELECT e.EmployeeID from mstEmployees e
where (exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 1
and e.DivisionID = s.MappingID)
or
not exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 1)
)
and
(exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 2
and e.LocationID = s.MappingID)
or
not exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 2)
)
and
(exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 3
and e.GradeID = s.MappingID)
or
not exists (select 1 from secUserMasterMappings s
where s.UserID = @userid
and s.MappingTypeID = 3)
)
GO
Russel Loski, MCSE Business Intelligence, Data Platform
January 15, 2007 at 6:32 am
Thanks for your efforts...Russell
The results seem allright with your fn. for this subset of data..
One more thing I’d like to notify..
Since this soln. involves sub-queries, which will result in multiple seeks of the same clustered index(which, probably will be 2 for a single data mapping type). This might cause performance issues when we’ve 15000+ employees with 10+ users performing data entry operations.
I really think there surely will be more better solutions…..
--Ramesh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply