Re: CLR Stored Procedures

["bryan allott" <homegrown () bryanallott net>]

Hi Nitin,

1) Security wise which one should be preferred ( CLR
or XP_) and why?
i would prefer to use the CLR alternative versus the xp_ precisely because 
it would be easier to manage the security of calling methods while at the 
same time making use of, in this case, something like xp_cmdshell, when u do 
need it.
now assuming u actually want to, absolutely have to, use something like 
xp_...
scenario: a user gains access to run a xp_nnn and similarily can gain access 
to run a clr equivalent. the OS will just execute the xp_nnn because it 
assumes the user is authenticated. with a CLR sp, u can explicitly 
deny/allow execution based on the identity's roles. just another layer- so 
not bullet proof, but another defense. having the logic expressed in the CLR 
makes it easier to integrate with other services/assemblies which *could* do 
more tracking/alerting/authenticating based on more comlpex business 
requirements.
personally, from a development perspective, it also makes testing and 
integration of these modules more maintainable over a lifecycle.

also, if u user can execute xp_cmdshell.. they can execute ANYTHING that can 
run in the shell... anything. but if they execute sp_HelloWorldClr guess 
what: all they can do is say "Hello World :)" which runs the shell.. but 
only what it can do. u can achieve the same effect by abstracting a stored 
proc but ultimately, it would still allow users to execute xp_cmdshell..


2) In case of using CLR stored procedures what are
  security pros and cons .

> From what i found it gives more control but is that
controll good enough to recommend it as a secure
alternative .

i think control is critical to managing security. if u can explicitly 
control and express that control easily [i.e. less error-prone with less 
assumptions and less obscurity] then security is definitely increased. not 
fort knox. but increased. so its def an added bonus and a "more" secure 
alternative- not the "most". good security still relies on diligent 
implementations and monitoring.


----- Original Message ----- 
From: "nitin patel" <kota_44 () yahoo com>
To: <webappsec () securityfocus com>
Sent: Sunday, October 09, 2005 9:18 AM
Subject: CLR Stored Procedures


Hi ,

Got a doubt regarding new feature in SQL 2005 .

Security wise genrally we discourage use of powerfull
stored procedures like xp_cmdshell from our
stored Procedure code and drop them if they are not
requierd .
If it all they are requierd it is recommended to have
access check on those stored procs calling these
powerfull SP's.

In Sql 2005 one can write CLR stored procedures and
produce same functionallity as xp_cmdshell.

My Doubts -

1) Security wise which one should be preferred ( CLR
or XP_) and why?

2) In case of using CLR stored procedures what are
  security pros and cons .

> From what i found it gives more control but is that
controll good enough to recommend it as a secure
alternative .

Thanks in Advance
Nitin







__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs




--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.13/123 - Release Date: 06-Oct-05