February 26, 2006 at 8:17 pm
Database 1 has some fairly broad permissions, public has All permissions on most tables. For reporting purposes, I want a second database with views created against these tables, where people will only have Select permissions (to discourage accidental updates while browsing data).
I have created views (simply as Select * from DB1..tablename), and Granted Select and Denied all other permissions to Public. However, anyone who is a member of Public in both databases can alter the underlying data. It seems that the permissions from the table in Database 1 are being accepted over the top of the more restrictive permissions on the view in database 2.
Any ideas how I create my 'read only' database would be appreciated. At this stage tightening up security in database 1 is not an option.
February 27, 2006 at 11:27 am
Sure they aren't accessing the tables directly instead of using the view?
Cross database ownership enabled?
February 27, 2006 at 1:53 pm
Quite sure, I have tested this myself, opening one of the views in Enterprise Manager (logged in as a test user). The view has deny update, but I can change data.
February 28, 2006 at 12:31 am
The user isn't system administrator or db_owner?
February 28, 2006 at 3:31 pm
The user is not dbo or sysadmin. But that question leaves me perplexed. My understanding of what BOL says is that a Deny overrides everything else. If you Deny permissions to public, then no one is going to be able to perform that task. It seems that here, the deny is being completely ignored.
I have tried creating the views with an owner other than dbo, just in case somehow ownership chaining was impacting on things. Again, with public set to Deny Update, Insert and Delete on the views, these permissions are being completely ignored. As both the test user and as dbo I can insert and update on all of the views.
March 1, 2006 at 5:51 am
It may have to do with the fact that you are creating a view in database B that references tables in database A. Without seeing all the details, it tough to say. Alternatively, maybe you could add a computed column to the view, say GetDate() AS CurrentDate to make the view not updatable under any circumstances.
--------------------------------------------------------------------
From BOL:
Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.
--------------------------------------------------------------------
March 1, 2006 at 8:28 am
Something else is going on because they should be blocked by the Deny. Just tested it and got the expected error.
How are the users logging into the SQL Server? Windows login? If so, what groups is that login a member of? Are any of the groups a member of the sysadmin fixed server role (including BUILTIN\Administrators, which is often left with such permission)?
K. Brian Kelley
@kbriankelley
March 1, 2006 at 5:13 pm
Thanks for your interest in this...
Here's what I know. The SQL Server is NT Authentication only. TestUser belongs to a group called TUsers. TUsers has no permissions on anything in the reporting database. Neither TUsers nor TestUser are members of any fixed server role except public, in either database.
I tried adding a calculated column to one of the views so the view was
I was still able to insert and update to this view - the calculated column did not make a difference.
I'm really perplexed by this one.
If there are specific outputs that would help, please post and I will get them.
Again, thanks all of you for your help to date
David
March 2, 2006 at 9:33 am
Is TestUser a member of any other group? If you look at the OS level, is TUsers a member of the local administrators group?
K. Brian Kelley
@kbriankelley
March 2, 2006 at 1:38 pm
No, Testuser is not a member of any other group and TUsers is not a member of the Local Administrators group.
March 2, 2006 at 9:43 pm
When you log in as TestUser and go to the database, if you execute the following query, what user is returned?
SELECT USER_NAME()
K. Brian Kelley
@kbriankelley
March 2, 2006 at 9:58 pm
I get DOMAIN\Testuser
March 7, 2006 at 2:52 pm
Did you check "Cross database ownership"
Like be low code replace Northwind with the database with the tables in it.
Tim S
USE master
EXEC sp_configure 'Cross DB Ownership Chaining'
EXEC sp_dboption 'Northwind', 'db chaining'
March 16, 2006 at 11:01 am
David, were you ever able to piece together why the users had access they shouldn't through said view?
K. Brian Kelley
@kbriankelley
March 16, 2006 at 8:46 pm
Never have sorted this out. For the moment it has lost priority, but I know that it is going to rear it's ugly head again. What has confused me the most is all of the questions about cross database ownership etc. Based on my understanding of things, there are two fundamentals that I want someone to confirm to me.
1) The privileges assigned to a view do or do not interact with the privileges assigned to the underlying tables. My understanding is that the two sets of privileges are independent of one another. I can grant someone select access to the view and deny select access on the underlying table and the person should be able to select on the view, but not if they tried t ogo straight to the table. This is one of the key reasons for using views, as a way of ensuring that people can only see the rows and columns that should be available to them.
2) A Deny overrides everything else, no matter where in the chain of privileges it resides. BOL seems to say that if Public is given a deny, then it won't matter what anyone else does, no-one can have that privilege on the particular object.
If these two statements are in fact correct, then I believe that what I am seeing is a bug, and I will escalate it to Microsoft. If the statements are incorrect then it would be great to be set straight.
Cheers
David
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply