DBMS_MFA_ADMIN Package

The DBMS_MFA_ADMIN package facilitates and configures the SQL Token Access.

Parent topic: Autonomous AI Database Supplied Package Reference

Summary of DBMS_MFA_ADMIN Subprograms

Summarizes the subprograms included in the DBMS_MFA_ADMIN package.

  • REGISTER_USER Procedure
    The DBMS_MFA_ADMIN.REGISTER_USER procedure registers an existing database user for MFA by associating the user with an email address as the external identity. You can enable MFA for logon authentication, SQL access, or both. It supports email, Slack, and authenticator apps for delivering secure access tokens.
  • SET_GLOBAL_TOKEN_ATTRIBUTES Procedure
    The DBMS_MFA_ADMIN.SET_GLOBAL_TOKEN_ATTRIBUTES procedure sets or updates SQL Access Token MFA attributes globally for all registered users.
  • SET_ATTRIBUTE Procedure
    The DBMS_MFA_ADMIN.SET_ATTRIBUTE sets or updates a single MFA attribute for a registered user and overrides any applicable global default for that attribute.
  • SET_ATTRIBUTES Procedure
    The DBMS_MFA_ADMIN.SET_ATTRIBUTES procedure sets or updates one or more MFA attributes for a registered user and overrides any applicable global defaults for those attributes.
  • CONFIGURE_NOTIFICATION Procedure
    The DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION procedure configures the channels through which MFA tokens are sent to registered users. This includes defining the delivery method and the channel-specific properties required for token distribution.
  • DEREGISTER_USER Procedure
    The DBMS_MFA_ADMIN.DEREGISTER_USER procedure removes a database user who is currently registered for MFA. After the user is removed, MFA is no longer enforced for that account, and the user can authenticate without a second factor.

Parent topic: DBMS_MFA_ADMIN Package

REGISTER_USER Procedure

The DBMS_MFA_ADMIN.REGISTER_USER procedure registers an existing database user for MFA by associating the user with an email address as the external identity. You can enable MFA for logon authentication, SQL access, or both. It supports email, Slack, and authenticator apps for delivering secure access tokens.

Syntax

DBMS_MFA_ADMIN.REGISTER_USER (       
       username    IN VARCHAR2,
       type        IN VARCHAR2,
       email       IN CLOB DEFAULT NULL,
       attributes  IN CLOB DEFAULT NULL
 );

Parameters

Parameter Description

username

Specifies the username for which the Multifactor Authentication needs to be enforced.

type

Specifies the MFA attribute that controls when MFA is enforced for the user.

type accepts the following values:

  • LOGON: MFA is required when you log on to the database.

  • SQL ACCESS: MFA is required to perform any SQL or DML operation on the database.

email

Specifies the email address to associate with the user for MFA.

attributes

Specifies MFA attributes in JSON format.

Use attributes to specify the following attributes to generate token:

  • auth_method: Specifies the MFA method used for database user authentication. Valid values for this parameter are:

    • OMA_PUSH: Both Logon and SQL Access MFA are supported.

    • DUO_PUSH: Only Logon MFA is supported.

    • EMAIL: Only SQL access MFA is supported.

    • SLACK: Only SQL access MFA is supported.

    Note:

    For type =>'LOGON', auth_method is the only supported attribute.

  • duration_min: Specifies a positive integer duration, in minutes, for which the token is valid.

    The default value for this attribute is 1440 mins (1 day).

  • idle_timeout_min: Specifies the maximum permitted idle time, in minutes, for a session before the SQL Access Token expires for that session.

    The default value is NULL.

  • read_only: Specifies whether the registered user has read-only SQL query access to the database.

    The default value is FALSE.

    This attribute applies only when scope is set to SESSION.

  • roles: Specifies a JSON Array of database roles that should be assigned to the SQL Access Token generated for the user.

    For example, "roles": JSON_ARRAY('DEVELOPER_ROLE', 'INFRA_ROLE').

    The default value for this attribute is NULL.

  • scope: Specifies the scope at which the SQL Access Token is valid.

    scope accepts the following values:
    • session:

      The validated token grants the SQL access only to the current database session for the duration specified in the duration_min attribute.

    • user:

      The validated token grants SQL access to the current and subsequent database sessions for the duration specified in the duration_min attribute.

    The default value for this attribute is session.

  • enable_authenticator: Specifies that the authenticator app is configured for the user and is used for SQL Access Token.

    The default value for this attribute is FALSE.

  • slack_member_id: Specifies the Slack id where to deliver the SQL Access Token through a Slack message.

