Jeff Moden (2/8/2012)
Elliott Whitlow (2/7/2012)
Jeff Moden (4/29/2011)
I know little about deploying CLR's so please bear with me. Does the creation of the Asymetric key make it so that a low privileged user can execute a proc with calls to your CLR's but can't use the CLR's directly?Jeff,
I'm sorry I missed this one and I know it is an ancient post..
But the answer to your question (if I understand it) about the asym key, it has a little to do with execute permissions. When an assembly is installed the database must either be tagged as trustworthy or the strong name key from the assembly be granted the rights to "create" UNSAFE or EXTERNAL_ACCESS assemblies. I believe this access is required at run-time as well. However the execution of the sproc can be done by any user granted the rights to exec it. Also keep in mind that there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS, a lot less than is presumed by the names. The only difference I am aware of is the use of P/Invoke is allowed in UNSAFE and not the others.
Not sure if this fully answered your question..
CEWII
Thanks for the feedback, Elliott. I'm more confused than ever though. Paul White built a CLR for me for the "Tally Oh!" article. It required neither a certificate, nor tagging the database as "trustworthy", nor allowing an UNSAFE CLR. From the sounds of it (and, again, I'm NOT a CLR Ninja by any means and could be flat out wrong), UNSAFE CLR's are generally something to avoid and, from personal experience, setting databases to "trustworthy" is a bit of a security headache.
Considering that Paul's CLR worked just fine for me, why would we ever need an UNSAFE CLR or a database to be set to "trustworthy" just to use one? It sounds almost (more actually) as risky as turning on xp_CmdShell and controlling it properly.
Hello Elliott and Jeff.
First, to Elliott's comment about "there is really not much of a difference between UNSAFE and EXTERNAL_ACCESS", that is not exactly correct. In fact, EXTERNAL_ACCESS is much closer to SAFE than it is UNSAFE. EXTERNAL_ACCESS is basically SAFE that also allows the code to see outside of SQL Server so it can access things like the file system, internet, other database connections (to the same server or anything else). UNSAFE, on the other hand, has NO restrictions. It will allow for unmanaged code, non-static variables, access to system areas, loading untested managed assemblies (any assembly that is part of regular .Net but not available by default to SQL Server assemblies).
So then to Jeff's point, we only need EXTERNAL_ACCESS and UNSAFE when doing things that do more than the basic set of .Net assemblies allow for or needing to look outside of the database. In this case being discussed here, just viewing the filesystem requires EXTERNAL_ACCESS. Very few things need UNSAFE. Sometimes either one of these is necessary, but certainly not for a split function. One should not use a higher lever of access than they truly need.
Regarding SQLCLR vs xp_cmdshell, there is a large difference with regards to controlling access. xp_cmdshell is a wide-open hole for anything. You can restrict access by database role, etc, but if two groups need to run CMDs or BATs or EXEs then you can't control who gets to call which ones. But with xp_cmdshell turned off and using SQLCLR instead, you can create a View Directories proc and grant EXECUTE only to RoleA and then create an FTP proc and grant EXECUTE only to RoleB. You can create those functions in CMD scripts but then both RoleA and RoleB would be able to execute both View Directories AND FTP since both would have access to xp_cmdshell. You might be able to abstract xp_cmdshell by restricting direct access and creating procs for both roles that have the explicit calls in there, but I haven't tried that to see if it is a practical solution.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR