Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
acgt
Starting Member
1 Post |
 Posted - 2005-08-06 : 08:10:46
|
| Desperate for Help Please,Im 80% of the way through an MS access 2003 (adp) and SQL Server 2000 project where I have implemented row level security via views using an approach that I have seen working elsewhere (but not in access). The views themselves are very basic and designed to restrict which rows a user can select. The views are a simple join of a “Row Access table” (which all users have select permissions on) to the base table itself which is hidden from the user (select permissions revoked).However I committed the cardinal sin of not testing this approach as a standard user up until now. I know I need to shoot myself for this; I’ll do this later once I sort this thing out.The approach I used worked fine when I was developing as db owner. However when I try this approach as a standard user (who does not have select on the underlying tables), the view displays the data correctly, however no matter what I attempt the records remains un-updatable. If however I grant select on the underlying table (something I don’t want to do) then the row I’m editing becomes updateable (a start at least). However once I go to commit this record (by moving onto the next) I get an error stating that I don’t have update rights on the base table. If I grant update on the base table to that user, then the whole thing works….However this goes against what Im trying to do, which is hide from the user rows that they are not permitted to see by hiding the base tableBTW:- all updates, deletes and inserts are managed by triggers- The join is not the issue as the same problem occurs if the view is simply based on the one base table- Forms are not the solution as they remain un-updateable even with the necessary unique table settings- The same problem occurs basing a form on a stored procedure, if the base table isnot accessible its not possible to update via the view or SPIf I try the same thing by calling a simple SQL command (“update V_objective set title = ‘Whaaaaa!’ where objective_id =22”) and it works a treat. I dont even need update permissions on the base table. As suchI know my SQL logic is correct…Is there anyway that I can get this going….? I need to hide the base tables (or at least the base records). This has caused my blood pressure to go through the roof!Your help would be sooooo appreciated….Thanks in advanceAdam |
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2005-08-10 : 21:30:06
|
| Look up the "WITH OWNERACCESS OPTION" declaration in the Access help file, and see if that doesn't help you here.~ Shaun MerrillSeattle, WA |
 |
|
|
|
|
|
SQL Server, Updating via views based on tables wit