why cannot i GRANT EXECUTE permission on assemblies anymore ?


Just a quick note that this is by design and no you don’t need to use it.


 What are we talking about ?


Books online talks about granting assembly permissions. You used to do


GRANT execute on ASSEMBLY :: <assembly_name> to <database_principal > with SQL Server 2005


Your database holds your assemblies and as such you could grant / revoke limited set of permissions. We initially allowed you to grant execute permission on the assembly but now when you execute the same on a server with the SP2  service pack, you get the following :


Msg 102, Level 15, State 1, Line 0


Incorrect syntax near ‘EXECUTE…’.


Why did this change ?


Execute permission gave a false sense of security and was really inappropriate. Here’s why:


As we shipped SQL Server 2005, this permission set was supported in TSQL but never enforced. So you could say “deny execute on assembly” but nothing happened. With this fix to revert the support from TSQL, you will not be misled anymore and we wanted to get this fix to you in a service pack of the same product that introduced you this immensely easy way of expressing your logic in assemblies. Going forward, with database upgrades to next release, any lingering  assemblies that display this permission will be automatically corrected. And so you don’t have to worry about a thing.


So how can i secure assemblies registered to the server ?


The assembly is really a container for types, objects etc which are independently permissible. The assembly itself can be marked safe, external_access and unsafe. In addition, you can use the ‘trustworthy’ property of the database for similar effect. For a complete list with description of our security model, please refer CLR Integration security in Books Online.


Thanks,


Comments (4)

  1. > CLR supports a rich security model that lets you think about granting security to the code in the assembly ( code access security ).

    True, but this could be problematic for database security.  In general, DBAs must not assume that developers understand security as it will be implemented in the database.  However, developers may be involved in creating CLR assemblies that will be registered in the database.  Assembly-level permissions give (or would have given) the DBA an appropriate level of granularity for enforcing appropriate permissions in the database instance.  Would this really be so hard to implement?  I don’t think that pushing database-level security into the CLR is going to be a good idea.  It certainly won’t help Enterprise-Level DBAs (who are already cautious about SQL-CLR, frequently for good reasons) to become more eager to implement CLR support in their databases.

    Your thoughts?

    Cheers,

    Chris Leonard

  2. RaviR says:

    The line you point out is indeed misleading if it comes across that there are no alternatives for a DBA. However, this is not the case. On the contrary, we have more appropriate controls for the DBA. For eg:

    1) By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable this option, use sp_configure.

    2) Each assembly is checked for security both at – registration-to-database time and also at runtime. This means that an assembly would never run inside sql server unless its appropriately permitted with SAFE, EXTERNAL_ACCESS or UNSAFE. This is available only from SQL server and is in addition to CLR’s Code Access Security model.

    3) Moreover, a DBA has more relevant controls such as the types and objects defined in the assembly. Besides, the TRUSTWORTHY database property enables him/her to reduce the threats further by limiting EXTERNAL_ACCESS and UNSAFE assemblies from attached databases.

    For a complete and descriptive list of all aspects of this security model, please read the CLR Integration Security page in books online at http://msdn2.microsoft.com/fr-fr/library/ms131071.aspx

  3. saarp says:

    <quote>

    3) Moreover, a DBA has more relevant controls such as the types and objects defined in the assembly. Besides, the TRUSTWORTHY database property enables him/her to reduce the threats further by limiting EXTERNAL_ACCESS and UNSAFE assemblies from attached databases.

    </quote>

    This can be an overly granular switch. Say you had two assemblies: safestuff.dll and unsafestuff.dll. Each assembly can define several types, procedures, functions, etc. It may be difficult to tell which functions belong to which assembly. It’s very simple to grant untrusted users access to safestuff.dll, but only allowing trusted users access to unsafestuff.dll.

    If nothing else, I think issuing a GRANT EXECUTE on an assembly should have returned a warning message instead of an error. Anyone who did this inside a try/catch in previous releases will now fail.