question

sathishkumar avatar image
sathishkumar asked

user permissions

Hi, I have a table with 4000 records and 4 users work in 1000 records each, now my question is in sql server 2000 can we give permission for the users to work only in that 1000 and they cannot access other records...is it possible. thanks sathish
permissionsuser
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
SQL Server does not implement row level permissions directly. If you want to limit the rows, to which an user has access, you will have to not allow query the table directly and instead of this create a stored procedures and/or functions which will be used to retrieve and manipulate the data according the user. You can create a configuration table for the row level security and according that table you will provide results and or modifications to the data. Option could be also use of InsteadOf triggers to handle the row level security, but I would be careful before going this way. You can take inspiration in the technet article although it is related to the SQL Server 2005. [Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005][1] [1]: http://technet.microsoft.com/en-us/library/cc966395.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your answer
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
One mechanism you can use, assuming their login is in the data, you could create a view that filters on the user's login as a parameter, that you either pass it from the app or pull from the USER_NAME function. It's likely to lead to a table scan since with such a limited number of users you'll be access 1/4 of the data at a shot, but assuming there are other selection criteria you might have an index that leads to seeks.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, I have tried row level security ,its working fine in query analyzer but in enterprise manager if do update in view table it showing error like "UPDATE permission not allowed for the view, but in permission for the user i have give rites to select and update for view and only select option for the table.
0 Likes 0 ·
Oleg avatar image
Oleg answered
I would like to add to the answers above because the question is about the SQL Server 2000 which unfortunately does not have table valued functions and the views do not accept any parameters. If the situation is as simple as it is described in the question then you can consider the following options: If users connect to the database with Windows authentication then **system_user** can be queried to figure the user's name. This way, you can ensure that neither of these 4 users has a **select permission** to the table and create a view which will serve only allowed records to each user. Something like this: create view dbo.YourTableSubsetView as select * from dbo.YourTable where RecordID = 3001 and system_user = 'your_domain\user4' go Even better option would be to add another column to the table in question. Something like alter table dbo.YourTable add AvailableTo varchar(50) null -- and then update dbo.YourTable set AvailableTo = 'your_domain\user1' where RecordID = 3001 alter table dbo.YourTable alter column AvailableTo varchar(50) not null This way you can create the view like this: create view dbo.YourTableSubsetView as select * from dbo.YourTable where AvailableTo = system_user go Once the view is created you can **grant select on dbo.YourTableSubsetView** to each of the 4 users. Either of the 2 ways described above is used, the view will furnish only those records to the logged in user which the latter is allowed to view/modify. Since the view can safely map each record it returns to a single record in the base table, this view will be updateable even without instead of trigger, and each user will be able to update only own records. If all users connect to the database using a common connection string so there is no way to identify the user without passing a parameter then you can delegate this work to the front end and instead of creating one view, create 4 views (one for each user) and grant select permission to each of the view to only one appropriate user. This will allow the same results, but will be a bit more difficult to implement. Oleg
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your answer sir, the same way can we give INSERT permission.
0 Likes 0 ·
@Oleg - +1, but I am 99.95% sure that 2000 does have table-valued functions... [MSDN page][1]: [1]: http://msdn.microsoft.com/en-us/library/aa258261(SQL.80).aspx
0 Likes 0 ·
@Matt Whitfield Yep, this was my bad, I should have checked the BOL before making a stupid assumption. Last time I had to work with SQL Server 2000 was about 2 years ago, and now we are already half way on 2008 R2, rest is 2005, so I don't have any 2000 instances to play with, even at home.
0 Likes 0 ·
@Oleg - quite understandable - I had to totally bust my balls to get a 2000 instance running properly for the Atlantis stuff...
0 Likes 0 ·
Hi, I have tried row level security ,its working fine in query analyzer but in enterprise manager if do update in view table it showing error like "UPDATE permission not allowed for the view, but in permission for the user i have give rites to select and update for view and only select option for the table.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered
I just wanted to add an option in here... Why don't you just create some updatable views, and specify them `WITH CHECK OPTION` - and then give permissions on those views, and not the underlying tables. From [this MSDN page][1]: >**CHECK OPTION** > >Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed. This basically means that the user with permission to view A gets to see the rows that view A allows, but doesn't allow those rows to be updated such that they no longer appear in view A. For example - you have views split so that view A only sees records with `magicNumber = 1` - if `WITH CHECK OPTION` was specified on the view then you would not be able to update a record using the view and set `magicNumber` to 2 (which would then mean the row was no longer included in the view). [1]: http://msdn.microsoft.com/en-us/library/aa258253(SQL.80).aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.