Seeding authentication

  • Updated on Aug 22, 2019
  • Published on Aug 22, 2019
  • 1 minute(s) read
Prev Next

The authentication database i multi-tenant, meaing it can contain configuration for many tenants.

Start by declaring the tenant:

DECLARE @organization NVARCHAR(255) = 'acme'
DECLARE @environment NVARCHAR(255) = '$(Environment)'
SQL

Configuration is very similiar across environments, so working with variables in the sql script is a way to reduce the number of files needed in the repository. In these examples we use the Azure Devops syntax for variables, $(VariableName).

One way to use continous integration for the seeding is like in this guide: Continuous integration for database seeding

You can have as many roles as you wish. These roles will be present in a client's JWT as role claims.

DECLARE @role NVARCHAR(255) = 'ExternalSystemUser'
IF NOT EXISTS (SELECT 1 FROM Role WHERE Name = @role)
BEGIN
    INSERT INTO Role (Name) VALUES (@role)
END
SET @role = 'InternalSystemUser'
IF NOT EXISTS (SELECT 1 FROM Role WHERE Name = @role)
BEGIN
    INSERT INTO Role (Name) VALUES (@role)
END
SQL

The clients are stored in the ApiUser table, and their roles in ApiUser_Role.

DECLARE @id UNIQUEIDENTIFIER = newid()

INSERT INTO ApiUser (Id, Organization, Environment, Name, Salt, HashedSecret)
    VALUES (@id, @organization, @environment, 'acme-api', '<salt>', '<hashed password>')
INSERT INTO ApiUser_Role (ApiUserId, RoleId) SELECT @id, Id FROM Role WHERE Name = @role
SQL

Unfortunately, it's a bit tricky at the moment to create the salt and hashed password and transfer it to the script as it involves

  1. Creating the ApiUser through the api of Nexus Fundamentals
  2. SELECT the Salt and HashedSecret columns from the authentication database
  3. Paste them either in the script, or more likely, as variables in Azure Devops

Welcome with any ideas you may have how to make this easier for you. There is always the option of always going for the api, but that may feel tricky as well.