9.2 Configure the Database for Local End-User Authentication

Complete the required configuration in your database to implement Oracle Deep Data Security (Deep Sec) when your end users are managed locally rather than through an IAM system. Your specific configuration steps vary depending on whether your local end users connect through an application or establish direct database sessions with password authentication.

9.2.1 Set Up Local Authentication for Application-Mediated Connections

If your application maintains its own user store (for example, a user registry in a separate database, LDAP directory, or an application-managed identity system) and those users connect to the database through the application, use the database configuration detailed here to implement Deep Sec, without requiring the users to have IAM accounts.

In this scenario, the local end user is identified by a user name and an optional security context lookup key supplied by the application.

Even though the end users are managed locally, the application must still be registered in your IAM system and must obtain a database-access token to authorize its own connection to the database. A TLS-secured connection, either a connection pool user account or an application identity, and the identity provider configuration in the database are therefore required. For application registration in IAM, see Register the Application in Microsoft Entra ID and Register the Application in OCI IAM.

  1. Configure the database for application sessions.
    Complete the TLS, connection pool user account, and identity provider setup described in Set Up IAM Integration for Application-Mediated Connections. The configuration for this scenario is identical.
  2. Create end users in the database.
    Provision end users with user names that match the users in the application’s user store. Because these users authenticate to the database through the application’s trust rather than directly, create the end-user accounts without a password.
    CREATE END USER emma;
    For the complete syntax and options, see Configure Local End Users.
  3. Create data roles that are managed locally in the database.
    Create the required data roles to enable within your end-user security contexts. See Configure Data Roles.
    CREATE DATA ROLE employee_role;
  4. Grant the data roles to end users.
    Explicitly grant appropriate data roles to your end-user accounts. See Grant and Revoke Data Roles.
    GRANT DATA ROLE employee_role TO emma;
  5. Perform optional additional configuration.
    The following tasks are not mandatory to establish a basic end-user security context, but may be required depending on your application’s authorization model.
    1. Create end-user context definitions.
      If your application logic or data grants rely on custom end-user context attributes, create the corresponding END USER CONTEXT schema objects before deployment. See Configure End-User Contexts and Attributes.
    2. Create the application identity.
      Set up a database identity for your application that matches its IAM client ID. See Configure Application Identities.
    3. Grant additional data roles to the application identity.
      Explicitly grant any additional or common data roles that your application is allowed to activate. If you skip this step, the database silently ignores the application’s requests for ungranted roles. See Grant and Revoke Data Roles.

9.2.2 Set Up Local Authentication for Direct Logon

For environments where local end users establish direct database sessions through a SQL client using credential-based authentication, perform the following configuration tasks to implement Deep Sec.

  1. Create end users in the database.
    For this scenario, create end-user accounts with password authentication enabled.
    CREATE END USER emma IDENTIFIED BY <password>;

    To enforce multifactor authentication (MFA), complete the MFA provider and SMTP prerequisites before creating or altering an end user. See Set Up Multifactor Authentication for Local End Users.

    For the complete syntax and options, see Configure Local End Users.

  2. Create data roles that are managed locally in the database.
    Create the required data roles to enable within your end-user security contexts. See Configure Data Roles.
    CREATE DATA ROLE employee_role;
  3. Create and grant a generic database role.
    Create a generic database role and grant it the CREATE SESSION privilege. Then grant this generic role to your data role used for direct logon. See Grant Database Role to Data Role.
    CREATE ROLE db_role;
GRANT CREATE SESSION TO db_role;

GRANT db_role TO employee_role;
  4. Grant the data roles to end users.
    Explicitly grant appropriate data roles to your end-user accounts. See Grant and Revoke Data Roles.
    GRANT DATA ROLE employee_role TO emma;

9.2.2.1 Set Up Multifactor Authentication for Local End Users

After you set up password-based direct logon, you can optionally require multifactor authentication (MFA) for local end users. The end user must enter a valid password and approve the second authentication factor before the database establishes the session.

