'sa' impersonation

  • SZ

    Old Hand

    Points: 338

     

    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 ???

     

  • Thom A

    SSC Guru

    Points: 98786

    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.

  • Jeff Moden

    SSC Guru

    Points: 997335

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • SZ

    Old Hand

    Points: 338

    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 weeks, 5 days ago by  SZ.
    • This reply was modified 3 weeks, 5 days ago by  SZ.
  • Jeff Moden

    SSC Guru

    Points: 997335

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • SZ

    Old Hand

    Points: 338

    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: 997335

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • SZ

    Old Hand

    Points: 338

    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: 997335

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Thom A

    SSC Guru

    Points: 98786

    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.

  • Jeff Moden

    SSC Guru

    Points: 997335

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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