Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may be as secure, but simpler.
PS - Please don't let this degenerate into a conversation about OR mappers. I know that happens a lot on these forums.
BoulderBum:
Basically, the connection context is using security for a low-privilaged Windows account ("UserX") and UserX has no permission to the table referenced in the dynamic SQL but because of the dyanmic nature of the query, the stored procedure ends up adopting the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Yes, this is by design. When you EXECUTE dynamic SQL statement. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure.
So in earlier SQL versions you have to choose: use dynamic SQL if you do have to, or not use it if not necessary. Fortunately in SQL 2005 we have another option: using EXECUTE AS clause when creating the stored procedure. For example:
create proc sp_testper WITH EXECUTE AS SELF
as
EXEC('select * from Orders')
go
For more information about this clause, please refer to:
http://msdn2.microsoft.com/en-us/library/ms188354(d=ide).aspx
|||Thanks, lori_Jay.
That's the solution I'm probably going to end up going with, though I didn't know until a few days ago that EXECUTE AS could apply toCLRstored procedures. I was just deploying via the IDE which doesn't offer such options and the documentation I saw didn't give an example of how to use EXECUTE AS with a CLR stored procedure.
Luckily I was informed that it was indeed possible and though I haven't sat down to do it yet, and that solution suffices for my needs!
Anyway, thanks again!
|||For future readers, something else I discovered comes in handy is that you can have a predeployscripts.sql and postdeployscripts.sql file in a database project.
With those files, I was able to rig the EXECUTE AS and change the schema for my stored procedures. It took some dropping/recreating to get everything where I wanted, but it was pretty easy and worked like a charm.
I now have one-touch deployment of my CLR stored procedures through Visual Studio again!
No comments:
Post a Comment