Database Connector Configuration setup
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}"
}
]
},
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"
}
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"
}
}
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\""
}
}
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>
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