Basics
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)'
Variables
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).
Automation
One way to use continous integration for the seeding is like in this guide: Continuous integration for database seeding
Table Role
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
Table ApiUser and ApiUser_Role
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
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
- Creating the ApiUser through the api of Nexus Fundamentals
- SELECT the Salt and HashedSecret columns from the authentication database
- 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.