August 17, 2012 at 2:18 pm
our general policy is to not provide direct data access to any user or group in the company for our data warehousing. Read access is generally controlled through SSAS or SSRS using proxy accounts to the underlying data.
We do have some instances where the user needs the ability to run a query using their AD account.
What we have done to accomplish this is setting up a login which has access to just the data they need.
Then create stored procedure which does context switching to that login.
The user or group login is given access to impersonate that other account and the ability to execute that one stored procedure.
by doing this we don't need to provide any direct access, and the users are unable to view any meta-data, but my question is: If one of these users was familiar with the schema could they simply write a SQL statement using the EXECUTE AS LOGIN = 'Domain\account'
right in an excel workbook and be able access data.
Second question, is there a better way?
August 20, 2012 at 1:34 pm
Tom Van Harpen (8/17/2012)
our general policy is to not provide direct data access to any user or group in the company for our data warehousing. Read access is generally controlled through SSAS or SSRS using proxy accounts to the underlying data.We do have some instances where the user needs the ability to run a query using their AD account.
What we have done to accomplish this is setting up a login which has access to just the data they need.
Then create stored procedure which does context switching to that login.
The user or group login is given access to impersonate that other account and the ability to execute that one stored procedure.
by doing this we don't need to provide any direct access, and the users are unable to view any meta-data, but my question is: If one of these users was familiar with the schema could they simply write a SQL statement using the
EXECUTE AS LOGIN = 'Domain\account'right in an excel workbook and be able access data.
Yes. In this way they could impersonate Domain\account and access any securables that login was able to access.
Second question, is there a better way?
Yes. I see no need to give impersonation rights as long as you can leverage ownership chaining. Ideally you will only need to grant exec on a proc. In the simplest example say you have a table in the dbo schema named Table1. If you create a proc in the dbo schema named GetTable1 that selects data from dbo.Table1 and grant a user exec rights on it then the user can execute that proc successfully to receive the data from Table1 yet they would not be able to issue a select directly against Table1. This is doable because of ownership chaining. You granted EXEC on a proc in a schema owned by a user, in this case dbo. When the proc is executed it initiates an ownership chain. Anything the proc accesses that is also owned by the owner of the proc, i.e. dbo, is accessed without checking permissions. This means the proc can access dbo.Table1 even though the user calling the proc cannot.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 22, 2012 at 12:25 pm
I think we tried the ownership chaining before but in this case the query is hitting a table over a linked server and the impersonated account is mapped to a sql login on the remote server.
My concern was could the users build their own SQL statement in Excel and include the "Execute AS" directive.
I've tested this and see that they can, so we need to create very specific accounts to use in the "execute as" so if they were ever able to create a query they still could only see data they already have access to. This is all assuming they would be able to guess all of the meta-data. Also in the workbook the only sql specified is the stored procedure name which they would not be able to view, only execute.
That said, is there a better way to give a user the ability to run a query in Excel without direct table access?
August 22, 2012 at 1:49 pm
Tom Van Harpen (8/22/2012)
I think we tried the ownership chaining before but in this case the query is hitting a table over a linked server and the impersonated account is mapped to a sql login on the remote server.
The Linked Server is a bit of a wrinkle not in the original post 😉
My concern was could the users build their own SQL statement in Excel and include the "Execute AS" directive.
I've tested this and see that they can, so we need to create very specific accounts to use in the "execute as" so if they were ever able to create a query they still could only see data they already have access to. This is all assuming they would be able to guess all of the meta-data. Also in the workbook the only sql specified is the stored procedure name which they would not be able to view, only execute.
That said, is there a better way to give a user the ability to run a query in Excel without direct table access?
Knowing about the Linked Server issue I would stick with Stored Procedures from Excel but would look into signing them with a Certificate that is mapped to a Login that has proper access to the Linked Server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 22, 2012 at 1:54 pm
yeah sorry about the omission on the linked server....
Thanks for the feedback I'll look into the cert idea.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply