Skip to main content

SQL Connector

Introduction

Description

The SQL connector allows an easy way to configure and manage relational databases.

Managed System

There are a lot of relational databases, currently, these are the currently supported databases.

  • MySQL
  • MariaDB
  • PostgreSQL
  • Oracle
  • Microsoft SQL Server
  • Informix
  • IBM DB2/400
  • Sybase
  • ODBC

For more information: List of relational databases

If your system is not in the previous list, it's possible to include it easily!

For more information to check if your system may be synchronized with this connector you do not hesitate to contact us through our Contact form

Prerequisites

It is needed a user with access and permissions to the schemes and tables required in the scope of the integration.

To configure DB2/400  or Sybase it is mandatory to install the drivers in the lib directory of the Sync Server.

The Java-ODBC bridge is deprecated in Java, and the support will be removed shortly.

Download and Install

The SQL is part of the default connectors, you do not need to install it, but you can upgrade it from the download management section.

For more information about the installation process you can visit the Addons Getting started page.

Agent Configuration

Basic

Generic parameters

After the installation of the addon, you may create and configure agent instances.

To configure this SQL connector you must select "Customizable SQL agent" in the attribute "Type" of the generic parameters section in the agent's page configuration.

For more information about how you may configure the generic parameters of the agent, see the following link: Agents configuration

image-1658999019877.png

Custom parameters

Below there are the specific parameters for this agent implementation:

Parameter

Description

User name

Database user name to authenticate

Password

The password of the database user

Driver

Identifies the driver of the relational database to use.

Currently, these are the supported databases: MySQL (& MariaDB), PostgreSQL, Oracle, MS SQL Server, Informix, DB2/400, DB2 Universal, Sybase, ODBC

DB URL

URL that identifies the connection properties. Please refer to the specific database vendor documentation to build this URL

Ejemplos de cada URL y enlace a la pagina de referencia

SQL Sentence to execute at startup


Password hash algorithm

The algorithm is used to encrypt the password. For instance SHA-1, SHA-256, MD5, etc

Password hash prefix

{SHA}

Enable debug

Two options: "Yes", "No": it enables or not more log traces in the Synchronization Server log

Synchronization method

Full synchronization

Incremental synchronization

image-1658999086220.png

Attribute mapping

This connector can manage users, accounts, roles, groups, and grants.

Properties

Some agents require to configure some custom attributes, you will use the properties section to do that.

Any SQL sentence gets its parameters in a three step process:

  1. The synchronization engine creates the Soffid object.
  2. The Soffid object is translated into a managed system object, using the attribute translation rules.
  3. Soffid parser looks for any identifier preceded by a colon (:) symbol. For any symbol found, the symbol is replaced by a parameter whose value is the managed system attribute with the replaced identifier.

Once the SQL sentence has been executed, in the case of SELECT clauses, the column names are used to generate a virtual managed system object. The last step is to apply the attribute translation to generate the Soffid object to be populated.

These are the properties required to map every object of the mapping:

Property

Value

selectAll

SQL sentence that needs to be executed to retrieve all the objects that currently exist on the database.

  • Applies to authoritative identity sources.

  • On non-authoritative identity sources, only the columns needed to calculate the name soffid attribute are needed.

You can use this property with the following objects: user, account, role, and authoritative change.

 

SELECT * FROM USERS
SELECT * FROM ROLES

check

SQL sentence that will return when a single object already exists on the database.

 

You can use this property with all the Soffid objects.


SELECT ID FROM USERS WHERE USER=:USER
SELECT ID FROM ROLES WHERE ROLE=:ROLE

insert

SQL sentence to create a new object.


INSERT INTO USERS VALUES (:USER, :FIRST_NAME, :LAST_NAME, :MAIL, :GROUP)
INSERT INTO USER_ROLES (USETNAME, ROLNAME) VALUES (:USERNAME, :ROLNAME)

update

SQL sentence to update an existing object.


UPDATE USERS SET FIRST_NAME=:FIRST_NAME, LAST_NAME=:LAST_NAME, MAIL=:MAIL, GROUP=:GROUP WHERE ID=:ID
UPDATE ROLES SET DESCRIPTION=:DESCRIPTION WHERE ROLE=:ROLE

delete

SQL sentence to remove (or disable) an existing object.


DELETE FROM USERS WHERE ID=:ID
DELETE FROM USER_ROLES WHERE ID=:ID

selectByAccountName

SQL sentence to get user data based on the account name (for a single account information).



SELECT * FROM USERS WHERE USER=:USER

selectByAccount

SQL sentence to retrieve all the role grants made to an account (for a single account information).

grants

SELECT * FROM USER_ROLES WHERE USERNAME=:USER

selectByName

SQL sentence to fetch role information based on its name (for a single role information).


roles

SELECT * FROM ROLES WHERE ROLE=:ROLE

selectByRole

SQL sentence to retrieve all the accounts grantee of a role (used to update a role) (for single role information).


SELECT * FROM USER_ROLES WHERE USERNAME=:USER

updatePassword

SQL sentence to update the user password.

usuario cuenta


UPDATE USERS SET PASS=:PASS WHERE USER=:USER

validatePassword

SQL sentence to check the user password.

usuario cuenta


SELET 1 FROM USERS WHERE PASS=:PASS AND USER=:USER

Attributes

You can customize attribute mappings, you only need to select system objects and the Soffid objects related, manage their attributes, and make either inbound or outbound attribute mappings.

You may map the attributes of the target system with the Soffid available attributes.

  • For the target system attributes are required to be accessible to its specification
  • For the Soffid attributes, you may follow the next link

For more information about how you may configure attribute mapping, see the following link: Soffid Attribute Mapping Reference

Example for roles:

image-1659003794802.png

Example for accounts:

image-1659003867652.png

Triggers

You can define BeanShell scripts that will be triggered when data is loaded into the target system (outgoing triggers). The trigger result will be a boolean value, true to continue or false to stop.

Triggers can be used to validate or perform a specific action just before performing an operation or just after performing an operation on target objects.

To view some examples, visit the Outgoing triggers examples page.

Load triggers

You can define BeanShell scripts that will be triggered when data is loaded into Soffid (incoming triggers). The trigger result will be a boolean value, true to continue or false to stop.

Triggers can be used to validate or perform a specific action just before performing an operation or just after performing an operation into Soffid objects.

To view some examples, visit the Incoming triggers examples page.

Account metadata

Agents allow you to create additional data, on the "Account metadata" tab, to customize the accounts created for that agent. This additional information will be loaded with the agent's information, or calculated as defined in the mappings.
The additional data can be used in both mappings and triggers.

The attributes which you define here will be shown when you click on the proper account, on the Accounts Tabs at user page.

Operational

Monitoring

After the agent configuration you could check on the monitoring page if the service is running in the Synchronization Server, please go to:

Main Menu > Administration > Monitoring and reporting > Syscserver monitoring

    Tasks

    Authoritative

    If you are checked "Authorized identity source", an automatic task to load identities from the managed system to Soffid is available, please go to:

    Main Menu > Administration > Monitoring and reporting > Scheduled tasks

    And you will something like "Import authoritative data from <AGENT_NAME>".

    image-1659012997074.png

    You can also run the Authoritative load from the Massive actions tab in the Agent

    image-1659013094703.png

    Reconcile

    If you are configured the "Attribute Mapping" tab with some of our objects: "user, account, role, group or grant", an automatic task to synchronize these objects from the managed system to Soffid is available, please go to:

    Main Menu > Administration > Monitoring and reporting > Scheduled tasks

    And you will do something like "Reconcile all accounts from <AGENT_NAME>".

    image-1659013025873.png

    You can also run the Reconcile from the Massive actions tab in the Agent

    image-1659013126808.png

    Synchronization

    Regarding the synchronization of the objects, there are two possible options:

    • If the "Read Only" attribute is checked in the "Basics" tab (select Yes option), only the changes in the managed systems will be updated in Soffid. We recommend these options until the global configuration of Soffid will be tested.
    • If the "Read Only" attribute is not checked in the "Basics" tab (select No option), all the changes in Soffid or the managed system will be updated in the other. Note that this synchronization must be configured in the "Basic" tab correctly.

    For more information about how you may configure the generic parameters of the agent, see the following link: Agents configuration


    Back to top