Updating Views

[Guest]

I want to use an updatable view to restrict the records end users can access
and manipulate. The view reads as follows:

Create View Customers_vw
WITH VIEW_METADATA
As
Select *
From Customers
Where Office ID In (Select OfficeID From Agents Where Agent =
dbo.fnc_GetAgentID())

dbo.fnc_GetAgentID() is a user defined function that returns an id based on
a user’s login information. WITH VIEW_METADATA is a directive to ADO that
update requests should update the view and not the table directly.

I have granted all permissions on the view. The view is updateable if I
give users all permissions on the customers table, but that defeats my
purpose. The view is also updateable if I change the In clause to a set
value:

Select *
From Customers
Where Office ID = 10

The view with the In clause is updatable if I use it to populate an ADO
Recordset object in code. The view is also updatable through tools like
Enterprise Manager. It seems to be an Access problem. The problem occurs in
both 2002 and 2003 versions.

KB307925 offers some suggestions, but again the view is not updateable when
there is an In statement in the view.

Thanks

Rick

 

[Malcolm Cook]

Rick,

are you using the view as a forms recordsource and find that you can't
update through the form?
if so, have you set the 'unique table' property of the form to be
'Customers'?

 

[Guest]

Malcom:

Thanks for your input.

Even when editing through a from with a Unique Table setting the view is not
updateable. I believe whether by design or by bug views with an 'In' filter
in the 'Where' clause are not updateable through Access.

 

[Brendan Reynolds]

Malcom:

Thanks for your input.

Even when editing through a from with a Unique Table setting the view is
not
updateable. I believe whether by design or by bug views with an 'In'
filter
in the 'Where' clause are not updateable through Access.

I'm afraid I don't have a solution to your problem, Rick, but this does not
seem to be the reason. I tested with the following simple view (in Access
2003), using the Northwind database.

SELECT CategoryID, CategoryName, Description
FROM dbo.Categories
WHERE (CategoryID IN (1, 2, 3))

This view is updatable. So I tried a slightly more complex view, including a
join ...

SELECT dbo.Categories.CategoryID, dbo.Categories.CategoryName,
dbo.Categories.Description, dbo.Products.ProductName
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID =
dbo.Products.CategoryID
WHERE (dbo.Categories.CategoryID IN (1, 2, 3))

This is still updatable.

 

[Guest]

Hi Brendan:

The issue only happens for me when the user does not have privileges on the
base table.

Both the base table and the view are owned by dbo. I have granted full
privileges to the view for the user group that should not have access to the
base table directly. My understanding is the users granted access to the
view should inherit dbo privileges for any dbo owned objects the view uses.

If I grant the end user account I privileges to the base table they are able
to update records through the view. When I remove all privileges to the base
table the view is read only. Even with base table privileges removed from
the base table, the view is updateable through tools other than Access
(Enterprise Manager, ADO in VBA Code) using the same login info.

Thanks for your help.

 

[caracol]

Hello together,

although I can not provide an solution as I'm searching for one for what I think is exactly the same problem, I hope I can provide some additional information. At leat Rick's description reads exactly like what I'm experiencing, too.

When I access my view directly (without a form) per .adp (OLE DB), the right is always SELECT only, unless I use an account with serverbased writing access like an admin or an account with datawriter role.

The error message is "INSERT / UPDATE on the object TABLE is not allowed." What I find strange is the reference to the table although I'm accessing the view. This could be a hint as where to continue searching.

Accessing the view with an .mdb per ODBC on the same view, the rights are SELECT, INSERT and UPDATE, just as they should be.

So I think the question is: Why are the rights different for one and the same view, when the only difference is the way of accessing the database (OLE DB / ODBC)?

Of course I could change the access to ODBC, but I'd have to cope with the inferior way when I could use the much more comfortable OLE DB.

I'm trying to find a solution since almost a week now, but everything I try just won't do.

Anyone else with any ideas, comments or hints?

Regards,
caracol

 

[caracol]

A little workaround, though possibly risky

Hi together,

I just wanted you to tell you one little trick that at least works without harming security context to much, at least to my understanding, after having tested and tried for weeks.

When I grant the group(s) that shall INSERT or UPDATE my views the right "CREATE VIEWS", the views become updatable even if they do not have UPDATE or INSERT rights on the underlying tables.


In my opinion this grant of an database-wide right is inferior to the fact of having to give them the same rights on the tables as they can only create views on other views or tables they already have access to.

I would appreciate it if anybody could tell me should I be wrong.

Perhaps this can help someone else, too.

Greetings,
caracol