Generally speaking, you can create views to horizontally partition data
based on your security requirements. The views filter data so that only
authorized users can access data. Do not allow direct access to the
underlying tables and grant user permissions only on views.
The script below illustrates this technique to implement security so that
users can only access employee data for those departments they are allowed
to see, based on their login and entries in the SecurityByDepar tment table.
This approach can be extended to include application-defined roles in order
to reduce security administration.
SET NOCOUNT ON
GO
CREATE TABLE Employees
(
EmployeeId int NOT NULL
CONSTRAINT PK_Employee
PRIMARY KEY NONCLUSTERED,
DepartmentId int NOT NULL,
SomeData varchar(30)
)
CREATE CLUSTERED INDEX Employees_cdx ON Employees(Depar tmentId)
GO
CREATE TABLE SecurityByDepar tment
(
UserName sysname NOT NULL,
DepartmentId int NOT NULL,
CONSTRAINT PK_SecurityByDe partment
PRIMARY KEY (UserName, DepartmentId)
)
GO
CREATE VIEW MyEmployees
AS
SELECT
e.EmployeeId,
e.DepartmentId,
e.SomeData
FROM Employees e
JOIN SecurityByDepar tment sbd ON
sbd.UserName = SUSER_SNAME() AND
sbd.DepartmentI d = e.DepartmentId
GO
EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyEmployees TO MyRole
GO
INSERT INTO Employees VALUES(1, 1, 'some data 1')
INSERT INTO Employees VALUES(2, 1, 'some data 2')
INSERT INTO Employees VALUES(3, 1, 'some data 3')
INSERT INTO Employees VALUES(4, 2, 'some data 4')
INSERT INTO Employees VALUES(5, 2, 'some data 5')
GO
INSERT INTO SecurityByDepar tment VALUES('Login1' ,1)
INSERT INTO SecurityByDepar tment VALUES('Login1' ,2)
INSERT INTO SecurityByDepar tment VALUES('Login2' ,1)
INSERT INTO SecurityByDepar tment VALUES('Login3' ,2)
GO
EXEC sp_addlogin 'Login1'
EXEC sp_grantdbacces s 'Login1'
EXEC sp_addrolemembe r 'MyRole', 'Login1'
EXEC sp_addlogin 'Login2'
EXEC sp_grantdbacces s 'Login2'
EXEC sp_addrolemembe r 'MyRole', 'Login2'
EXEC sp_addlogin 'Login3'
EXEC sp_grantdbacces s 'Login3'
EXEC sp_addrolemembe r 'MyRole', 'Login3'
GO
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO
SETUSER 'Login1'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO
SETUSER
SETUSER 'Login2'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO
SETUSER
SETUSER 'Login3'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO
SETUSER
GO
DROP VIEW MyEmployees
DROP TABLE Employees
DROP TABLE SecurityByDepar tment
EXEC sp_revokedbacce ss 'Login1'
EXEC sp_droplogin 'Login1'
EXEC sp_revokedbacce ss 'Login2'
EXEC sp_droplogin 'Login2'
EXEC sp_revokedbacce ss 'Login3'
EXEC sp_droplogin 'Login3'
EXEC sp_droprole 'MyRole'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Elham.Ghoddous i" <el******@yahoo .com> wrote in message
news:4c******** *************** ***@posting.goo gle.com...
How can I implement "Row Level Security" in SQL Server 2000?
Thanks alot.
