Lamenting the lack of full DDL triggers and SQLCLR in SQL Azure

Recently I have been working with an alpha release of Aditibus™ Policy Server which supports some nice features well beyond the traditional role based access control (RBAC). Some of these features include native support for:

• Geospatial Constraints – base on physical location of user
• Historical Constraints – based on user’s past actions or other’s actions
• Temporal  Constraints – based on time, for example, is someone on duty or not
• Policy Effectivity – ability to set policy rules to be turned on or off in the future automatically
• Strict Delegation – giving someone else a set of permissions and by doing so, you no longer have those permissions until you recall the permissions
• Soft Delegation  --giving someone else a set of permissions and by doing so, you still retain those permissions until you revoke the permissions (or someone removes those permission from you)
• Obligation – following the UCONabc model

Aditibus™ recommended interface for SQL Server is actually sweet. It is done via DDL, for example their code snipet for controlling login is a very simple:

 

Code Snippet

1. CREATE TRIGGER Login_Permission
2. ON ALL SERVER
3. FOR LOGON
4. AS
5. BEGIN
6. IF Aditybus.dbo.HasPermission(EVENTDATA()) !=1
7.     BEGIN
8.     ROLLBACK
9.     RETURN
10.     END
11. END

 

This allows the addition of Aditibus™ “Odin’s Oak” product to existing SQL Systems without needing to modify the existing code base. Unfortunately this will not work on SQL Azure per the documentation. Their HasPermission function is a SQLCLR function which fortunately has alternatives available – but at a cost of lower performance.

 

So I am hoping that SQL Azure will evolve to integrate better (or that Aditibus will find elegant solutions). As a FYI to newbies, the list of DDL events supported in SQL Server may be found here.