Configuring MySQL

Configuring Account Schema

IDHub MySQL connector provides a default account schema which you can configure as per your MySQL database table and it’s schema configuration. To provide a more detailed explanation of the account schema and how to configure, let's walk through each section and its purpose:

General Structure

The JSON schema is used by IDHub to connect to a MySQL database through JDBC to manage user accounts. It defines various settings like attribute mappings, table configurations, and the SQL queries for account provisioning and modification.

Meta data and identification

{
  "id": "urn:sath:params:scim:schemas:core:1.0:Account",
  "name": "Account",
  "description": "The JSON file mentioned below contains the default settings used to fetch and provision accounts using specific attributes."
}

id: The unique identifier for this schema.
name: Denotes that this JSON file represents an account object.
description: A brief description of the file's purpose. It indicates that this configuration manages user accounts, which can be modified to align with the customer's database.

Attributes

"attributes": [
  {
    "name": "displayName",
    "type": "string",
    "idhubFieldName": "displayName",
    "required": true,
    "syncDirection": "bi-directional"
  },
  ...
]

Each attribute defines a mapping between the IDHub attribute and the MySQL table field. The key attributes are explained below:

name: The attribute name used in the MySQL table.
type: Data type of the attribute. It can be a string, int, or complex (for multi-valued or hierarchical data).
idhubFieldName: The corresponding field name used in IDHub.
required: Indicates if this field is mandatory (true or false).
syncDirection: Specifies the data synchronization. For more info, see below

Example:

displayName: This is mapped directly to the "displayName" field in the database and is a required field.
Use function idhubFieldName and add from IDHub field list to match the fields. For each attribute, you can customize the database field names, types, and sync behavior to match their schema.

note

List of IDHub fields can be viewed in your IDHub instance in Admin Settings page in Admin Module. Target System fields can be matched with custom added IDHub field as well.

Multi-value Field

For making an attribute multi-valued in IDHub, use function multivalue and mark it as true. Some common examples are email addresses, membership, licenses attributes which provides multiple values.

Unique Field

Every target system provides at-least one unique attribute to distinguish their accounts. To mark that attribute as unique account name (In case multiple unique keys are present and you do not wish for IDHub to fetch all unique keys and mark only one as account name) use function uniqueness and value can be server or global

note

Reconciliation will fail if more than one attribute is added as unique.

For MySQL in the default account schema, below are some of the attributes among others that can be unique account name.

userLogin - Recommended
email
employeeNumber
managerLogin

Reconciliation Key

Possible unique reconciliation key to match account in MySQL with User in IDHub in the default account schema are as follows:

userLogin (MySQL) with login (IDHub) - Recommended
email (MySQL) with email (IDHub)
employeeNumber (MySQL) with employeeNumber (IDHub)
managerLogin (MySQL) with managerLogin (IDHub)

Use function

isRecon and mark as true AND
mutability and mark as immutable

Both functions needs to be added to the attribute to mark it as a reconciliation key.

Example: Below is a userLogin attribute which is marked as Reconciliation key.

{
  "name": "userLogin",
  "type": "string",
  "idhubFieldName": "login",
  "required": true,
  "isRecon": true,
  "mutability": "immutable",
  "uniqueness": "server",
  "syncDirection": "bi-directional"
}

note

More than one attribute can be used as reconciliation key. It is used to match IDHub users with accounts and combination of reconciliation keys will be used to match the account.

Default Identity Attribute

Attribute Name	Attribute Type	Attribute Description
`id`	`string`	Unique Identifier of Identity
`name`	`string`	Name of Identity
`description`	`string`	Description of Identity
`schema`	`string`	Schema path of Identity

Default Account Attribute

Below are the attributes in the default Account Schema for MySQL

Attribute Name	Attribute Type	Attribute Description	Required (Yes/No)
`displayName`	`string`	Full name of the user	Yes
`userLogin`	`string`	Unique login identifier for the user	Yes
`phoneNumber`	`string`	User's contact phone number	No
`email`	`string`	User's email address	Yes
`department`	`string`	Department the user belongs to	No
`firstName`	`string`	User's first name	No
`employeeNumber`	`string`	Unique employee identification number	No
`jobTitle`	`string`	User's job or role title	No
`lastName`	`string`	User's last name	No
`location`	`string`	User's work location	No
`managerDisplayName`	`string`	Full name of the user's manager	No
`managerLogin`	`string`	Login identifier of the user's manager	No
`organizationName`	`string`	Organization the user belongs to	No
`status`	`string`	Current status of the user's account (e.g., active, inactive)	No

