This procedure is used with SAML 2.0-based single sign on. It is called each time a user signs in and serves to create and customize the user's account and permissions.
The procedure receives a SAML Response containing a SAML Assertion that describes the user. It must extract at least the username, and preferably also first name, last name and, email address, as well as group memberships or other permissions from this assertion. It must then create or update a Softadmin-user with these values and return its UserId.
This is a fully working example of a procedure handing an assertion from an AD FS-server. Other SAML Identity Providers and older versions of AD FS will use different attribute names.
CREATE PROCEDURE dbo.ExampleSamlLogin
@SamlResponse xml
AS
BEGIN
SET XACT_ABORT ON;
-- Get asssertion from Response.
DECLARE @AssertionXml xml = @SamlResponse.query('
declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
declare namespace samlp="urn:oasis:names:tc:SAML:2.0:protocol";
(samlp:Response/saml:Assertion[1])');
-- Get username from Subject.
DECLARE @Username varchar(120) = @AssertionXml.value('
declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
(saml:Assertion/saml:Subject/saml:NameID)[1]', 'varchar(120)');
IF @Username IS NULL
BEGIN
RAISERROR('Was unable to determine a username from the supplied assertion.', 16, 1);
RETURN -1;
END;
-- Get attributes from AttributeStatement.
CREATE TABLE #Attribute
(
AttributeName nvarchar(4000) NULL,
AttributeValue nvarchar(4000) NULL
);
INSERT #Attribute
(
AttributeName,
AttributeValue
)
SELECT
Attributes.Attribute.value('@Name', 'nvarchar(4000)'),
AttributeValues.AttributeValue.value('.', 'nvarchar(4000)')
FROM
@AssertionXml.nodes('
declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
(saml:Assertion/saml:AttributeStatement/saml:Attribute)') Attributes(Attribute)
CROSS APPLY Attributes.Attribute.nodes('
declare namespace saml="urn:oasis:names:tc:SAML:2.0:assertion";
(saml:AttributeValue/text())') AttributeValues(AttributeValue);
-- Get user properties from the attributes.
DECLARE @UsernameFirst nvarchar(50) =
(
SELECT AttributeValue
FROM #Attribute
WHERE AttributeName = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname'
);
DECLARE @UsernameLast nvarchar(50) =
(
SELECT AttributeValue
FROM #Attribute
WHERE AttributeName = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname'
);
DECLARE @UserEmail varchar(100) =
(
SELECT AttributeValue
FROM #Attribute
WHERE AttributeName = 'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress'
);
-- Create or update the user.
DECLARE @UserId int =
(
SELECT UserId
FROM SoftadminApi.[User]
WHERE Username = @Username
);
IF SoftadminApi.User_IsSoftadminAdministrator(@UserId) = 1
BEGIN
RAISERROR('User %d is an administrator account and should not use SAML login.', 16, 1, @UserId);
RETURN -1;
END;
EXEC SoftadminApi.User_InsertUpdate
@id = @UserId OUTPUT,
@UserName = @Username,
@UserNameFirst = @UsernameFirst,
@UserNameLast = @UsernameLast,
@UserEmail = @UserEmail,
@IsEnabled = 1,
@Password = NULL,
@RoleId = NULL;
-- Return the ID to the caller.
SELECT
@UserId AS UserId;
END;
If you want to map Active Directory security groups to function groups in the system then you need a table that maps group names to function group ids, and to reassign user functions for each login.
BEGIN TRANSACTION;
EXEC SoftadminApi.User_InsertUpdate
@id = @UserId OUTPUT
.........
DELETE dbo.UserFunctionGroup WHERE UserId = @UserId;
INSERT dbo.UserFunctionGroup (UserId, FunctionGroupId)
SELECT
@UserId,
FG.FunctionGroupId
FROM
#Attribute A
JOIN dbo.FunctionGroup FG ON FG.ActiveDirectoryName = A.AttributeValue
WHERE
A.AttributeName = 'http://schemas.xmlsoap.org/claims/Group';
EXEC dbo.UserFunctionGroup_Refresh
@UserId = @UserId;
COMMIT TRANSACTION;