Migration guide: database¶

This document describes how the database to store security data was structured in the legacy design, and how it has been implemented for the new Sequel Security Server.

Legacy Security Schema¶

The legacy security schema in a legacy shared database that contained security data looked like shown on the following diagram:

This section will not cover the details of this design (e.g.: the information stored into each table) as this design is very similar to the new one described in the next section.

New Security Schemas¶

The new database design, split into two database schemas, is explained below.

Authentication Schema¶

The [authentication] schema in the new Sequel Security Service database persists all the information about authentication. This feature is implemented by Identity Server, so the information handled by this technology is stored into the tables represented by the following diagram:

Authorization Schema¶

The [authorization] schema in the new Sequel Security Service database is intended to contain the data associated to authorization. The data model is quite similar to the legacy data model implemented by the [security] schema in the shared databases (mentioned at the beginning of this document); the main change is the addition of the new Application table to categorize roles, groups and user types by product in the suite.

User table in the legacy version contains several columns that comes from Identity and have never been used; we will use this opportunity to remove them. Columns to keep are described at Domain Model section.

The following E/R diagram shows the data model structure:

Application (security)¶

New table for storing information related to each application in the system, e.g. Claims, Origin, Impact, RE, Workflow, Product Builder, Rating Engine, Security Management,... Definition of this table will look like:

CREATE TABLE [authorization].[Application](
    [Id]   NVARCHAR (20) NOT NULL,
    [Description] NVARCHAR (50) NOT NULL,
    [UpdateUser] [nvarchar](50) NOT NULL CONSTRAINT [DF_Application_UpdateUser]  DEFAULT ('Unknown'),
    [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_Application_UpdateDate]  DEFAULT (getutcdate()),
    CONSTRAINT [PK_Application] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UK_Application_Description] UNIQUE NONCLUSTERED ([Description] ASC)
);
GO