Security Checking

[Kevin Frey]

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?

[Erland Sommarskog]

Re: Security Checking

Kevin Frey (kevin_g_frey@h otmail.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, esquel@sommarsk og.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