Wednesday, February 9, 2011

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.

No comments:

Post a Comment