Overview:

This document demonstrates how to configure the Database Connector application.

Prerequisite:

  • The Form attributes and their datatype are known to the configurator.
  • TargetSystem service account-specific connection detail  like connection URL, dialect, database userID, password etc
  • Authentication Type
  • There should be a configuration folder present in the same directory where the connector application jar has been deployed/copied

Configuration Directory Structure

/<Deployable Directory>

          /<Deployable Directory>/configurations

                    /<Deployable Directory>/configurations/scim 

                    /<Deployable Directory>/configurations/schemas

Configuration Detail:

Every connector require 2 types of configuration one is scim configuration and schema configuration for a different type of resources.

There is 2 YAML file one for specific to IDHUB connector application and another for splice related configuration.

Resource Configuration:

All resource-specific configuration is kept under directory  /<Deployable Directory>/configurations/schemas . 

There is a file for each type of resource. For example for resource Account, there will be a file Account.json

Each resource file will have a JSON object with the following attribute

id:

id of the resource in a specific format "urn:sath:params:scim:schemas:core:<compatible version number>:<Resource Name>"

name:

Resource name as string i.e Account

description:

Description of the resource as a string

attributes:

An array of JSON Object for different Resource specific attributes of IDHUB system.

Each object will have 3 attributes 

name 

as string

type

as string

required

as boolean

meta

This is a JSON object which has 2 attributes. This doesn't need to change until asked by the Service provider on IDHUB new version release.

resourceType
location

schema

Same as id attribute

matching-attributes

This is a list of attribute names which will be used by splice to determine unique resource in the database table.

i.e.

"matching-attributes": [
"username",
"id"
]

attribute-map

This is a mapping of the IDHUB Resource attribute with the TargetSystem Resource corresponding attribute.

"attribute-map": {
             "<TargetSystem Attribute Name>": {
                                      "type": "string",
                                      "attribute": "<IDHUB Attribute Name>"
                                        },

......

}

Check the sample below.

scripts

This is a JSON object which holds  TargetSystem CRUD operation related scripts if required and the TargetSystem response mapping.

Each attribute in this section is a list of string. So a list of queries can be defined for each attribute.

The query is using the target system/ specific attribute name.


This has the following attributes

insert

List of Insert query as a string. The attribute in the query should be the target system table attribute

e.g.

"insert": ["INSERT INTO user_tbl (id,phone,email,username) VALUES(${id},'${phone}','${email}','${username}') "]
assign

List of insert script for mapping a user account with other table objects.The attribute in the query should be the target system permission/role table attribute.

e.g

"assign": ["INSERT INTO usr_permission_map (username,permission) VALUES('${username}','${entitlements}') "]
update

List of update script as a string.The attribute in the query should be the target system table attribute

e.g.

"update": ["UPDATE user_tbl set phone='${phone}',email='${email}'"]
delete

List of delete script as a string. The attribute in the query should be the target system table attribute.

e.g.

"delete": ["DELETE FROM user_tbl"]
select

List of select/search script as a string. The attribute in the query should be the target system table attribute.

e.g.

"select": ["SELECT * FROM user_tbl"]
mappingToExistingToResponse

This is a list of a JSON objects.

Check sample doc for format and reference for setup

"mappingToExistingToResponse": [
      {
        "id": "${id}",
        "externalId": "${username}",
        "UserLogin": "${username}",
        "DisplayName": "${username}",
        "email": "${email}",
        "PhoneNumber": "${phone}"
      }
    ]
  },
JAVA


configs

This is a JSON object which holds table-specific config for inserting, updating, and searching resource. 

tableName

This is a primary table name which holds the account/user info.

uniqueKey:

This is a unique key attribute in the table to match with IDHUB user/account

primaryKey:

This is a primary key of the table <tableName>. It can be similar to uniqueKey

primaryKeyGenerationType

This is primary generation type. It can be auto or custom. This attribute is in use only when primeryKey!= uniqueKey

If GenareationType is auto then there is no need to pass primary key attribute value explicitly in insert script or SQL query.

If GenareationType is custom then we have to explicitly mention the attribute in the insert script. The connector will create a custom value for primaryKey attribute and insert that value.

Check the sample below for reference. 

"configs": {
    "tableName": "user_tbl",
    "uniqueKey":"username",
    "primaryKey": "id",
    "primaryKeyGenerationType": "custom"
   }
CODE

The Account Resource sample file for the database connector is given below

