CLR problems with security under ADO.NET and SQL Server 2005

[Brian Henry]

Ok I'm confused on this... in T-SQL Say I have TableA and it has no select
permissions on it for the user... but the user has a stored procedure with
execute permission on it and contents of it is SELECT * from TableA... well
of course this executes! and returns the TableA contents... where doing a
SELECT * Fromt TableA in a stand alone query returns a permission error...
now when I do a similar thing in the new CLR compiled stored procedures
using an ADO.NET Command object... with SELECT * FROM TableA it comes back
with the permission error that I cant select from tablea... why?! if i can
do it in T-SQL why can't I in a CLR stored procedure? which is more secure
to start with then a plain text T-SQL statement! I'm trying to convert some
T-SQL procs to ADO.NET's CLR store procs on SQL Server 2005 but this hitch
kinda put everything im doing at a hault because i cant do anything if i
cant get select permission! and security wise i dont want to give people
select permission on tables... anything to help with this problem? thanks!

 

[Alazel Acheson [MS]]

Short answer:
use EXECUTE AS OWNER clause in your CREATE PROC statement.

Longer answer:
This works by default in TSQL due to ownership chaining -- if the table and
procedure are owned by the same entity, then user permissions are not
checked against the table, just against the procedure. In Sql Server 2005,
there is a new paradigm using the EXECUTE AS clause on various DDL
statements. This allows you to specify that the code should execute with
the credentials of a particular entity, including a couple of dynamic ids
(USER, OWNER, SELF). For better security, .Net procedures execute as USER
by default. Executing as OWNER is the closest match to the TSQL default
ownership chaining.

--
~Alazel

Alazel Acheson
Software Developer
Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Brian Henry]

thanks for the clarification!

Short answer:
use EXECUTE AS OWNER clause in your CREATE PROC statement.

Longer answer:
This works by default in TSQL due to ownership chaining -- if the table
and procedure are owned by the same entity, then user permissions are not
checked against the table, just against the procedure. In Sql Server
2005, there is a new paradigm using the EXECUTE AS clause on various DDL
statements. This allows you to specify that the code should execute with
the credentials of a particular entity, including a couple of dynamic ids
(USER, OWNER, SELF). For better security, .Net procedures execute as USER
by default. Executing as OWNER is the closest match to the TSQL default
ownership chaining.

--
~Alazel

Alazel Acheson
Software Developer
Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights.