tell me in short the advantages and disadvantages of CLR?
Why do we need to make database trustworthy for assemblies with external permission set?
You do NOT need to enable
TRUSTWORTHY . EVER! At least not for more than a few seconds to load something and then immediately disable it. This is an unfortunately highly common misconception. Please see:
What should be done is signing the assembly with a strong name key or certificate and registering that in SQL Server, creating a login from it, granting that login the
EXTERNAL ACCESS ASSEMBLY or
UNSAFE ASSEMBLY permission, and then load the assembly. Please see:
is there any risk associated with trustworthy database for CLR implementation?
SQLCLR, on its own, is not an inherent security risk (regardless of how many people say that it is). People only believe SQLCLR is a security risk due to some people making that claim when it was introduced, and then many other people merely repeating those unfounded claims. True, SQLCLR can be used to do bad things, but only if you let it. And you don't need to let it do such things. SQLCLR is more secure than Extended Stored Procedures (deprecated) and the OLE Automation Stored Procedures (sp_OA*, not officially deprecated, but recommended against).
TRUSTWORTHY is a security risk. Mainly due to the following two reasons:
AUTHENTICATE SERVERpermission, then an impersonated context will have any instance-level permissions of a login having the same SID as the impersonated user. Similarly, stored procedures created with the
EXECUTE AS 'dbo'clause or
EXECUTE AS OWNER(assuming the owner is [dbo]) will have the instance-level permissions of the login associated with the [dbo] user (which in way too many cases is [sa]).
TRUSTWORTHYis enabled, all assemblies can be set to either
UNSAFE. There are no restrictions. But with signed assemblies, only those that have a) the asymmetric key or certificate loaded into [master], and b) have a login created from that which c) is also granted the desired permission, can be set to either level. Thus you can allow some assemblies to have a higher level of permissions while preventing those that don't meet that criteria.
2 People are following this question.