{
"id": "urn:sath:params:scim:schemas:core:1.0:Account",
"name": "Account",
"description": "User Account",
"attributes": [
{
"name": "DisplayName",
"type": "string",
"required": true
},
{
"name": "UserLogin",
"type": "string",
"required": true
},
{
"name": "PhoneNumber",
"type": "string",
"required": true
},
{
"name": "email",
"type": "string",
"required": true
},
{
"name": "entitlements",
"type": "array",
"required": true
}
],
"meta": {
"resourceType": "Schema",
"location": "/v2/Schemas/urn:sath:params:scim:schemas:core:1.0:Account"
},
"schemas": "urn:sath:params:scim:schemas:core:1.0:Account",
"matching-attributes": [
"username",
"email",
"id"
],
"attribute-map": {
"id": {
"type": "number",
"attribute": "id"
},
"displayName": {
"type": "string",
"attribute": "DisplayName"
},
"username": {
"attribute": "UserLogin",
"type": "string"
},
"email": {
"type": "string",
"query": ".email"
},
"phone": {
"attribute": "PhoneNumber",
"type": "string"
},
"entitlements": {
"attribute": "entitlements",
"type": "array"
}
},
"scripts": {
"insert": ["INSERT INTO user_tbl (id,phone,email,username) VALUES(${id},'${phone}','${email}','${username}') "],
"assign": ["INSERT INTO usr_permission_map (username,permission) VALUES('${username}','${entitlements}') "],
"update": ["UPDATE user_tbl set phone='${phone}',email='${email}'"],
"delete": ["DELETE FROM user_tbl"],
"select": ["SELECT * FROM user_tbl"],
"mappingToExistingToResponse": [
{
"id": "${id}",
"externalId": "${username}",
"UserLogin": "${username}",
"DisplayName": "${username}",
"email": "${email}",
"PhoneNumber": "${phone}"
}
]
},
"configs": {
"tableName": "user_tbl",
"uniqueKey":"username",
"primeryKey": "id",
"primeryKeyGenerationType": "custom"
}
}
JAVA


SCIM Configuration

SCIM configuration is the place to define the scope of what connector can support and some metadata related to the connector. This needs to be updated by the service provider.

The ServiceProfiderConfig.json file looks like this.

{
  "schemas": [
    "urn:ietf:params:scim:schemas:core:2.0:ServiceProviderConfig"
  ],
  "documentationUri": "https://www.sath.com/idhub/documentation",
  "patch": {
    "supported": false
  },
  "bulk": {
    "supported": false,
    "maxOperations": 0,
    "maxPayloadSize": 0
  },
  "filter": {
    "supported": false,
    "maxResults": 0
  },
  "changePassword": {
    "supported": false
  },
  "sort": {
    "supported": false
  },
  "etag": {
    "supported": false
  },
  "authenticationSchemes": [
    {
      "name": "OAuth Bearer Token",
      "description": "Authentication scheme using the OAuth Bearer Token Standard",
      "specUri": "http://www.rfc-editor.org/info/rfc6750",
      "documentationUri": "no documentation",
      "type": "oauthbearertoken",
      "primary": true
    },
    {
      "name": "HTTP Basic",
      "description": "Authentication scheme using the HTTP Basic Standard",
      "specUri": "http://www.rfc-editor.org/info/rfc2617",
      "documentationUri": "no documentation",
      "type": "httpbasic"
    }
  ],
  "meta": {
    "location": "scim/v2/ServiceProviderConfig",
    "resourceType": "ServiceProviderConfig",
    "created": "2019-09-03T00:00:00Z",
    "lastModified": "2019-09-03T00:00:00Z",
    "version": "W\/\"3694e05e9dff594\""
  }
}
CODE


Application Configuration:

application.yml

This YAML file has Core Compatibility version, IDHUB services, and security configurations.

info:
  app:
    name: Connector API Application
    description: IDHUB Connector Application
    version: X.X.X
    compatible-core-version: X.X
security:
  oauth2:
    client:
      client-id: IDEShim
      client-secret: <service auth token>
      grant_type: client_credentials
      access-token-uri : https://<IDHUB Base URL>/auth/realms/IDHub/protocol/openid-connect/token
management:
  endpoints:
    web:
      exposure:
        include: info,health,httptrace
idhub-core:
  url: https://<IDHUB Base URL>/api/core
target-system:
  name: <Target Systen/ Application Name>
server:
  port: <Connector Application PORT>
spring:
  security:
    user:
      name: <Service Account/Client ID>
      password: <Service Accpunt Password>
CODE


splice.yml

This YAML file has a target-system database-specific connection detail.          

The sample is given below for the Database based target system

spring:
  profile: splice
  name: JDBC-splice
  environment: dev
  version: 1.0.0
target-system:
  name: JDBC
  host: 10.2.9.132
  port: 3306
  username: <user_id>
  password: <password>
  driver_class: com.mysql.cj.jdbc.Driver
  dialect: mysql
  url: jdbc:mysql://10.2.9.132:3306/db?useSSL=false
CODE