Deep Sec supports push notification authenticators as a second factor for local end users. The two supported authenticators are:
  • Cisco Duo, which uses the duo_push factor.
  • Oracle Mobile Authenticator (OMA), which uses the oma_push factor.

On Oracle AI Database, the MFA configuration is specific to the pluggable database (PDB); the initialization parameters and wallet secrets apply only to the PDB in which you configure them. On Oracle Autonomous AI Database, the notification and provider configuration applies to the database to which the ADMIN user is connected.

After you complete the prerequisites in this section, assign the second factor to an end user with the CREATE END USER or ALTER END USER statement. See Create End User and Alter End User.

Note:

  • On Oracle AI Database, run the SQL and PL/SQL statements in this section as a named user with the DBA role, and run the statements that create credential objects as SYS. Connect to the target PDB before applying configuration changes.
  • On Oracle Autonomous AI Database, run the SQL and PL/SQL statements as the ADMIN user. Use DBMS_CLOUD to create credentials and DBMS_MFA_ADMIN to configure email and authenticator notifications as shown in this section.

MFA initialization parameters (Oracle AI Database only)

Before you configure MFA with Cisco Duo or OMA on Oracle AI Database, review the required initialization parameters.

Set all MFA initialization parameters for each PDB. Use ALTER SYSTEM to modify these dynamic parameters. The database stores these parameters as strings, except for MFA_SMTP_PORT, which is an integer.

Parameter Type Default Description

MFA_DUO_API_HOST

String

None

Host name of the Cisco Duo Auth API, used to enroll users and manage the Duo factor.

Example value: api-<id>.duosecurity.com.

MFA_OMA_IAM_DOMAIN_URL

String

None

OCI IAM identity domain URL, used to enroll users in the OCI IAM domain and manage the OMA factor.

Example value: https://idcs-<id>.identity.oraclecloud.com.

MFA_SMTP_HOST

String

None

SMTP server that sends the device-registration email during enrollment.

MFA_SMTP_PORT

Integer

587

SMTP server port. Valid values are 25 to 587.

MFA_SENDER_EMAIL_ID

String

None

Email address that appears in the From header of enrollment emails.

MFA_SENDER_EMAIL_DISPLAYNAME

String

None

Display name that appears in the From header of enrollment emails.

Additional Oracle AI Database configuration notes

  • Provider secrets in the server wallet

    Before you configure a provider, ensure that the required secrets are present in the PDB server wallet. For instructions, see Complete common prerequisites.

    Use the following provider secrets:

    • Cisco Duo: To access Duo APIs, provide the Duo integration key and Duo secret key. Store the integration key in the oracle.security.mfa.duo.integrationkey alias and the secret key in the oracle.security.mfa.duo.secretkey alias.
    • Oracle Mobile Authenticator (OMA): To access OCI IAM administration APIs for OMA, provide the client ID and client secret for the database application registration in the OCI IAM identity domain. Store the client ID in the oracle.security.mfa.oma.clientid alias and the client secret in the oracle.security.mfa.oma.clientsecret alias. The client ID must be assigned the User Administrator and MFA Client roles.

  • Server wallet locations

    The database searches for the PDB server wallet in this order:
    • <WALLET_ROOT>/[pdb_guid]/mfa
    • <WALLET_LOCATION>/[pdb_guid]
    • <ORACLE_BASE|ORACLE_HOME>/admin/<db_unique_name>/[pdb_guid]/wallet

    For the container database (CDB) root container, the database searches for the server wallet in this order:

    • <WALLET_ROOT>/mfa
    • <WALLET_LOCATION>
    • <ORACLE_BASE|ORACLE_HOME>/admin/<db_unique_name>/wallet

Order of configuration tasks:

Complete common prerequisites

Complete the following prerequisite tasks once for each target PDB on Oracle AI Database or once for each Oracle Autonomous AI Database. The tasks apply to Cisco Duo and OMA.

  1. Store Simple Mail Transfer Protocol (SMTP) credentials
    During enrollment, the database sends a device-registration email to the end user through an SMTP server.
    • On Oracle AI Database, if the SMTP server requires authentication, store the SMTP user name and password in the SYS.MFA_SMTP_CREDENTIAL$ credential object. Run the following PL/SQL block as SYS.
      BEGIN
  DBMS_CREDENTIAL.CREATE_CREDENTIAL(
     credential_name => 'MFA_SMTP_CREDENTIAL$',
     username        => '<SMTP_username>',
     password        => '<SMTP_password>'
  );
END;
/
    • On Oracle Autonomous AI Database, create a credential for the SMTP user name and password. Run the following block as ADMIN.
      BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'EMAIL_CRED',
    username        => '<SMTP_username>',
    password        => '<SMTP_password>'
  );
END;
/
  2. Configure SMTP notification settings
    • On Oracle AI Database, connect to the target PDB and set the SMTP initialization parameters.
      ALTER SESSION SET CONTAINER = <your-target-PDB>;
 
ALTER SYSTEM SET MFA_SMTP_HOST = 'smtp.email.us-region-1.oci.oraclecloud.com' SCOPE=BOTH;
ALTER SYSTEM SET MFA_SMTP_PORT = 587 SCOPE=BOTH;
ALTER SYSTEM SET MFA_SENDER_EMAIL_ID = 'database_mfa_registration@supremo.com' SCOPE=BOTH;
ALTER SYSTEM SET MFA_SENDER_EMAIL_DISPLAYNAME = 'Database MFA Registration' SCOPE=BOTH;

      The MFA_SMTP_PORT parameter defaults to 587. Set this parameter only when your SMTP server uses a different port in the valid range of 25 through 587.

    • On Oracle Autonomous AI Database, configure the email notification through DBMS_MFA_ADMIN. The credential_name attribute refers to the credential created in the previous step.
      BEGIN
  DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION(
    notification_type => 'EMAIL',
    attributes        => JSON_OBJECT(
      'credential_name'           VALUE 'EMAIL_CRED',
      'smtp_host'                 VALUE '<SMTP_host>',
      'sender'                    VALUE '<sender_email_address>',
      'smtp_port'                 VALUE 587,
      'sender_email_display_name' VALUE '<sender_display_name>'
    )
  );
END;
/
  3. Set the inbound connection timeout (Oracle AI Database only)
    Set the SQLNET.INBOUND_CONNECT_TIMEOUT parameter in the database server's sqlnet.ora file to a value greater than 60 seconds. This value gives the end user enough time to approve the push notification during sign-in.
    SQLNET.INBOUND_CONNECT_TIMEOUT=120
  4. Trust the SMTP server certificate (Oracle AI Database only)

    During enrollment, the database server makes an outbound connection to the SMTP server to send the device-registration email. If the database host doesn't trust the SMTP server certificate chain, add the root certificate for the certificate authority to the database server certificate store.

  5. Create the server wallet for provider secrets (Oracle AI Database only)

    Create the wallet that stores the Cisco Duo and OMA secrets in the PDB-specific MFA wallet location.

    First, determine the PDB GUID.
    SELECT guid FROM v$containers
  WHERE name = SYS_CONTEXT('USERENV', 'CON_NAME');
    Then, create the wallet in the WALLET_ROOT location. Substitute the GUID value for <pdb_guid>:
    mkdir -p <WALLET_ROOT>/<pdb_guid>/mfa
cd <WALLET_ROOT>/<pdb_guid>/mfa
orapki wallet create -wallet ./ -pwd <wallet_password> \
  -auto_login -compat_v12

Configure Cisco Duo

In addition to the common prerequisites, complete these tasks to enable Cisco Duo as a second authentication factor.

  1. Configure the Duo API endpoint and credentials
    • On Oracle AI Database, in the target PDB, set MFA_DUO_API_HOST to your Cisco Duo Auth API host name.
      ALTER SYSTEM SET MFA_DUO_API_HOST = '<Duo_API_host>' SCOPE=BOTH;
      In the PDB-specific MFA wallet location, store the Duo integration key and secret key under their required aliases. For details about the integration key and secret key, see MFA initialization parameters.
      orapki secretstore create_entry -wallet ./ -pwd <wallet_password> \
  -alias oracle.security.mfa.duo.integrationkey -secret <integration_key>
 
orapki secretstore create_entry -wallet ./ -pwd <wallet_password> \
  -alias oracle.security.mfa.duo.secretkey -secret <secret_key>
    • On Oracle Autonomous AI Database, create the Duo credential and configure the Duo notification endpoint as follows.
      BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DUO_CRED',
    username        => '<Duo_integration_key>',
    password        => '<Duo_secret_key>'
  );
END;
/

BEGIN
  DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION(
    notification_type => 'DUO',
    attributes        => JSON_OBJECT(
      'credential_name' VALUE 'DUO_CRED',
      'api_endpoint'    VALUE '<Duo_API_host>'
    )
  );
END;
/
  2. Enroll local end users with Duo
    After you complete the Duo configuration, create or alter a local end user with the duo_push factor. The following example creates a local end user with Duo Push as the second factor:
    CREATE END USER emma
  IDENTIFIED BY <password>
  AND FACTOR 'duo_push' AS 'emma.baker@supremo.com';

    When you create or alter a local end user with the duo_push factor, the database calls the Duo Auth APIs, enrolls the user in the Duo domain, and sends an email that contains a device-registration link to the user's registered email address. The user installs the Duo Mobile application and uses the link in the email to register the device.

    After device registration, the end user signs in to the database with their password. When the database validates the password, it sends a push notification to the Duo Mobile application through Duo Auth APIs. When the end user approves the notification, MFA is complete and the database establishes the session.

Configure Oracle Mobile Authenticator

In addition to the common prerequisites, complete these steps to enable OMA as a second authentication factor.

  1. Configure the OCI IAM endpoint and credentials

    The OMA client ID must be assigned the User Administrator and MFA Client roles in the OCI IAM identity domain.

    • On Oracle AI Database, in the target PDB, set MFA_OMA_IAM_DOMAIN_URL to your OCI IAM identity domain URL.
      ALTER SYSTEM SET MFA_OMA_IAM_DOMAIN_URL = '<OCI_IAM_identity_domain_URL>' SCOPE=BOTH;
      In the PDB-specific MFA wallet location, store the OAuth client ID and client secret under their required aliases. For details about the client ID and client secret, see MFA initialization parameters.
      orapki secretstore create_entry -wallet ./ -pwd <wallet_password> \
  -alias oracle.security.mfa.oma.clientid -secret <OMA_client_id>
 
orapki secretstore create_entry -wallet ./ -pwd <wallet_password> \
  -alias oracle.security.mfa.oma.clientsecret -secret <OMA_client_secret>
    • On Oracle Autonomous AI Database, create the OMA credential and configure the OMA notification endpoint as follows.
      BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OMA_CRED',
    username        => '<OMA_client_id>',
    password        => '<OMA_client_secret>'
  );
END;
/

BEGIN
  DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION(
    notification_type => 'OMA',
    attributes        => JSON_OBJECT(
      'credential_name' VALUE 'OMA_CRED',
      'api_endpoint'    VALUE '<OMA_API_endpoint>'
    )
  );
END;
/
  2. Enroll local end users with OMA
    After you complete the OMA configuration, create or alter a local end user with the oma_push factor. The following example creates a local end user with OMA as the second factor:
    CREATE END USER emma
  IDENTIFIED BY <password>
  AND FACTOR 'oma_push' AS 'emma.baker@supremo.com';

    When you create or alter a local end user with the oma_push factor, the database calls the OCI IAM administration APIs, enrolls the user in the configured OCI IAM identity domain, and sends an email that contains a device-registration link to the user's registered email address. The user installs the OMA application and uses the link in the email to register the device.

    After device registration, the end user signs in to the database with their password. When the database validates the password, it sends a push notification to the OMA application through OCI IAM on-demand MFA APIs. When the end user approves the notification, MFA is complete and the database establishes the session.