Connecting Databases with Lumos

• Read capabilities use SELECT statements. The column labels in your query results must match the naming requirements documented below.
• Write capabilities use INSERT, UPDATE, or DELETE statements to modify data in your database.
• Stored procedures are supported for both read and write capabilities.
• All field values should be selected as strings unless otherwise noted.

​

Minimum required capabilities

1. Validate Credentials: confirms the database connection and credentials work.
2. List Accounts: retrieves the list of user accounts from your database.

Authentication method Lumos authenticates to your database with a database user and password only. Windows Authentication, Kerberos, certificate-based, and IAM-based authentication are not supported. CSV connections don’t use credentials. See CSV files.

​

Read-only or provisioning

​

1. Set up the On-Premise Agent

java -version

​

2. Download the connector package

The connection can take more than one step If the card shows a connector version download step in Configure Integrations instead of connection settings, complete the download first. The settings screen follows.

​

3. Configure a database user

​

4. Verify network access

​

5. Enter authentication details

• Username and Password for the database user
• Database URI in JDBC format, excluding username and password, for example jdbc:postgresql://db.server:5430/a_database?ssl=true
• Database Type: one of MySQL, MariaDB, PostgreSQL, SQL Server, Oracle, Db2, MongoDB, Snowflake, Databricks, or CSV. CSV queries CSV files on the agent host and needs JDBC connector v0.0.40 or later.

​

6. Define read capabilities

​

Validate Credentials

SELECT id AS integration_specific_id, email, first_name AS given_name, last_name AS family_name, uname AS username, status AS user_status FROM users LIMIT 1;

​

List Accounts

​

Get Account

SELECT id AS integration_specific_id, email FROM users WHERE id = {account_id};

​

List Updated Accounts (incremental syncs)

SELECT id AS integration_specific_id, email FROM users WHERE updated_at > {since};

​

List Entitlements

​

Find Entitlement Associations

Roles must be entitlements to be reviewable Access returned as extra columns on List Accounts becomes custom attributes, and attributes never appear in Access Reviews. Access you want to review or provision must come through List Entitlements and Find Entitlement Associations.

​

Get Last Activity

​

List Resources

​

7. Define write capabilities

UPDATE users SET status = 'INACTIVE' WHERE id = {account_id};

DDL identifiers need the ! suffix For write queries containing DDL (creating or altering users), identifiers that should not be quoted need an ! suffix: CREATE USER {email!} IDENTIFIED BY TempPass123 PASSWORD EXPIRE. Values that should be passed as string literals take no suffix.

Returning the ID of a created account Lumos needs the new account’s id back from Create Account. Return a column named id, or fill in the dedicated Create Account Return ID query (for example SELECT id FROM accounts WHERE email = {email};). It runs in the same transaction with the same parameters. Use the dedicated field rather than a multi-statement Create Account query, since some JDBC drivers don’t reliably return the id from multi-statement queries.

​

CSV files

​

Connection details for CSV

• Database URI: Point the URI at a directory on the agent host using the form jdbc:relique:csv:/path/to/csv/directory. Each <name>.csv file in the directory is a table named <name>. Lumos rejects any URI that doesn’t start with jdbc:relique:csv:.
• Username and Password: Not used. Leave them empty. Windows integrated authentication is rejected for CSV connections.
• The agent service account needs read access to the directory.

jdbc:relique:csv:/opt/lumos/csv
jdbc:relique:csv:/opt/lumos/csv?separator=;&charset=UTF-8

-- Validate Credentials
SELECT id AS integration_specific_id FROM users

-- List Accounts
SELECT id AS integration_specific_id, first_name AS given_name, last_name AS family_name,
  email, status AS user_status, department
FROM users

​

URI options

​

Restrict the CSV directory

LUMOS_JDBC_CSV_ROOT=/opt/lumos/csv

​

Data mapping

• Every column is text unless you set columnTypes in the URI, for example ?columnTypes=Int,String,String.
• Boolean fields such as is_assignable can be 1/0 or true/false (any case) in the file.
• Blank cells are read as null. A blank email means no email, and a blank custom attribute is left out like a SQL NULL. A blank cell in a column typed Boolean through columnTypes is read as false.
• Templated fields such as {account_id} work the same way as for other database types.

​

Limitations

• Read only. Define only SELECT queries and read capabilities. Validate Credentials fails if any write query is configured, and write capabilities fail without opening the files.
• One table per query. Joins are not supported. Subqueries work.

​

Troubleshooting