Executing as my own context

Question

Executing as my own context

Steve Jones - SSC Editor

SSC Guru

Points: 736524
More actions
May 26, 2016 at 8:04 pm

#75741

Comments posted to this topic are about the item Executing as my own context
May 26, 2016 at 11:37 pm

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 1

Yes, "really dangerous".

I can't imagine any situation where it would be good practice to do this (although I understand how it could be tempting).

Does anyone have an example of where EXECUTE AS SELF is justifiable?

paul s-306273 SSChampion Points: 10730 More actions · Answer 2

Stewart "Arturius" Campbell (5/26/2016)
Nice, easy question to end the week on, thanks Steve

Yes, it was - but I still managed to get it wrong.

Ouch!

George Vobr SSChampion Points: 10011 More actions · Answer 3

Thanks, Steve for this question, which highlights that the use of SELF is really dangerous. 🙁 If you create a procedure and add WITH EXECUTE AS SELF to it, anyone who runs the procedure will execute with your permissions. (And anything they update, you will be held accountable for.), see:

http://www.sommarskog.se/grantperm.html#execasowner

Mike Good SSCertifiable Points: 7415 More actions · Answer 4

Nice question but now I reckon I don't properly understand this. I compiled the proc w/out modification, using my sysadmin login, but when I run it I always get 0.

--execute as me (I'm a DBA sysadmin)

select is_srvrolemember('sysadmin')

exec ReallyDangerous

-----------

1

-----------

0

GSquared SSC Guru Points: 260824 More actions · Answer 5

martin.whitton (5/27/2016)
Yes, "really dangerous".
I can't imagine any situation where it would be good practice to do this (although I understand how it could be tempting).
Does anyone have an example of where EXECUTE AS SELF is justifiable?

I've used it in DDL triggers.

Make it so non-sa can't modify data in the DDL log table. That keeps the integrity of the log. Then set the trigger to Execute As Self, so it can write into the table.

You could, of course, create a credential specifically for logging, with the rights it needs, then Execute As with that. But if you treat the DDL log as a DBA tool, executing it under a DBA credential makes sense to me.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

Mike Good (5/27/2016)
Nice question but now I reckon I don't properly understand this. I compiled the proc w/out modification, using my sysadmin login, but when I run it I always get 0.
--execute as me (I'm a DBA sysadmin)
select is_srvrolemember('sysadmin')
exec ReallyDangerous
-----------
1
-----------
0

With EXECUTE AS SELF in a stored procedure created by a sysadmin, you're effectively doing an EXECUTE AS USER='dbo'.

With an EXECUTE AS USER='dbo', IS_SRVROLEMEMBER('sysadmin') will show 0 unless the DB has TRUSTWORTHY set to ON (or you specify SUSER_NAME() as the optional second parameter) and the database owner is a sysadmin, if my memory serves correctly.

Cheers!

Mike Good SSCertifiable Points: 7415 More actions · Answer 7

Mike Good

SSCertifiable

Points: 7415

May 27, 2016 at 12:09 pm

#1881864

Doh!. Thank you.

j-1064772 SSCertifiable Points: 5316 More actions · Answer 8

George Vobr (5/27/2016)
Thanks, Steve for this question, which highlights that the use of SELF is really dangerous. 🙁 If you create a procedure and add WITH EXECUTE AS SELF to it, anyone who runs the procedure will execute with your permissions. (And anything they update, you will be held accountable for.), see:
http://www.sommarskog.se/grantperm.html#execasowner

A t work others were responsible for security and I never encountered a situation where this would be useful so I never played with [font="Courier New"]EXECUTE AS[/font].

So your comments really made it clear for me why [font="Courier New"]EXECUTE AS SELF[/font] is "dangerous" (though it seems it was obvious to others).

I'm sure this only the short of it but I think that you hammered that nail pretty good.

Thanks.

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 9

I haven't looked at the details but if EXECUTE AS SELF runs the stored procedure as the owner then I do not consider that dangerous at all as this improves security since the user does not need object privileges but instead can only perform the action coded in the procedure. What would be dangerous would be coding dynamic SQL that performs DDL or DML without parameter length and data values checking in such a procedure.

- -

This is how stored procedures and packages work in Oracle by default. The code executes as the owner and the user only needs execute privilege. Optionally you can make the code run as the user in which case object privileges are necessary to the user.

- -

IMHO -- Mark D Powell --

j-1064772 SSCertifiable Points: 5316 More actions · Answer 10

I was left with the impression that this would cause the name of the CREATOR of the stored proc to appear even if an another user ran it.

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 11

Mark D Powell

SSCarpal Tunnel

Points: 4775

June 2, 2016 at 12:59 pm

#1882913

Appear where?

j-1064772 SSCertifiable Points: 5316 More actions · Answer 12

Thanks, Steve for this question, which highlights that the use of SELF is really dangerous. If you create a procedure and add WITH EXECUTE AS SELF to it, anyone who runs the procedure will execute with your permissions. (And anything they update, you will be held accountable for.), see:
http://www.sommarskog.se/grantperm.html#execasowner

and see also the reply from GSquared