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?

Re: Security Checking

Kevin Frey (kevin_g_frey@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, esquel@sommarskog.se

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