Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JeffK95z
Starting Member
19 Posts |
 Posted - 2007-10-31 : 12:43:37
|
| Hi all,this should be a easy question, but I can't really seem to find anything on it...Here's the scenerio:n-tier web app, with asp/iis/sql... All database calls are done via stored procedures with the same user (lets call the user: webuser)webuser has NO access to the db in question, but it is granted EXEC on all stored procedures. My question is, when a user tells the web app to say delete a record, the application server (iis) makes a call to the database with the webuser security cred's... It says execute the delete stored proc.webuser has the ability to do this, so it happens. However, in what context (this may not be the right word) does the stored procedure execute?ie: which user does the stored proc exeucte as. It can't be webuser can it? Because webuser does not have access to the base tables.Does the stored proc execute as the user that created it?I'm confused...thx all! |
|
|
Hippi
Yak Posting Veteran
63 Posts |
Posted - 2007-10-31 : 12:45:18
|
| Web application pool identity |
 |
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2007-10-31 : 14:28:01
|
I don't think that's it... maybe i'm confusing myself (and others?)ie: webuser is the security user that the webserver passes to the db server to execute the stored proc.another way to look at it...lets take the web app completely out of the picture.DB SetupName: TestTable: companySP: add_companySQL LoginUserATest Database UserUserATest DB AccessUserA - public only, no read/writeUserA - EXEC on add_company SPSecurity Testing- UserA CAN NOT perform an insert into the company table directly
- UserA CAN execute the add_company SP and the insert works
My question is, how does that work? What context does the add_company execute under, to allow UserA the ability to run that statement.I believe that the add_company gets executed as some higher privileged user?Also, if someone were to overload/sql inject that stored proc, what other statements could they execute?Hope this helps! and thx for the reply hippi! |
|
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2007-10-31 : 15:34:08
|
| As a note, I believe? that the stored procedure executes under the security context of the stored proc OWNER.Can anyone confirm this to be true?And if so, that means that any sql injection payload would also execute as the owner, which in most cases is dbo? (scary!)thanks again!jeff |
|
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2007-10-31 : 15:58:02
|
| Alright!So Ownership Chains is what helped me find my answer...The stored procedure will by default execute as SELF/Owner...If you do not want this, you can use with execute as caller as follows:CREATE PROCEDURE procnameWITH EXECUTE AS CALLERASblahblahblahCheers,Jeff |
|
|
|
|
|
|
|
|
Stored procedure security?