Matching and Unique Key

"matching-attributes": ["id"],
"entitlement-matching-attributes": ["userLogin"]

matching-attributes: This defines the primary attribute used to identify users. In this case, the id field is used to uniquely identify a user in the MySQL database.
entitlement-matching-attributes: userLogin is used to match users to entitlements.

Configuration Details for Database

"configs": {
  "tableName": "usr",
  "uniqueKey": "UserLogin"
}

configs: Contains configuration details for interacting with the MySQL database.

tableName: Specifies the table name where user data is stored (here, "usr").
uniqueKey: The field used as a unique key for the user account, which here is "UserLogin". It ensures no duplicate users with the same login

Attribute Mapping

"attribute-map": {
  "displayName": {
    "type": "string",
    "attribute": "displayName"
  },
  ...
}

The attribute-map section maps the database table fields to the schema attributes used in IDHub. For each attribute, you can specify:

type: Data type of the attribute.
attribute: The actual field name in the database.

This mapping ensures that the correct data is fetched from the MySQL database when an operation is performed on the user accounts.

EXAMPLE:

displayName: The display name of the user in the IDHub corresponds to the displayName field in the MySQL database.

SQL Queries for Operations

In this section, different SQL queries are defined for creating, updating, deleting, and fetching user accounts from the MySQL database. You can customize these queries according to your database schema and requirements.

Insert Query (Provisioning):

"insert": [
  "INSERT INTO usr (phoneNumber, email, userLogin, displayName, department, ...) VALUES ('${phoneNumber}', '${email}', '${userLogin}', ...)"
]

This query provisions a new user in the usr table by inserting values for attributes like phone number, email, etc. The values are substituted from IDHub using ${} syntax.

Assign Query (Entitlements):

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

This query assigns entitlements to users by inserting the userLogin and entitlements into the usr_permission_map table

Update Query:

"update": [
  "UPDATE usr SET phoneNumber='${phoneNumber}', email='${email}', ..."
]

This query updates user account information in the usr table, ensuring that all mapped attributes are updated with their latest values.

Delete Query:

"delete": [
  "DELETE FROM usr"
],
"deleteEntitle": [
  "DELETE FROM usr_permission_map"
]

The delete query removes the user from the usr table.
The deleteEntitle query revokes all entitlements by deleting rows in the usr_permission_map table that are related to the user.

Select Queries (Fetching Data):

"select": [
  "SELECT * FROM usr"
],
"selectUsrPermissionMapCondition": [
  "SELECT * FROM usr_permission_map WHERE userLogin='${userLogin}'"
],
"selectPermissionMapCondition": [
  "SELECT * FROM permission WHERE name='${name}'"
]

These queries retrieve information from the usr table, usr_permission_map, and permission tables. They fetch all users, specific user entitlements, and permission details based on certain conditions like userLogin or name.

Response Mapping

"mappingToExistingToResponse": [
  {
    "userLogin": "${userLogin}",
    "id": "${id}",
    "displayName": "${displayName}",
    ...
  }
]

This section defines how the MySQL query results map back into IDHub’s response structure. It ensures that once the data is retrieved from MySQL, it is properly transformed to match IDHub’s format.

Final Note

This account JSON serves as a customizable template to integrate IDHub with a MySQL database. Each section allows you to tailor the configuration to your database’s schema and tables. You can modify the table names, attributes, and SQL queries to align with your database’s unique structure, ensuring a seamless connection between IDHub and the MySQL backend for account management.

Configuring Entitlement Schema

IDHub MySQL connector provides a default entitlement schema which you can configure as per your MySQL database table and it’s schema configuration. To provide a more detailed explanation of the entitlement schema and how to configure, let's walk through each section and its purpose:

General Structure

