Integrate with Database Application Tables (Oracle)

Related Topics

Prerequisites

Before you install and configure a Database Application Tables (Oracle) Orchestrated System, you should consider the following prerequisites and tasks.

  1. Your Database Application Tables (Oracle) system is certified with Oracle Access Governance. Refer to Database Application Tables (Oracle) Components Certified for Integration with Oracle Access Governance for details of the versions supported.

Configure

You can establish a connection between customer databases and Oracle Access Governance by entering connection details. To achieve this, use the orchestrated systems functionality available in the Oracle Access Governance Console.

Navigate to the Orchestrated Systems Page

Navigate to the Orchestrated Systems page of the Oracle Access Governance Console, by following these steps:
  1. From the Oracle Access Governance navigation menu icon Navigation menu, select Service Administration → Orchestrated Systems.
  2. Click the Add an orchestrated system button to start the workflow.

Select system

On the Select system step of the workflow, you can specify which type of system you would like to onboard. You can search for the required system by name using the Search field.

  1. Select Database Application Table (Oracle DB).
  2. Click Next.

Enter details

On the Enter Details step of the workflow, enter the details for the orchestrated system:
  1. Enter a name for the system you want to connect to in the What do you want to call this system? field.
  2. Enter a description for the system in the How do you want to describe this system? field.
  3. Determine if this orchestrated system is an authoritative source, and if Oracle Access Governance can manage permissions by setting the following checkboxes.
    • This is the authoritative source for my identities
    • I want to manage permissions for this system
    The default value in each case is Selected.
  4. Click Next.

Add Owners

You can associate resource ownership by adding primary and additional owners. This drives self-service as these owners can then manage (read, update or delete) the resources that they own. By default, the resource creator is designated as the resource owner. You can assign one primary owner and up to 20 additional owners for the resources.

Note:

When setting up the first Orchestrated System for your service instance, you can assign owners only after you enable the identities from the Manage Identities section.
To add owners:
  1. Select an Oracle Access Governance active user as the primary owner in the Who is the primary owner? field.
  2. Select one or more additional owners in the Who else owns it? list. You can add up to 20 additional owners for the resource.
You can view the Primary Owner in the list. All the owners can view and manage the resources that they own.

Account settings

On the Account settings step of the workflow, enter details of how you would like to manage accounts with Oracle Access Governance when configured as a managed system:
  1. Select where to send notification emails when an account is created. The default setting is User. You can select one, both, or none of these options. If you select no options then notifications will not be sent when an account is created.
    • User
    • User manager
  2. When an identity moves within your enterprise, for example when moving from one department to another, you may need to adjust what accounts the identity has access to. In some cases the identity will no longer require certain accounts which are not relevant to their new role in the enterprise. You can select what to do with the account when this happens. Select one of the following options:
    • Disable
    • Delete
  3. When an identity leaves your enterprise you should remove access to their accounts. You can select what to do with the account when this happens. Select one of the following options:
    • Disable
    • Delete

Note:

If you do not configure your system as a managed system then this step in the workflow will display but is not enabled. In this case you proceed directly to the Integration settings step of the workflow.

Note:

If your orchestrated system requires dynamic schema discovery, as with the Generic REST and Database Application Tables (Oracle) integrations, then only the notification email destination can be set (User, Usermanager) when creating the orchestrated system. You cannot set the disable/delete rules for movers and leavers. To do this you need to create the orchestrated system, and then update the account settings as described in Configure Orchestrated System Account Settings.

Integration settings

On the Integration settings step of the workflow, enter the details required to allow Oracle Access Governance to connect to your customer database.

Table - Integration settings

Parameter Name Mandatory? Description

Easy Connect URL for Oracle database

Yes

URL of the server hosting the customer database system you want to integrate with.

For Oracle Database use the format host/port/database service/sid. For Oracle Autonomous Database use the format jdbc:oracle:thin:@<SERVICE_NAME>?TNS_ADMIN=<WALLET-DIR> as described in Configure Wallet for Autonomous Database Integration.

User name

Yes

 The username required to connect to the user database system to perform data reconciliation and provisioning.

Password/Confirm password

Yes

The password that authenticates the user you are connecting to the user database system with.

User account table name

Yes

The name of the database table containing your user accounts.

Note:

Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_PERSON else you will see errors. User name is passed as a separate parameter as detailed in this table.

Permission tables

  

Add the names of your permission tables in a comma-separated list. This parameter only applies if your orchestrated system is configured in managed system mode.

Note:

Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_PERMISSION else you will see errors. User name is passed as a separate parameter as detailed in this table.

Account permission tables

  

If you have account data resident in parent and child tables, then provide a comma-separated list of the child tables names.

Note:

Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_ACCOUNTPERMISSION else you will see errors. User name is passed as a separate parameter as detailed in this table.

Lookup tables

  

Comma-separated list of lookup tables for attributes such as country.

Note:

Do not include the user name of the table owner in the table name e.g. MYUSER.MYDBAT_LOOKUP else you will see errors. User name is passed as a separate parameter as detailed in this table.

Key column mappings

Yes

 Comma-separated list of key column mappings. These mappings should be entered in the format Table:KeyColumn.

Note:

This parameter is applicable for ACCOUNT, ENTITLEMENT, and LOOKUP tables only.

Name column mappings

Yes

 Comma-separated list of name column mappings. These mappings should be entered in the format Table:NameColumn.

Note:

This parameter is applicable for ACCOUNT, ENTITLEMENT, and LOOKUP tables only.

User account table password column mapping

  

Password column mapping for user account table in the format Table:PasswordColumn.

User account table status column mapping

 Yes

Status column mapping for the user account table in the format Table:StatusColumn. The status column holds the status of a user record. In case of special values, please configure the enable/disabled value.

User account enabled status value

  

This value will be used as the enable value if the status column is configured, and it is a String type. If no value is provided for this parameter, then it defaults to 'ACTIVE'.

User account disabled status value

  

This value will be used as the disable value if the status column is configured, and it is a String type. If no value is provided for this parameter, then it defaults to 'INACTIVE'.

Date format

  

Format for date data that is being converted to strings. If you want to handle date data as a date editor, then do not enter any value for this parameter. If you want to handle date data as text, then you must enter the date format. Specifying a value for this parameter invalidates the allNative parameter.

Timestamp format

  

Format for timestamp data that is being converted to strings. Specifying this property invalidates the nativeTimestamps and allNative properties

User account filter condition

  

A WHERE clause which defines the subset of user account records that you want to bring from your customer database into Oracle Access Governance.

Create script

  

Custom script to use custom stored procedures or SQL statements rather than the default SQL statements for performing provisioning operations. Enter the file URL of the Groovy script created for the create user account provisioning operation. You must enter the file URL in the following format: /directoryName/fileName.

Sample value:

/app/scripts/create_user.groovy

For further details on scripting with the Database Application Tables (Oracle) integration, see Develop Custom Scripts for Database Application Tables (Oracle) Using Groovy

Update script

  

Custom script to use custom stored procedures or SQL statements rather than the default SQL statements for performing provisioning operations. Enter the file URL of the Groovy script created for the update user account provisioning operation. This script is called when you update the account attribute form, enable or disable the user account. You must enter the file URL in the following format: /directoryName/fileName.

Sample value:

/app/scripts/update_user.groovy

For further details on scripting with the Database Application Tables (Oracle) integration, see Develop Custom Scripts for Database Application Tables (Oracle) Using Groovy

Delete script

  

Custom script to use custom stored procedures or SQL statements rather than the default SQL statements for performing provisioning operations. Enter the file URL of the Groovy script created for the delete user account provisioning operation. This script is called when you revoke or delete an account. You must enter the file URL in the following format: /directoryName/fileName.

Sample value:

/app/scripts/delete_user.groovy

For further details on scripting with the Database Application Tables (Oracle) integration, see Develop Custom Scripts for Database Application Tables (Oracle) Using Groovy

Dataload script

  

Custom script to use custom stored procedures or SQL statements rather than the default SQL statements for performing provisioning operations. Enter the file URL of the Groovy script created for reconciliation. The connector delegates the data load operation to the Groovy script, which is responsible for passing the information (connector object) to the callback handler. This script is called while performing an account search (operations such as full data load). You must enter the file URL in the following format: /directoryName/fileName.

Sample value:

/app/scripts/full_data_load.groovy

For further details on scripting with the Database Application Tables (Oracle) integration, see Develop Custom Scripts for Database Application Tables (Oracle) Using Groovy

Add relationship data script

  

Custom script to use custom stored procedures or SQL statements rather than the default SQL statements for performing provisioning operations. Enter the file URL of the Groovy script created for the add multivalued attribute (including permissions for account) provisioning operation. This script is called when you add multivalued child attributes. You must enter the file URL in the following format: /directoryName/fileName.

Sample value:

/app/scripts/add_mulval_attr.groovy

For further details on scripting with the Database Application Tables (Oracle) integration, see Develop Custom Scripts for Database Application Tables (Oracle) Using Groovy

Remove relationship data script

  

Custom script to use custom stored procedures or SQL statements rather than the default SQL statements for performing provisioning operations. Enter the file URL of the Groovy script created for the remove multivalued attribute (including permissions for account) provisioning operation. This script is called while removing multivalued child attributes. You must enter the file URL in the following format: /directoryName/fileName.

Sample value:

/app/scripts/remove_mulval_attr.groovy

For further details on scripting with the Database Application Tables (Oracle) integration, see Develop Custom Scripts for Database Application Tables (Oracle) Using Groovy
  1. Click Add to create the orchestrated system.

Finish Up

The final step of the workflow is Finish Up where you are prompted to download the agent for your Orchestrated System. Once you have downloaded the agent, you can install and configure the agent in your environment using the instructions in Manage Oracle Access Governance Agent for Indirect Integrations.

You are given a choice whether to further configure your orchestrated system before running a data load, or accept the default configuration and initiate a data load. Select one from:
  • Customize before enabling the system for data loads
  • Activate and prepare the data load with the provided defaults

Post Configuration

Update Intermediate Schema JSON File

When you have completed installation of your agent, an intermediate schema JSON file, schema.json is created on the agent host. This file maps the tables in the integrated database with the schema which is represented on Oracle Access Governance. The initial schema JSON file is created with basic attributes enabled for data load, UID, NAME, STATUS and PASSWORD (if configured by user). The full data load operation can execute with this initial schema JSON file, loading data for only these basic attributes. You can then further modify the schema JSON file to include more attributes for the next data load operations.

Note:

Ensure that you have granted read/write permissions on the schema JSON file for the operating system user that will be running the agent.

For full details on the structure and options available when editing the schema.json, refer to Schema JSON File Reference.

Fetch Latest Custom Attributes

You should perform a schema discovery operation which will fetch the latest custom attribute information. For details on how to perform this task, see Fetch Latest Custom Attributes.

Configuring SSL/TLS Communication in Oracle Database

To secure communication between your Oracle Access Governance agent and the Oracle database, you can configure Secure Sockets Layer/Transport Layer Security (SSL/TLS). To do this, ensure that you have completed the following steps:
  1. Configure Data Encryption and Integrity in Oracle Database

    See Configuring Transport Layer Security Authentication for information about configuring data encryption and integrity.

  2. To configure your Oracle Access Governance agent to use SSL/TLS when communicating with the database, perform the following steps:
    1. Export the certificate on the Oracle Database host computer.
    2. Copy the database certificate to your Oracle Access Governance agent host.
    3. Import the database certificate into the Java truststore of the agent using the command:
      <%JAVA_HOME%>/bin/keytool -import -alias database-cert -file <AD-cert-file> -keystore <agent-install-dir>/cacerts
    4. Update the agent config.properties file to include the following:
      JAVA_OPTS=-Djavax.net.ssl.trustStore=/app/cacerts-Djavax.net.ssl.trustStorePassword=changeit

Configure Wallet for Autonomous Database Integration

A connection to Oracle Autonomous Database requires the client, in this case the Oracle Access Governance agent, to be configured to support SSL communication between the agent and the database service. To enable this feature, you should download the autonomous database wallet to your agent host, and then update the Easy Connect URL for Database field in the orchestrated system configuration. Complete the following steps to configure this feature:

  1. Create a Database User Management (Oracle) orchestrated system and configure the agent.
  2. Download the autonomous database wallet using the instructions in Download Client Credentials (Wallets).
  3. Create a wallet directory on the agent host. For example:
    mkdir /app/db-wallet
  4. Copy the zipfile containing the wallet you downloaded in Step 2, to the wallet folder, and unzip using the command: 
    cp -rf Wallet_<DATABASENAME>.zip /app/db-wallet
  5. The unzipped wallet file will contain the tnsnames.ora file, which contains the service names available for the Oracle Autonomous Database. Choose from one of the following depending on your workload:
    • databasename_tpurgent
    • databasename_tp
    • databasename_high
    • databasename_medium
    • databasename_low
    For further details on Oracle Autonomous Database service names see Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database.
  6. Edit the integration settings for your orchestrated system by following the instructions in Configure settings for an Orchestrated System. Update the Easy Connect URL for Database field with the connect string for your database, based on the service name you selected in the previous step. The connect string should take the following format:
    jdbc:oracle:thin:@<SERVICE_NAME>?TNS_ADMIN=<WALLET-DIR>
    For example:
    jdbc:oracle:thin:@MYAUTDB_TP?TNS_ADMIN=/app/db-wallet