Sequel Security Tool¶

This document provides useful information about the sequel-security command line tool.

Installation¶

sequel-security is a global tool implemented using the Global Tools feature in .NET Core 6.0. The .NET Core 6.0 runtime must be installed for executing the tool.

For production, get the version of this tool (Sequel.Security\Packages\Tools\sequel-security\sequel-security.exe) from the same Sequel.Deployment.Manager you work with, for example:

\\buildoutput.office.sbs\drops\Security\stable\**version-number**\Sequel.Security\Packages\Tools\sequel-security\sequel-security.exe

The tool has to be executed with a user with enough permissions in the database for adding, deleting and updating data on security database.

Functionality¶

The sequel-security tool has been implemented to cover the following functionality:

import data packages into the new Sequel Security Service, either migrated from a legacy system or from the new one.
export data from the new Sequel Security Service.
add-admin-user to the new Sequel Security Service and assign the membership: SEC.CONFIGURATOR (Role) / SEC.PUBLIC (Group).
sync synchronizes a legacy security database with a Security database.
migrate from legacy systems (Claims and Origin) to the new Sequel Security Service, so data migration packages can be generated with the tool and imported later into the new service.
migrate-validate the compatibility from legacy systems to the new Sequel Security Service, and generates a report with the issues detected.
repoint-client changes the Redirect, Post Logout Redirect and CORS Origin URIs from one value to another.
idsrv-license commands for managing Identity Server licenses.
multitenancy commands for manipulating tenants configuration.

Commands¶

After installing the global tool as described above, the easiest way to learn how to use it is by just running the following command from any location:

sequel-security -h

That command will show the help page with detailed information about each allowed command.

Import¶

Import data packages into the new Sequel Security Service, either migrated from a legacy system or from the new one.

Required parameters:

    -d|--domain         /* Domain to be imported. Expected values:  authentication | authorization | users | entities | configuration */
    -a|--application    /* Valid when domain is equal to [authentication | authorization]. Application key used to filter data to be imported.*/
    -i|--input          /* Path to the folder or zip file that contains the package to be imported. */
    -c|--connection     /* Connection string to the target Security database. */

Optional parameters:

    -e|--entities               /* Entities to be imported (all by default) related to domain. Expected values (multiple allowed):
                                    [authentication] => clients | api-resources
                                    [authorization] => applications | securables | roles | groups | user-types | permissions | users | memberships | user-type-assignments
                                    [users ] => users | memberships | user-type-assignments
                                    [entities] => entities | entityMemberships | entityUserTypeAssignments | entityUsers */
    -ieu|--includeEntityUsers   /* Valid when domain is equal to 'entities'. Import entities including EntityUsers */
    -tr|--tokenReplacement      /* Replacement of tokens values in the package before being imported (multiple allowed). Expected syntax: {token}{value} */
    -trf|--tokenReplacementFile /* Path to the text file that contains the tokens to replace in the package before being imported. Text file contains one line per token with the expected syntax: {token}{value} */
    -tp|--tracedPackage         /* Path (folder or zip file) where the traced package will be exported. The trace package will contain updated entities (and tokens resolved if some token replacement parameter defined too). */
    -v|--verbose                /* Activates verbose mode */
    -s|--silence                /* Execute tool in silence mode for executing in background tasks */
    -st|--strategy              /* Import strategy, options are merge (default) or replace. 
                                    Merge is not deleting items in collections.
                                    Replace ensures collections at the system are equals to the definition on the import file. 
                                    Only applies to User domain. */

Example 1: We want to import all Workflow authorization entities into a security database called SECURITYDB located at DBSERVER server from a ZIP file called Package.zip:

sequel-security import -a WF -d authorization -i Package.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 2: We want to import Workflow membership entities into a security database called SECURITYDB located at DBSERVER server from a ZIP file called Package.zip:

sequel-security import -a WF -d users -e memberships -i Package.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 3: We want to import all Workflow entities (entities, entityMemberships, entityUserTypeAssignments, entityUsers) into a security database called SECURITYDB located at DBSERVER server from a ZIP file called Package.zip:

sequel-security import -a WF -d entities -ieu -i Package.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 4: We want to import Security authentication into a security database called SECURITYDB located at DBSERVER server from a ZIP file called Package.zip. The values of tokens such as ClientSecretHash, SecurityApiUrlExternal, SecurityAdminUrlExternal and SecurityAuthorizationUrl will be replaced in the package before being imported:

sequel-security import -a Sec -d authentication -i Package.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -tr "{__ClientSecretHash__}{kx7uWQCFrSHtUXMs0jfZ83Ljf15QyxA6hLPKtFX1I08=}" -tr "{__SecurityApiUrlExternal__}{https://FVMSEC256333.OFFICE.SBS/SecurityApi" -tr "{__SecurityAdminUrlExternal__}{https://FVMSEC256333.OFFICE.SBS/Administration" -tr "{__SecurityAuthorizationUrl__}{https://FVMSEC256333.OFFICE.SBS/Authorization}"

Example 5: We want to import Security authentication into a security database called SECURITYDB located at DBSERVER server from a ZIP file called Package.zip. Each line of the TEXT file called tokens.txt contains the values of tokens that will be replaced in the package before being imported:

sequel-security import -a Sec -d authentication -i Package.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -trf tokens.txt

where a tokens.txt file sample is the following:

{__ClientSecretHash__}{kx7uWQCFrSHtUXMs0jfZ83Ljf15QyxA6hLPKtFX1I08=}  
{__SecurityApiUrlExternal__}{https://littensec.office.sbs/SecurityApi}  
{__SecurityAdminUrlExternal__}{https://littensec.office.sbs/Administration}  
{__SecurityAuthorizationUrl__}{https://littenauth.office.sbs/Authorization}

Example 6: We want to import Security authentication into a security database called SECURITYDB located at DBSERVER server from a ZIP file called Package.zip. At the same time we want to generate a traced package in a ZIP file called TracedPackage.zip with updated entities and replaced tokens.

sequel-security import -a Sec -d authentication -i Package.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -trf tokens.txt -tp TracedPackage.zip

Export¶

Export data from the new Sequel Security Service.

Required parameters:

    -c|--connection     /* Connection string to the source Security database. */
    -o|--output         /* Output path (folder or zip file) where the package will be exported. */

Optional parameters:

    -d|--domain                 /* Domain to be exported (all by default). Expected values (multiple allowed): authorization | authentication | users | entities | configuration */
    -ieu|--includeEntityUsers   /* Valid when domain is equal to 'entities'. Export entities including EntityUsers */
    -a|--application            /* Valid when domain is equal to [authentication | authorization]. Application key used to filter data to be imported. If not specified then data for all the applications in the input will be exported. */
    -v|--verbose                /* Activates verbose mode */
    -s|--silence                /* Execute tool in silence mode for executing in background tasks */

Example 1: We want to export all application with all entities from a security database called SECURITYDB located at DBSERVER server and store it in a ZIP file called AllApplicationsExport.zip:

sequel-security export -o AllApplicationsExport.zip -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -ieu

Example 2: We want to export only Workflow and Security entities related to authentication from a database called SECURITYDB located at DBSERVER server and store it in a folder called WF_Sec_AuthenticationExport:

sequel-security export -a WF -a Sec -d authentication -o WF_Sec_AuthenticationExport -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 3: We want to export only Workflow and Security entities (entities, entityMemberships, entityUserTypeAssignments) without entityUsers from a database called SECURITYDB located at DBSERVER server and store it in a folder called WF_Sec_AuthenticationExport:

sequel-security export -a WF -a Sec -d entities -o WF_Sec_AuthenticationExport -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Add-admin-user¶

add-admin-user command adds an administrator user to the new Sequel Security Service and assign the membership: SEC.CONFIGURATOR (Role) / SEC.PUBLIC (Group). If the user already exists, then the password, email and membership are updated.

Required parameters:

    -n|--name           /* Admin user username.*/
    -m|--mail           /* Admin user email address. */
    -p|--password       /* Admin user password. */
    -c|--connection     /* Connection string to the target Security database. */

Optional parameters:

    -s|--silence        /* Activate silent mode */

Example: We want to add the admin user named ADMIN_NAME whose email address is ADMIN_MAIL and the password is ADMIN_PASSWORD into a security database called SECURITYDB located at DBSERVER server:

sequel-security add-admin-user -n ADMIN_NAME -m ADMIN_MAIL -p ADMIN_PASSWORD -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Sync¶

sync command synchronizes a legacy security database with a Security database.

Required parameters:

    -c|--connection            /* Connection string to the source Security database. */
    -lc|--legacyconnection     /* Connection string to the destination database with the legacy [security] schema. */

Optional parameters:

    -d|--domain                /* Domain to be synchronized (authorization and users by default). Expected value: authorization | users*/
    -dp|--deletepolicy         /* Delete policy applied during synchronization process (PhysicalThenLogical by default). Expected value: Logical | Physical | PhysicalThenLogical
    -v|--verbose               /* Activates verbose mode */
    -s|--silence               /* Execute tool in silence mode for executing in background tasks */

Example 1: We want to synchronize all applications with their authorization entities and users from a security database called SECURITYDB located at DBSERVER server into a legacy security database LEGACYSECURITYDB located and DBSERVER:

sequel-security sync -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -lc "Server=DBSERVER;Database=LEGACYSECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 2: We want to synchronize all users from a security database called SECURITYDB located at DBSERVER server into a legacy security database LEGACYSECURITYDB located and DBSERVER using Logical deletion:

sequel-security sync -d users -c "Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -lc "Server=DBSERVER;Database=LEGACYSECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -dp Logical

Migrate¶

migrate from legacy systems (Claims and Origin) to the new Sequel Security Service, so data migration packages can be generated with the tool and imported later into the new service.

Required parameters:

    -c|--connection     /* Connection string to the database with the legacy [security] schema to be migrated. */
    -o|--output         /* Output path (folder or zip file) where the migration package will be generated. */

Optional parameters:

    -a|--application    /* Application key to choose from WF, Sec, Origin, PB, CLM (by default all of them) to be included in the migration package. (multiple allowed)*/
    -d|--domain         /* Domain to be exported (all by default). Expected values (multiple allowed): authorization | users */
    -val|--validate     /* Validates package before creates output file. */
    -v|--verbose        /* Activates verbose mode */

Example 1: We want to migrate all Workflow entities from a legacy database called LEGACYDB located at DBSERVER server and store it in a ZIP file called WorkflowMigration.zip:

sequel-security migrate -a WF -o WorkflowMigration.zip -c "Server=DBSERVER;Database=LEGACYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 2: We want to migrate only Workflow entities related to users from a legacy database called LEGACYDB located at DBSERVER server and store it in a folder called WorkflowMigrationOnlyUsers:

sequel-security migrate -a WF -d users -o WorkflowMigrationOnlyUsers -c "Server=DBSERVER;Database=LEGACYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Migrate-validate¶

migrate-validate the compatibility from legacy systems to the new Sequel Security Service, and generates a report with the issues detected.

Required parameters:

    -c|--connection     /* Connection string to the database with the legacy [security] schema to be validated. */

Optional parameters:

    -r|--references     /* JSON filename with references to the legacy database columns of other applications (WF, PB, Origin, CLM) with security data which will be validated too. */
    -v|--verbose        /* Activates verbose mode */
    -s|--silence        /* Activate silent mode */

JSON file format is described in below sample:

{
  "Groups": [
    {
      "DbSchema": "workflow",
      "DbTable": "DiaryAction",
      "DbColumn": "assignedGroupId"
    },
    {
      "DbSchema": "workflow",
      "DbTable": "DiaryActionAcls",
      "DbColumn": "GroupId"
    }
  ],
  "Roles": [],
  "Securables": [
    {
      "DbSchema": "workflow",
      "DbTable": "DiaryActionType",
      "DbColumn": "securableId"
    }
  ],
  "Users": [
    {
      "DbSchema": "workflow",
      "DbTable": "Alert",
      "DbColumn": "userId"
    }
  ],
  "UserTypes": []
}

Example 1: We want to validate the users and authorization entities of all applications from a legacy database called LEGACYDB located at DBSERVER server:

sequel-security migrate-validate -c "Server=DBSERVER;Database=LEGACYDB;Trusted_Connection=True;MultipleActiveResultSets=true"

Example 2: We want to validate the users and authorization entities of all applications plus a list of columns with security data defined in a JSON filename called SECURITY_COLUMNS from a legacy database called LEGACYDB located at DBSERVER server:

sequel-security migrate -c "Server=DBSERVER;Database=LEGACYDB;Trusted_Connection=True;MultipleActiveResultSets=true" -r "SECURITY_COLUMNS"

repoint-client¶

repoint-client repoints one or more Clients from old URIs to new URIs after a database restore or URL changes in a environment. The match for old URIs is done from the start of them (https://example.com). The CORS Origins will also be replaced if the Origins does not contain a path but the old URI contains one (Origins should not have a path in them).

Arguments:

  CURRENT_VALUE  Start value from URIs that are going to be replaced
  NEW_VALUE      Start value from URIs that will point to after repoint

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database
  -f|--fail                            [OPTIONAL] If set, the command will fail if no clients are repointed

Example: You have restored a database from a client and to make it work in a support environment, these URIs must be repointed. The client has all products under the URI https://client.example.com, and now they will point to https://secclientsuppenv.office.sbs:

sequel-security repoint-client -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true' 'https://client.example.com' 'https://secclientsuppenv.office.sbs'

normalize-cors¶

normalize-cors normalizes CORS origins stored in database to match the pattern: protocol://host:port. Invalid and duplicated values will be removed.

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example:

sequel-security normalize-cors -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

Add-config-setting¶

add-config-setting command adds (or update if exists a setting with same key) a new configuration setting.

Arguments:

  KEY       Configuration setting key
  VALUE     Configuration setting value
  CATEGORY  [OPTIONAL] Configuration setting category

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: Add/Update subject for forgot password email:

sequel-security add-config-setting 'ForgotPasswordEmailSubjectTemplate' 'You have requested a password reset!' 'ForgotPasswordEmailTemplate' -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

Idsrv-license¶

idsrv-license show a set of commands for managing Identity Server licenses.

idsrv-license <COMMAND>

add-license¶

add-license command adds (or update if exists a license with same key) an Identity Server license.

Arguments:

  KEY       License key
  VALUE     License value
  PRIORITY  [OPTIONAL] Priority for the license if several licenses are valid for the   same interval of time

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: Add/Update license:

sequel-security idsrv-license add-license 'licenseKey' 'LicenseValueJWT' -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

del-license¶

del-license command deletes an Identity Server license.

Arguments:

  KEY       License key

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: Delete MyLicenseKey license:

sequel-security idsrv-license del-license 'MyLicenseKey' -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

list-license¶

list-license command list available Identity Server licenses.

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: :

sequel-security idsrv-license list-license -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

get-license-ring¶

get-license-ring Gets the Identity Server licenses ring for current configuration.

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: :

sequel-security idsrv-license get-license-ring -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

get-license-info¶

get-license-info Gets the Identity Server info info for current configuration.

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: :

sequel-security idsrv-license get-license-info -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

get-license-settings¶

get-license-setting Gets the Identity Server license settings.

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: :

sequel-security idsrv-license get-license-settings -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

set-license-settings¶

set-license-license command sets Identity Server license settings.

Arguments:

  VALIDATION_MODE       License validation mode.                                      Allowed values are: Complete, PayloadOnly
  REMAINING_DAYS        Number of days before show license expiration warning

Validation mode must be set to Complete. Payload Only mode is only for testing.

Options:

  -c|--connection <CONNECTION_STRING>  [REQUIRED] Connection string to the source Security database

Example: Sets Identity Server license settings with Complete validation mode and remaining days to 30:

sequel-security idsrv-license set-license-settings Complete 30 -c 'Server=DBSERVER;Database=SECURITYDB;Trusted_Connection=True;MultipleActiveResultSets=true'

multitenancy¶

multitenancy subcommands serves to operate the tenants configuration with ease from the tool.

configure¶

Usage: sequel-security multitenancy configure [options]

Options:
  -c|--connection <CONNECTION_STRING>                         [REQUIRED] Connection String to the Multitenancy database.

  -tk|--tenant-key <TENANT_KEY>                               [OPTIONAL] The key of the tenant (by default is
                                                              "Default").
                                                              Default value is: Default.
  -tn|--tenant-name <TENANT_NAME>                             [OPTIONAL] The name of the tenant (defaults to the key).
  -tcs|--tenant-connection-string <TENANT_CONNECTION_STRING>  [REQUIRED] The connection string of the tenant.
  -h|--help                                                   Show help information.

Configures a tenant with a name and connection string. It can also be used to configure the default tenant but just providing the tenant connection string.