Handling Grants in a Function Hierarchy

Applications usually implement some kind of security or grant management which allows a user to perform certain operations or restricts them from invoking them. This post presents a generic way of handling grants in a function hierarchy.

First we need a list of all functions in the application. For illustration, I use a simple shop application with a minimal set of functions. A function can be a general operation, or an operation on a certain database record:

CREATE TABLE #Functions (
	ID	INT NOT NULL PRIMARY KEY,
	Name	NVARCHAR(50),
	ParentFunctionID INT NULL, -- REFERENCES #Functions (ID),
	Parameter1 	NVARCHAR(50),
	-- optionally more parameters
	CargoParameters	NVARCHAR(50),
	ResultType	NVARCHAR(50)
)

SET NOCOUNT ON 

INSERT INTO #Functions (ID, Name, CargoParameters, ResultType)
  VALUES (1, 'CreateProduct', 'ProductDescription pd', 'int')
INSERT INTO #Functions (ID, Name, ParentFunctionID, Parameter1)
  VALUES (10, 'ManageProduct', NULL, 'ProductID')
INSERT INTO #Functions (ID, Name, ParentFunctionID, Parameter1)
  VALUES (11, 'EditProduct', 10, 'ProductID')
INSERT INTO #Functions (ID, Name, ParentFunctionID, Parameter1)
  VALUES (12, 'RemoveProduct', 10, 'ProductID')
INSERT INTO #Functions (ID, Name) VALUES (20, 'GoShopping')
INSERT INTO #Functions (ID, Name, ParentFunctionID)
  VALUES (21, 'ListProducts', 20)
INSERT INTO #Functions (ID, Name, ParentFunctionID, Parameter1, CargoParameters, ResultType)
  VALUES (22, 'AddToCart', 20, 'ProductID', 'int Quantity', 'ShoppingResult')

SELECT * FROM #Functions

This results in the following table:

ID Name Parent FunctionID Parameter1 CargoParameters ResultType
1 CreateProduct NULL NULL ProductDescription pd int
10 ManageProduct NULL ProductID NULL NULL
11 EditProduct 10 ProductID NULL NULL
12 RemoveProduct 10 ProductID NULL NULL
20 GoShopping NULL NULL NULL NULL
21 ListProducts 20 NULL NULL NULL
22 AddToCart 20 ProductID int Quantity ShoppingResult

Once we have the complete list of all functions, we can generate code to implement a number of methods for each function:

IsPossibleToX Is it possible to execute function X now (depending on the information / status of the record
IsUserAllowedToX Does the user have sufficient rights to execute X
ExecuteX Invoke execution of function with parameters and result value

In this post, I use a class named UserContext which identifies the current user and encapsulates the way the user is identified: whether it is a Guid, an integer, is not relevant for this code.

The usual TSQL cursor magic:

DECLARE @Name NVARCHAR(50), @Parameter1 NVARCHAR(50), @FP_Name NVARCHAR(50),
  @FP_Parameter1 NVARCHAR(50)
DECLARE @CargoParameters NVARCHAR(50), @ResultType NVARCHAR(50)

PRINT 'public partial class FunctionBase
{'

DECLARE c CURSOR FOR
SELECT F.Name, F.Parameter1, FP.Name AS FP_Name, FP.Parameter1 AS FP_Parameter1,
  F.CargoParameters, F.ResultType
FROM #Functions F
LEFT OUTER JOIN #Functions FP ON F.ParentFunctionID = FP.ID
ORDER BY F.ID

OPEN c
FETCH c INTO @Name, @Parameter1, @FP_Name, @FP_Parameter1, @CargoParameters,
  @ResultType

WHILE @@FETCH_STATUS = 0 BEGIN

For each of the methods above, we generate a C# method with the default behavior:

  • If the function has a parent, call the parent.
  • If the function has no parent, return false.
  • The Execute method throws an exception.
  PRINT '	public virtual bool IsPossibleTo' + @Name + '(' +
    CASE WHEN @Parameter1 IS NOT NULL THEN 'int ' + @Parameter1 ELSE '' END + ')'
  PRINT '	{'

  IF @FP_Name IS NULL
    PRINT '		return false;'
  ELSE IF ISNULL(@Parameter1, '') = ISNULL(@FP_Parameter1, '')
    PRINT '		return IsPossibleTo' + @FP_Name + '(' + ISNULL(@Parameter1, '') + ');'
  ELSE IF @FP_Parameter1 IS NULL
    PRINT '		return IsPossibleTo' + @FP_Name + '();'
  ELSE -- no @param, but @fpparam
    PRINT '		return IsPossibleTo' + @FP_Name + '(0);'

  PRINT '	}'

  PRINT '	public virtual bool IsUserAllowedTo' + @Name + '(UserContext u' +
    CASE WHEN @Parameter1 IS NOT NULL THEN ', int ' + @Parameter1 ELSE '' END + ')'
  PRINT '	{'

  IF @FP_Name IS NULL
    PRINT '		return false;'
  ELSE IF ISNULL(@Parameter1, '') = ISNULL(@FP_Parameter1, '')
    PRINT '		return IsUserAllowedTo' + @FP_Name + '(u' +
      CASE WHEN @Parameter1 IS NOT NULL THEN ', ' + @Parameter1 ELSE '' END + ');'
  ELSE IF @FP_Parameter1 IS NULL
    PRINT '		return IsUserAllowedTo' + @FP_Name + '(u);'
  ELSE -- no @param, but @fpparam
    PRINT '		return IsUserAllowedTo' + @FP_Name + '(u, 0);'

  PRINT '	}'

  PRINT '	public virtual ' + ISNULL(@ResultType, 'void') + ' Do' + @Name + '(UserContext u' +
    CASE WHEN @Parameter1 IS NOT NULL THEN ', int ' + @Parameter1 ELSE '' END +
    CASE WHEN @CargoParameters IS NOT NULL THEN ', ' + @CargoParameters ELSE '' END +
    ')'
  PRINT
'	{
		throw new Exception("FunctionBase.Do' + @Name + ' is not implemented");
	}'

End of cursor code:

  FETCH c INTO @Name, @Parameter1, @FP_Name, @FP_Parameter1,
    @CargoParameters, @ResultType

END

CLOSE c
DEALLOCATE c

PRINT '}'

DROP TABLE #Functions

This results in a class FunctionBase which implements security methods for each function, such as:

public partial class FunctionBase
{
  public virtual bool IsPossibleToCreateProduct()
  {
    return false;
  }
  public virtual bool IsUserAllowedToCreateProduct(UserContext u)
  {
    return false;
  }
  public virtual int DoCreateProduct(UserContext u, ProductDescription pd)
  {
    throw new Exception("FunctionBase.DoCreateProduct is not implemented");
  }

Not too impressive at first glance, but look at EditProduct, which is below ManageProduct in the function hierarchy:

  public virtual bool IsPossibleToManageProduct(int ProductID)
  {
    return false;
  }
  public virtual bool IsUserAllowedToManageProduct(UserContext u, int ProductID)
  {
    return false;
  }
  public virtual void DoManageProduct(UserContext u, int ProductID)
  {
    throw new Exception("FunctionBase.DoManageProduct is not implemented");
  }
  public virtual bool IsPossibleToEditProduct(int ProductID)
  {
    return IsPossibleToManageProduct(ProductID);
  }
  public virtual bool IsUserAllowedToEditProduct(UserContext u, int ProductID)
  {
    return IsUserAllowedToManageProduct(u, ProductID);
  }
  public virtual void DoEditProduct(UserContext u, int ProductID)
  {
    throw new Exception("FunctionBase.DoEditProduct is not implemented");
  }

The IsPossibleToEditProduct and IsUserAllowedToEditProduct methods automatically inherit the result from IsPossibleToManageProduct and IsUserAllowedToManageProduct.

What would be the value of a generated function which only throws exceptions, you might ask. Well, this is the point where the actual coding begins:

public partial class Function : FunctionBase
{
  private static Function _instance;
  public static Function Instance
  {
    get
    {
      if (_instance == null)
        _instance = new Function();
      return _instance;
    }
  }

  public override bool IsPossibleToManageProduct(int ProductID)
  {
    // implement your checks here, all functions below ManageProduct inherit this result by default
    return Result;
  }
  public override bool IsUserAllowedToManageProduct(UserContext u, int ProductID)
  {
    // implement your checks here, all functions below ManageProduct inherit this result by default
    return Result;
  }
  public virtual void DoEditProduct(UserContext u, int ProductID)
  {
    // implement your function here
  }
}

And to check, whether an operation is possible and allowed, use:

UserContext uc = RetrieveUserContext();
int id = RetrieveRecordIdentifier();
if (Function.Instance.IsPossibleToEditProduct(id) &&
    Function.Instance.IsUserAllowedToEditProduct(uc, id))
  Function.Instance.DoEditProduct(uc, id);

Note that you can define additional parameters and a result type for the execution method.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: