Security - View is not able to select from base table
-
First, I am not the SQL expert, but I am becomeing it by default. I do not handle overall security on the db server, but I do handle
security on specific tables. I have created users before, but not with all of the restrictions that I want this time.
I am trying to create a role that will grant a use read-only on some views. They should not be able to see any of the base tables.
When I assign the role to a user, they get an error saying:
The SELECT permission was denied on the object 'baseWorksheet', database 'TestDatabase', schema 'Stats'.
but the frustrating thing is that is only sometimes throws this error. I can get it to execute successfully if I right click on the view
and choose to Script View As>select to>new query window, but only if I remove the go statement after the using (with the go statement
it throws the same error as above" or if I right click and choose select top 1000 roles.
If I just open up a query window and type select * from [reporting].[reportWorksheet] then it throws the error above.
Also on script select, if you add a space to one of the fields then it throws the error above.
this is the first time I have seen this and it is completely confusing (and frustrating) to me.
What I have done:
I assigned all ownership to user dbo. This includes all schema ownership as well.
I created a role reporter, in which the owner is set to dbo and its owned schema is reporting.
I then created a user and assigned membership to the role reporter.
TABLES:
baseworksheet is in schema [Stats]
viewworksheet is in schema [reporting]
there are about 5 different schema's in total, and different views in the reporting schema use different schema's (or combinations), but I
assume that if I can get this simple case to work, then I should be able to fix the others (hopefully)
Thanks,
In way over my head, Arbra
-
Hi Arbra,
Can you please reate a simplified repro script of what you are trying to do? So just create a simple demo table of two columns or so, add one or two rows of data, then create a simple view on top if it (can be as simple as SELECT Col1 FROM DemoTable), and then add the DCL used to set the permissions, and your observations on where it does not work the way you want it to. That information makes it much easier for others to check what you did, reproduce it on our test system, and troubleshoot it.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
I will try. I mostly use the UI, so some of the scripts below are after I created it with the UI I then just generated scripts.
Generate Tables:
****** Object: Table [Stats].[WorksheetE] Script Date: 4/4/2016 3:18:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
if OBJECT_ID (N'Stats.WorksheetBase', N'U') is not null
Drop Table [Stats].[WorksheetBase]
CREATE TABLE [Stats].[WorksheetBase](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Code] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert Values:
insert into [Stats].[WorksheetBase] (Code)
values ('First'),('Second'),('Third');
Create View:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [Reporting].[WorksheetReporting]
AS
SELECT
.[Code]FROM [Stats].[WorksheetBase] AS
;GO
Create User:
/****** Object: User [cmstest] Script Date: 4/4/2016 3:31:17 PM ******/
CREATE USER [cmstest] FOR LOGIN [cmstest] WITH DEFAULT_SCHEMA=[Reporting]
GO
Owned Schema's of user DBO (Picture since I have no clue how to do this without UI):
Owned Schema's of role REPORTERS (this is the only role it owns, nothing else) (Picture since I have no clue how to do this without UI):
User Membership (User has no owned schema's selected) (Picture since I have no clue how to do this without UI):
After logging in as the user cmstest I see the following:
No tables listed (this is correct)
Only views that are in schema REPORTING (this is correct)
When running a select statement I get this error (which is the problem)
Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'WorksheetBase', database 'CmsTestDB', schema 'Stats'.
As stated before, my knowledge of SQL in relating to security is VERY new.....I usually use the UI for most things, this is not my main field or expertise, but due to the small company it is something that has fallen in my lap. I have tried everything I know, and am now way over my head. Any help is appreciated. Also, it is not possible to drop and recreate the database since it is
1. In production and
2. the database is already over 260 GB of data (which is why the users cannot have access to tables, they would bring down the system running select * with no conditions due to the number of records in some of the tables)
Any help is appreciated.
-
Hi Arbra,
My apologies for the delayed reply.
The problem you are seeing is caused by ownership chaining. Or rather, lack thereof.
So what is ownership chaining? It is a bit like the difference between a family house and a student dormitory. If I, as the owner of my house, decide to give you a key, I more or less implicitly trust you with all that is in ny house. By giving youa ccess to my home, I implicitly give you access to everything in the house. I can make that decision because all that's in the house is mine.
However, if I share a student dorm with others and give a key to my newest girl friend, the other inhabitants might not want her to raid the fridge - and because most of the beers in that fridge are theirs, they have all right to refuse that. So perhaps we would need a separate lock on each fridge, and anyone who is okay with my firl drinking their beers can give her the keys as well.
Your setup is similar to the dorm. The "Stats" schema (and hence the table) is owned by user dbo. However, the "Reporting" schema (and hence the view) has a different owner, the database role "Reporters".
When "Reporters" gives a user access to the view (the house), SQL Server cannot simply assume that the owner of the table (the beers in the fridge) is okay with that person accessing the data. They will need to give permission explicitly. If the view had been owned by the same user, then this would not have been the case and the permission to grab a beer would have been implied.
The more technical explanation is that SQL Server first checks that cmstest has SELECT permission on the view (which, as member of the owning role, they have). The view then accesses a table. If that table has the same owner, no further checks are needed (that is ownership chaining in practice). But in this case the owner is different and SQL Server checks for SELECT permission on the table as well, resulting in an error.
The best way to fix this is to make a single user (usually dbo) the owner of all objects in the database, so that ownership chaining can always be applied. In your case, that would mean that the Reporting schema has to be owned by dbo, not by the Reporters role. To make sure that members of the Reporters role can still use this view, isntead of making them the owner they should be given SELECT permission:
GRANT SELECT ON Reporting.WorksheetReporting TO Reporters;Now when cmstest logs in and uses the view, SQL Server will check SELECT permission on the view, see that they have this permission by virtue of being in the Reporters role, and then no further permission on the underlying table are checked because it is owned by the same user.
If you are really in a dorm-like situation, with multiple users in the same database being responsible for security to their objects and not a single owner for all of them, then the above will not work. There are ways to work around this, but they are more complex. I think that in your case, they are not needed.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Thank you,
I am not in a dorm-like situation (and it appears that is very good that I am not). I have complete control over my database, I however do not have complete control of the server, so your solution will solve the issue that I am having.
That worked perfectly and gave me the expected results, with the exception of one minor bug/issue that I cannot explain (and is just leaving a "why?" question nagging in the back of my mind.
So I did as you suggested, and put user "dbo" as the owner of all of the schema's in the database. I then created a role "Reporters" and gave it select access to the schema "Reporting". Then I assigned the users I wanted to access the database the "Reporters" role and they are only able to see the "Reporting" schema and select from them. All works as you laid out in your description.
Now while testing this on my dummy user, I hit the following that I cannot explain.
I right clicked on a view and used "the Script View as" option to generate a select statement that looks like the following:
USE [CmsTestDB]
GO
SELECT
FROM [Reporting].[WorksheetReporting]
GO
Which is a pretty standard select statement. When I run it, I get the following error (that lead to me asking for a solution):
Msg 229, Level 14, State 5, Line 4
The SELECT permission was denied on the object 'WorksheetBase', database 'CmsTestDB', schema 'Stats'.
But if I just do the following (in the same window):
USE [CmsTestDB]
--GO
SELECT
FROM [Reporting].[WorksheetReporting]
GO
then the query executes as expected and returns the results from the view.
I am also able to run this query successfully:
--USE [CmsTestDB]
--GO
SELECT
FROM [Reporting].[WorksheetReporting]
GO
And if I use the "New Query" button and type in my SQL statement, it works just fine (again not using the using statement of the go command)
I do not understand why the "GO" statement is causing issues. If you have any ideas as to why I would love to hear since it is aggravating me (and took me forever to find because I thought that the changes made were not working, I only accidently discovered what the issue was and that the changes were actually working correctly, that it was the "GO" that was throwing the error - or at least that line - since commenting it out solved the issue) and is leaving a nagging feeling that I might have something else set incorrectly in the DB.
-
If you run just these statements, do you still see the error?
USE [CmsTestDB]GO
And how about
GOSELECT
FROM [Reporting].[WorksheetReporting]
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
The GO causing an error is certainly something I've never seen before.
I know this is really reaching, but I'm going to ask anyway because the error makes no sense. Just as a sanity check, tell us you haven't changed the batch separator in SSMS. Click Tools...Options...Query Execution. Look in the Batch separator text box and tell us it has GO in there.
Nothing else I can think of makes any sense.
-
I tried the following:
For this code block:
Phil Parkin (4/11/2016)
If you run just these statements, do you still see the error?USE [CmsTestDB]GO
The above query runs with no errors.
And as to this code block:
And how about
GOSELECT
FROM [Reporting].[WorksheetReporting]
The above query throws the error listed previously.
-
Ed Wagner (4/11/2016)
The GO causing an error is certainly something I've never seen before.I know this is really reaching, but I'm going to ask anyway because the error makes no sense. Just as a sanity check, tell us you haven't changed the batch separator in SSMS. Click Tools...Options...Query Execution. Look in the Batch separator text box and tell us it has GO in there.
Nothing else I can think of makes any sense.
I have never seen this behavior either (and agree that it makes no sense), and was lucky that I even found out what was happening, otherwise, I would still be thinking that nothing was working!
As to your question, I had not modified the setting, but double checked just to make sure. GO is listed as the batch separator, and is the only option listed.
-
Very strange indeed.
Try this one:
GO 3SELECT
FROM [Reporting].[WorksheetReporting]
Do you see the error once or three times?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I am testing this on SQL Server 2014 and am not able to reproduce your findings. I get the expected query results with or without the GO or database context change.
So....
Is your version SQL Server 2012? If so, I will commence testing there.
As for now, I can only presume that maybe you are running into an issue where SSMS is confusing which user is actually performing the queries (as can be manifested by performing an execute as, changing database context, and then trying to revert permissions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Phil Parkin (4/11/2016)
Very strange indeed.Try this one:
GO 3SELECT
FROM [Reporting].[WorksheetReporting]
Do you see the error once or three times?
Ok...I tried running it, I only see the error 1 time. I am getting the tilde under the 3 in the editor and it states that "Could not find the stored procedure", but it still allows me to execute the query.
Additional Information:
I am on a SQL 2014 instance (12.0.2000) and using SSMS 2014 to view the server in case that helps. The database in question is set up as Sql Server 2014 (120). To my knowledge there is nothing special in the creation of the DB, defaults were used.
Also, I am only seeing the error under the restricted test user account (and assuming anyone else utilizing the role). When I log onto the server as myself (and I am an admin), then the using and the go statements work without throwing an error.
-
Quick questions, which objects in db CmsTestDB schema Stats are used? what are the permissions the user has? Are all references schema qualified? What is the user's default schema in CmsTestDB ?
😎
-
This
I am getting the tilde under the 3 in the editor and it states that "Could not find the stored procedure", but ...
Is odd.
I don't know what is happening, but you should not see that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Eirikur Eiriksson (4/11/2016)
Quick questions, which objects in db CmsTestDB schema Stats are used?I do not understand this question. Under the DB CmsTestDB, there are multiple tables in multiple schema. The schema's are "logical" breaks, so the Stats schema contains compiled calculations to assist with query performance (as in averages, etc. Numbers that are always the same since the data is only updated at set periods of time). This table does not reference any other tables.
what are the permissions the user has?
As stated earlier, the user is assigned a role. That role only has select access to the schema "Reporting". The view under this schema references a base table that is under schema "Stats". DBO user is the owner of all of these schema's. Please see previous threads for exact examples of how the DB is laid out.
Are all references schema qualified?
Yes
[Quote] What is the user's default schema in CmsTestDB ?[/quote]
Reporting
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply