Re: Stored Procedure Ignoring Table Permissions
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 25 Jul 2007 09:01:14 +0000 (UTC)
(dschruth@xxxxxxxxx) writes:
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.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
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
.
- References:
- Stored Procedure Ignoring Table Permissions
- From: dschruth
- Stored Procedure Ignoring Table Permissions
- Prev by Date: Re: Preventing windows users accessing a database
- Next by Date: Re: Stored Procedure Ignoring Table Permissions
- Previous by thread: Stored Procedure Ignoring Table Permissions
- Next by thread: Re: Stored Procedure Ignoring Table Permissions
- Index(es):
Relevant Pages
|
