Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stored Procedure Security Expand / Collapse
Author
Message
Posted Monday, July 6, 2009 1:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892

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?
Post #747995
Posted Monday, July 6, 2009 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 10,380, Visits: 13,434
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #748013
Posted Monday, July 6, 2009 1:48 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892
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.
Post #748019
Posted Monday, July 6, 2009 1:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 10,380, Visits: 13,434
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #748028
Posted Monday, July 6, 2009 2:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892
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.
Post #748043
Posted Monday, July 6, 2009 2:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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?
Post #748047
Posted Monday, July 6, 2009 2:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892
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
Post #748051
Posted Monday, July 6, 2009 2:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,355, Visits: 15,819
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #748056
Posted Monday, July 6, 2009 2:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 8, 2012 12:30 AM
Points: 150, Visits: 3,892
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)
Post #748072
Posted Monday, July 6, 2009 3:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,355, Visits: 15,819
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #748090
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse