469,138 Members | 1,251 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Security Checking

SQL Server allows for a user to have SELECT permission on a View without
that user requiring an associated SELECT permission on the underlying table
that the VIEW accesses, but the user can still access the data through the
View. A similar arrangement holds true for stored procedures.

So based on these initial known behaviours, I have a couple of questions:

1. If a stored procedure A executes stored procedure B, does the user of A
require execute permission for B also? Or will access to B be permitted
regardless because the user was given access to A?

2. Similarly, if a stored procedure A accesses a View, does the user of A
require permissions on the referenced View?

I guess to paraphrase what I am trying to determine is whether SQL Server
only checks permissions at the "entry" point of a particular function, or
whether permission checks are performed "intra-function". My opening
examples imply they are only checked "on entry", but I am wondering if this
behaviour is entirely consistent. Perhaps the SQL Standard mandates this?

Nov 16 '07 #1
1 1422
Kevin Frey (ke**********@hotmail.com) writes:
SQL Server allows for a user to have SELECT permission on a View without
that user requiring an associated SELECT permission on the underlying
table that the VIEW accesses, but the user can still access the data
through the View. A similar arrangement holds true for stored
procedures.

So based on these initial known behaviours, I have a couple of questions:

1. If a stored procedure A executes stored procedure B, does the user of A
require execute permission for B also? Or will access to B be permitted
regardless because the user was given access to A?
Maybe.
2. Similarly, if a stored procedure A accesses a View, does the user of A
require permissions on the referenced View?
Maybe.
I guess to paraphrase what I am trying to determine is whether SQL
Server only checks permissions at the "entry" point of a particular
function, or whether permission checks are performed "intra-function".
My opening examples imply they are only checked "on entry", but I am
wondering if this behaviour is entirely consistent.
No, it's not that way. Permissions are checked all along the way. Except
in one situations: you access a view/stored procedure/etc which in its
turn access another object *owned by the same user that owns the "entry
point".* This is known as ownership chaining. Note also, that ownership
chaining is essentially limited to INSERT, DELETE, SELECT and UPDATE.

In many databases dbo owns all objects, and in that case it works the way
you thought in practice. As long as you don't throw dynamic SQL into
the mix that is.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 16 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Rod Carrol | last post: by
3 posts views Thread by Tech Witch | last post: by
1 post views Thread by RBisch | last post: by
116 posts views Thread by Mike MacSween | last post: by
7 posts views Thread by Stephen | last post: by
5 posts views Thread by Norsoft | last post: by
3 posts views Thread by Dave Wurtz | last post: by
5 posts views Thread by Jarod_24 | last post: by
4 posts views Thread by tony | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.