Authenticate and Authorize Microsoft Azure Active Directory Users for Autonomous Database

APPLIES TO: Applicable Oracle Public Cloud only

An Oracle Autonomous Database on Dedicated Exadata Infrastructure instance can be configured for Microsoft Azure AD users to connect using Azure OAuth2 access tokens.

About Integrating Oracle Autonomous Database on Dedicated Exadata Infrastructure with Microsoft Azure AD

Oracle Autonomous Database on Dedicated Exadata Infrastructure and Microsoft Azure AD can be configured to allow users and applications to connect to the database using their Azure AD credentials.

Azure AD users and applications can log in with Azure AD Single Sign On (SSO) credentials to access the database. This is done with an Azure AD OAuth2 access token that the user or application first requests from Azure AD. This OAuth2 access token contains the user identity and database access information and is then sent to the database. Refer to Refer to the Microsoft article Passwordless authentication options for Azure Active Directory for information about configuring multi-factor and passwordless authentication.

You can perform this integration in the following Oracle Database environments:

  • On-premises Oracle Database release 19.18 and later
  • Oracle Autonomous Database on Shared Exadata Infrastructure
  • Oracle Autonomous Database on Dedicated Exadata Infrastructure
  • Oracle Base Database Service
  • Oracle Exadata Cloud Service (Oracle ExaCS)

The instructions for configuring Azure AD use the term "Oracle Database" to encompass these environments.

This type of integration enables the Azure AD user to access an Oracle Autonomous Database on Dedicated Exadata Infrastructure instance. Azure AD users and applications can log in with Azure AD Single Sign On (SSO) credentials to get an Azure AD OAuth2 access token to send to the database.

The Azure AD administrator creates and registers Oracle Autonomous Database on Dedicated Exadata Infrastructure with Azure AD. Within Azure AD, this is called an app registration, which is short for application registration. This is the digital information that Azure AD must know about the software that is using Azure AD. The Azure AD administrator also creates application (app) roles for the database app registration in Azure AD. App roles connect Azure users, groups, and applications to database schemas and roles. The Azure AD administrator assigns Azure AD users, groups, or applications to the app roles. These app roles are mapped to a database global schema or a global role or to both a schema and a role. An Azure AD user, group, or application that is assigned to an app role will be mapped to a database global schema, global role, or to both a schema and a role. An Oracle global schema can also be mapped exclusively to an Azure AD user. An Azure AD guest user (non-organization user) or an Azure AD service principal (application) can only be mapped to a database global schema through an Azure AD app role. An Oracle global role can only be mapped from an Azure app role and cannot be mapped from an Azure user.

Oracle Autonomous Database on Dedicated Exadata Infrastructure tools including Oracle APEX, Database Actions, Oracle Graph Studio, and Oracle Database API for MongoDB are not compatible with using Azure AD tokens to connect with the database.

Tools and applications that are updated to support Azure AD tokens can authenticate users directly with Azure AD and pass the database access token to the Oracle Autonomous Database on Dedicated Exadata Infrastructure instance. You can configure existing database tools such as SQL*Plus to use an Azure AD token from a file location. In these cases, Azure AD tokens can be retrieved using tools like Microsoft PowerShell or Azure CLI and put into a file location. An Azure AD OAuth2 database access tokens are issued with an expiration time. The Oracle Database client driver will ensure that the token is in a valid format and that it has not expired before passing it to the database. The token is scoped for the database, which means that there is information in the token about the database where the token will be used. The app roles the Azure AD principal was assigned to in the database Azure AD app registration are included as part of the access token. The directory location for the Azure AD token should only have enough permission for the user to write the token file to the location and the database client to retrieve these files (for example, just read and write by the user). Because the token allows access to the database, it should be protected within the file system.

Azure AD users can request a token from Azure AD using a number of methods to open an Azure login window to enter their Azure AD credentials.

Oracle Autonomous Database on Dedicated Exadata Infrastructure accepts tokens representing the following Azure AD principals:

  • Azure AD user, who is registered user in the Azure AD tenancy
  • Guest user, who is registered as a guest user in the Azure AD tenancy
  • Service, which is the registered application connecting to the database as itself with the client credential flow (connection pool use case)

Oracle Autonomous Database on Dedicated Exadata Infrastructure supports the following Azure AD authentication flows:

  • Authorization code, most commonly used for human users (not applications) to authenticate to Azure AD in a client environment with a browser
  • Client credentials, which are for database applications that connect as themselves (and not the end-user)
  • On-Behalf-Of (OBO), where an application requests an access token on behalf of a logged-in user to send to the database
  • Resource owner password credential (ROPC), which is not recommended for production use, but can be used in test environments where a pop-up browser user authentication would be difficult to incorporate. ROPC needs the Azure AD user name and password credential to be part of the token request call.

Architecture of the Microsoft Azure AD Integration with an Autonomous Database

Microsoft Azure Active Directory tokens follow the OAuth2 standard with extensions. Using an Azure AD token to access an Oracle database is similar to using OCI IAM tokens. See Architecture of the Microsoft Azure AD Integration with an Oracle Database in Security Guide for a detailed explanation.

Azure AD Users Mapping to the Autonomous Database

Microsoft Azure users must be mapped to an Autonomous Database schema and have the necessary privileges (through roles) before being able to authenticate to the Autonomous Database instance. See Azure AD Users Mapping to the Oracle Database in Security Guide for information about the different ways of mapping users, groups, and application in Microsoft Azure.

Use Cases for Connecting to an Autonomous Database Using Azure AD

Oracle Database supports three types of use cases for connecting to an Autonomous Database instance using Microsoft Azure Active Directory. See Use Cases for Connecting to an Oracle Database Using Azure AD for more details.

General Process of Authenticating Microsoft Azure AD Identities with Oracle Autonomous Database on Dedicated Exadata Infrastructure

The Oracle Database administrator and the Microsoft Azure AD administrator play roles to allow Azure AD users to connect to the database using Azure AD OAuth2 access tokens.

The general process is as follows:

  1. The Oracle Database administrator ensures that the Oracle Database environment meets the requirements for the Microsoft Azure AD integration. See Oracle Database Requirements for the Microsoft Azure AD Integration.
  2. The Azure AD administrator creates an Azure AD app registration for the database and the Oracle Database administrator enables the database to use Azure AD tokens for database access.

    As part of the app registration process, the Azure AD administrator creates Azure app roles to be used for the mappings between the Azure users, groups, and applications to the Oracle Database schemas and roles.

  3. The Oracle Database administrator creates and maps global schemas to either an Azure AD user (exclusive schema mapping) or to an Azure app role (shared schema mapping). The Azure AD user or application must be mapped to one schema.
  4. Optionally, the Oracle administrator creates and maps global Oracle Database roles to Azure app roles.
  5. The Azure AD end user who wants to connect with the Oracle Autonomous Database on Dedicated Exadata Infrastructure instance registers the client application as an Azure AD client (similar to how the Oracle database is registered).

    The Azure AD client will have a client identification and a client secret, unless the application client is public. If the application client is public, then only the application client identification is necessary.

  6. The Azure AD end user (who can be a database administrator) connects using an utility such as PowerShell or the Azure command-line interface to retrieve the OAuth2 database access token and store it in a local file directory. An application can also request an Azure AD OAuth2 access token directly from Azure AD and pass it through a database client API. Refer to the following Oracle Database client documentation for information about passing Azure AD OAuth2 tokens:
  7. Once connected to the Oracle Autonomous Database on Dedicated Exadata Infrastructure instance, the Azure AD end user performs database operations as needed.

Enable Azure AD Authentication on Autonomous Database

An Azure AD administrator and an Autonomous Database administrator perform steps to configure Azure AD authentication on Autonomous Database.

Prerequisites

The Microsoft Azure AD integration with the Oracle Autonomous Database on Dedicated Exadata Infrastructure requires:

  1. The Autonomous Database to be version 19.18 or higher.

  2. Connectivity to the database on TLS port 2484. Non TLS connections are not supported.

  3. Outbound network connectivity to Azure AD so that the database can request the Azure AD public key.

  4. The Autonomous Database to be registered with Azure AD.

Procedure

Implement the following tasks to configure your Autonomous Database for Microsoft Azure AD integration.

  1. Register the Autonomous Database instance with a Microsoft Azure AD tenancy: A user with Azure AD administrator privileges uses Microsoft Azure AD to register the Oracle Database instance with the Microsoft Azure AD tenancy. Refer to Register the Oracle Autonomous Database Instance with a Microsoft Azure AD Tenancy in Security Guide.

  2. Enable Microsoft Azure AD v2 Access Tokens: If your organization uses the Microsoft Azure AD v2 access token (instead of v1 tokens), then you may need to make additional changes in Azure AD to support the upn: claim in your token. See Enabling Microsoft Azure AD v2 Access Tokens and Checking the Azure AD Access Token Version in Security Guide.

  3. Manage app roles in Microsoft Azure AD: In Azure AD, you can create and manage app roles that will be assigned to Azure AD users and groups and also be mapped to Oracle Database global schemas and roles. Refer to Manage App Roles in Microsoft Azure AD in Security Guide.

  4. Configure outbound connectivity to Microsoft Azure AD using a NAT gateway:

    Create a NAT gateway in the Virtual Cloud Network (VCN) where your Autonomous Database resources reside by following the instructions in Create a NAT Gateway in Oracle Cloud Infrastructure Documentation.

    After creating the NAT gateway, add a route rule and an egress security rule to each subnet (in the VCN) where Autonomous Database resources reside so that these resources can use the gateway to obtain a public key from your Azure AD instance:

    1. Go to the Subnet Details page for the subnet.

    2. In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.

    3. In the table of existing Route Rules, check whether there is already a rule with the following characteristics:

      • Destination: 0.0.0.0/0
      • Target Type: NAT Gateway
      • Target: The name of the NAT gateway you just created in the VCN

      If such a rule does not exist, click Add Route Rules and add a route rule with these characteristics.

    4. Return to the Subnet Details page for the subnet.

    5. In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.

    6. In the side menu, under Resources, click Egress Rules.

    7. In the table of existing Egress Rules, check whether there is already a rule with the following characteristics:

      • Destination Type: CIDR
      • Destination: 0.0.0.0/0
      • IP Protocol: TCP
      • Source Port Range: 443
      • Destination Port Range: All

      If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

  5. Configure Azure AD as an external identity provider for Autonomous Database:

    By default, Autonomous Databases and Autonomous Container Databases are configured to connect users with Oracle Cloud Infrastructure (IAM) authentication and authorization. An application DBA can also change this to another external authentication scheme, such as Centrally Managed Users with Active Directory (CMU-AD) or Kerberos. However, an Autonomous Database can enable only one external authentication scheme at a time.

    To enable Azure AD as an external identity provider on an Autonomous Database instance:

    1. Log in to the Autonomous Database instance as a user who has the EXECUTE privilege on the DBMS_CLOUD_ADMIN PL/SQL package. The ADMIN user has this privilege.
    2. As there can only be one external authentication scheme enabled for an Autonomous Database at any given time, run the DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION procedure to disable any external authentication scheme that is already enabled for your database.
      To run the procedure, you must be logged in as ADMIN user or have the EXECUTE privilege on DBMS_CLOUD_ADMIN.
      BEGIN
    DBMS_CLOUD_ADMIN.DISABLE_EXTERNAL_AUTHENTICATION;
END;
/
    3. Run the DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION procedure with the Azure AD required parameters.
      BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
      type   =>'AZURE_AD',
      params => JSON_OBJECT('tenant_id' VALUE 'tenant_id',
                            'application_id' VALUE 'application_id',
                            'application_id_uri' VALUE 'application_id_uri'),
      force => TRUE
  );
END;

      In this procedure the Azure AD parameters are:

      • type: Specifies the external authentication provider. For Azure AD, as shown, use 'AZURE_AD'.
      • params: Values for the required Azure AD parameters are available from the Azure portal on the app registration Overview pane for Azure Active Directory. The required params for Azure AD are:
        • tenant_id: Tenant ID of the Azure Account. Tenant Id specifies the Autonomous Database instance's Azure AD application registration.
        • application_id: Azure Application ID created in Azure AD to assign roles/schema mappings for external authentication in the Autonomous Database instance.
        • application_id_uri: Unique URI assigned to the Azure Application.

          This it the identifier for the Autonomous Database instance. The name must be domain qualified (this supports cross tenancy resource access).

          The maximum length for this parameter is 256 characters.

      • force: Set this parameter to TRUE if another EXTERNAL AUTHENTICATION method is configured for the Autonomous Database instance and you want to disable it.
      For example:
      BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
      type   =>'AZURE_AD',
      params => JSON_OBJECT('tenant_id' VALUE '29981886-6fb3-44e3-82',
                            'application_id' VALUE '11aa1a11-aaa',
                            'application_id_uri' VALUE 'https://example.com/111aa1aa'),
      force  => TRUE
  );
END;

      This sets the IDENTITY_PROVIDER_TYPE system parameter.

      For example, you can use the following to verify IDENTITY_PROVIDER_TYPE:
      SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';
 
