Group Management

Cinchy Groups

Cinchy Groups are containers that contain Users and other Groups within them as members, and used to provision access controls throughout the platform. Cinchy Groups enable centralized administration for access controls.

Groups are defined in the "Groups" table within the Cinchy domain. By default this table can only be managed by members of the Cinchy Administrators group. Each group has the following attributes:

• Name - Group name, this must be unique across all groups within the system

• Users - Users which are members of the group

• User Groups - Groups which are members of the group

• Owners - Users which are able to administer the membership of this group. By default Owners are also members of the group (i.e. they do not need to also be added into Users).

• Owner Groups - Groups whose members are able to administer the membership of this group. By default, members of Owner Groups are also members of the group (i.e. they do not need to also be added into Users or User Groups).

• Group Type - Either "Cinchy Group" or "AD Group". If this is a "Cinchy Group", this means that membership is maintained directly in Cinchy. If this is an "AD Group", then a sync process will be leveraged to maintain the membership and overwrite the Users.

How to define a new AD Group

1. Create a new record within the Groups Table with the same name as the AD Group (use the cn attribute).

2. Set the Group Type = "AD Group".

How to convert an existing group to sync with AD

1. Update the Name attribute of the existing group record to match the AD Group (use the cn attribute).

2. Set the Group Type to "AD Group".

Group Membership Sync

AD Groups defined in Cinchy have their members sync'ed from AD through a batch process that leverages the Cinchy Command-line-interface (CLI).

Execution Flow

The sync operation performs the following high-level steps:

1. Fetches all Cinchy registered AD Groups using a Saved Query.

2. Retrieves the usernames of all members for each AD Group. The default attribute for username that is retrieved is "userPrincipalName", but configurable as part of the sync process.

3. For each AD Group, loads the users that are both a member in AD and exist in the Cinchy Users table (matched on the Username) into the "Users" attribute of the Cinchy Groups table.

Dependencies

1. Cinchy CLI Model must be installed in your instance of Cinchy, steps are mentioned here

2. An instance of the Cinchy CLI must be available to execute the sync

3. A task scheduler is required to perform the sync on a regular basis (e.g. Autosys)

Configuration Steps

Create a Saved Query to retrieve AD Groups from Cinchy

Create a new query within Cinchy with the below CQL to fetch all AD Groups from the Groups table. The domain & name assigned to the query will be referenced in the subsequent step.

SELECT [Name]
FROM [Cinchy].[Cinchy].[Groups]
WHERE [Group Type] = 'AD Group' 
AND [Deleted] IS NULL

Create the Sync Config

Copy the below XML into a text editor of your choice and update the attributes listed in the table below the XML to align to your environment specific settings. Once complete, create an entry with the config in your Data Sync Configurations table (part of the Cinchy CLI model).

<?xml version="1.0" encoding="utf-16" ?>
<BatchDataSyncConfig name="AD_GROUP_SYNC" version="1.0.0" xmlns="http://www.cinchy.co">
  <Parameters />
  <LDAPDataSource objectCategory="group" ldapserver="LDAP:\\activedirectoryserver.domain.com" username="encryptedUsername" password="encryptedPassword" >
    <Schema>
      <Column name="cn" ordinal="1" dataType="Text" maxLength="5000" isMandatory="false" validateData="false" trimWhitespace="true" description=""/>
      <Column name="member.userPrincipalName" ordinal="2" dataType="Text" maxLength="200" isMandatory="false" validateData="false" trimWhitespace="true" description=""/>
    </Schema>
    <Filter>
      lookup('Domain Name','Query Name')
    </Filter>
  </LDAPDataSource>
  <CinchyTableTarget model="" domain="Cinchy" table="Groups" suppressDuplicateErrors="false">
    <ColumnMappings>
      <ColumnMapping sourceColumn="cn" targetColumn="name" />
      <ColumnMapping sourceColumn="member.userPrincipalName" targetColumn="Users" linkColumn="Username" />
    </ColumnMappings>
    <SyncKey>
      <SyncKeyColumnReference name="name" />
    </SyncKey>
	<ChangedRecordBehaviour type="UPDATE" />
    <DroppedRecordBehaviour type="IGNORE" />
  </CinchyTableTarget>
</BatchDataSyncConfig>

Sync Execution & Scheduling

The below CLI command (see here for additional information on the syncdata command) should be used to execute the sync. Update the command parameters (described in the table below) with your environment specific settings. Execution of this command can be scheduled at your desired frequency using your scheduler of choice.

dotnet Cinchy.CLI.dll syncdata -s cinchyAppServer -u username -p "encryptedPassword" -m "Model" -f "AD_GROUP_SYNC" -d "TempDirectory" 

References

• Cinchy CLI Reference

• Syncdata Command

Overview

Cinchy supports integration with any Identity Provider that issues SAML tokens (e.g. Active Directory Federation Services) for authenticating users. It follows an SP Initiated SSO pattern where the SP will Redirect to the IdP and the IdP must submit the SAML Response via an HTTP Post to the SP Assertion Consumer Service. Below is a diagram outlining the flow when a non-authenticated user attempt to access a Cinchy resource.

Configure SAML Authentication

Cinchy must be registered with the Identity Provider. As part of that process you'll supply the Assertion Consumer Service URL, choose a client identifier for the Cinchy application, and generate a metadata XML file.

The Assertion Consumer Service URL for Cinchy is the base URL for the CinchySSO application followed by "/{AcsURLModule}/Acs" e.g. https://myCinchyServer/CinchySSO/identity/AuthServices/Acs. {AcsURLModule} value needs to be defined in appsettings.json file

To enable SAML authentication within Cinchy, follow the below steps:

1. You can find the necessary metadata XML from the applicable identity provider you're using the login against. Place the metadata file in the deployment directory of the CinchySSO web application.

2. Update the value of the below app settings in the CinchySSO appsettings.json file.

• SAMLClientEntityId - The client identifier chosen when registering with the Identity Provider

• SAMLIDPEntityId - The entityID from the Identity Provider metadata XML

• SAMLMetadataXmlPath - The full path to the metadata XML file

• AcsURLModule - This parameter is needs to be configured as per your SAML ACS URL.

  • Example ACS URL: "https:///CinchySSO/identity/AuthServices/Acs"

  • Example parameter value: "/identity/AuthServices"

3. The following parameters pertain to signed SAML IdP requests. Within the IDPSSODescriptor tag of the metadata is the below WantAuthnRequestSigned attribute:

<IDPSSODescriptor WantAuthnRequestsSigned="true" protocolSupportEnumeration="urn:oasis:names:tc:SAML:2.0:protocol">

If the value for this attribute is set to "true" then your Identity Provider is expecting the request to be "Signed". In this case, please enter the following parameters:

• SAMLSignCertificatePath - This parameter needs to be the path for the PFX file of the certificate. This must match the same certificate in your identity provider.

• SAMLSignCertificatePassword - This parameter is the password for the above mentioned PFX file. You may choose to encrypt it or not.

• SAMLSignCertificateMinAlgorithm - This parameter is optional and only needed for PFX files that are generated at different algorithm levels.

  • The possible options for this parameter are:

    • SHA1

    • SHA256

    • SHA384

    • SHA512

    • http://www.w3.org/2000/09/xmldsig#rsa-sha1

    • http://www.w3.org/2000/09/xmldsig#rsa-sha256

    • http://www.w3.org/2000/09/xmldsig#rsa-sha384

    • http://www.w3.org/2000/09/xmldsig#rsa-sha512

4. If the Identity Provider is configured for the request to be encrypted please provide a PFX file, with a non-empty password, for the below attributes:

• SAMLEncryptedCertificatePath - This parameter needs to be the path for the PFX file of the certificate. This must match the same certificate in your identity provider.

• SAMLEncryptedCertificatePassword - This parameter is the password for the above mentioned PFX file.

Once SSO is enabled, the next time a user arrives at the Cinchy login screen they will see an additional button "Single Sign-On". Before a user is able to login through the SSO flow, the user must be set up in Cinchy with the appropriate authentication configuration. See the User Management section below for instructions on how to perform this setup.

User Management

Users in Cinchy are maintained within the Users table in the Cinchy domain. Each user in the system is configured with 1 of 3 Authentication Methods:

• Cinchy User Account - These are users that are created and managed directly in the Cinchy application. They log into Cinchy by entering their username and password on the login screen.

• Non Interactive - These accounts are intended for application use.

• Single Sign-On - These users authenticate through the SSO Identity Provider (configured using the steps above). They log into Cinchy by clicking the "Login with Single Sign-On" link on the login screen.

How to define a new SSO User

Create a new record within the Users table with the Authentication Method set to "Single Sign-On".

How to convert an existing user to SSO User

Change the Authentication Method of the existing user to "Single Sign-On".

Logging in with SSO

Once a user is configured for SSO, they can then click the "Login with Single Sign-On" link on the login page and that will then take them through the Identity Provider's authentication flow and bring them into Cinchy.

Automatic User Creation

Once SSO has been enabled on your instance of Cinchy, by default, any user that does not exist in the Cinchy Users table will not be able to login regardless if they are authenticated by the Identity Provider.

Enabling Automatic User Creation means that upon login, if the Identity Provider authorizes the user, an entry for this user will automatically be created in the Cinchy Users table if one does not already exist. This means that any SSO authenticated user is guaranteed to be able to access the platform.

In addition to creating a user record, if AD Groups are configured within Cinchy, then the authenticated user will automatically be added to any Cinchy mapped AD Groups where they are a member. See AD Group Integration for additional information on how to define AD Groups in Cinchy.

See below for details on how to enable Automatic User Creation.

Pre-requisites

The Identity Provider configuration must include the following claims in addition to the base configuration in the SAML token response:

• First Name

• Last Name

• Email

In order to enable automatic group assignment for newly created users (applicable if you plan on using AD Groups), then also include an attribute that captures the groups that this user is a member of (e.g. memberOf field in AD)

Config setup

Enabling automatic user creation requires the following changes to the appsettings.json file in the CinchySSO web application.

• Add "ExternalClaimName" attribute values under "ExternalIdentityClaimSection" in appsettings.json file. Do not add the value for "MemberOf" if you don't want to enable automatic group assignment .

• The ExternalClaimName value must be updated to create a mapping between the attribute name in the SAML response and the required field. (e.g. http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname is the name in the SAML response for the FirstName field)

"ExternalIdentityClaimSection": {
			"FirstName": {
				"ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname"
			},
			"LastName": {
				"ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname"
			},
			"Email": {
				"ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress"
			},
			"MemberOf": {
				"ExternalClaimName": "http://schemas.microsoft.com/ws/2008/06/identity/claims/role"
			}
		}

ADFS Configuration

On your ADFS Server, Open AD FS Management.

Righ-click on Relying Party Trusts and select Add Relying Party Trust. This will launch the Add Relying Party Trust Wizard.

ADFS Wizard

Welcome

Select Claims aware. Click Start.

Select Data Source

Choose Enter data about the relying party manually. Click Next.

Specify Display Name

Enter a Display Name of your choice.

Configure Certificate

Do not choose any certificates.

Configure URL

Select Enable support for the SAML 2.0 SSO Web SSO protocol.

Enter your login URL in the following format:

https://<cinchy-sso-URL>/Saml2/Acs

Configure Identifiers, Choose Access Control Policy, Ready to Add Trust, Finish

Choose an Identifier and click Next until you are complete.

Claim Issuance Policy

1. Right-click on the Relying Party Trust you just created (look for the Display Name) and click Edit Claim Issuance Policy.

2. Click on the Add Rule... and choose Claim Rule as Send LDAP Attributes as Claims.

3. Add Claim rule name, choose Active Directory under Attribute store and map LDAP attribute to outgoing claim types:

4. Click Finish.

5. Click on Edit Rule...

6. Click on View Rule Language and copy out the Claim URLs for the claims defined. This information will be needed in a later step to configure your Cinchy appsettings.json. This will look something like this:

c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname", Issuer == "AD AUTHORITY"]
  => issue(store = "Active Directory",
           types = ("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier",
                    "sub",
                    "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname",
                    "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname",
                    "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress",
                    "http://schemas.microsoft.com/ws/2008/06/identity/claims/role"),
           query = ";userPrincipalName,sAMAccountName,givenName,sn,mail,memberOf;{0}",
           param = c.Value);

7. Click Ok to save the rule.

8. Right-click on your Relying Party Trust again and click Properties.

9. Go to the Advanced tab and set the secure hash algorithm to SHA-256

Cinchy Configuration

1. Open https://<your.AD.server>/FederationMetadata/2007-06/FederationMetadata.xml in a browser and save the XML file in the cinchysso folder.

2. Open IIS Manager and create an HTTPS binding on the Cinchy site (if necessary).

3. Go to sso site and bind HTTPS with it. Make sure to use the same port as the login URL above if specified.

Cinchy appsettings.json

AppSettings Section

External Identity Claim Section

You will need the Rule Language URLs you copied out from the ADFS Configuration above. Using the same example as above, we would get the following (replace with your own URLs).

{
  "AppSettings": {
    ...
    },
  "ExternalIdentityClaimSection": {
    "FirstName": {
      "ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname"
    },
    "LastName": {
      "ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/surname"
    },
    "Email": {
      "ExternalClaimName": "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress"
    },
    "MemberOf": {
      "ExternalClaimName": "http://schemas.microsoft.com/ws/2008/06/identity/claims/role"
    }
  }
}

Web.config

Add the 3 following lines to your web.config within the appSettings section:

<appSettings>
  ...
  <add key="UseHttps" value="true" />
  <add key="StsAuthorityUri" value="https://<your.cinchy.url>" />
  <add key="StsRedirectUri" value="https://<your.cinchysso.url>/Account/LoginRedirect" />
  ...
</appSettings>

If you are syncing someone with a lot of ADFS groups, the server may reject the request for the header being too large. If you are able to login as a user with a few groups in ADFS but run into an error with users with a lot of ADFS groups (regardless of if those ADFS groups are in Cinchy), you will need to make 2 changes.

Server Max Request Header Size

CinchySSO App Settings

In your CinchySSO app settings, you will also need to increase the max size of the request header.

    "AppSettings": {
      ...
      "MaxRequestHeadersTotalSize": {max size in bytes},
      "MaxRequestRequestBufferSize": {max size in bytes, use same as above},
      "MaxRequestBodySize": -1
    }

For more details on the app settings see the app settings section of Configuring ADFS.

Rather than traditional code-centric applications which creates data silos, you can build application experiences on the Cinchy platform which looks and feels like regular applications, but persists its data on the data fabric autonomously, rather than managing its own persistence.

Once you deploy your UI, API and logic, you will need to create an integrated client to leverage the data fabric for persistence and controls. If you would like a link to your experience from the data fabric, you will need to create an Experience in the Applets table. See to see how to set up both.

The Cinchy Platform also comes with a built-in Experience called My Data Network, this is a tool to help you visualize your data through its connections. You can read more about, or create your own on your own data.

Pre-Deployment Steps

• As "Integrated Apps" table will get modified to "Applets" table after 2.0 upgrade, please save "Integrated Apps" table data by running a select statement on this table. This data will be required to populate new table named "Integrated Clients" after deployment.

• Please run below Update statement for updating "Integrated App" to "Applet" under "Launcher Objects" table

• Install .net core Hosting bundle Version 2.1 -

• Install .Net Framework 4.7.2 on the server

• Create a new application pool with configuration as "No managed Code" and "Integrated" in IIS manager.

• Take backup - Cinchy DB, Cinchy Web and Cinchy SSO

