Stored Procedure Security

Question

Stored Procedure Security

Carleton

SSCommitted

Points: 1706
More actions
July 6, 2009 at 1:13 pm

#155922

I know I should know this...
How do you give the code within a stored procedure SA access without giving the executer of that stored procedure SA access?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Well, in reality, you probably don't want to give SA access within a stored procedure, but you can use EXECUTE AS to change security context

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Carleton SSCommitted Points: 1706 More actions · Answer 2

You are correct about the SA access within a sproc...I only used it as an example as I didn't want to have to explain the whole security model I was looking for. 🙂

In the tests I've done, using the EXECUTE AS option within the sproc requires that the person calling the sproc has impersonation rights to that login...which I don't want them to have.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

From the link in my previous post:

To execute a module specified with EXECUTE AS, the caller must have EXECUTE permissions on the module.

And

To specify the EXECUTE AS clause when you create or modify a module, you must have IMPERSONATE permissions on the specified principal and also permissions to create the module. You can always impersonate yourself. When no execution context is specified or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Carleton SSCommitted Points: 1706 More actions · Answer 4

I did read those posts from BOL, and they seem to support my findings that you cannot solve my problem with the EXECUTE AS. Please consider the following scenario:

LOGIN1 is SA

LOGIN2 is not SA and should not have SA rights

After logging in as LOGIN1

CREATE PROC TEMP AS

EXECUTE AS LOGIN = 'LOGIN1'

--Code requiring SA Access

REVERT

After logging in as LOGIN2:

EXEC TEMP

Yields this error:

Cannot execute as the server principal because the principal "LOGIN1" does not exist, this type of principal cannot be impersonated, or you do not have permission.

To overcome this error, I understand that I must give LOGIN2 impersonation access to LOGIN1, and if I do that I've opened up an security hole for LOGIN2 to have unfettered SA access through impersonation--which is the very problem I'm trying to avoid in this exercise.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

Perhaps being less abstract in your request might be beneficial, what precisely do you want to give a non-admin user the ability to accomplish?

Carleton SSCommitted Points: 1706 More actions · Answer 6

I'm not sure how being less abstract will help as the solution should be able to apply to any security context.

But to answer your question, in this specific case, I want to give read access to the system tables:

Master.sys.dm_exec_requests

Master.sys.dm_exec_sql_text

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

Being clear helps in that perhaps there is something specific to what you are accessing.

In this case, I am surprised that you cannot grant select to these views. Perhaps there is a security hole we are not aware of.

Carleton SSCommitted Points: 1706 More actions · Answer 8

Thanks for the reply Steve...I did weigh the option of giving security directly to the users as well. The above example was only the present need, but I wanted to apply this security model across my environment, and so a specific example would not capture this breadth.

I was very much hoping that security could be managed within the context of a stored procedure (through the EXECUTE AS or other method) for the following reason:

If you could give a sproc all the access it needs without giving the same level of access to the user/login executing it, it would drastically reduce the abilities a user/login needs to the system. If you could limit the world's access to only running specific stored procedures, this would be a tighter ship and potentially a simpler security model to administer.

For example, instead of giving update access to table TABLE1 to login LOGIN1, you would give update access to the sproc updating TABLE1. That way you know that LOGIN1 will not be able to perform updates not intended--they can only execute the relevant procedure. If I understand the available security options, LOGIN1 must have update access to TABLE1 in order to execute any procedure they call which updates TABLE1--and there are no ways around this? (outside of using the service broker...not an option for calling every sproc)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 9

I don't think that's true. You don't need to grant a user rights to a table, if the table owner and the stored procedure owner are the same.

I need to get another instance set up (somehow out of sorts with 4 machines all semi-working here), and test this. I wonder if you have

MyUser owns a table.

A procedure to update the table is created by MyClient and set to Execute as MyUser.

Can anyone with rights to the proc execute it and update the table?

Trying to build a nice framework is a good idea, however what you are proposing could potentially cause security issues with some procs. The key is often to ensure that your table and objects fall under the same schema, in which case permission chaining should apply.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 10

You might want to look at signing your procedure.

I have played around with EXECUTE AS trying to get access to the view and function you are interested in and can't get it to work so signing might be the best way. I have not tried that yet.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184393 More actions · Answer 11

I realize I am back in this thread long after the original problem was solved, but I'm wondering what the solution was and also wanted to offer some more information. You might be interested in this article on SQLTeam

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Carleton SSCommitted Points: 1706 More actions · Answer 12

What I was able to discover is that the solution I was seeking wasn't available via SQL Server 2005. I ended up implementing user based security rather than the sproc based security I hoped was available.

I checked out that link, but didn't see the relevance of Dynamic Management Views to my security implementation dilemma.

Thanks,

Carleton

(This was the model I hoped existed):

I was very much hoping that security could be managed within the context of a stored procedure (through the EXECUTE AS or other method) for the following reason:
If you could give a sproc all the access it needs without giving the same level of access to the user/login executing it, it would drastically reduce the abilities a user/login needs to the system. If you could limit the world's access to only running specific stored procedures, this would be a tighter ship and potentially a simpler security model to administer.
For example, instead of giving update access to table TABLE1 to login LOGIN1, you would give update access to the sproc updating TABLE1. That way you know that LOGIN1 will not be able to perform updates not intended--they can only execute the relevant procedure. If I understand the available security options, LOGIN1 must have update access to TABLE1 in order to execute any procedure they call which updates TABLE1--and there are no ways around this? (outside of using the service broker...not an option for calling every sproc)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 13

I had pointed to the link on DMV's because the objects you were trying to access were DMV's and in the article it mentioned that a user needs VIEW SERVER STATE permissions for server based DMV's and VIEW DATABASE STATE permissions for database level DMV's I thought it might help to know that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mchristie SSC Rookie Points: 29 More actions · Answer 14

After struggling with user-based security we've transitioned to proc-based security. This works well when the schema of the proc and tables are the same -- except in cases of dynamic sql, and for that we've been using "execute as owner".

To simplify matters we have also switched to keeping everything in the dbo schema.