'sa' impersonation

Question

'sa' impersonation

SZ

Old Hand

Points: 338
More actions
September 30, 2020 at 2:14 pm

#3791957

To give a user the ability to execute a stored procedure (EXECUTE AS) I must first grant him the ability to impersonate the 'sa'.
(that procedure creates new login and adds to role which a normal user can not execute)
Am I right ?
But when I give him the ability to impersonate 'sa' is not that a security risk ?
Or is the impersonation valid ONLY for that stored procedure ???

Viewing 11 posts - 1 through 10 (of 10 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

If you give the USER (LOGIN actually here) permission to impersonate the sa, that is certainly a security risk, yes. Then would be able to then use EXECUTE AS LOGIN = 'sa' outside of the Procedure, and do what ever they want.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

SZ wrote:

To give a user the ability to execute a stored procedure (EXECUTE AS) I must first grant him the ability to impersonate the 'sa'.
(that procedure creates new login and adds to role which a normal user can not execute)
Am I right ?
But when I give him the ability to impersonate 'sa' is not that a security risk ?
Or is the impersonation valid ONLY for that stored procedure ???

Not correct. The stored procedure needs EXECUTE AS OWNER, the owner of the database should be someone or something with 'SA' Privs (normally, the disabled SA login), and the only priv the user will need is to be able to execute the stored procedure.

If you're using something like xp_CmdShell in the proc (for example), the xp_CmdShell proxy will likely need to be setup but no one other than trusted DBAs should ever have privs to call it directly. It should only be done through well written and safe stored procedures as outlined above.

No non-DBA users should ever be granted any elevated privs... period.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SZ Old Hand Points: 338 More actions · Answer 3

Would WITH EXECUTE AS OWNER make any difference ???
or

Can I just add this stored procedure to the list of securables their role can execute ?

Would not then this procedure be just executed without giving any user impersonation privilege ?

This reply was modified 3 years, 6 months ago by SZ.
This reply was modified 3 years, 6 months ago by SZ.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

SZ wrote:

Would WITH EXECUTE AS OWNER make any difference ???
or
Can I just add this stored procedure to the list of securables their role can execute ?
Would not then this procedure be just executed without giving any user impersonation privilege ?

You asked how someone could run the code that needs elevated privs without the person having elevated privs. In order to do that, the code must be capable of running as someone else and the EXECUTE AS OWNER can do that for you. If no elevated privs are required in the proc, then you don't need the EXECUTE AS OWNER in the proc. The user just needs privs to execute the proc either way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SZ Old Hand Points: 338 More actions · Answer 5

so if I understand you correctly, if I give them the permission to execute the procedure I am not giving them any god given rights so they can execute something else on the system? You are permitted to execute this procedure only and that's it, Right ?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

SZ wrote:

so if I understand you correctly, if I give them the permission to execute the procedure I am not giving them any god given rights so they can execute something else on the system? You are permitted to execute this procedure only and that's it, Right ?

Yes. Done correctly, they only need PUBLIC privs to connect to the database and they won't be able to do anything except execute the stored procedure. They won't even be able to examine the contents of the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SZ Old Hand Points: 338 More actions · Answer 7

are you sure ...?

if I understand ...to run the procedure with EXECUTE AS 'sa' I have to give a user a grant to impersonate 'sa'.

Am I granting him the priviledge on that particular procedure and nothing else or can he

use SQL with EXECUTE AS 'sa' on some other things than that procedure.

I want to be sure that he has the permission on that procedure only.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

SZ wrote:

are you sure ...?
if I understand ...to run the procedure with EXECUTE AS 'sa' I have to give a user a grant to impersonate 'sa'.
Am I granting him the priviledge on that particular procedure and nothing else or can he
use SQL with EXECUTE AS 'sa' on some other things than that procedure.
I want to be sure that he has the permission on that procedure only.

You do NOT have to grant the user privs to impersonate 'sa'. You only need to grant the user privs to EXECUTE the stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99126 More actions · Answer 9

SZ wrote:

are you sure ...?
if I understand ...to run the procedure with EXECUTE AS 'sa' I have to give a user a grant to impersonate 'sa'.
Am I granting him the priviledge on that particular procedure and nothing else or can he
use SQL with EXECUTE AS 'sa' on some other things than that procedure.
I want to be sure that he has the permission on that procedure only.

You're confusing the command EXECUTE AS {USER/LOGIN} = '{USER/LOGIN}'; and a Stored Procedure with an EXECUTE AS option in CREATE OR ALTER PROCEDURE. Only the former requires impersonation privileges.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

And, to be sure, the stored procedure would NOT have EXECUTE AS 'SA' in it.

As I said before, the database should be owned by some login that has "sysadmin" privs. Usually, that owner will be the disabled "SA" login. Then the stored procedure would have the following in it...

EXECUTE AS OWNER

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)