Stored Procedure Ignoring Table Permissions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dschruth@gmail.com - 25 Jul 2007 00:00 GMT

Hi,

I've been bashing my head against this one all day:

We have a MS T-SQL 2005 database with ~12 tables and ~45 stored
procedures (all created and owned by myself under a dbo account).
The access front end calls these stored procedures and everything
works well ... but a little too well... It seems that no mater what
permissions I set on the tables, the stored procedures do anything and
everything that is written in the code... regardless of which domain
user is logged in to windows and using the access font end.

The main example of this problem is a domain user called "NTreader"
who is a member of a group called "NTreaders". the "readers" group
has a corresponding group on the sql server as "SQLreader" and this is
a member of a role called "SQLreaders" . I have explicitly denyed
"SQLreaders" deletion permissions on "TableA". But when "NTreader"
runs stored procedure "spDeleteA", it runs and deletes flawlessly.

I've tried changing (mixing) ownership of the tables and stored
procedures and using the "WITH EXECUTE AS CALLER" inside "spDeleteA"
but nothing seems to prevent the procedure from doing its unauthorized
job ... except for denying execution rights on "spDeleteA" all-
together.

Any ideas why this is happening?

Thanks,

Dave

Reply to this Message

Erland Sommarskog - 25 Jul 2007 10:01 GMT

> The main example of this problem is a domain user called "NTreader"
> who is a member of a group called "NTreaders". the "readers" group
> has a corresponding group on the sql server as "SQLreader" and this is
> a member of a role called "SQLreaders" . I have explicitly denyed
> "SQLreaders" deletion permissions on "TableA". But when "NTreader"
> runs stored procedure "spDeleteA", it runs and deletes flawlessly.

That's exactly the gist of stored procedures. First, you make sure that
the user does not have direct access to the tables. Then you write stored
procedures and hand out EXECUTE permissions. Then the procedures performs
the action in a way that complies with business rules etc.

This is due to something called ownership chaining. It occurs only if
the procedures and the table have the same owner. If you don't want
this to happen, change the ownership of the procedure to someone else
than dbo.

For more details, you may be interested in a longer article on my web
site: http://www.sommarskog.se/grantperm.html.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Uri Dimant - 25 Jul 2007 10:02 GMT

Hi
I assumed this user is not a memebr of sysadmin or db_owner roles

use demo
--deny permission on table test to the user 'Myuser'

execute as user= 'myUser'

delete from test

--The DELETE permission was denied on the object 'test',

-- database 'demo', schema 'dbo'.

revert

go

--create a sp that does deletion

create procedure dbo.myp

as

delete from test

--

grant execute ON object::dbo.myp

TO myUser;

execute as user= 'myUser'

exec dbo.myp --worked fine

revert

> Hi,
>
[quoted text clipped - 26 lines]
>
> Dave

Reply to this Message