> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lumos.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Connecting Databases with Lumos

Connect Lumos to any JDBC-compatible database by defining SQL queries for each capability. Each capability (a specific action like listing accounts or assigning entitlements) maps to a single SQL query that you write directly in the Lumos UI. The underlying mechanism is JDBC, and the connector supports the database engines in its **Database Type** list (see step 5).

* **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.

Not sure what queries to write? Check [Database Query Examples](/integrations/generics/databases/examples) for ready-made query sets per database engine.

## Minimum required capabilities

A working database connection needs SQL queries for at least two capabilities:

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

Additional capabilities unlock incremental syncing, entitlement management, activity tracking, and automated provisioning and deprovisioning.

> **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](#csv-files).

## Read-only or provisioning

The connector is entirely query-driven, so access level follows from what you configure. A database user with read permissions and no write queries configured provides a sync-only connection. Moving to provisioning later means adding write queries and granting the matching write permissions to the same database user, not creating a separate connection.

## 1. Set up the On-Premise Agent

If your database isn't reachable from the internet, the connector runs on the Lumos On-Premise Agent. Follow [Installation](/integrations/on-premise-agent/installation) if you haven't deployed one.

If you're using the Windows agent, the machine needs **Java 11 or newer**. Validate with:

```text theme={null}
java -version
```

Microsoft distributes OpenJDK builds for Windows if you need to install Java.

## 2. Download the connector package

Download the zip file containing the connector packages and JDBC drivers when the integration card prompts you.

> **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

Create a database user with the access your queries need, from the server hosting your agent. Read permissions for sync queries. Write permissions on the relevant tables only if you configure provisioning queries. Grant the minimum your configured queries touch.

## 4. Verify network access

The machine hosting your agent must reach the database server. Install a database client on the agent machine and confirm you can connect and run queries with the Lumos database user's credentials.

## 5. Enter authentication details

In Lumos, open the database connector's card from [Integrations](https://app.lumosidentity.com/integrations?tab=can_integrate) and provide the connection fields:

* **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.

To query CSV files on the agent host instead of a database server, see [CSV files](#csv-files).

## 6. Define read capabilities

Write a `SELECT` statement per capability, aliasing your columns to the labels Lumos expects. Enter each query on the same connection form, in the field named after its capability, such as **Validate Credentials** or **List Accounts**. Any extra columns become custom attributes in Lumos, named by their column label.

### Validate Credentials

Same as your List Accounts query, limited to one record. Include custom attributes, since this query detects the attribute schema.

```sql theme={null}
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

| Field | Required | Notes |
| :- | :- | :- |
| `integration_specific_id` | Yes | Must be unique and stable |
| `email` | No | |
| `given_name` | No | |
| `family_name` | No | |
| `username` | No | |
| `user_status` | No | One of ACTIVE, INACTIVE, SUSPENDED, DEPROVISIONED, PENDING, DELETED |
| `account_type` | No | One of SERVICE, USER |

### Get Account

Same fields as List Accounts. The runtime value `{account_id}` is available:

```sql theme={null}
SELECT id AS integration_specific_id, email FROM users WHERE id = {account_id};
```

### List Updated Accounts (incremental syncs)

Same fields as List Accounts. The runtime value `{since}` holds a DATETIME-equivalent of the last incremental sync:

```sql theme={null}
SELECT id AS integration_specific_id, email FROM users WHERE updated_at > {since};
```

### List Entitlements

| Field | Required | Notes |
| :- | :- | :- |
| `entitlement_type` | Yes | |
| `integration_specific_id` | Yes | |
| `integration_specific_resource_id` | Yes | Empty string if not resource-scoped |
| `label` | Yes | |
| `is_assignable` | No | Boolean |
| `description` | No | |

### Find Entitlement Associations

| Field | Required | Notes |
| :- | :- | :- |
| `account_id` | Yes | |
| `integration_specific_entitlement_id` | Yes | `integ_specific_entitlement_id` on engines limiting identifiers to 30 characters |
| `integration_specific_resource_id` | Yes | Same 30-character alternative. Empty string if not resource-scoped |

> **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

| Field | Required |
| :- | :- |
| `account_id` | Yes |
| `event_type` | Yes |
| `happened_at` | Yes |

### List Resources

| Field | Required |
| :- | :- |
| `integration_specific_id` | Yes |
| `label` | Yes |
| `resource_type` | Yes |

## 7. Define write capabilities

Write capabilities receive runtime values in curly brackets, the same `{field}` syntax as read capabilities. Wrap multi-statement capabilities in a transaction.

| Capability | Available fields |
| :- | :- |
| Activate Account | `{account_id}` |
| Deactivate Account | `{account_id}` |
| Delete Account | `{account_id}` |
| Create Account | `{email}`, `{username}`, `{given_name}`, `{family_name}`, `{user_status}` |
| Update Account | `{id}` plus any custom attribute placeholder |
| Assign Entitlement | `{account_integration_specific_id}`, `{resource_integration_specific_id}`, `{resource_type}`, `{entitlement_type}`, `{entitlement_integration_specific_id}` |
| Unassign Entitlement | Same five fields as Assign |

Example:

```sql theme={null}
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

Set **Database Type** to `CSV` to run SQL read queries against CSV files on your On-Premise Agent host. The JDBC connector bundles the [csvjdbc](https://github.com/simoc/csvjdbc) driver, so you don't need to install a separate driver. CSV support requires JDBC connector v0.0.40 or later.

Use the CSV database type when identity data is exported to CSV files and you want to define capabilities with SQL instead of loading the files into a database.

### 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.

```text theme={null}
jdbc:relique:csv:/opt/lumos/csv
jdbc:relique:csv:/opt/lumos/csv?separator=;&charset=UTF-8
```

For example, if `/opt/lumos/csv/users.csv` has the header row `id,first_name,last_name,email,status,department`, you can define these queries:

```sql theme={null}
-- 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

Lumos accepts only csvjdbc's documented formatting options in the URI query string:

`separator`, `charset`, `quotechar`, `fileExtension`, `headerline`, `headerline.<table>`, `suppressHeaders`, `isHeaderFixedWidth`, `trimHeaders`, `trimValues`, `columnTypes`, `columnTypes.<table>`, `indexedFiles`, `timestampFormat`, `dateFormat`, `timeFormat`, `locale`, `useDateTimeFormatter`, `timeZoneName`, `commentChar`, `skipLeadingLines`, `skipLeadingDataLines`, `maxDataLines`, `ignoreNonParseableLines`, `missingValue`, `fileTailPrepend`, `defectiveHeaders`, `transposedLines`, `transposedFieldsToSkip`, `quoteStyle`, `fixedWidths`, and `randomSeed`.

See the [csvjdbc documentation](https://github.com/simoc/csvjdbc/blob/master/docs/doc.md) for what each option does. Lumos rejects any other option before connecting, including `function.*` and `cryptoFilterClassName`. The `class:` and `classpath:` path prefixes are also rejected. `zip:` archives are allowed.

### Restrict the CSV directory

Set the optional `LUMOS_JDBC_CSV_ROOT` environment variable on the On-Premise Agent service to a directory. When it's set, CSV connections can only read directories and `zip:` archives inside that directory. Lumos resolves symlinks and `..` segments before the check.

Setting `LUMOS_JDBC_CSV_ROOT` is recommended. Without it, anyone who can edit the integration settings can point the URI at any directory the agent service account can read.

```bash theme={null}
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

`java.lang.OutOfMemoryError `**during sync.** Increase the JVM heap on the agent server by setting `JAVA_TOOL_OPTIONS` with an `-Xmx` value. Start at 4 GB and raise in 2 GB increments. Windows: `setx JAVA_TOOL_OPTIONS "-Xmx4g" /M` in an Administrator PowerShell. Docker: add `-e JAVA_TOOL_OPTIONS='-Xmx4g'` to your run command.

**Connector fails to start on the agent.** The connector requires Java 11 or newer on the agent host. Install or upgrade Java, then restart the agent.

**Create Account succeeds but the user shows "no accounts found".** Put the account-ID lookup in the **Create Account Return ID** field, not only inside the Create Account query.