By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

ROW LEVEL SECURITY

P: n/a
How can I implement "Row Level Security" in SQL Server 2000?

Thanks alot.
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.