> For the complete documentation index, see [llms.txt](https://docs.openiam.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.openiam.com/connector-configuration/oracle.md).

# Oracle RDBMS connector

This page describes how to enable provisioning to manage Oracle database user accounts

The Oracle connector provides the ability to get user data and user permissions from an **Oracle RDBMS** system and provision data to Oracle RDBMS.

| Operation                                                    | Supported |
| ------------------------------------------------------------ | --------- |
| Create user                                                  | ✅         |
| Modify user (incl. profiles, privileges, roles, tablespaces) | ✅         |
| Enable / disable user                                        | ✅         |
| End-date user                                                | ✅         |
| Search / synchronize users (with privileges and roles)       | ✅         |
| Test connection                                              | ✅         |

{% hint style="info" %}
**Which Oracle connector do I need?** This page covers **Oracle RDBMS** (database user accounts). For **Oracle E-Business Suite** application users, see the [Oracle EBS connector](/connector-configuration/oracleebs.md); for **Oracle Identity Cloud Service**, see the [Oracle IDCS connector](/connector-configuration/oracle-idcs.md).
{% endhint %}

## Use cases

* Managing **Oracle database accounts** — creation, status, profiles, privileges, roles, and tablespaces — from OpenIAM.
* **Importing database users** (optionally with their privileges and roles) into OpenIAM for visibility and access review.
* For rows in an application's own tables (rather than DB accounts), use the [JDBC connector](/connector-configuration/jdbc.md) instead.

## Versions and compatibility

The end application is an **Oracle database**, reached over JDBC. The connector manages database accounts and the roles granted to them, so it has no API version of its own — what it supports is what the server and the JDBC driver support.

<table><thead><tr><th width="170">Property</th><th>Value</th></tr></thead><tbody><tr><td><strong>Platform</strong></td><td>Oracle Database</td></tr><tr><td><strong>Interface</strong></td><td>JDBC</td></tr><tr><td><strong>Managed objects</strong></td><td>Database accounts, and the roles granted to them</td></tr><tr><td><strong>Connection</strong></td><td>Connection string and driver, set on the managed system</td></tr></tbody></table>

{% hint style="info" %}
**Which Oracle connector do I need?** This page covers **Oracle database accounts**. For Oracle **E-Business Suite** application users and responsibilities, see the [Oracle EBS connector](/connector-configuration/oracleebs.md); for **Oracle Identity Cloud Service**, see the [Oracle IDCS connector](/connector-configuration/oracle-idcs.md).
{% endhint %}

## Prerequisites

A service account in Oracle for the connector. Create it with the following script:

{% code overflow="wrap" %}

```
create user OracleConnectorUser identified by passwd00;
grant resource, connect, dba, create session, UNLIMITED TABLESPACE to OracleConnectorUser;
alter user OracleConnectorUser default role all profile default;
```

{% endcode %}

The service account must have permission to run the SQL listed under [Behind the scenes](#oracle-scripts-behind-the-scenes).

## Installing the connector

In an RPM installation, use the general startup script to start the connector. See [Connectors via RPM](/installation/rpm/rpm-single-node/add-a-connector.md) for deploying on a separate host.

## Starting, stopping, and restarting

<table><thead><tr><th width="191">Deployment</th><th>Commands</th></tr></thead><tbody><tr><td>RPM (systemd)</td><td><code>systemctl start|stop|restart openiam-connector@oracle-connector-rabbitmq</code></td></tr><tr><td>Kubernetes</td><td><code>kubectl rollout restart</code> the connector deployment.</td></tr></tbody></table>

## Command-line parameters and properties

The connector is a Spring Boot application. Its defaults ship inside the jar, and you override them in either of two places:

* **An external property file**, read at `${confpath}/conf/properties/oracle-connector-rabbitmq.properties`. The file is optional, and `confpath` is itself a system property, set with `-Dconfpath=/data/openiam/`.
* **Directly on the command line**, as `-D` arguments, which win over the file.

{% code overflow="wrap" %}

```bash
java -Dconfpath=/data/openiam/ -Dlogging.level.root=INFO -Dorg.openiam.connector.queue=Oracle_Connector_1_Request -jar oracle-connector-rabbitmq.jar
```

{% endcode %}

| Property                      | Default                      | Purpose                                                                                     |
| ----------------------------- | ---------------------------- | ------------------------------------------------------------------------------------------- |
| `org.openiam.connector.queue` | `Oracle_Connector_1_Request` | Mixed-case default — the connector record in OpenIAM must match it exactly, including case. |
| `oracle.property.date.format` | `MM/dd/yyyy`                 | Date format used with Oracle date fields.                                                   |

| Property                                  | Default      | Purpose                                                       |
| ----------------------------------------- | ------------ | ------------------------------------------------------------- |
| `org.openiam.health.check.sweeptime`      | `30000`      | How often, in milliseconds, the connector reports its health  |
| `management.health.elasticsearch.enabled` | `false`      | Left disabled — the connector has no Elasticsearch dependency |
| `management.health.redis.enabled`         | `false`      | Left disabled — the connector has no Redis dependency         |
| `oracle.property.date.format`             | `MM/dd/yyyy` | Date format used when reading and writing Oracle date columns |

The connection details themselves are **not** set here — they are managed system parameters, covered under [Configuring the managed system](#configuring-the-managed-system).

## Verifying the connector is running

{% stepper %}
{% step %}
In the Webconsole, open the managed system — the **Running connector status** indicator must be green.
{% endstep %}

{% step %}
Click **Test Connection** — it must return success.
{% endstep %}

{% step %}
In the RabbitMQ management console, confirm the `Oracle_Connector_1_Request` queue exists and has a consumer.
{% endstep %}
{% endstepper %}

## Troubleshooting

Connector troubleshooting can be done by raising the logging level to DEBUG mode (`-Dlogging.level.org.openiam=DEBUG`).

| Symptom                           | Likely cause                           | Resolution                                                                                |
| --------------------------------- | -------------------------------------- | ----------------------------------------------------------------------------------------- |
| Connector never receives messages | Queue-name mismatch — including case   | The connector record's queue must equal `Oracle_Connector_1_Request` exactly (capital O). |
| Test connection fails             | Wrong connection string or credentials | Verify the `jdbc:oracle:thin:@host:port/sid` format and the service account.              |
| Grants/roles not applied          | Service account lacks rights           | Re-check the grants in Prerequisites and the SQL under Behind the scenes.                 |
| Sync misses privileges/roles      | Not enabled in the attributes script   | Add `"privileges"`/`"roles"` in `/sync/user/oracle/UserSyncAttributes.groovy`.            |

See also: [general connector troubleshooting](/connector-configuration/4-troubleshootingconnector.md) and the [connector error-code reference](/connector-configuration/4-troubleshootingconnector.md#connector-error-codes).

## Registering the connector in OpenIAM

Configure the connector's optional fields under **Left menu → Connector Configuration**:

The screen groups the available fields, and shows how many of the 25 you have configured. The **Connection** group is the one that matters for a JDBC connector:

<table><thead><tr><th width="238">Field</th><th>Purpose</th></tr></thead><tbody><tr><td><strong>Connection String</strong></td><td>The JDBC connection string for the database, for example <code>jdbc:oracle:thin:@dbhost.example.com:1521:orcl</code></td></tr><tr><td><strong>JDBC Driver URL</strong></td><td>The JDBC driver the connector loads</td></tr><tr><td><strong>Is Primary Repository</strong></td><td>Whether this managed system is the primary one. All managed systems are primary by default; clearing it marks the system as a reserve, used when provisioning to the primary has failed</td></tr><tr><td><strong>Secondary Repository</strong></td><td>Enables use of a secondary repository</td></tr><tr><td><strong>Always Update Secondary</strong></td><td>When set, the secondary repository is always updated</td></tr></tbody></table>

The remaining groups — **Operation Handlers**, **Directory & Search**, **User Objects** and **Group Objects** — are shared with every other connector type.

{% hint style="info" %}
The **Directory & Search** and **Group Objects** groups hold settings such as *Base DN for Group* and *Search Base DN for User*, whose own descriptions say they are for LDAP and Active Directory connectors. They have no meaning for a JDBC connection — leave them unselected here.
{% endhint %}

## Configuring the managed system

<table><thead><tr><th width="245">Field</th><th>Value</th></tr></thead><tbody><tr><td>Login Id</td><td><code>OracleConnectorUser</code></td></tr><tr><td>Password</td><td><code>&#x3C;password></code></td></tr><tr><td>JDBC driver</td><td><code>oracle.jdbc.driver.OracleDriver</code></td></tr><tr><td>Connection String</td><td><code>jdbc:oracle:thin:@oracle_host:port/sid</code></td></tr><tr><td>Search filter for User</td><td><code>USERNAME=?</code></td></tr><tr><td>Search filter for Group</td><td><code>Role=?</code></td></tr></tbody></table>

Search filters must contain the char `?`, which is replaced by the principal name to find the object. The filter is used in reconciliations as well.

## Attribute mapping and policy map

Map OpenIAM user attributes (username, password, account status, default/temporary tablespace, profile, roles, privileges) in the managed system's [policy map](/administration-guide/lifecycle/policy-map.md) — see the SQL the connector generates under [Behind the scenes](#oracle-scripts-behind-the-scenes).

## Oracle scripts behind the scenes

Oracle SQL used by the connector:

**Create new user:**

{% code overflow="wrap" %}

```
CREATE USER <user_name> IDENTIFIED BY <password> DEFAULT TABLESPACE <default_tablespace> TEMPORARY TABLESPACE <temporary_tablespace> COUNT <account_status>;
ALTER USER <user_name> DEFAULT ROLE <default_roles>;
GRANT <roles>,<privileges>
```

{% endcode %}

**Modify user:**

{% code overflow="wrap" %}

```
ALTER USER <user_name> DEFAULT TABLESPACE <default_tablespace> TEMPORARY TABLESPACE <temporary_tablespace> PROFILE <profile> ACCOUNT <account_status>;
ALTER USER <user_name> DEFAULT ROLE <default_roles>;
GRANT <roles>,<privileges>;
```

{% endcode %}

**Search / lookups:**

{% code overflow="wrap" %}

```
SELECT * FROM DBA_USERS WHERE USERNAME='TEST_ORACLECONNECTORUSER';
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';
```

{% endcode %}

## Reconciliation and synchronization

OpenIAM provides out-of-the-box sync configurations and Groovy scripts. Example queries:

* `*` — select all records.
* `USERNAME=Jons%` — select by user name (uses `where USERNAME like ('Jons%')` — "starts with").
* Concatenate filters with `;` (= AND): `USERNAME=Jons%;ACCOUNT_STATUS=OPEN`.

Fields available for filtering: `USERNAME`, `ACCOUNT`.

To include **privileges** from Oracle, add `"privileges"` in `/sync/user/oracle/UserSyncAttributes.groovy`; the following SQL will run:

{% code overflow="wrap" %}

```
SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';
```

{% endcode %}

To include **roles**, add `"roles"` in the same script:

{% code overflow="wrap" %}

```
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='TEST_ORACLECONNECTORUSER';
```

{% endcode %}

Make sure the service account has permission to perform the SQL above.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://docs.openiam.com/connector-configuration/oracle.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.