NAME                   VALUE   
---------------------- -------- 
identity_provider_type AZURE_AD
    See ENABLE_EXTERNAL_AUTHENTICATION Procedure for more information.

Map Oracle Database Schemas and Roles

Azure AD users will be mapped to one database schema and optionally to one or more database roles.

You have the following options for mapping Oracle database schemas and roles to Microsoft Azure AD users:

Configure Client Connections to Azure ADs

There are numerous ways that you can configure a client to connect with an Oracle Autonomous Database on Dedicated Exadata Infrastructure instance using Azure AD tokens.

You should choose the client connection method that works best with your environment. This guide provides examples of connecting SQL*Plus with different methods of getting an Azure AD OAuth2 access token. All Oracle Database release 19c clients can accept a token that is passed as a file. The JDBC-thin, Instant Client, and ODP.net drivers also accept the token through the database client API from an application. Oracle Database tools such as SQL*Plus cannot retrieve the tokens directly, so tools such as PowerShell or Azure CLI must be used to retrieve the Azure AD OAuth2 access token. To retrieve an Azure AD token, the client must be registered through the Azure AD app registration process. Registering the client is similar to registering the Oracle Autonomous Database on Dedicated Exadata Infrastructure server with Azure AD using app registration. Both the database and client must be registered with Azure AD.

The database must be registered so the client can get permission to get an access token for the database. The client must be registered so that Azure AD can recognize a trusted client is asking for an access token.

Note:

On the client, you must set the TOKEN_AUTH and TOKEN_LOCATION parameters in the sqlnet.ora file to retrieve the Azure AD database access token from a location and use it when the / slash login is used. Exact details are discussed in Configure SQL*Plus for Azure AD Access Tokens.

See the following Microsoft Azure articles for more information about connecting clients to Azure AD:

Operational Flow for SQL*Plus Client Connection in PowerShell to Autonomous Database

The connection between the Azure user, Azure AD, and the Autonomous Database instance relies on the passing of the OAuth2 token throughout these components.

Refer to Operational Flow for SQL*Plus Client Connection in PowerShell to Oracle Database in Security Guide for an example that shows the use of the Resource Owner Password Credential (ROPC) flow with a public client.

Registering a Client with Azure AD Application Registration

This type of registration is similar to registering Autonomous Database with Azure AD app registration. Refer to the following sections for more details:

Examples of Retrieving Azure AD OAuth2 Tokens

For examples showing the different ways that you can retrieve Azure AD OAuth2 tokens, refer to Examples of Retrieving Azure AD OAuth2 Tokens in Security Guide.

Configure SQL*Plus for Azure AD Access Tokens

You must configure SQL*Plus to retrieve the Azure AD database access token from a location and use it when the / slash login is used. Refer to Configuring SQL*Plus for Azure AD Access Tokens in Security Guide for detailed instructions.

Trace Files Used for Troubleshooting Connections

You can generate two levels of trace files to troubleshoot Microsoft Azure AD connections on client side.

The two levels of trace files that you can generate are as follows:

  • Low level tracing prints traces in case of failures:
    • If TCPS is not set up for the Azure AD connection, then it prints a message that the protocol has to be TCPS.
    • If SSL_SERVER_DN_MATCH is not set to TRUE, then it prints a message that the value is FALSE.
    • If TOKEN_LOCATION has not been specified, then it prints a message that the token location does not exist.
    • If the token is not present at the specified TOKEN_LOCATION, then it prints a message.
    • If the application has passed in the token without setting OCI_ATTR_TOKEN_ISBEARER to true, it prints a message for the missing attribute.
    • If the application has set OCI_ATTR_TOKEN_ISBEARER to TRUE and not passed in the token, it prints a message for the missing attribute.
    • If the token has expired, then it prints a message.
  • High level tracing prints traces in case of failure as mentioned above. In addition, it prints traces in case of success, as follows:
    • It prints where SSL_SERVER_DN_MATCH is present, tnsnames.ora or sqlnet.ora. It also prints the value as TRUE if set to TRUE.
    • If both the token and OCI_ATTR_TOKEN_ISBEARER=true are set by the application, then it prints a message.
    • If TOKEN_AUTH has the correct value OAUTH, then it prints the value.
    • If the token is not expired, then it prints a message.

Refer to Setting Client Tracing for Token Authentication to learn how to add EVENT settings to the client-side sqlnet.ora file to control client tracing.