Deployment

• Deploy binaries

• Assign newly created application pool in Pre-Deployment steps to the Cinchy SSO application

• Configure appsettings.json file under Cinchy SSO as described below, most of these configurations can be taken from the web.config of previous version of Cinchy SSO

Below configurations are only required for External login authentication, otherwise can be left as blank

• SAMLSSOServiceURL - Configure service endpoint for SAML authentication.

• AcsURLModule - This parameter is needs to be configured as per your SAML ACS URL. For example, if your ACS URL looks like this - "https:///CinchySSO/identity/AuthServices/Acs", then the value of this parameter should be "/identity/AuthServices"

• In Cinchy SSO, Log folder will required to be configured under log4net.config and web.config files. Please make sure that Identity under which application pool is running must have access to logs and certificate folder as configured.

• In Cinchy Web application web.config file, modify "StsAuthorityUri" parameter to remove "identity" keyword from the URL.

  • URL will modify from "https://<your server URL>/SiteName/CinchySSO/identity" to "https://<your server URL>/sitename/cinchysso"

  • Please make sure the sitename and cinchysso is in lower case

Post Deployment Steps

• Login to Cinchy and follow these below steps to update Launcher Objects table-

  • Go to Design Table

  • Update "Integrated App" link column name to "Applet".

  • Update "Type" choice column's choice from "Integrated App" to "Applet"

• "Integrated Clients" table would be required to be populated from the data taken from "Integrated Apps" table in pre-deployment steps as shown in below table

• Please check off Query permissions under Design controls of "Users" table for "All Users" row.

• Upload your organization logo on Admin screen - https://<your server URL>/Cinchy/Admin/Index

1. Create a Database

On your SQL Server 2012+ instance, create a new database named Cinchy (or any other name you prefer). If you choose an alternate name, in the remaining instructions wherever the database name is referenced, replace the word Cinchy with the name you chose.

A single user account with db_owner privileges is required for the Cinchy application to connect to the database. If you choose to use Windows Authentication instead of SQL Server Authentication, the account that is granted access must be the same account under which the IIS Application Pool runs.

2. Create an IIS Application Pool

On the Windows Server machine, launch an instance of PowerShell as Administrator.

Run the below commands to create the application pool and set its properties.

If you chose to use Windows Authentication in the database or want to run the application under a different user account, execute the below commands to change the application pool identity.

3. Create the Application Directories

1. Unzip the application package on your C drive. This will create 2 directories, C:\Cinchy and C:\CinchySSO. Ensure your application pool accounts has read and execute access to these directories (default accounts are IIS AppPool\CinchyWeb and IIS AppPool\CinchySSO).

2. Run the below commands in the Administrator instance of PowerShell to create directories for the application logs. Ensure your application pool account has write access to these directories.

4. Update the CinchySSO appsettings.json

Open the C:\CinchySSO\appsettings.json file in a text editor and update the values below.

Config Settings

App Settings

Under AppSettings section, update the values outlined in the table. Wherever you see <base url> in the value, replace this with the actual protocol (i.e. http or https) and the domain name (or ip address) you plan to use. e.g. if you're using https with the domain app.cinchy.co, <base url> should be replaced with https://app.cinchy.co

Below values are only required for SSO, otherwise leave them as blank

Connection String

Under the "ConnectionStrings" section you'll see

The "SqlServer" value needs to be set for the application to connect to the database. If you're using SQL Server Authentication you can use the below as a reference and update the Server, User Id, and Password properties. If you chose a different database name earlier, you'll need to update that as well.

If you're using Windows Authentication, then use the below as a reference and update the Server property (and Database if required).

External Identity Claim Section

Under the "ExternalIdentityClaimSection" section you'll see, these values are used for SAML SSO. If you are not using SSO, keep these values as blank

Log4net.config and Web.config

The log folder is required to be configured under log4net.config and web.config files. Please make sure the identity under which the application pool is running has access to the log and certificate folders as configured.

Under the log4net.config, you'll see a RollingLogFileAppender section, and within that you need to update the value of <file> tag as below

Under web.config, update "stdoutLogFile" value to "C:\CinchyLogs\CinchySSO\stdout" under "aspNetCore" tag. Also, update the value of "ASPNETCORE_ENVIRONMENT" to "Production".

5. Update the Cinchy Web.Config

Open the C:\Cinchy\Web.config file in a text editor and update the sections outlined below.

Connection String

Under the <connectionStrings> section you'll see

Replace this with the same connection string value you set in the C:\CinchySSO\appsettings.json file.

Application Settings

Under the <appSettings> section, update the values outlined in the table. Wherever you see <base url> in the value, replace this with the actual protocol (i.e. http or https) and the domain name (or ip address) you plan to use. e.g. if you're using https with the domain app.cinchy.co, <base url> should be replaced with https://app.cinchy.co

Log File Path

Under the <log4net> section you'll see a RollingLogFileAppender, and within that is the following line

Replace the value attribute with the target log file location:

Under the <elmah> section you'll see

Replace the logPath attribute with the target error log location:

6. Create the IIS Applications

In the Administrator instance of PowerShell, execute the below commands to create the IIS applications and enable anonymous authentication (required to allow authentication to be handled by the application).

7. Test the Application

Access the <base url>/Cinchy (e.g. http://app.cinchy.co/Cinchy) through Google Chrome. The login screen should appear. The default username is admin and the password is cinchy. You will be prompted to change your password the first time you log in.

Cinchy performs maintenance tasks through the CLI. This currently includes the data erasure and data compression deletions.

Legacy Data Network Visualizer Applet

The legacy standalone applet is no longer supported. For instructions on the standalone applet version, see Setup Data Network Visualizer in v2.2.0.

My Data Network

The Data Network Visualizer now ships with Cinchy as a system applet called My Data Network. It uses the user's entitlements for viewable tables and linked columns. You will find the My Data Network data experience in the Marketplace:

My Data Network is another way to view and navigate the data you have access to within Cinchy.

Each node represents a table you have access to within Cinchy, and each edge is one link between two tables. The size of the table is determined by the number of links referencing that table. The timeline on the bottom allows you to check out your data network at a point in the past and look at the evolution of your network.

When you click on a node, you will see its description in the top right hand corner. You can click the Open button to navigate to the table.

Custom Data Network Visualizer

If you want to create your own custom data network visualizer, see Custom Data Network Visualizer

Integrated Client Setup

Applet Setup

Nodes

The nodes query defines the nodes in the network.

Edges

The edges query defines the relationships between the nodes.

Node Groups

Node groups are an optional query you can provide to group your nodes.

Timeline

If no start or end date is specified, the data network is just shown as is. If there's a start or end date, the other CQLs need to have a @date parameter and that will be used to render the data network at a point in time.

You can use @date between [Modified] and [Replaced] with a version history query to see data at a point in time. You can also simply use @date > [Created] if it's an additive system.

Timeline Start Date

This CQL should return a date value as 'startDate'.

Timeline End Date

This CQL should return a date value as 'endDate'.

Slicers

To use slicers, you need to define the slicers in the [Slicers] column and add the additional attributes to the nodes query.

[
    {
        "attribute": "slice1",
        "displayName": "First Slicer"
    },
    {
        "attribute": "slice2",
        "displayName": "Second Slicer"
    }
]

Attribute is the column name from the nodes query, displayName is what shows up in the visualizer.

System Tables

All the information above is entered into the [Cinchy].[Networks] table. To access the network, go to

<Cinchy URL>/Cinchy/apps/datanetworkvisualizer?network=<NAME>

Alternatively you can go to My Data Network and then add ?network=<NAME> to the end of it.

It is highly recommended to add a new applet for each custom data network visualizer for ease of access.

Example Network

For ease of testing, save the following as saved queries and then in the Networks table simply add exec [Domain].[Saved Query Name] as the CQLs.

Node Groups CQL

-- Sample Data
SELECT
  [Sub Network] = 'Blue Network'
, [Color] = '#03a9f4'
INTO #TEMP
UNION SELECT
  [Sub Network] = 'Green Network'
, [Color] = '#8bc34a'
-- Node Groups CQL
SELECT
  t.[Sub Network] as 'sub network'
, t.[Color] as 'color'
FROM #TEMP t

Nodes CQL

-- Sample Data
SELECT
   [Id] = 'N01'
,  [Title] = 'Hover text on the node.'
,  [Label] = 'Purple Node 1 (Ax)'
,  [Value] = 5
,  [Mass] = 5
-- optional
,  [Group] = 'Green Network'
,  [Color] = '#ab47bc'
,  [Description] = 'This description shows up in the top right hand corner when you select a node.'
,  [Node URL] = 'https://www.cinchy.com'
,  [Slicer 1] = 'A'
,  [Slicer 2] = 'x'
INTO #TEMP
UNION SELECT
   [Id] = 'N02'
,  [Title] = 'Hover text on node 2.'
,  [Label] = 'Node 2 (Ay)'
,  [Value] = 10
,  [Mass] = 10
-- optional
,  [Group] = 'Green Network'
,  [Color] = ''
,  [Description] = 'This description shows up in the top right hand corner when you select a node.'
,  [Node URL] = ''
,  [Slicer 1] = 'A'
,  [Slicer 2] = 'y'
UNION SELECT
   [Id] = 'N03'
,  [Title] = 'Hover text on node 3.'
,  [Label] = 'Node 3 (Bx)'
,  [Value] = 1
,  [Mass] = 1
-- optional
,  [Group] = 'Blue Network'
,  [Color] = ''
,  [Description] = ''
,  [Node URL] = ''
,  [Slicer 1] = 'B'
,  [Slicer 2] = 'x'
UNION SELECT
   [Id] = 'N04'
,  [Title] = 'Hover text on node 4.'
,  [Label] = 'Minimum Node 4 (y)'
,  [Value] = 3
,  [Mass] = 3
-- optional
,  [Group] = ''
,  [Color] = ''
,  [Description] = ''
,  [Node URL] = ''
,  [Slicer 1] = ''
,  [Slicer 2] = 'y'
UNION SELECT
   [Id] = 'N05'
,  [Title] = 'Hover text on node 4.'
,  [Label] = 'Orphan Node 5 (By)'
,  [Value] = 2
,  [Mass] = 2
-- optional
,  [Group] = ''
,  [Color] = ''
,  [Description] = ''
,  [Node URL] = ''
,  [Slicer 1] = 'B'
,  [Slicer 2] = 'y'
-- Nodes CQL
SELECT
   t.[Id] as 'id'
,  t.[Title] as 'title'
,  t.[Label] as 'label'
,  t.[Value] as 'value'
,  t.[Mass] as 'mass'
,  t.[Group] as 'group'
,  t.[Color] as 'color'
,  t.[Description] as 'description'
,  t.[Node URL] as 'nodeURL'
,  t.[Slicer 1] as 'slice1'
,  t.[Slicer 2] as 'slice2'
FROM #TEMP t

Edges CQL

-- Sample Data
SELECT
  [Id] = 'E01'
, [Label] = 'Node 1 to Node 2, Double Arrows'
, [From] = 'N01'
, [To] = 'N02'
-- optional
, [Show Arrow To] = 'True'
, [Show Arrow From] = 'True'
INTO #TEMP
UNION SELECT
  [Id] = 'E02'
, [Label] = 'Node 2 to Node 3, To Arrow'
, [From] = 'N02'
, [To] = 'N03'
-- optional
, [Show Arrow To] = 'True'
, [Show Arrow From] = 'False'
UNION SELECT
  [Id] = 'E03'
, [Label] = 'Node 1 to Node 4, From Arrow'
, [From] = 'N01'
, [To] = 'N04'
-- optional
, [Show Arrow To] = ''
, [Show Arrow From] = 'True'
UNION SELECT
  [Id] = 'E04'
, [Label] = 'Node 3 to Node 3, No Arrow'
, [From] = 'N03'
, [To] = 'N03'
-- optional
, [Show Arrow To] = ''
, [Show Arrow From] = ''

-- Edges CQL
SELECT
   t.[Id] as 'id'
,  t.[Label] as 'label'
,  t.[From] as 'from'
,  t.[To] as 'to'
,  t.[Show Arrow To] as 'showArrowTo'
,  t.[Show Arrow From] as 'showArrowFrom'
FROM #TEMP t

System Properties is a table within Cinchy for managing system properties, such as default time zones, system lockout durations, password expirations, password properties, password attempts allowed etc.

Set up

The Default of the Systems Properties table is set up as follows:

The System Properties requirements can be changed by an admin user simply by editing the 'Value' columns where applicable:

Default Time Zone

Users can set their own time zones in their user profile. If a user does not set one up, the system default time zone will take effect. If this property does not exist or is invalid, the default time zone will default to UTC.

Minimum Password Length

The minimum password length is 8 characters and it will default to 8 if an invalid value is provided. However, this number can be changed in the 'Value' column to require users to have longer or shorter passwords.

Password Requires Symbols

This property specifies whether symbols are required in a user's password. The 'Value' 0 means symbols are not required and 1 means they are required.

Password Requires Numbers

This property specifies whether numbers are required in a user's password. The 'Value' 0 means numbers are not required and 1 means they are required.

Password Expiration (Days)

This property specifies how many days until a password expires. Defaults to 90 but can be set to be shorter or longer by changing the number in the 'Value' column.

Password Attempts Allowed

This property specifies how many bad password attempts a user can make before they are locked out of the system. The default is 3 but this can be set to be more or less attempts by changing the number in the 'Value' column.

System Lockout Duration (minutes)

This property specifies how long a user is locked out of the system once they've run out of bad password attempts. The default is 15min but this can be set to be shorter or longer by changing the number in the 'Value' column.

Maintenance Enabled

This is a property, defaulted to 0, that is simply responsible for showing this warning when a data owner is setting up Data Erasure or Data Compression on a table. It is the administrator's responsibility to set up a scheduled maintenance job for performing compression and erasure, and then to change the property to 1 so that the warning no longer appears.

Forbidden Passwords

There is a new Cinchy table called Forbidden Passwords. This table comes with a prepopulated list of passwords from https://www.ncsc.gov.uk/static-assets/documents/PwnedPasswordsTop100k.txt

You can add more blocked passwords to this list as well, and users will not be able to set their password to it (this can be used to add your company's name, or to import other blocked password lists). The check against the list is case insensitive.

Like other password policies, this check occurs when your password changes, so to enforce this you will need to set all passwords to be expired.

Cinchy 2.0 has added the feature to encrypt data at rest. This means that you can encrypt data in the database such that users with access to view data in the database will see ciphertext in those columns. However, all users with authorized access to the data via Cinchy will see the data as plain text.

In order to use this feature, your database administrator will be need to create a database master key (see below for instructions).

Create Master Key in Database

Connect directly to the database Cinchy is currently using.

Run the below query to create your master key - password to be used should adhere to your organization's password policy.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'; 

You can now encrypt data via the user interface

Backup Master Key

After you have created your master key you can create a backup file of that key in case any data corruption occurs in future. You will need the password you used to create your master key in order to complete this operation.

BACKUP MASTER KEY TO FILE = 'path_to_file'   
    ENCRYPTION BY PASSWORD = 'password' 

Further documentation.

Restore Master Key

In the use case where you require to restore your master key due to data corruption use the command below to do so. You will need the password you used to create you master key in order to complete this operation.

RESTORE MASTER KEY FROM FILE = 'path_to_file'   
    DECRYPTION BY PASSWORD = 'password'  
    ENCRYPTION BY PASSWORD = 'password'  
    [ FORCE ] 

Further documentation.