The entitlement JSON schema defines the structure and settings used by IDHub to manage entitlements within a MySQL database. It includes the configuration for mapping attributes, SQL queries, and other essential details for provisioning entitlements.

Meta data and identification

{
  "id": "urn:sath:params:scim:schemas:core:1.0:Entitlement",
  "name": "Entitlement",
  "description": "The JSON file mentioned below contains the default settings used to fetch and provision entitlements using specific attributes."
}

id: A unique identifier for this schema, representing the entitlement object.
name: Specifies that this JSON file defines an "Entitlement" object.
description: A brief description indicating that this schema handles the fetching and provisioning of entitlements, allowing customization as required.

Attributes

"attributes": [
{
"name": "id",
"type": "string"
},
{
"name": "name",
"type": "string",
"required": true
},
{
"name": "description",
"type": "string",
"required": true
},
{
"name": "displayName",
"type": "string",
"required": true
},
{
"name": "targetSystem",
"type": "string",
"required": true
}
]

The attributes section defines the fields that make up an entitlement. Each attribute is described with a name, type, and additional properties where applicable:

id:
- Type: string
- This attribute represents the unique identifier for an entitlement. It’s not marked as required but can be used to reference specific entitlements in the system.
name:
- Type: string
- Required: true
- This is the primary attribute representing the name of the entitlement, which must be unique and is mandatory for creating an entitlement.
description:
- Type: string
- Required: true
- This field contains a description of the entitlement, providing additional details about its purpose. It’s mandatory to ensure the user understands what the entitlement is for.
displayName:
- Type: string
- Required: true
- A human-readable name for the entitlement. It is required and typically used in user interfaces to provide a clear, descriptive label for the entitlement.
targetSystem:
- Type: string
- Required: true
- Specifies the target system or application where the entitlement is applicable. This is crucial in multi-system environments to identify the context in which the entitlement is valid.

info

These attributes form the core structure of the entitlement object, ensuring that each entitlement is well-defined with necessary details, such as its name, description, and target system. The required fields enforce that essential information is always provided when creating or managing entitlements in IDHub.
You can customize the above attributes as per your required schema in the MySQL database

Default Entitlement Attribute

Below are the attributes in the default entitlement Schema for MySQL

Attribute Name	Attribute Type	Attribute Description	Required (Yes/No)
`id`	`string`	Unique identifier for the entity	No
`name`	`string`	Name of the entity	Yes
`description`	`string`	Detailed description of the entity	Yes
`displayName`	`string`	Display name for the entity	Yes
`targetSystem`	`string`	Target system associated with the entity	Yes

Matching and Unique Key

"matching-attributes": [
"name"
]

matching-attributes: Specifies the attribute used to identify and match entitlements uniquely. In this case, the "name" field is used.

Configuration Details for Database

"configs": {
"tableName": "permission",
"uniqueKey": "name"
}

configs: Configuration details for how this schema interacts with the MySQL database.

tableName: The database table where entitlements are stored, which in this case is "permission"
uniqueKey: The unique key that ensures there are no duplicate entries for the same entitlement, set to "name"

These configurations help define the schema’s interaction with the database and ensure that each entitlement is managed uniquely by its name.

Attribute Mapping

"attribute-map": {
"id": {
"attribute": "id",
"type": "string"
},
"name": {
"attribute": "name",
"type": "string"
},
"displayName": {
"attribute": "displayName",
"type": "string"
},
"description": {
"attribute": "description",
"type": "string"
},
"targetSystemDisplayName": {
"attribute": "targetSystemDisplayName",
"type": "string"
}
}

The attribute-map defines how IDHub's entitlement attributes map to the corresponding fields in the MySQL database. Each attribute is described in terms of:

type: The data type of the attribute (string in this case).
attribute: The actual field name in the database.

EXAMPLE:

id: Maps to the "id" field in the database.
name: Maps to the "name" field.
displayName: Maps to the "displayName" field.

This mapping ensures that the correct data is extracted from and written to the database when provisioning or fetching entitlements.

SQL Queries for Operations

In this section, different SQL queries are defined for creating, updating, deleting, and fetching entitlements from the MySQL database. You can customize these queries according to your database schema and requirements.

Insert Query (Provisioning):

"insert": [
"INSERT INTO permission (name,displayName,description,targetSystemDisplayName) VALUES('${name}','${displayName}','${description}','${targetSystemDisplayName}')"
]

This query provisions a new entitlement in the permission table by inserting values for name, displayName, description, and targetSystemDisplayName.
The ${} syntax is used to substitute the actual values from IDHub.

Update Query:

"update": [
"UPDATE permission set name='${name}',description='${description}'"
]

This query updates the permission table, allowing modification of an entitlement's name and description fields.

Delete Query:

"delete": [
"DELETE FROM permission"
]

This query removes an entitlement from the permission table, deleting the associated record.

Select Queries (Fetching Data):

"select": [
"SELECT * FROM permission"
]

This query retrieves all records from the permission table, fetching all defined entitlements.

Response Mapping

"mappingToExistingToResponse": [
{
"id": "${id}",
"displayName": "${displayName}",
"name": "${name}",
"description": "${description}",
"targetSystemDisplayName": "${targetSystemDisplayName}"
}
]

EXAMPLE:

The id field in the MySQL result will map to the id in the response.
The name, displayName, description, and targetSystemDisplayName fields from the database will be mapped similarly.

This ensures that the data returned from the database is properly transformed to match the format IDHub expects for entitlements

Final Note

The entitlement JSON file serves as a customizable template for integrating IDHub with a MySQL database for managing entitlements. It provides the following key functionalities:

Defines the attributes of entitlements and their data types.
Specifies the primary key (name) used to uniquely identify entitlements.
Maps IDHub attributes to the corresponding database fields.
Provides SQL queries to insert, update, delete, and retrieve entitlements from the database.

This schema can be easily modified to align with the your MySQL database structure, ensuring seamless interaction between IDHub and the database for managing entitlements.

Attribute Sync Direction

While adding attribute in json file, you can chose which direction your data flows for every attribute in IDHub.

Use function syncDirection for every attribute to choose their data flow directions.

Possible values are as follows:

'bi-directional': 'Bidirectional Synchronization',
'idhub-to-app': 'Sync IDHub to Application only',
'app-to-idhub': 'Sync Application to IDHub only',
'no-sync': 'No Synchronization',

Based on attribute functions we recommend directions. See below:

RBAC (Role based Access Control) : app-to-idhub or bi-directional
Reporting and not sensitive : bi-directional
Modify functions in target system and RBAC : idhub-to-app
Only for display : no-sync
Sensitive Attribute not used for audit/ reporting : Do not add attribute

Configuring MySQL

Configuring Account Schema​

General Structure​

Meta data and identification​

Attributes​

Multi-value Field​

Unique Field​

Reconciliation Key​

Default Identity Attribute​

Default Account Attribute​

Matching and Unique Key​

Configuration Details for Database​

Attribute Mapping​

SQL Queries for Operations​

Insert Query (Provisioning):​

Assign Query (Entitlements):​

Update Query:​

Delete Query:​

Select Queries (Fetching Data):​

Response Mapping​

Final Note​

Configuring Entitlement Schema​

General Structure​

Meta data and identification​

Attributes​

Default Entitlement Attribute​

Matching and Unique Key​

Configuration Details for Database​

Attribute Mapping​

SQL Queries for Operations​

Insert Query (Provisioning):​

Update Query:​

Delete Query:​

Select Queries (Fetching Data):​

Response Mapping​

Final Note​

Attribute Sync Direction​

Configuring Account Schema

General Structure

Meta data and identification

Attributes

Multi-value Field

Unique Field

Reconciliation Key

Default Identity Attribute

Default Account Attribute

Matching and Unique Key

Configuration Details for Database

Attribute Mapping

SQL Queries for Operations

Insert Query (Provisioning):

Assign Query (Entitlements):

Update Query:

Delete Query:

Select Queries (Fetching Data):

Response Mapping

Final Note

Configuring Entitlement Schema

General Structure

Meta data and identification

Attributes

Default Entitlement Attribute

Matching and Unique Key

Configuration Details for Database

Attribute Mapping

SQL Queries for Operations

Insert Query (Provisioning):

Update Query:

Delete Query:

Select Queries (Fetching Data):

Response Mapping

Final Note

Attribute Sync Direction