tell me in short the advantages and disadvantages of CLR?
In short:
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:
PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
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:
SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2
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 SERVER
permission, 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]).TRUSTWORTHY
is enabled, all assemblies can be set to either EXTERNAL_ACCESS
or 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.