home
training courses
why choose us?
solutions
support
company
LearnItFirst User Forum
Welcome Guest
Search
|
New Posts
|
Members
|
Log In
|
Register
SQL Server Forum - LearnItFirst.com
»
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....
Back to top
Scott Whigham
Posted:
Thursday, October 05, 2006 11:37:28 AM
Rank: Super Mod
Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: Dallas, TX
hu0s wrote:
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....
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...
Back to top
Scott Whigham
Posted:
Thursday, October 05, 2006 11:48:28 AM
Rank: Super Mod
Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: 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.
Back to top
Users browsing this topic
Guest
Forum Jump
SQL Server Database Administration
- General SQL Database Question & Answer
- Backup, Recovery and Disaster Recovery
- SQL Server Security
- Integration Services (SSIS) and DTS
Transact-SQL Programming
- DML (SELECT, INSERT, UPDATE, DELETE) Questions
- Stored Procedures, Triggers, & Functions
SQL Server Scripts, Code Samples and SSMS Custom Reports
- All SQL Server Versions
- SQL Server 2005/2008
- SQL Server Management Studio Custom Reports
Customer Service
- Video Requests
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.
SQL Server 2005 DBA Training Videos
SQL Server 2008 DBA Training Videos
Watch this topic
RSS Feed
Email this topic
Print this topic
Threaded
Normal