December 31, 2003 at 10:22 am
Is it possible to update/insert/delete rows in a View if the user doesn't have any permissions on the underlying table? I created a table and revoked all permissions for all database users. I created a view based on the table and granted select,insert,update,delete permissions to a test user account named TEST. I open the Enterprise Manager as the TEST user and open the view. When I try to update, insert or delete a row, I receive the following message: UPDATE permission denied on object 'tbltest', database 'Northwind', woner 'dbo'. According to SQL Server Books Online, a user should be able to update a view even if they don't have permissions to the underlying table. Here's an excerpt from Books OL:If the owner of a base table wants to prevent users from accessing the table directly, they can grant permissions on views or stored procedures referencing the table, but not grant any permissions on the table itself.
December 31, 2003 at 10:39 am
The owner of the view must be the same as the owner of the table and you must grant update permissions on the view.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 31, 2003 at 10:53 am
dbo is the owner of both the view and the table. The logged in user only has update permissions on the view and not the table. If the logged in user doesn't own the table or view, can they still update the view. I'm a little confused by the ownership and update permissions issue.
December 31, 2003 at 11:13 am
quote:
dbo is the owner of both the view and the table. The logged in user only has update permissions on the view and not the table. If the logged in user doesn't own the table or view, can they still update the view.
Yes. Why, is it not working?
December 31, 2003 at 11:41 am
No, it is not working. I created a very simple test using Northwind database. I created a singe table named tblTest with 3 columns. Here's the script to create the table: CREATE TABLE [dbo].[tbltest] (
[Pkey] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]. Here's the view script: CREATE VIEW dbo.vwTest
AS
SELECT fname, lname
FROM dbo.tbltest
Public role has Select permission on the table and no permissions on the view. The logged in user has Select, Insert, Update, Delete permisssion on the view only. I believe this should work. I created an Access 2000 project for the Northwind database. I created a form and set the recordsource property to the view. When I try to update,insert,delete I receive an error. Is there something I'm forgetting in SQL?
December 31, 2003 at 12:38 pm
What happens when you run the same update query from Query Analyzer?
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 31, 2003 at 4:10 pm
I just did the same with Access 2003 (don't have Access 2000). It worked perfectly for me.
January 2, 2004 at 10:22 am
I ran this from one session:
CREATE TABLE [dbo].[tbltest] (
[Pkey] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
go
CREATE VIEW dbo.vwTest
AS
SELECT fname, lname
FROM dbo.tbltest
go
grant select on tbltest to public
go
grant insert, update on vwtest to steve
go
drop view vwtest
go
drop table tbltest
go
(except for the drops) and from another session, logged in as "steve", I ran:
select * from tbltest
which worked and then
select * from vwtest
which didn't. As expected. This worked.
insert vwtest select 'steve', 'jones'
but this did not, I get a "select permissions denied"
update vwtest set fname = 's' where fname = 'steve'
however
update vwtest set fname = 's'
works. Apparently the WHERE clause needs select access.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
January 5, 2004 at 7:32 am
I opened Query Analyzer and ran script to insert, update, delete rows for the view named vwtest. It worked fine. When I open the vwTest it opens read-only. I created an Access 2003 project that connected to Northwind. I opened the vwTest in Access and attempted to update a column. I received the error: UPDATE permission denied on object 'tbltest', database 'Northwind', owner 'dbo'.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply