Set different permission for roles on same table - SQL Server Security

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » SQL Server Security » Set different permission for roles on same table

Set different permission for roles on same table

Options

Previous Topic · Next Topic

hu0s

Posted: Tuesday, October 03, 2006 12:06:38 AM

Rank: Newbie

Joined: 10/2/2006
Posts: 1
Points: 3

I want to accomplish the following

I have microsoft access FE and SQL server BE. The tables in SQL are linked via ODBC. I have 15-20 users which belong to different SQL roles and need INSERTS , UPDATES , DELETE permission on a certain table . That is quite staright forward and I have done that. Now the problem is that each role should be able to update and delete only those records which they have created / inserted already, Not every record in the table. How can I do that? Is it possible in SQL server itself, if yes how? or I have to use some FE code?

Please help....

Scott Whigham

Posted: Thursday, October 05, 2006 11:37:28 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

hu0s wrote:

What you're talking about is what we call "row level" security; you want certain people to only see certain rows in the table instead of the entire table. Generally, when we give someone SELECT permission, we give them select on the entire table. Now we might say that they're only allowed to use x, y, and x columns but they can access all the rows.

What you're going to need to do to implement row-based security in SQL Server is to modify the underlying table to include some column that indicates the row's owner. Something like:

Code:

ALTER TABLE Customers ADD Owner SYSNAME
GO

Now, instead of letting users directly access the underlying tables, you'll give them access to the stored procedures:

Code:

CREATE PROC AddCustomer
AS
INSERT Customers (CustId, Owner) VALUES ('ALFKI', USER) -- returns the user name
GO
CREATE PROC GetCustomers AS SELECT * FROM Customers WHERE Owner = USER
GO

Is that what you're looking for? If so, search in Books Online for the functions "USER", "SUSER_SNAME()", SUID and others (depending on your version). This code is probably best for SQL 2000 but it also works for SQL 2005. You didn't mention which version of SQL Server so this is pretty generic.

Oh, and feel free to make the GetCustomers stored proc a view...

Scott Whigham

Posted: Thursday, October 05, 2006 11:48:28 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

You might also like this article: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx]Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005. It's quite deep but, if you'll search the page for "Roles", you'll find some good information.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.