Examples

Example to register a user for MFA logon:

BEGIN
    DBMS_MFA_ADMIN.REGISTER_USER(
      user_name        => 'SCOTT',
      type             => 'LOGON',
      email            => 'scott@example.com',
      token_attributes => '{"auth_method":"oma_push"}'
   );
END;
/

Example to register a user for SQL access by setting the MFA attributes.

BEGIN
    DBMS_MFA_ADMIN.REGISTER_USER( 
      user_name   => 'SCOTT',
      type        => 'SQL ACCESS',
      email       => 'scott@example.com',
      attributes  => '{
             "duration_min"   : 720,
             "read_only"      : true,
             "roles"          : ["DEVELOPER_ROLE", "INFRA_ROLE"],
             "scope"          : "SESSION",
             "slack_member_id": "<slack_user_id>"
      }'
   );
END;
/

Usage Notes

  • To register a user you must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package.

  • You can register database users for MFA using different providers depending on the MFA type: Duo and OMA are supported for login-time MFA, while Slack, OMA, and email are supported for SQL Access Token MFA.

  • You can register the same user for both MFA types (MFA Logon and SQL Access) by registering them separately for each. You may use the same or different email address for each registration; once configured, the user receives a push notification to the registered email address at logon, and another notification, to the same address, if used, when the user runs INITIALIZE_SESSION for SQL Access.

Parent topic: Summary of DBMS_MFA_ADMIN Subprograms

SET_GLOBAL_TOKEN_ATTRIBUTES Procedure

The DBMS_MFA_ADMIN.SET_GLOBAL_TOKEN_ATTRIBUTES procedure sets or updates SQL Access Token MFA attributes globally for all registered users.

Syntax

DBMS_MFA_ADMIN.SET_GLOBAL_TOKEN_ATTRIBUTES(       
    attributes IN VARCHAR2
);

Parameters

Parameter Description

attributes

Specifies token attributes in JSON format to generate a Access Token. Valid values are:

  • duration_min: Specifies duration, in minutes, for which the token is valid.

    The default value is 1440 mins (1 day).

  • idle_timeout_min: Specifies the maximum permitted idle time, in minutes, for a session before the SQL Access Token expires for that session.

    The default value is NULL.

  • read_only: Specifies whether the registered user has read-only SQL query access to the database. The default value is FALSE. This attribute applies only when scope is set to SESSION.

  • roles: Specifies a JSON Array of database roles that should be assigned to the SQL Access Token generated for the user.

    For example, "roles": JSON_ARRAY('DEVELOPER_ROLE', 'INFRA_ROLE').

    The default value is NULL.

  • scope: Specifies the scope at which the SQL Access Token is valid.

    Valid values are:

    • session: The validated token grants the SQL access only to the current database session for the duration specified in the duration_min attribute.

    • user: The validated token grants SQL access to the current and subsequent database sessions for the duration specified in the duration_min attribute.

    The default value is session.

  • enable_authenticator: Specifies that the authenticator app is configured for the user and is used for SQL Access Token.

    The default value is FALSE.

Usage Note

  • To set the token attributes you must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package.

Parent topic: Summary of DBMS_MFA_ADMIN Subprograms

SET_ATTRIBUTE Procedure

The DBMS_MFA_ADMIN.SET_ATTRIBUTE sets or updates a single MFA attribute for a registered user and overrides any applicable global default for that attribute.

Note:

For log-in time MFA, auth_method is the only supported attribute.

Syntax

DBMS_MFA_ADMIN.SET_ATTRIBUTE(            
  username         IN VARCHAR2,
  type             IN VARCHAR2,
  email            IN VARCHAR2 DEFAULT NULL,
  attribute_name   IN VARCHAR2,
  attribute_value  IN CLOB
);

Parameters

Parameter Description

username

Specifies the database user whose MFA attribute is to be updated.

type

Specifies the MFA attribute that controls when MFA is enforced for the user.

Valid values are:

  • LOGON: MFA is required when you log in to an Oracle application.

  • SQL ACCESS: MFA is required to perform any SQL or DML operation on the database.

email

Specifies the email address to be associated with the user for MFA.

This parameter is optional and defaults to NULL.

attribute_name
Specifies the name of the MFA attribute to be updated. Valid values are:
  • auth_method: Specifies the MFA method used for database user authentication. Valid values for this parameter are:

    • OMA_PUSH: Both Logon and SQL Access MFA are supported.

    • DUO_PUSH: Only Logon MFA is supported.

    • EMAIL: Only SQL access MFA is supported.

    • SLACK: Only SQL access MFA is supported.

  • duration_min: Specifies duration, in minutes, for which the token is valid. The default value is 1440 mins (1 day).

  • idle_timeout_min: Specifies the maximum permitted idle time, in minutes, for a session before the SQL Access Token expires for that session.

    The default value is NULL.

  • read_only: Specifies whether the registered user has read-only SQL query access to the database.

    The default value is FALSE.

    This attribute applies only when scope is set to SESSION.

  • roles: Specifies a JSON Array of database roles that should be assigned to the SQL Access Token generated for the user.

    For example, "roles": JSON_ARRAY('DEVELOPER_ROLE', 'INFRA_ROLE').

    The default value is NULL.

  • scope: Specifies the scope at which the SQL Access Token is valid.

    Valid values are:
    • session:

      The validated token grants the SQL access only to the current database session for the duration specified in the duration_min attribute.

    • user:

      The validated token grants SQL access to the current and subsequent database sessions for the duration specified in the duration_min attribute.

    The default value for this attribute is session.

  • enable_authenticator: Specifies that the authenticator app is configured for the user and is used for SQL Access Token.

    The default value for this attribute is FALSE.

  • slack_member_id: Specifies the Slack id where to deliver the SQL Access Token through a Slack message.

attribute_value

New value for the specified attribute.

Example

Example to update the MFA setting for user SCOTT:
BEGIN
  DBMS_MFA_ADMIN.SET_ATTRIBUTE(    
    username         => 'scott',
    type             => 'sql access',
    email            => 'testscott@example.com',
    attribute_name   => 'duration_min',
    attribute_value  => '20');
END;
/

Usage Notes

  • The username parameter must specify an existing database user registered for MFA.

  • You must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package to run this procedure.

Parent topic: Summary of DBMS_MFA_ADMIN Subprograms

SET_ATTRIBUTES Procedure

The DBMS_MFA_ADMIN.SET_ATTRIBUTES procedure sets or updates one or more MFA attributes for a registered user and overrides any applicable global defaults for those attributes.

Note:

For log-in time MFA, auth_method is the only supported attribute.

Syntax

DBMS_MFA_ADMIN.SET_ATTRIBUTES (          
   username    IN VARCHAR2,
   type        IN VARCHAR2,
   attributes  IN CLOB
);

Parameters

Parameter Description

username

Specifies the username whose MFA attributes are to be updated.

type

Specifies the MFA attribute that controls when MFA is enforced for the user.

Valid values are:

  • LOGON: MFA is required when you log in to the database.

  • SQL ACCESS: MFA is required to perform any SQL or DML operation on the database.

attributes
Specifies MFA attributes in JSON format. Valid values are:
  • auth_method: Specifies the MFA method used for database user authentication. Valid values for this parameter are:

    • OMA_PUSH: Both Logon and SQL Access MFA are supported.

    • DUO_PUSH: Only Logon MFA is supported.

    • EMAIL: Only SQL access MFA is supported.

    • SLACK: Only SQL access MFA is supported.

  • duration_min: Specifies duration, in minutes, for which the token is valid. The default value is 1440 mins (1 day).

  • idle_timeout_min: Specifies the maximum permitted idle time, in minutes, for a session before the SQL Access Token expires for that session.

    The default value is NULL.

  • read_only: Specifies whether the registered user has read-only SQL query access to the database.

    The default value is FALSE.

    This attribute applies only when scope is set to SESSION.

  • roles: Specifies a JSON Array of database roles that should be assigned to the SQL Access Token generated for the user.

    For example, "roles": JSON_ARRAY('DEVELOPER_ROLE', 'INFRA_ROLE').

    The default value is NULL.

  • scope: Specifies the scope at which the SQL Access Token is valid.

    Valid values are:
    • session:

      The validated token grants the SQL access only to the current database session for the duration specified in the duration_min attribute.

    • user:

      The validated token grants SQL access to the current and subsequent database sessions for the duration specified in the duration_min attribute.

    The default value for this attribute is session.

  • enable_authenticator: Specifies that the authenticator app is configured for the user and is used for SQL Access Token.

    The default value for this attribute is FALSE.

  • slack_member_id: Specifies the Slack id where to deliver the SQL Access Token through a Slack message.

Example

Example to update the MFA setting for user SCOTT:
BEGIN
  DBMS_MFA_ADMIN.SET_ATTRIBUTES(
    username   => 'SCOTT',
    type       => 'SQL ACCESS',
    attributes => json_object(
                    'duration_min'        VALUE 15,
                    'email'               VALUE 'testscott@example.com',
                    'scope'               VALUE 'session',
                    'idle_timeout_min'    VALUE 10)
  );
END;
/

Usage Notes

  • The USERNAME parameter must specify an existing database user registered for MFA.

  • You must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package to run this procedure.

Parent topic: Summary of DBMS_MFA_ADMIN Subprograms

CONFIGURE_NOTIFICATION Procedure

The DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION procedure configures the channels through which MFA tokens are sent to registered users. This includes defining the delivery method and the channel-specific properties required for token distribution.

Syntax

DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION (        
    notification_type  IN VARCHAR2,
    attributes         IN CLOB
);

Parameters

Parameter Description

notification_type

Specifies the type of notification channel to be configured. Valid values are: EMAIL, SLACK, OMA, DUO.

attributes

Provides the configuration details for the specified notification channel in JSON format.

Following are the valid attribute types for each of these notification channel:

EMAIL: Required for MFA logon setup; OTP via email supported only for SQL Access Token MFA

  • credential_name: The name of the email credential object created in the database.

  • sender: The email address of the approved host that will appear as the sender of MFA messages. This address must be permitted by your SMTP server.

  • smtp_host: The fully qualified domain name or IP address of the SMTP server that will send MFA notification emails.

  • smtp_port: The port number used to connect to the SMTP server, typically 587 for TLS or 465 for SSL. This parameter is optional and defaults to 587.

  • sender_email_display_name: The display name that will appear as the sender. This parameter is optional and defaults to NULL.

SLACK: Supported only for SQL Acccess Token MFA

  • credential_name: The name of the credential containing authentication information for sending tokens as Slack notifications.

OMA: Supported for both Logon and SQL Acccess Token MFA

  • credential_name: The name of the credential object that contains authentication information used to securely send MFA notifications through OMA.

  • api_endpoint: The URL of the Oracle Identity and Access Management (IAM) domain associated with this credential. This endpoint identifies the IAM instance used to enroll users in the OCI IAM domain, manage the MFA factor, and process OMA authentication requests, including sending MFA notifications to users.

DUO: Only supported only for Logon MFA:

  • credential_name: The name of the credential object that contains authentication information used to securely send MFA notifications through OMA.

  • api_endpoint: The API hostname provided by Duo Security for your integration, for example, api-xxxxxxxx.duosecurity.com..

Examples

Example: Configure EMAIL notification channel

BEGIN
    DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION (
    notification_type => 'EMAIL',
    attributes        => JSON_OBJECT(
                          'credential_name'           VALUE 'EMAIL_CRED',
                          'smtp_host'                 VALUE 'smtp.email.us-phoenix-1.oci.oraclecloud.com',
                          'sender'                    VALUE 'send.email@test.com',
                          'smtp_port'                 VALUE 587,
                          'sender_email_display_name' VALUE 'DB SECURITY')
  );
END;
/

Example: Configure Slack notification channel

BEGIN
  DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION(
        notification_type => 'SLACK',
        attributes             => JSON_OBJECT('credential_name' VALUE 'SLACK_CRED')
  );
END;
/

Example: Configure OMA notification channel

BEGIN
  DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION(
    notification_type => 'OMA',
    attributes        => JSON_OBJECT(
                                     'credential_name' VALUE 'OMA_CRED_01',
                                     'api_endpoint'    VALUE 'https://idcs-xyz.identity.oraclecloud.com'
    )
  );
END;
/

Example: Configure DUO notification channel

BEGIN
  DBMS_MFA_ADMIN.CONFIGURE_NOTIFICATION(
    notification_type => 'OMA',
    attributes        => JSON_OBJECT(
                                      'credential_name' VALUE 'OMA_CRED_01',
                                      'api_endpoint'    VALUE 'https://idcs-xyz.identity.oraclecloud.com'
    )
  );
END;
/

Usage Notes

  • To run this procedure you must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package.

  • For Slack notifications, set the required credential and confirm that Slack integration is enabled in your environment. Slack notifications are supported only for SQL access MFA operations.

    • To set the token attributes you must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package.

    • For Authenticator App–based Tokens:

      • Install a supported authenticator app on the mobile device you intend to register for MFA.

      • You must set up MFA using a device that you can access every time you sign in. MFA cannot be enabled on your behalf by another user.

      • To enable MFA, use the authenticator app on your mobile device to scan the QR code generated in the Database Actions UI. See Configuring the Oracle Mobile Authenticator for more information.

        You can register only one mobile device for MFA.

    • For Email-based Token delivery, ensure that email delivery is enabled and that valid email addresses are configured for database users. See Send Email on Autonomous AI Database for more information.
    • For Slack-based Token delivery, configure the Slack plugin and register the user’s Slack ID for OTP delivery. See Send Slack Notifications from Autonomous AI Database for more information.

Parent topic: Summary of DBMS_MFA_ADMIN Subprograms

DEREGISTER_USER Procedure

The DBMS_MFA_ADMIN.DEREGISTER_USER procedure removes a database user who is currently registered for MFA. After the user is removed, MFA is no longer enforced for that account, and the user can authenticate without a second factor.

Syntax

PROCEDURE DBMS_MFA_ADMIN.DEREGISTER_USER (
  username IN VARCHAR2,
  type     IN VARCHAR2,
  email    IN VARCHAR2 DEFAULT NULL
);

Parameters

Parameter Description

username

Specifies the username for which the Multifactor Authentication needs to be removed.
type

Specifies the MFA attribute that controls when MFA is enforced for the user. Valid values are:

  • LOGON: MFA is required when you log in to the database.
  • SQL ACCESS: MFA is required to perform any SQL or DML operation on the database.
email

Specifies the email address associated with the user for MFA.

This parameter is optional and defaults to NULL.

Examples

BEGIN
  DBMS_MFA_ADMIN.DEREGISTER_USER(
    username => 'SCOTT',
    type     => 'SQL ACCESS',
    email    => 'scott@example.com'
  );
END;
/

This example deregisters user SCOTT from MFA for the specified type (SQL ACCESS) and removes the associated email identity (scott@example.com) from the MFA configuration.

Usage Note

  • To run this procedure you must be logged in as the ADMIN user or have privileges on the DBMS_MFA_ADMIN package.

  • If a user is registered with multiple email addresses, unregister each email address individually.

Parent topic: Summary of DBMS_MFA_ADMIN Subprograms