471,092 Members | 1,076 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

ROW LEVEL SECURITY

How can I implement "Row Level Security" in SQL Server 2000?

Thanks alot.
Jul 20 '05 #1
1 2221
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 SecurityByDepartment 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(DepartmentId)
GO

CREATE TABLE SecurityByDepartment
(
UserName sysname NOT NULL,
DepartmentId int NOT NULL,
CONSTRAINT PK_SecurityByDepartment
PRIMARY KEY (UserName, DepartmentId)
)
GO

CREATE VIEW MyEmployees
AS
SELECT
e.EmployeeId,
e.DepartmentId,
e.SomeData
FROM Employees e
JOIN SecurityByDepartment sbd ON
sbd.UserName = SUSER_SNAME() AND
sbd.DepartmentId = 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 SecurityByDepartment VALUES('Login1' ,1)
INSERT INTO SecurityByDepartment VALUES('Login1' ,2)
INSERT INTO SecurityByDepartment VALUES('Login2' ,1)
INSERT INTO SecurityByDepartment VALUES('Login3' ,2)
GO

EXEC sp_addlogin 'Login1'
EXEC sp_grantdbaccess 'Login1'
EXEC sp_addrolemember 'MyRole', 'Login1'
EXEC sp_addlogin 'Login2'
EXEC sp_grantdbaccess 'Login2'
EXEC sp_addrolemember 'MyRole', 'Login2'
EXEC sp_addlogin 'Login3'
EXEC sp_grantdbaccess 'Login3'
EXEC sp_addrolemember '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 SecurityByDepartment
EXEC sp_revokedbaccess 'Login1'
EXEC sp_droplogin 'Login1'
EXEC sp_revokedbaccess 'Login2'
EXEC sp_droplogin 'Login2'
EXEC sp_revokedbaccess 'Login3'
EXEC sp_droplogin 'Login3'
EXEC sp_droprole 'MyRole'
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP

"Elham.Ghoddousi" <el******@yahoo.com> wrote in message
news:4c**************************@posting.google.c om...
How can I implement "Row Level Security" in SQL Server 2000?

Thanks alot.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by robert | last post: by
13 posts views Thread by MFS 43 | last post: by
reply views Thread by Jéjé | last post: by
3 posts views Thread by Dave Wurtz | last post: by
2 posts views Thread by evenlater | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.