🛢️ Database (JDBC) Connector

1.Set Up On-Premise Agent

If not previously completed, follow detailed instructions outlined in: Installation section.

2.Download JDBC Connector Packages

Download the relevant zip file containing the appropriate connector packages and JDBC drivers.

3. Configure database user

Set up a database user that has the appropriate read and/or write access to the database from the server set up for your agent. The access required for your service account will be determined by the SQL queries that you define for each capability under your Authentication Details.

4. Configure Lumos Agent access to the DB server

In order for the JDBC connector to work, the machine hosting your Lumos Agent must have access to the database server over the network. In order to validate this, install a client for your database server on the Lumos Agent machine. Then, using the credentials configured for your database user, test that you are able to connect to the server and execute queries.

In addition, if you are using the Windows version of the Lumos On Premise Agent, you must make sure that the windows machine has a version of Java installed that is greater than or equal to Java 11. To validate this, open powershell and run

java - version

The version number will be displayed in the ouput. If you need to install java, distributions of OpenJDK from Microsoft can be found here https://learn.microsoft.com/en-us/java/openjdk/download

5. Enter Authentication Details

Configure the appropriate authentication details in the following sections.

Connection Details

Provide the authentication fields:

i. Username

ii. Password

iii. Database URI:

The database URI should be in JDBC format, excluding the username and password. ex:jdbc:postgresql://db.server:5430/a_database?ssl=true&loglevel=2

iv. Database Type

Defining Read Capabilities

• Define a SQL query for the relevant read capabilities.
• The SQL query needs to be a SELECT statement.
• Each read capability has specific column label naming requirements as detailed in the next section.
  • example sql query "SELECT external_id as integration_specific_id, email_address as email, uname as username FROM my_user_table;"
• All fields should be selected as strings unless otherwise noted
• Calling stored procedures as part of the SQL query is supported.

Column Label Naming Requirements for Read Capabilities

The following read capabilities are described in detail in Connector capabilities.

Validate Credentials

• This query should be the same as the query for ListAccounts, but limited to one record. Please be sure to include any custom attributes as this will be used to detect the custom attribute schema

List Accounts

• integration_specific_id (required)
• email
• given_name
• family_name
• username
• user_status (one of ACTIVE, INACTIVE, SUSPENDED, DEPROVISIONED, PENDING, DELETED)
• account_type (one of SERVICE, USER)
• Any additional fields selected will be considered custom attributes, they should be selected with the column name that you would like to show up in the Source of Truth settings within Lumos

Find Entitlements Association

• account_id (required)
• integration_specific_entitlement_id (required)
• integration_specific_resource_id (required) (required, but can be selected as an empty string if the entitlement is not for a resource)

Get Last Activity

• account_id (required)
• event_type (required)
• happened_at (required)

List Entitlements

• entitlement_type (required)
• integration_specific_id (required)
• integration_specific_resource_id (required, but can be selected as an empty string if the entitlement is not for a resource)
• is_assignable (boolean)
• label (required)

List Resources

• integration_specific_id (required)
• label (required)
• resource_type (required)

Defining Write Capabilities

• Define a SQL query for the relevant write capabilities.
• Each write capability has specific requirements in defining the SQL Query.
  • An example sql query "UPDATE accounts SET status='ACTIVE' WHERE external_id = {account_id}"
• The field names detailed in the next section will be made available to the query and should be included in the INSERT or UPDATE enclosed within curly brackets.
• All values will be strings unless otherwise noted
• If a write capability contains multiple statements, it should be wrapped in the transaction.
• Calling stored procedures as part of the SQL query is supported.

Column Label Naming Requirements for Write Capabilities

The following write capabilities are described in detail in Connector capabilities.

Active Account

• account_id

Assign Entitlement

• account_integration_specific_id
• resource_integration_specific_id
• resource_type
• entitlement_type
• entitlement_integration_specific_id

Create Account

• email
• username
• given_name
• family_name
• user_status (one of ACTIVE, INACTIVE, SUSPENDED, DEPROVISIONED, PENDING, DELETED)

📘

Returning the ID of the created account

In order for Lumos to identify the account created, this query should return the id of the account. This can be done by making your Create Account query return a column with the name "id". If this is not an option with your DB/schema, you can also fill out the Create Account Return ID query. This query should select a single column "id" and return the id of the account created. This query will be executed within the same transaction as the Create Account query.

Deactivate Account

• account_id

Delete Account

• account_id

Unassign Entitlement

• account_integration_specific_id
• resource_type
• resource_integration_specific_id
• entitlement_type
• entitlement_integration_specific_id