row level security for SQL Server 7.0

[sharonc]

I have an application with a MS Access 2000 frontend and a SQL Server 7.0, NT 4 for a back end.

My application uses 24 tables. I need to setup security so that the users can only modify or add the records(rows) they add to the tables. I don't want them to be able to change someone else's record.

Is the only way to do this through stored procedures?
Or is there a better way?

 

[tlbroadbent]

I assume the tables have a column identifying the row owner. If this is true then try the following.[ol][li]Create a view of each table similar to the following.

Create vTable1 As
Select * From Table1 Where RowOwner=SYSTEM_USER
Create vTable2 As
Select * From Table2 Where RowOwner=SYSTEM_USER
.
.
.
Create vTable24 As
Select * From Table24 Where RowOwner=SYSTEM_USER

NOTE: I also assumed that the SQL login name is used for the row owner.

FYI: you can set the default value of a column to SYSTEM_USER and SQL Server will automatically insert the login in the column.

Grant the users all permissions on the views and grant NO permissions on the tables. They will only be able to update rows that contain their login in the RowOwner column through the views. The tables will be locked down from any access except by the DB owners or DBA's.

Of course, in Access you'll want to reference the views rather than the tables. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[sharonc]

thank you for the info.

I will give this a try.