This procedure is called a user signs in with OpenID Connect. Its purpose is to transform the claims in the ID token (and optionally also claims from the @ExtraUserInfo parameter) into a user, which involves either updating an existing user or generating a new one.
This is an example of what an ID token might look like
{
"at_hash": "R4HNCtA0ul4-yPXu7D7UWA",
"sub": "user-subject-1234531",
"aud": "Softadmin",
"iss": "https://www.certification.openid.net/test/a/dsa43fdgsgf/",
"exp": 1697558253,
"nonce": "638331547528131249.TNXmEdaCeFv52FFCWzagkEc7qUNsRTgawkGrYUUCso8=",
"email": "bob@example.com",
"given_name": "Bob",
"family_name": "Smith",
"locale": "en"
}
CREATE PROCEDURE Example.OpenIdSignIn
@IdToken nvarchar(MAX),
@ExtraUserInfo nvarchar(MAX) = NULL
AS
BEGIN
SET XACT_ABORT, NOCOUNT ON;
-- Get claims from the id token.
DECLARE
@Issuer nvarchar(MAX),
@Subject nvarchar(MAX),
@Email nvarchar(MAX),
@GivenName nvarchar(MAX),
@FamilyName nvarchar(MAX),
@Locale nvarchar(MAX);
SELECT
-- issuer and subject are guaranteed to exist in the id token.
@Issuer = JSON_VALUE(@IdToken, '$.iss'),
@Subject = JSON_VALUE(@IdToken, '$.sub'),
-- The following are just examples.
-- A server may not return them, or return them in @ExtraUserInfo instead of in @IdToken.
@Email = JSON_VALUE(@IdToken, '$.email'),
@GivenName = JSON_VALUE(@IdToken, '$.given_name'),
@FamilyName = JSON_VALUE(@IdToken, '$.family_name'),
@Locale = JSON_VALUE(@IdToken, '$.locale');
-- Subject is a claim that will always be present and so it is used in this example.
-- There may be another claim that is more suitable for usernames sent by the server
-- you're integrating against.
-- Using subject alone as username is NOT a good idea if you have more than one IdP.
DECLARE @Username varchar(120) = @Subject;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
DECLARE
@UserId int,
@RoleId int,
@LanguageId int,
@IsNewUser bit = 1;
SELECT
@UserId = U.UserId,
@RoleId = U.RoleId,
@LanguageId = U.LanguageId,
@IsNewUser = 0
FROM
SoftadminApi.[User] U
WHERE
U.Username = @Username;
IF @LanguageId IS NULL AND @Locale IS NOT NULL
BEGIN
SELECT @LanguageId = (
SELECT TOP (1)
L.LanguageId
FROM
SoftadminApi.Language
L WHERE
L.CultureCode LIKE @Locale + '%' AND
L.IsEnabled = 1
);
END;
DECLARE @id int;
EXEC SoftadminApi.User_InsertUpdate
@id = @UserId OUTPUT,
@Password = NULL,
@UserName = @Username,
@UserNameFirst = @GivenName,
@UserNameLast = @FamilyName,
@UserEmail = @Email,
@RoleId = @RoleId,
@LanguageId = @LanguageId,
@IsEnabled = 1;
--IF @IsNewUser = 1
--BEGIN
-- -- Assign default permissions to the new user here,
-- -- or try to extract their permissions from the claims.
--END
COMMIT TRANSACTION;
-- Return the user id that should be signed in.
SELECT @UserId AS UserId;
END;
User ID to sign in as. Must belong to a user that exists and is not disabled.