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
  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.


Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape