SQL
The SQL Collector is a template collector that can extract data from foreign databases using configurable SQL statements.
Supported objects and operations
Identity data
| System objects | Omada Identity Data Model | Operations |
|---|---|---|
| Identities | - | Read |
| Contexts | - | Read |
| Identity Owner | - | Read |
| Context Owner | - | Read |
| Context Assignment | - | Read |
Access rights
| System objects | Omada Identity Data Model | Operations |
|---|---|---|
| Account | - | Read |
| Resource | - | Read |
| Resource Owner | - | Read |
| Resource Assignment | - | Read |
| Resource Parent/Child | - | Read |
Minimum required permissions
The service account used for running the application pool in Omada Identity must have access to the target SQL database. No additional configuration is required.
Implementation notes
In the advanced mode, you can use a different OLEDB provider (through changes in the connection string). For MySQL, MariaDB and PostgreSQL only default providers are supported.
For Oracle databases, as you cannot introduce the TNSNAMES.ORA file that defines databases addresses for establishing connections, you need to configure a connection string to work without TNSNAMES.ORA. The following example can be used:
Provider=OraOLEDB.Oracle;
Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));
User Id=myUsername;Password=myPassword;
Network requirements
N/A
Prerequisites on-prem
Perform the following steps depending on the technology used:
IBM database platforms
The IBM database platform requires both the IBM Data Server Runtime Client software and either an IBM or Microsoft driver. See the IBM software package and Microsoft db2oledb driver for downloads.
Oracle database platforms
The Oracle database platform requires the ODAC Xcopy (with the OLE DB component) package installed. Go to Oracle Downloads and download the package.
MySQL database platforms
The MySQL database platform requires MySQL Connector/NET version 8.4.0. package. See the MySQL Product Downloads.
Delta support
The delta requires the table or view to contain the following columns:
- Change Date (datetime)
- Action (Create, Update, Delete)