IT:AD:NETSqlAzMan:HowTo:Authenticate a custom IIdentity

Consider an application authenticated by a SSO strategy. The IIdentity/IPrincipal returned will be a custom one that needs the following.

Basically, the original code was for WindowsIdentity and expected SIDs…so the hack that was put in later was to create a function that retrieves from a custom table reference to the user you want.

The official instructions are:

From the NetSqlAzMan ver. 1.3.0.0 is possible to execute the check-access not only through Windows authentication but even through custom authentication for defined users in a MS Sql Server database. This new functionality exists because of the dbo.GetDBUsers table-function, defined in the NetSqlAzMan storage Database storage. This table-function has to return a list of users defined through a value pair: CustomSid, Username (varbinary(85), varchar(255)). It’s very important to modify, before starting to use this functionality, the GetDBUsers table-function to correctly the list of users from any users table (even from a different Database), previously defined. At run-time we find 2 methods to retrieve such list (or just a specific user) through the GetDbUsers/GetDBUser functions, defined in IAzManStorage, IAzManStore e IAzManApplication object type.

One of the mentioned stored procs is:

USE [NetSqlAzManStorage]
GO
/****** Object:  UserDefinedFunction [dbo].[netsqlazman_GetDBUsers]    Script Date: 1/5/2013 3:25:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* 
    Example NetSqlAzMan GetDBUsers TABLE Function. Update this Function with your CUSTOM CODE. 
    Use: 
    1) SELECT * FROM dbo.GetDBUsers(<storename>, <appName>, NULL, NULL)            -- to retrieve all DB Users
    2) SELECT * FROM dbo.GetDBUsers(<storename>, <appName>, <customsid>, NULL)  -- to retrieve DB User with specified <customsid>
    3) SELECT * FROM dbo.GetDBUsers(<storename>, <appName>, NULL, <username>)  -- to retrieve DB User with specified <username>
    - Returned DBUserSid and DBUserName must be unique.
*/
CREATE FUNCTION [dbo].[netsqlazman_GetDBUsers] (@StoreName nvarchar(255), @appName nvarchar(255), @DBUserSid VARBINARY(85) = NULL, @DBUserName nvarchar(255) = NULL)  
RETURNS TABLE 
AS  
RETURN 
    -- Note hack to convert INT ID to a VARBINARY(85) SID to match older code:
    -- First two columns are SID,UserName:
    SELECT TOP 100 PERCENT CONVERT(VARBINARY(85), UserID) AS DBUserSid, UserName AS DBUserName, FullName, OtherFields FROM dbo.UsersDemo
    WHERE 
        (@DBUserSid IS NOT NULL AND CONVERT(VARBINARY(85), UserID) = @DBUserSid OR @DBUserSid  IS NULL)
        AND
        (@DBUserName IS NOT NULL AND UserName = @DBUserName OR @DBUserName IS NULL)
    ORDER BY UserName
GO

Obviously, you'd start by redirect from the provided demo table (dbo.UsersDemo) to a more appropriate table, whose two first columns follow the required convention.

The idea is that you keep in your app a local copy of the external SSO authenticated user.

NEVER EVER Keep passwords and authentication in your local table.

In practice, this means that new users that sign up via a WIF or other SSO based solutino are redirected back to an app Handler that makes a local identity.

The local identity of new users is unknown to the application.

If it is not found in the Db's Usertable (whatever it's now called) an entry is made, from info in the Identity. You don't need much – you just need a CustomSID and UserName.

The CustomSID has to end up as a VARBINARY(85).

using System;
using System.Collections.Generic;
using System.Security.Principal;
using ClassLibrary1;
using NetSqlAzMan;
using NetSqlAzMan.Interfaces;

namespace XAct.Spikes.ActivatorAndInternal
{
    class Program
    {
        static void Main(string[] args)
        {



            string connectionString =
                //"Data Source=(local);Initial Catalog = NetSqlAzManStorage;Integrated Security = SSPI;";
                "Data Source=XACT-WS2K8-02;Initial Catalog=NetSqlAzManStorage;User Id=Spikes;Password=Passw0rd1;";

IAzManStorage storage = new SqlAzManStorage(connectionString);//Get an IIDentity

            //Depending on the scenarion, Either this way:
            //WindowsIdentity identity = WindowsIdentity.GetCurrent();
            //or t:
            //identity = HttpContext.Current.Request.LogonUserIdentity;
            //But let's just fake it for now:
            System.Security.Principal.GenericIdentity fake = new GenericIdentity("MaryP");
            //And wrap it:
            IAzManDBUser iAzManDbUser = new AzManDBUser(fake,2);
                
            //test it against an Operation:AuthorizationType authorizationType = 
            AuthorizationType authorizationTypeA1 = storage.CheckAccess("Biz", "App1", "ExpenseProcess", iAzManDbUser, DateTime.Now, true);
            AuthorizationType authorizationTypeA2 = storage.CheckAccess("Biz", "App1", "ExpenseApprove", iAzManDbUser, DateTime.Now, true);
            AuthorizationType authorizationTypeA3 = storage.CheckAccess("Biz", "App1", "ExpenseDisapprove", iAzManDbUser, DateTime.Now, true);





            fake = new GenericIdentity("SkyS");
            //And wrap it:
            iAzManDbUser = new AzManDBUser(fake,1);

            //test it against an Operation:AuthorizationType authorizationType = 
            AuthorizationType authorizationTypeB1 = storage.CheckAccess("Biz", "App1", "ExpenseProcess", iAzManDbUser, DateTime.Now, true);
            AuthorizationType authorizationTypeB2 = storage.CheckAccess("Biz", "App1", "ExpenseApprove", iAzManDbUser, DateTime.Now, true);
            AuthorizationType authorizationTypeB3 = storage.CheckAccess("Biz", "App1", "ExpenseDisapprove", iAzManDbUser, DateTime.Now, true);

            try
            {
                AuthorizationType authorizationType0 = storage.CheckAccess("Biz", "App1", "JustMadeThisUp", iAzManDbUser,
                                                                           DateTime.Now, true);
            }catch(System.Exception e)
            {
                //throw an exception for unknown Operation
            }



            //IMyService o = (IMyService) Activator.CreateInstance(typeof (MyService));
            //Console.WriteLine(o.Do());

            XAct.Services.IoC.NinjectBootstrapper.Initialize();

            Initializer.Initialize();

            Console.WriteLine(XAct.Services.ServiceLocatorService.Current.GetInstance<IMyService>().Do());

        }
    }

    public class AzManDBUser : IAzManDBUser
    {
        public IAzManSid CustomSid { get; private set; }
        public string UserName { get; private set; }
        public Dictionary<string, object> CustomColumns { get; private set; }

        public AzManDBUser(IIdentity identity, int id)
        {
            UserName = identity.Name;
            var o = new SqlAzManSID(SqlAzManSID.GetBytesFromInt32(id),true);
            CustomSid = o;
        }
    }
}

Other than the sortof weird process of converting an int into a SID…it works!