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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 View base table permission problem

Author  Topic 

angelran
Starting Member

7 Posts

Posted - 2013-02-22 : 07:54:32
Hi guys,

I'm new here and seeking for some help.

I created a new user in DB and granted "select" permission on a view. when I run the view as that user no row was returned unless I granted "select" permisson on the base table. The new user only has "public" access to the DB.

I've done the samething on other DB and it worked fine, don't know what went wrong on this one?

Any advice?

thanks guys.

Rocky

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 08:00:56
It may have to do with a broken ownership chain. Are the view and table owned by the same principal? This page can explain what I am talking about better than I can: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.90).aspx

If they are owned by different principals, you will need to grant select permission on the base table also. If they are in two databases, then again you will need to grant permissions on the base table unless you set up cross database ownership chaining (which you probably should not).
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 2013-02-22 : 09:24:10
quote:
Originally posted by James K

It may have to do with a broken ownership chain. Are the view and table owned by the same principal? This page can explain what I am talking about better than I can: http://msdn.microsoft.com/en-us/library/ms188676(v=sql.90).aspx

If they are owned by different principals, you will need to grant select permission on the base table also. If they are in two databases, then again you will need to grant permissions on the base table unless you set up cross database ownership chaining (which you probably should not).



Thanks James. The view and the base table are in the same DB. How do I check if the view and the base table belongs to different owner?

thanks.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 10:01:23
Usually the owner is a schema. Run these two queries, they probably will return NULL.
SELECT principal_id FROM sys.objects WHERE [name] = 'YourViewNameHere'
SELECT principal_id FROM sys.objects WHERE [name] = 'YourTableNameHere'
If they return null, run this:
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[VIEWS] WHERE TABLE_NAME = 'YourViewNameHere';
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[TABLES] WHERE TABLE_NAME = 'YourTableNameHere';
That will tell you which schema owns the objects.
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 2013-02-22 : 10:21:47
quote:
Originally posted by James K

Usually the owner is a schema. Run these two queries, they probably will return NULL.
SELECT principal_id FROM sys.objects WHERE [name] = 'YourViewNameHere'
SELECT principal_id FROM sys.objects WHERE [name] = 'YourTableNameHere'
If they return null, run this:
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[VIEWS] WHERE TABLE_NAME = 'YourViewNameHere';
SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.[TABLES] WHERE TABLE_NAME = 'YourTableNameHere';
That will tell you which schema owns the objects.



Thanks.Just tried and both of them belong to dbo.
I tried following:
select * from myView -- no row returned
select * from myBaseTable -- select denied

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 10:43:01
I am not sure why you are seeing what you are seeing. Even if there was a deny on the table, ownership chaining should have circumvented that. Regardless, can you do two things?

1. Right click on the table name in SSMS object explorer and in properties/permissions see if there are any deny's in the permissions tab.

2. Do an experiment with a test table and test view like shown below:
CREATE TABLE dbo.TestTable(id INT);
GO
CREATE VIEW dbo.TestView AS SELECT id FROM dbo.tempTable;
GO
GRANT SELECT ON [dbo].[TestTable] TO [LessPrivilegedUser]
GO
INSERT INTO dbo.tempTable VALUES (1);

-- Connect as the [LessPrivilegedUser]
SELECT * FROM dbo.TestView;
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 2013-02-22 : 10:58:46
I assume "tempTable" should be TestTable?

I got the below message:

The SELECT permission was denied on the object 'TestView'

but select * from TestTable is working fine because the permission was granted.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 11:04:25
Darn,I messed up the whole thing - Was multi tasking at work :) Let me rewrite it again. What I have below is really what I meant, sorry about the confusion:
CREATE TABLE dbo.TestTable(id INT);
GO
CREATE VIEW dbo.TestView AS SELECT id FROM dbo.TestTable;
GO
GRANT SELECT ON [dbo].[TestView] TO [LessPrivilegedUser]
GO
INSERT INTO dbo.TestTable VALUES (1);

-- Connect as the [LessPrivilegedUser]
SELECT * FROM dbo.TestView;
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 2013-02-22 : 11:17:54
Not a problem. :)
I've tried as per your instuction and it worked!!

So I guess something must be messed up in the base table or view?

thanks.
Go to Top of Page

angelran
Starting Member

7 Posts

Posted - 2013-02-22 : 11:38:58
Ah ha!! It wasn't anything about the ownership chain...I used "IDENT_Current" in the view...of cause it would not run, from my understanding the user needs to be db_onwer to read meta data?

Anyway, thanks heaps for your help James!!
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 11:50:49
Not sure that a user needs to be dbowner to view metadata. That would be way too much permission. From this page it seems like the user needs some permission (such as select) on the object http://msdn.microsoft.com/en-us/library/ms175098.aspx

That would explain why you were able to view the data via the view when you granted select on the table.

How to get around that... I can't think of a good option at the moment. The only thing that comes to mind is to create a stored procedure and change the execution context in the stored procedure (and revert after the select statement). But then that wouldn't be the same as a view i.e., you wouldn't be able to use JOINs against it etc.
Go to Top of Page
   

- Advertisement -