January 18, 2007 at 12:26 pm
We are now running Office 2007 so now Access can actually deal with schemas but I still have a problem with security.
We want out users to be able to modified their data using views (queries) only. That way we can restrict them to only the data they are authorized to modify. Views seemed like a logical way. Mind you that they are using MS Access and not vb or Management Studio; therefore, we have created the views on the sql server and gave the users "select" permissions on the views, then we gave them "select, update, delete, insert" permissions on the related table(s).
The problem now is when using MS Access Projects (.adp) we haven't found a way to restrict them from going directly to the tables. When we removed the "select" permission from the table(s) then they won't show up in MS Access but then they will only be able to view and insert records and not update or delete from the views (queries).
And ideas?
January 19, 2007 at 5:49 am
Could you give them no permissions to the tables themselves so that they do everything through the views?
For views that need to support insert/update/delete you can create instead of triggers to handle the modifications to the real table.
January 19, 2007 at 7:58 am
I don't remember the syntax off the top of my head, but there is also a way to create a view that will prevent a user from making inserts/updates/deletes against the view that the view can't see.
I remember reading about it in Microsoft's MCTS study guide which I don't have with me right now, but I just did a quick search and don't see it in Books Online.
The Redneck DBA
January 19, 2007 at 9:27 am
We are building forms (vb and access) to deal with normal users who need to edit data and for the most part this will do fine but for those users who are a little bit more knowledgeable about MS Access will also be able to bypass the forms and views and edit data directly from the tables. So this is my dilemma. I think at this point it is what it is and we just have to monitor who is modifying what then correct the problem when it occurs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply