Exec permissions on SP 2

[donutman]

I was startled to discover that a recordset returned by a Select statement in a SP is update-able even when the user doesn't have explicit rights to the underlying table, but only has Exec permission on the SP. I thought that SQL Server would only give Select permissions unless the SP itself did an Update, Insert or Delete. Do I need to explicitly deny those permissions in the underlying table? That seems a bit annoying as I thought that using a SP helped make the db more secure. It doesn't really do that...in fact it makes it more insecure, IMO. What am I missing?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oops. I jumped to the wrong conclusion. The VB code I was looking at did not do the update with the recordset...it did it the correct way, with an Update SP. Sorry for the post. I assume that it is safe to assume that an update on the Select SP would fail...although there is that old saw about assuming. Can someone confirm to easy my concern?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

Actually, I'm getting ready to test that theory in the next couple of days. We've got apps that do all their work via SPs and I'm setting up logins that have EVERYTHING denied except for Exec permissions on the necessary SPs. As soon as one of our programmers get a chance to test it, I'll post the results.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[donutman]

I would think that an explicit deny would take precedence over the Exec permission, but then as you know, I know very little about SQL Server security.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

Well, we did a minor test several weeks back where the Insert, Select, Update, Delete and DRV permissions were denied on one table, but we had a Stored Procedure which did an insert on that table and the user had Exec permission on the SP but was denied all else. Had someone logged into the SQL Server as that user and run the SP and the insert worked without a problem.

Now we just need to test it on a bigger (application wide) scale.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[JamesLean]

The way ownership chaining works is that if the two objects (in this case the SP and the table) have the same owner, permissions are only checked on the first object (in this case the SP). Therefore, as you discovered Catadmin, even if you excplicitly deny access to the underlying table the user can still gain access to it thru the SP.

Karl, I very much doubt whether a user could update a recordset returned by an SP, unless they had explicit permissions on the underlying table too. I'll try and test it this afternoon though to confirm..

--James

 

[JamesLean]

After testing, it does work the way we thought.

1) Create table t1
2) Create SP proc1 - simple SELECT * FROM t1
3) Create user user1 and grant execute on proc1 (no permissions on t1)

User1 can now execute proc1 and display the recordset. However, as soon as I added code to update the recordset, it failed with "SELECT permission denied on object 't1', database 'CTEL', owner 'dbo'".

(Yes, SELECT permission!). After granting select on t1 to user1, failed again with same error but about UPDATE permission. After adding UPDATE permission, user was able to update the recordset.

I guess this illustrates that as soon as you start trying to manipulate the recordset the application forgets where it originally came from and just goes directly to the table, at which point the permissions are checked against that rather than the SP and everything fails.

--James

 

[Catadmin]

James,

Did you the Select/Update test (when it failed) with nothing denied nor granted on the table or did you do this test with specifically denied permissions?



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[JamesLean]

No permissions were explicitly granted or denied.

--James

 

[Catadmin]

Thanks, James!



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[miq]

Ok,
but what if instead of directly updating recordset we use an SP which passes a single record, fetched from that recordset, to SQLserver for update or insert within.
I guess ownership chaining prevail here. could you test this one james?


B.R,
miq

 

[Catadmin]

I've already tested for Insert with an SP that just inserted one record into a table for which regular Insert, Select, Update and Delete permissions were denied. It worked when we tested it.

But James is saying that he already tested for Update and other stuff, which doesn't work.

However, my boss wants me to go ahead with our previously planned test as soon as the programmers fix the code and take out a few Select / Update statements I found that shouldn't be there and replace them with SPs.

The app we're using is a Web app that takes input directly from the user and does its processing. I'll post results when I'm through with testing.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"