July 13, 2010 at 3:21 am
Hi.
This question relates to sql server 2008.
We are putting together a solution which includes deploying reporting services. We are going to use stored procedures to access a dimension model. However we want one of the dimensions, lets call it dimSecurity to not be directly accessable by the reports, i.e we don't want the report writers to be able to select directly from the table in the stored procedures.
The problem is that even when you deny select on the table, the table can still be viewed because its in a stored proc. How can this be prevented?
Ideally we would want to maintain the abillity to access the table through a UDF (for a few reasons i won't go into).
Thanks
Bob
July 15, 2010 at 8:43 am
The issue that you're facing is something called ownership chaining. Normally it's a good thing because it means I can use a stored procedure to reference a base table and cotnrol how that table is queried. As long as I only give access to the stored procedure, the end user has to do it my way. Here's how ownership chaining works:
If two objects have the same owner and one object refers to the other (such as the stored procedure referring to the table), then security isn't checked on the referral. So if a user has the ability to execute the stored procedure, and the stored procedure references the table, then the end user can use the stored procedure to access the table, but only through said stored procedure.
Now, as of SQL Server 2005 with the introduction of true schemas, most objects don't have an owner. In that case, if an owner isn't specified on the object, that the owner of the schema is used. And as long as owners are the same, the referred to object's security will be ignored. So that explains what you're seeing.
Here's a question for you... is the stored procedure ever accessed directly? Or is it only accessed through other stored procedures? If it's only accessed by other stored procedures and not by the end user directly, then you can use ownership chaining here. Simply revoke all access to said stored procedure. As long as the referring object has the same owner, the stored procedure is still accessible, but only through the referring object.
K. Brian Kelley
@kbriankelley
July 19, 2010 at 10:54 am
Hi Brian,
Thanks for replying. I have found a few articles that refer to ownership chaining but haven't been able to find anything specific enough to my problem to help so far.
LEt me set the scene a little better first, we have a report, this report could be viewed by 10 different people (with unique logins) each person is only allowed to see certain rows in one of the tables in the database. (the rest of the rows have to be anonomised) The issue for us is that the developers writing the reports (the report writers) will be using stored procedures to get the data, and because of that have full access to the table because of ownership chaining.
The report writers will be writing the stored procedures to retreive the data for the reports. We need a way to stop the report writers referencing the table directly in any stored procedures they create. Instead we want them to only use a user-defined-function that we've created that returns anonomised data for the rows the login viewing the report is allowed to see (probably having a @login parameter).
I hope this makes sense, and i look forward to reading your reply.
Thanks
Bob
July 19, 2010 at 11:08 am
A key is to break the ownership chain between the stored procedures and the table, but to keep it in place between the function and the table. You've got two options.
1) Create a new schema. Either allow the developers to create stored procedures in this schema or move the table and function to it. Grant the appropriate permissions to access the function, but ensure there is no access to the table (you don't need a DENY here as SQL Server's default is to block unless specifically allowed in). Ensure that the owners of the two schema are different. You can CREATE USER blah WITHOUT LOGIN if you just need a database user for this specific purpose.
2) Leave everything in the same schema, but specifically change the owner of both the function and the table using ALTER AUTHORIZATION. Make sure this owner is different than the owner of the schema. Again, you may need that CREATE USER statement from above. Grant permission to access the function but do not give access to the table. Same as above.
This will ensure that the developers can write the stored procedures using the functions just fine. If they try to write it against the table that's fine, too, just when they go to run said stored procedures, they'll get the permission check on the table and they'll get the nice little access denied message. And then when they ask what's up, you can remind them to use the function. Since the function and the table have the same owner, the permission check should only happen on the function.
K. Brian Kelley
@kbriankelley
August 18, 2010 at 3:45 pm
I would suggest you look at using EXECUTE AS in the stored procedures and functions. That is assuming that the devs only have execute access to the procs and functions.
Basically, EXECUTE AS enables you to change session user context within the procedure as needed, then revert to the original session's permissions. You can then "lock" down the access of the devs and yet allow access to tables via the functions or procs only as needed without compromising security.
Take a look at http://msdn.microsoft.com/en-us/library/ms181362.aspx for an overview of using this.
You can also search TechNet for EXECUTE AS. There may be a turorial there that goes through the essentials.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply