Database User Management

Before You Begin

Introduction

This document describes how to synchronize users, roles, privileges and profiles between Oracle Database and Oracle Identity Cloud Service.

About Oracle Database

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multi-user environment so that many users can concurrently access the same data.

After integrating Oracle Database and Oracle Identity Cloud Service, administrators can use the Identity Cloud Service console to:

  • Synchronize users from Oracle Database to Oracle Identity Cloud Service.
  • Assign and revoke user access to Oracle Database.
  • Manage Database users, roles, privileges, tablespaces, temporary tablespaces and profiles.

What Do You Need?

  • An Oracle Identity Cloud Service account with authorization rights to manage apps and users (by being assigned to the identity domain administrator, security administrator, or application administrator role).
  • An Oracle Database account with authorization rights to manage users, roles, privileges, tablespaces, temporary tablespaces and profiles.

Prerequisite Steps

Before you can synchronize users, roles, and responsibilities between Oracle Database and Oracle Identity Cloud Service, you must:

  • Install and configure a Provisioning Bridge in Oracle Identity Cloud Service. This bridge provides a link between Oracle Database and Oracle Identity Cloud Service. Oracle Identity Cloud Service can synchronize with Oracle Database so that any new, updated, or deleted users are transferred into Oracle Identity Cloud Service. See the Manage Provisioning Bridges for Oracle Identity Cloud Service chapter in Administering Oracle Identity Cloud Service.
  • Use the App Catalog to add the Oracle Database User Management app to Oracle Identity Cloud Service. The Provisioning Bridge uses this app to synchronize, import, and provision manage users, roles, privileges, tablespaces, temporary tablespaces and profiles between Oracle Database and Oracle Identity Cloud Service. See Add an App Catalog Application in Administering Oracle Identity Cloud Service.

Create a User in Oracle Database

Use this section to create a user in Oracle Database that the Provisioning Bridge can use to synchronize, import, and provision users between Oracle Database and Oracle Identity Cloud Service. Oracle Identity Cloud Service requires this user to access Oracle Database. You provide the credentials of this user while performing the procedure described in Enable Provisioning and Synchronization for Oracle Database.

Login to the oracle database sys as sysdba and create a user using below database commands.

Create a Login

Create Login using the following query: CREATE USER serviceuser IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;

Assign Permissions and Roles

Assign the following permissions and roles to the created user: GRANT CONNECT TO serviceuser; GRANT SELECT on dba_role_privs TO serviceuser; GRANT SELECT on dba_sys_privs TO serviceuser; GRANT SELECT on dba_ts_quotas TO serviceuser; GRANT SELECT on dba_tablespaces TO serviceuser; GRANT SELECT on dba_users TO serviceuser; GRANT CREATE USER TO serviceuser; GRANT ALTER ANY TABLE TO serviceuser; GRANT ANY PRIVILEGE TO serviceuser; GRANT GRANT ANY ROLE TO serviceuser; GRANT DROP USER TO serviceuser; GRANT SELECT on dba_roles TO serviceuser; GRANT SELECT ON dba_profiles TO serviceuser; GRANT ALTER USER TO serviceuser; GRANT CREATE ANY TABLE TO serviceuser; GRANT DROP ANY TABLE TO serviceuser; GRANT CREATE ANY PROCEDURE TO serviceuser; REVOKE DROP ANY PROCEDURE TO serviceuser;

Enable Provisioning and Synchronization for Oracle Database

In this section, you enable provisioning and synchronization for managing users in Oracle Database through Oracle Identity Cloud Service. You do this by assigning the Provisioning Bridge that you installed and configured to the on-premises Oracle Database app in Oracle Identity Cloud Service. This bridge serves as a provisioning and synchronizing agent between Oracle Identity Cloud Service and the app.

Note: You installed and configured a Provisioning Bridge in Oracle Identity Cloud Service and you added the Oracle Database app to Oracle Identity Cloud Service in Prerequisite Steps.

Enable Provisioning

  1. From the Identity Cloud Service console that you're accessing as an identity domain administrator, security administrator, or application administrator, expand the Navigation Drawer, and then click Applications.
  2. Click the Oracle Database app that you added in Prerequisite Steps.
  3. Click Provisioning.
  4. Turn on the Enable Provisioning option.
  5. In the Confirmation window, click OK.
  6. From the Associate with Provisioning Bridge list, select the Provisioning Bridge that you installed and configured in Prerequisite Steps. Note: If the Provisioning Bridge has an inactive status, then activate it. See Activate Provisioning Bridges in Administering Oracle Identity Cloud Service.

  7. In the Configure Connectivity section, use the following table to configure connectivity for establishing a connection with the Oracle Database app:

    Parameter Value
    Administrator Username Enter the user name of the Oracle Database user that you created in Create a User in Oracle Database.
    Administrator Password Enter the password of the Oracle Database user that you created in Create a User in Oracle Database.
    JDBC URL template Enter the JDBC connection string.
  8. Click Test Connectivity to verify the connection with the Oracle Database app. Oracle Identity Cloud Service displays a confirmation message.
  9. To view predefined attribute mappings between the users in Oracle Identity Cloud Service and the users in Oracle Database, click Attribute Mapping, and then click OK.

  10. Use the following table to see the default attribute mappings when users are provisioned from Oracle Identity Cloud Service to Oracle Database.

    Oracle Identity Cloud Service User Oracle Database User
    $(user.userName) Name
    #random_password($(user.id)) password
    $(user.active) Active
    Password authType

  11. Specify the provisioning operations that you want to enable for Oracle Database:

    Note: By default, the Create Account, Update Account, De-activate Account, and Delete Account check boxes are selected.

    • Create Account: Automatically creates an Oracle Database user when Oracle Database access is granted to the corresponding user in Oracle Identity Cloud Service.
    • Update Account: Automatically updates an Oracle Database user when the corresponding user is updated in Oracle Identity Cloud Service.
    • De-activate Account: Automatically activates or deactivates the Oracle Database user when the corresponding user is activated or deactivated in Oracle Identity Cloud Service.
    • Delete Account: Automatically revokes a user from Oracle Database when Oracle Database access is revoked from the corresponding user in Oracle Identity Cloud Service.

Enable Synchronization

  1. In the Provisioning page, turn on the Enable Synchronization option.
  2. From the User Identifier drop-down list, select the Oracle Identity Cloud Service user attribute that you want to match with the corresponding record fetched from Oracle Database:
    • Primary Email Address: Primary email address of the Oracle Identity Cloud Service user.
    • User Name: User name of the Oracle Identity Cloud Service user.
    Note: By default, the User Name option is selected from the drop-down list. Leave this default attribute for accurate synchronization of user records.
  3. To match an Oracle Database user attribute with the existing Oracle Identity Cloud Service user, select an attribute from the Application Identifier drop-down list. Note: By default, the name option is selected. This option represents the username attribute of the Oracle Database user. Don't change this default option.
  4. From the When exact match is found drop-down list, select one of the following actions to be performed when a matching Oracle Identity Cloud Service user is found for an Oracle Database user:
    • Link and confirm: Automatically links and confirms the matched Oracle Database user to the corresponding Oracle Identity Cloud Service user based on the defined User Identifier and Application Identifier fields.
    • Link but do not confirm: Automatically links all matched Oracle Database users to the corresponding Oracle Identity Cloud Service users based on the defined User Identifier and Application Identifier fields. You need to confirm the linked users manually.
  5. In the Max. number of creates field, enter a number that's greater than or equal to 10. This value limits the number of accounts to be created during the synchronization run.
  6. In the Max. number of deletes field, enter a number that's greater than or equal to 10. This value limits the number of accounts to be deleted during the synchronization run.
  7. To specify a schedule for the synchronization, from the Synchronization Schedule drop-down list, select Never, Every Hour, Every Day, or Every Week. You can manage Oracle Database users through Oracle Identity Cloud Service. For more information on performing provisioning tasks, see the Managing Oracle Identity Cloud Service Users and Managing Oracle Identity Cloud Service Groups chapters in Administering Oracle Identity Cloud Service.
  8. Click Save, Activate, and then, in the Confirmation window, click OK. Oracle Identity Cloud Service displays a confirmation message.