Implementing Organization Permissions in SQL Server - Overview

Permissions are often a complex problem that depends very much on the context. The closest non-computer analogy that I can think of is that of human language grammars. Sentence structure is not always <Subject> <Verb> <Object> in English and definitely not so in many languages. My experience is that there is no universal “permissions structure”, just like there is no universal sentence structure.


In this series I’m going to look at a modest complexity implementation  with the following organization structure

  • Organizations are in a strict reporting hierarchy (tree) with:
    • Organization Units (OU) being of specific types
    • The type of parents/child OU are defined and enforced in SQL Server
    • Relationships are just parent/child
  • Organization are in a “cross-reporting” hierarchy (tree) that is relative but simple.
    • OU relationships are just parent/child.
    • One OU may have multiple parents
  • Roles are sets of fine grain Access Rights (AR) on an OU
    • Examples are:
      • View Budget
      • Add Employee
      • Add Document
  • Users may be granted Roles on OU. Roles can be one of two types:
    • OU scoped (AR applies to OU only, not to children)
    • OU Tree (AR applies to OU and all children under a specific tree)

I will be developing the SQL Server 2008 implementation as well as a complementary C# implementation for a Web Site.  The code is not optimized for use in large/huge organizations but should be sufficient for small or medium size organizations . In general ID’s are done as INT Identity(1,1) for code simplicity and columns are minimal (no CreatedBy, CreatedDate, ModifiedBy, ModifiedDate etc etc) and creators do not become owners.


The purpose of this series is to illustrate a simple implementation for a complex problem that exploits TSQL Table and Scalar Functions in SQL Server.


Stay tune…


Popular posts from this blog

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

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

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