CG.Infrastructure.Authorization.Data 1.1.1

Infrastructure.Authorization.Data

A .NET library that contains database migration scripts and schema definitions for the Infrastructure.Authorization library, specifically designed for Duende IdentityServer persistence storage.

Overview

Infrastructure.Authorization.Data is a .NET 9.0 library that provides database infrastructure for the Authorization library. It contains DbUp SQL scripts for creating and maintaining the database schema required for Duende IdentityServer functionality, including clients, resources, scopes, and persisted grants.

Features

• Database Schema Management: Automated schema creation and management
• Duende IdentityServer Support: Complete database structure for IdentityServer framework
• DbUp Integration: Seamless integration with DbUp for database migrations
• Clean Architecture: Separation of database scripts from business logic
• Comprehensive Authorization Tables: Clients, ApiResources, ApiScopes, IdentityResources, PersistedGrants

Target Framework

• .NET 9.0

Package Information

• Package ID: CG.Infrastructure.Authorization.Data
• Version: 1.1.0
• Authors: Matthew Evans
• Company: Matthew Evans
• Product: CG.Infrastructure.Authorization.Data
• Description: Infra Authorization Data library that contains DbUp SQL scripts and functionality to create the necessary tables for Duende IdentityServer persistence storage

Project Structure

Infrastructure.Authorization.Data/
├── Scripts/
│   ├── Schemas/
│   │   └── EnsureSchema.sql                    # Schema creation script
│   └── Migrations/
│       ├── 000001 - AlterDatabase.sql         # Database configuration
│       ├── 000002 - CreateTable - Clients.sql
│       ├── 000003 - CreateTable - ClientGrantTypes.sql
│       ├── 000004 - CreateTable - ClientRedirectUris.sql
│       ├── 000005 - CreateTable - ClientPostLogoutRedirectUris.sql
│       ├── 000006 - CreateTable - ClientScopes.sql
│       ├── 000007 - CreateTable - ClientSecrets.sql
│       ├── 000008 - CreateTable - ClientClaims.sql
│       ├── 000009 - CreateTable - ClientIdPRestrictions.sql
│       ├── 000010 - CreateTable - ClientCorsOrigins.sql
│       ├── 000011 - CreateTable - ClientProperties.sql
│       ├── 000012 - CreateTable - ApiResources.sql
│       ├── 000013 - CreateTable - ApiResourceScopes.sql
│       ├── 000014 - CreateTable - ApiResourceSecrets.sql
│       ├── 000015 - CreateTable - ApiResourceClaims.sql
│       ├── 000016 - CreateTable - ApiResourceProperties.sql
│       ├── 000017 - CreateTable - ApiScopes.sql
│       ├── 000018 - CreateTable - ApiScopeClaims.sql
│       ├── 000019 - CreateTable - ApiScopeProperties.sql
│       ├── 000020 - CreateTable - IdentityResources.sql
│       ├── 000021 - CreateTable - IdentityResourceClaims.sql
│       ├── 000022 - CreateTable - IdentityResourceProperties.sql
│       ├── 000023 - CreateTable - PersistedGrants.sql
│       └── 000024 - CreateIndexes.sql         # Performance indexes

Note: Query constants are now defined in the main Infrastructure.Authorization project under Data/Queries/AuthorizationQueries.cs for better organization and maintainability.

Database Schema

Core Authorization Tables

Clients Table

CREATE TABLE [dbo].[Clients]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ClientId] [nvarchar](200) NOT NULL,
    [ClientName] [nvarchar](200) NULL,
    [ClientUri] [nvarchar](2000) NULL,
    [LogoUri] [nvarchar](2000) NULL,
    [Description] [nvarchar](1000) NULL,
    [Enabled] [bit] NOT NULL,
    [ProtocolType] [nvarchar](200) NOT NULL,
    [RequireClientSecret] [bit] NOT NULL,
    [RequireConsent] [bit] NOT NULL,
    [RequirePkce] [bit] NOT NULL,
    [AllowPlainTextPkce] [bit] NOT NULL,
    [AllowAccessTokensViaBrowser] [bit] NOT NULL,
    [AllowOfflineAccess] [bit] NOT NULL,
    [AllowRememberConsent] [bit] NOT NULL,
    [AlwaysIncludeUserClaimsInIdToken] [bit] NOT NULL,
    [AlwaysSendClientClaims] [bit] NOT NULL,
    [IncludeJwtId] [bit] NOT NULL,
    [RequireRequestObject] [bit] NOT NULL,
    [AccessTokenLifetime] [int] NOT NULL,
    [AuthorizationCodeLifetime] [int] NOT NULL,
    [IdentityTokenLifetime] [int] NOT NULL,
    [AbsoluteRefreshTokenLifetime] [int] NOT NULL,
    [SlidingRefreshTokenLifetime] [int] NOT NULL,
    [RefreshTokenUsage] [int] NOT NULL,
    [RefreshTokenExpiration] [int] NOT NULL,
    [AccessTokenType] [int] NOT NULL,
    [EnableLocalLogin] [bit] NOT NULL,
    [FrontChannelLogoutSessionRequired] [bit] NOT NULL,
    [FrontChannelLogoutUri] [nvarchar](2000) NULL,
    [BackChannelLogoutSessionRequired] [bit] NOT NULL,
    [BackChannelLogoutUri] [nvarchar](2000) NULL,
    [ClientClaimsPrefix] [nvarchar](200) NULL,
    [PairWiseSubjectSalt] [nvarchar](200) NULL,
    [UserSsoLifetime] [int] NULL,
    [UserCodeType] [nvarchar](100) NULL,
    [DeviceCodeLifetime] [int] NOT NULL,
    [ConsentLifetime] [int] NULL,
    [AllowedIdentityTokenSigningAlgorithms] [nvarchar](100) NULL,
    [LastAccessed] [datetime2](7) NULL,
    [UpdateAccessTokenClaimsOnRefresh] [bit] NOT NULL,
    [NonEditable] [bit] NOT NULL,
    CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED ([Id] ASC)
)

ApiResources Table

CREATE TABLE [dbo].[ApiResources]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [DisplayName] [nvarchar](200) NULL,
    [Description] [nvarchar](1000) NULL,
    [Enabled] [bit] NOT NULL,
    [ShowInDiscoveryDocument] [bit] NOT NULL,
    [RequireResourceIndicator] [bit] NOT NULL,
    [AllowedAccessTokenSigningAlgorithms] [nvarchar](100) NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NULL,
    [LastAccessed] [datetime2](7) NULL,
    [NonEditable] [bit] NOT NULL,
    CONSTRAINT [PK_ApiResources] PRIMARY KEY CLUSTERED ([Id] ASC)
)

ApiScopes Table

CREATE TABLE [dbo].[ApiScopes]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [DisplayName] [nvarchar](200) NULL,
    [Description] [nvarchar](1000) NULL,
    [Enabled] [bit] NOT NULL,
    [Required] [bit] NOT NULL,
    [Emphasize] [bit] NOT NULL,
    [ShowInDiscoveryDocument] [bit] NOT NULL,
    [UserClaims] [nvarchar](max) NULL,
    [Properties] [nvarchar](max) NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NULL,
    [LastAccessed] [datetime2](7) NULL,
    [NonEditable] [bit] NOT NULL,
    CONSTRAINT [PK_ApiScopes] PRIMARY KEY CLUSTERED ([Id] ASC)
)

IdentityResources Table

CREATE TABLE [dbo].[IdentityResources]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [DisplayName] [nvarchar](200) NULL,
    [Description] [nvarchar](1000) NULL,
    [Enabled] [bit] NOT NULL,
    [Required] [bit] NOT NULL,
    [Emphasize] [bit] NOT NULL,
    [ShowInDiscoveryDocument] [bit] NOT NULL,
    [UserClaims] [nvarchar](max) NULL,
    [Properties] [nvarchar](max) NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NULL,
    [NonEditable] [bit] NOT NULL,
    CONSTRAINT [PK_IdentityResources] PRIMARY KEY CLUSTERED ([Id] ASC)
)

PersistedGrants Table

CREATE TABLE [dbo].[PersistedGrants]
(
    [Key] [nvarchar](200) NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
    [SubjectId] [nvarchar](200) NULL,
    [SessionId] [nvarchar](100) NULL,
    [ClientId] [nvarchar](200) NOT NULL,
    [Description] [nvarchar](200) NULL,
    [CreationTime] [datetime2](7) NOT NULL,
    [Expiration] [datetime2](7) NULL,
    [ConsumedTime] [datetime2](7) NULL,
    [Data] [nvarchar](max) NOT NULL,
    CONSTRAINT [PK_PersistedGrants] PRIMARY KEY CLUSTERED ([Key] ASC)
)

Related Tables

The schema also includes supporting tables for:

• Client Relationships: GrantTypes, RedirectUris, PostLogoutRedirectUris, Scopes, Secrets, Claims, IdPRestrictions, CorsOrigins, Properties
• Resource Relationships: ApiResourceScopes, ApiResourceSecrets, ApiResourceClaims, ApiResourceProperties
• Scope Relationships: ApiScopeClaims, ApiScopeProperties
• Identity Resource Relationships: IdentityResourceClaims, IdentityResourceProperties

Usage

Database Migration

This library is designed to be used with DbUp for database migrations. The scripts are embedded as resources and can be executed using the DbUp framework.

Integration with Authorization.DbUp

The scripts are typically executed through the Authorization.DbUp project, which provides a command-line interface for database migrations.

Script Execution Order

1. EnsureSchema.sql: Creates the database schema if it doesn't exist
2. AlterDatabase.sql: Configures database settings and collation
3. CreateTable scripts: Creates all Authorization tables in dependency order
4. CreateIndexes.sql: Creates performance indexes

Note: This project now uses query constants instead of stored procedures for better maintainability and version control.

Dependencies

This library has no external dependencies and is designed to be lightweight, containing only the necessary SQL scripts for database setup.

Best Practices

1. Version Control: All database scripts are version-controlled
2. Idempotency: Scripts are designed to be run multiple times safely
3. Schema Variables: Uses DbUp variables for schema names
4. Clean Separation: Database scripts are separate from business logic
5. Query Constants: SQL queries are defined as C# constants for better maintainability
6. Performance Indexes: Includes strategic indexes for common query patterns
7. Direct SQL Execution: Queries are executed directly using CommandType.Text for flexibility

Migration Process

Using DbUp

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(typeof(Program).Assembly)
    .WithVariable("schemaname", "dbo")
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

Manual Execution

Scripts can also be executed manually in SQL Server Management Studio or any other SQL client.

Duende IdentityServer Configuration

To use this with Duende IdentityServer, configure the stores:

services.AddIdentityServer()
    .AddConfigurationStore(options =>
    {
        options.ConfigureDbContext = builder =>
            builder.UseSqlServer(connectionString);
    })
    .AddOperationalStore(options =>
    {
        options.ConfigureDbContext = builder =>
            builder.UseSqlServer(connectionString);
    });

Query Constants Usage

The project now uses C# query constants instead of stored procedures. Here's how to use them:

// Example: Using the query constants with IDataAccess
await dataAccess.SaveData(AuthorizationQueries.Clients_Insert, client, connectionName, CommandType.Text);
await dataAccess.LoadData<Client>(AuthorizationQueries.Clients_GetByClientId, new { ClientId = clientId }, connectionName, CommandType.Text);

Available Query Categories:

• Client Queries: Insert, Get operations for clients
• Client Grant Types Queries: Client grant type management operations
• Client Redirect URIs Queries: Client redirect URI management operations
• Client Post Logout Redirect URIs Queries: Post-logout redirect URI operations
• Client Scopes Queries: Client scope relationship operations
• Client Secrets Queries: Client secret management operations
• Client Claims Queries: Client claim management operations
• Client IDP Restrictions Queries: Identity provider restriction operations
• Client CORS Origins Queries: CORS origin management operations
• Client Properties Queries: Client property management operations
• Api Resources Queries: API resource CRUD operations
• Api Resource Scopes Queries: API resource scope relationship operations
• Api Resource Secrets Queries: API resource secret management operations
• Api Resource Claims Queries: API resource claim management operations
• Api Resource Properties Queries: API resource property management operations
• Api Scopes Queries: API scope CRUD operations
• Api Scope Claims Queries: API scope claim management operations
• Api Scope Properties Queries: API scope property management operations
• Identity Resources Queries: Identity resource CRUD operations
• Identity Resource Claims Queries: Identity resource claim management operations
• Identity Resource Properties Queries: Identity resource property management operations
• Persisted Grants Queries: Persisted grant CRUD operations

Contributing

This project is part of the CG Infrastructure Libraries. For contributions, please refer to the main infrastructure repository guidelines.

License

See the LICENSE file in the root directory for licensing information.

Support

For issues and questions related to this library, please contact the development team or create an issue in the project repository.