Authenticating and Authorizing Microsoft Azure Active Directory Users for Oracle Databases

An Oracle Database instance can be configured for Microsoft Azure AD users to connect using Azure OAuth2 access tokens.

Introduction to Authorizing Microsoft Azure AD Users for an Oracle Database

Before you begin authenticating and authorizing Microsoft Azure AD users for an Oracle Database, you should understand the overall process.

About Authorizing Microsoft Azure AD Users for an Oracle Exadata Database Service on Dedicated Infrastructure

Users for Oracle Exadata Database Service on Dedicated Infrastructure can be centrally managed in a Microsoft Azure Active Directory (Azure AD) service.

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

  • On-premises Oracle Database release 19.16 and later, but not for Oracle Database 21c
  • Oracle Exadata Database Service on Dedicated Infrastructure on Database versions 19.17 and later. This feature is not supported on Oracle Database release 21c.
  • Oracle Base Database Service

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 Exadata Database Service on Dedicated 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 administrator creates and configures the application registration (app registration) of the Oracle Exadata Database Service on Dedicated Infrastructure instance with Azure AD. The database administrator also creates application (app) roles for the database app registration in Azure AD, and assigns these roles to Azure AD users, groups, and applications. These app roles will be mapped to the database global schemas and global roles. An Azure AD principal that is assigned to an app role will be mapped to either a database global schema or database global role. An Oracle global schema can also be mapped exclusively to an Azure AD user. When the principal is a guest user or service principal, they can only be mapped to the database schema through an Azure app role. An Oracle global role can only be mapped to an Azure app role.

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 Exadata Database Service on Dedicated 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 token is a bearer token 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. Assigned app roles for the Azure AD principal 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 process user). Because the token allows access to the database, it should be protected within the file system.

Azure AD users can request a token as a client registered with Azure AD app registration by using methods such as the following:

  • Passing the Azure AD user name and password through a command line, script, file, or any other supported method
  • Entering the Azure AD credentials into an Azure AD authentication screen with or without multi-factor authentication

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

  • Resource owner password credential (ROPC), which is used in non-graphic user interface environments when a pop-up window cannot be used to authenticate a user.
  • Authorization code, which is used when a browser can be used to enter credentials for the user
  • Client credentials, which are for 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

Oracle Exadata Database Service on Dedicated 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)

Azure AD Users Mapping to an Oracle Database Schema and Roles

Microsoft Azure users must be mapped to an Oracle Database schema and have the necessary privileges (through roles) before being able to authenticate to the Oracle Database instance.

In Microsoft Azure, an Azure AD administrator can assign users, groups, and applications to the database app roles.

Exclusively mapping an Azure AD schema to a database schema requires the database administrator to create a database schema when the Azure AD user joins the organization or is authorized to the database. The database administrator must also modify the privileges and roles that are granted to the database schema to align them with the tasks the Azure AD user is assigned to. When the Azure AD user leaves the organization, the database administrator must drop the database schema so that an unused account is not left on the database. Using the database app roles enables the Azure AD administrator to control access and roles by assigning users to app roles that are mapped to global schemas and global roles. This way, user access to the database is managed by Azure AD administrators and database administrators do not need to create, manage, and drop schemas for every user.

An Azure AD user can be mapped to a database schema (user) either exclusively or through an app role.

  • Creating an exclusive mapping between an Azure AD user and an Oracle Database schema. In this type of mapping, the database schema must be created for the Azure AD user. Database privileges and roles that are needed by the Azure AD user must be granted to the database schema. The database schema not only must be created when the Azure AD user is authorized to the database, but the granted privileges and roles must be modified as the Azure AD roles and tasks change. Finally, the database schema must be dropped when the Azure AD user leaves the organization.
  • Creating a shared mapping between an Azure AD app role and an Oracle Database schema. This type of mapping, which is more common than exclusive mappings, is for Azure AD users who have been assigned directly to the app role or is a member of an Azure AD group that is assigned to the app role. The app role is mapped to an Oracle Database schema (shared schema mapping). Shared schema mapping allows multiple Azure AD users to share the same Oracle Database schema so a new database schema is not required to be created every time a new user joins the organization. This operational efficiency allows database administrators to focus on database application maintenance, performance, and tuning tasks instead of configuring new users, updating privileges and roles, and removing accounts.

In addition to database roles and privileges being granted directly to the mapped global schema, additional roles and privileges can be granted through mapped global roles. Different Azure AD users mapped to the same shared global schema may need different privileges and roles. Azure app roles can be mapped to Oracle Database global roles. Azure AD users who are assigned to the app role or are a member of an Azure AD group that is assigned to the app role will be granted the Oracle Database global role when they access the database.

The following diagram illustrates the different types of assignments and mappings that are available.

Figure 5-1 Assignments and Mappings Between Azure AD and Oracle Database

Description of Figure 5-1 follows

These mappings are as follows:

  • An Azure AD user can be mapped directly to an Oracle Database global schema (user).
  • An Azure AD user, Azure AD group, or application is assigned to an app role, which is then mapped to either an Oracle Database global schema (user) or a global role.

Use Cases for Connecting to an Oracle Database Using Azure AD

Oracle Database supports four types of use cases for connecting to an Oracle Database instance using Microsoft Azure Active Directory.

  • Connection using OAuth 2.0 authorization flow: The client directs the resource owner to an authorization server, which in turn directs the resource owner back to the client with the authorization code. See the Microsoft Azure article Microsoft identity platform and OAuth 2.0 authorization code flow.
  • Connection using the resource owner password credentials: The resource owner password credentials (that is, the user name and password) can be used directly to obtain an access token. Azure AD requires an additional client Id and a secret for this flow. (The secret is not required for public client.) See the Microsoft Azure article Microsoft identity platform and OAuth 2.0 Resource Owner Password Credentials.
  • Connection using the client credentials: The client acts on its own behalf (the client is also the resource owner) or requests access to protected resources based on an authorization arranged with the authorization server. This flow is used to get the Azure OAuth2 access token for the service principal. An application can also request an Azure AD OAuth2 access token directly from Azure AD and pass it through a database client API. See the Microsoft Azure article Get Azure AD tokens by using a service principal.
  • Connection using on-behalf-of (OBO) token: An Azure application requests an OBO token for a logged in user. The OBO token will also be an access token for the database with the Azure AD user identity and assigned app roles for the database. This enables the Azure AD user to log in to the database as the user and not the application. Only an application can request an OBO token for its Azure AD user and pass it to the database client through the API.

General Process of Integrating Microsoft Azure AD with Oracle Exadata Database Service on Dedicated Infrastructure

Both the Oracle and the Microsoft Azure administrators play roles in configuring the connection between Oracle Exadata Database Service on Dedicated Infrastructure and Microsoft Azure AD.

The general process is as follows:

  1. The Oracle 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 Oracle administrator registers the database instance with the Microsoft Azure AD tenancy and then enables the connection between the Oracle Exadata Database Service on Dedicated Infrastructure and the Azure AD endpoint.

    As part of the registration process, the Oracle administrator or the Azure administrator creates or designates Azure app roles to be used for the mappings between the Oracle database and the Microsoft Azure endpoint.

  3. The Oracle 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 Exadata Database Service on Dedicated 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 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 Exadata Database Service on Dedicated Infrastructure instance, the Azure AD end user performs tasks as needed.

Configuring the Oracle Database for Microsoft Azure AD Integration

The Microsoft Azure AD integration with the Oracle Database instance requires the database to be registered with Azure AD so that the database can request the Azure AD public key.

Prerequisites for Azure AD Authentication

Before using Azure AD authentication on databases in the Exadata Cloud Infrastructure, you must use the Networking service to add a service gateway, a route rule, and an egress security rule to the Virtual Cloud Network (VCN) and subnets where your database resources reside.

  1. Create a service gateway in the VCN where your database resources reside by following the instructions in Task 1: Create the service gateway in OCI documentation.
  2. After creating the service gateway, add a route rule and an egress security rule to each subnet (in the VCN) where the database resources reside so that these resources can use the gateway to use Azure AD authentication:
    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
    8. If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

Configure TLS to Use Azure AD tokens

When sending Azure AD tokens from the database client to the database server, a TLS connection must be established. The TLS wallet with the database certificate for the ExaDB-D service instance must be stored under the WALLET_ROOT location. Create a tls directory so it looks like: WALLET_ROOT/<PDB GUID>/tls.

When configuring TLS between the database client and server there are several options to consider.

  • Using a self-signed database server certificate vs a database server certificate signed by a commonly known certificate authority
  • One-way TLS (TLS) vs Mutual or two-way TLS (mTLS)
  • Client with or without a wallet

Self-Signed Certificate

Using a self-signed certificate is a common practice for internally facing IT resources since you can create these yourself and it's free. The resource (in our case, the database server) will have a self-signed certificate to authenticate itself to the database client. The self-signed certificate and root certificate will be stored in the database server wallet. For the database client to be able to recognize the database server certificate, a copy of the root certificate will also be needed on the client. This self-created root certificate can be stored in a client-side wallet or installed in the client system default certificate store (Windows and Linux only). When the session is established, the database client will check to see that the certificate sent over by the database server has been signed by the same root certificate.

A Well-Known Certificate Authority

Using a commonly known root certificate authority has some advantages in that the root certificate is most likely already stored in the client system default certificate store. There is no extra step for the client to store the root certificate if it is a common root certificate. The disadvantage is that this normally has a cost associated with it.

One-Way TLS

In the standard TLS session, only the server provides a certificate to the client to authenticate itself. The client doesn't need to have a separate client certificate to authenticate itself to the server (similar to how HTTPS sessions are established). While the database requires a wallet to store the server certificate, the only thing the client needs to have is the root certificate used to sign the server certificate.

Two-Way TLS (also called Mutual TLS, mTLS)

In mTLS, both the client and server have identity certificates that are presented to each other. In most cases, the same root certificate will have signed both of these certificates so the same root certificate can be used with the database server and client to authenticate the other certificate. mTLS is sometimes used to authenticate the user since the user identity is authenticated by the database server through the certificate. This is not necessary for passing IAM tokens but can be used when passing IAM tokens.

Client with a Wallet

A client wallet is mandatory when using mTLS to store the client certificate. However, the root certificate can be stored either in the same wallet or in the system default certificate store.

A Client without a Wallet

Clients can be configured without a wallet when using TLS under these conditions: 1) One-way TLS is being configured where the client does not have its own certificate and 2) the root certificate that signed the database server certificate is stored in the system default certificate store. The root certificate would most likely already be there if the server certificate is signed by a common certificate authority. If it's a self-signed certificate, then the root certificate would need to be installed in the system default certificate store to avoid using a client wallet.

For details on how to configure TLS between the database client and database server including the options described above, see Configuring Transport Layer Security Authentication in the Oracle Database Security Guide.

If you choose to use self-signed certificates and for additional wallet related tasks, see Managing Public Key Infrastructure (PKI) Elements in the Oracle Database Security Guide.

Oracle Database Requirements for the Microsoft Azure AD Integration

Before you can configure an Oracle Database instance with Microsoft Azure AD, you must ensure that your environment meets special requirements.

The Microsoft Azure AD integration with the Oracle Exadata Database on Dedicated Infrastructure requires:
  • The ExaCS Database to be version 19.17 or higher.
  • Connectivity to the database using TLS. Non TLS connections are not supported.
  • Outbound network connectivity to Azure AD so that the database can request the Azure AD public key.
  • The ExaDB-D Database to be registered with Azure AD.

Note the following:

  • The Oracle Database server must be able to request the Azure AD public key. Depending on the enterprise network connectivity setup, you may need to configure a proxy setting.
  • Users and applications that need to request an Azure AD token must also be able to have network connectivity to Azure AD. You may need to configure a proxy setting for the connection.
  • You must configure Transport Layer Security (TLS) between the Oracle Database client and the Oracle Database server so that the token can be transported securely. This TLS connection can be either one-way or mutual.
  • You can create the TLS server certificate to be self-signed or be signed by a well known certificate authority. The advantage of using a certificate that is signed by a well known Certificate Authority (CA) is that the database client can use the system default certificate store to validate the Oracle Database server certificate instead of having to create and maintain a local wallet with the root certificate. Note that this applies to Linux and Windows clients only.

Registering the Oracle Database Instance with a Microsoft Azure AD Tenancy

A user with administrator privileges uses Microsoft Azure AD to register the Oracle Database instance with the Microsoft Azure AD tenancy.

  1. Log in to the Azure portal as an administrator who has Microsoft Azure AD privileges to register applications.
  2. In the Azure Active directory admin center page, from the left navigation bar, select Azure Active Directory.
  3. In the MS - App registrations page, select App registrations from the left navigation bar.
  4. Select New registration.
    The Register an application window appears. Description of azure-reg.png follows
  5. In the Register an application page, enter the following Oracle Database instance registration information:
    • In the Name field, enter a name for the Oracle Database instance connection (for example, Example Database).
    • Under Supported account types, select the account type that matches your use case.
      • Accounts in this organizational directory only (tenant_name only - Single tenant)
      • Accounts in any organizational directory (Any Azure AD directory - Multitenant)
      • Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox)
      • Personal Microsoft accounts only
  6. Bypass the Redirect URI (Optional) settings. You do not need to create a redirect URI.
  7. Click Register.
    After you click Register, Azure AD displays the app registration's Overview pane, which will show the Application (client) ID under Essentials. This value is a unique identifier for the application in the Microsoft identity platform.
  8. Register a scope, which will set the permission for the registered app.
    1. In the left navigation bar, select Expose an API.
    2. Under Set the App ID URI, in the Application ID URI field, enter the app ID URI for the database connection using the following format, and then click Save:
      your_tenancy_url/application_(client)_id

      In this specification:

      • your_tenancy_url must include https as the prefix and the fully qualified domain name of your Azure AD tenancy.
      • application_(client)_id is the ID that was generated when you registered the Oracle Database instance with Azure AD. It is displayed in the Overview pane of the app registration.

      For example:

      https://sales_west.example.com/1aa11111-1a1z-1a11-1a1a-11aa11a1aa1a
    3. Select Add a scope and then enter the following settings:
      Description of azure-scope.png follows
      • Scope name specifies a name for the scope. Enter the following name:
        session:scope:connect

        This name can be any text. However, a scope name must be provided. You will need to use this scope name later when you give consent to the database client application to access the database.

      • Who can consent specifies the necessary permissions. Select Admins and users, or for higher restrictions, Admins only.
      • Admin consent display name describes the scope's purpose (for example, Connect to Oracle), which only administrators can see.
      • Admin consent display name describes the scope's purpose (for example, Connect to Example Database), which only administrators can see.
      • User consent display name is a short description of the purpose of the scope (for example, Connect to Example Database), which users can see if you specify Admins and users in Who can consent.
      • User consent description is a more detailed description of the purpose of the scope (for example, Connect to Example Database), which users can see if you specify Admins and users in Who can consent.
      • State enables or disables the connection. Select Enabled.
After you complete these steps, you are ready to add one or more Azure app roles, and then perform the mappings of Oracle schemas and roles.

Enabling Microsoft Entra ID v2 Access Tokens

To enable the Microsoft Entra ID v2 access token, you must configure it to use the upn attribute from the Azure portal.

Oracle Database supports the Entra ID v2 token as well as the default v1 token. However, to use the Entra ID v2 token, you must perform some additional steps to ensure it works with the Oracle Database.
  1. Check the version of the Entra ID access token that you are using.
  2. Log in to the Microsoft Entra ID portal.
  3. Search for and select Entra ID.
  4. Under Manage, select App registrations.
  5. Choose the application for which you want to configure optional claims based on your scenario and desired outcome.
  6. Under Manage, select Token configuration.
  7. Click Add optional claim and select upn.
Checking the Entra ID Access Token Version

You can check the version of the Entra ID access token that your site uses by using the JSON Web Tokens web site.

By default, Entra ID v1 access token, but your site may have chosen to use v2. Oracle Database supports v1 tokens and Autonomous Database Serverless supports v2 tokens, as well. If you want to use the v2 access tokens, then you can enable their use for the Oracle database. To find the version of the Entra ID access token that you are using, you can either check with your Entra ID administrator, or confirm the version from the JSON Web Tokens website, as follows.
  1. Go to the JSON Web Tokens website.
    https://jwt.io/
  2. Copy and paste the token string into the Encoded field.
  3. Check the Decoded field, which displays information about the token string.
    Near or at the bottom of the field, you will see a claim entitled ver, which indicates either of the following versions:
    • "ver": "1.0"
    • "ver": "2.0"

Testing the Accessibility of the Azure Endpoint

You must ensure that your Oracle Database instance can access the Azure AD endpoint.

For an Oracle database to accept Azure AD OAuth2 tokens, the database must request the public key from the Azure AD endpoint.

  • Run the following test to determine if the database can connect with the Azure AD endpoint:
    SET SERVEROUTPUT ON SIZE 40000
    DECLARE
      req UTL_HTTP.REQ;
      resp UTL_HTTP.RESP;
    BEGIN
      UTL_HTTP.SET_WALLET(path => 'system:');
      req := UTL_HTTP.BEGIN_REQUEST('https://login.windows.net/common/discovery/keys');
      resp := UTL_HTTP.GET_RESPONSE(req);
      DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || resp.status_code);
      UTL_HTTP.END_RESPONSE(resp);
    END;
    /

    If this test is successful, then a PL/SQL procedure successfully completed message appears.

    If the following messages appear, then it means that a database network access control list (ACL) policy blocked your test and you will need to temporarily set an access control list policy to allow you to test this:
    ORA-29273: HTTP request failed
    ORA-24247: network access denied by access control list (ACL)
    1. Set the ACL as follows:
      BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                             principal_name => 'username_placeholder',
                             principal_type => xs_acl.ptype_db));
      END;
      /
      Replace username_placeholder with the user name of the database user who is running the test. For example:
      BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                             principal_name => 'DBA_DEBRA',
                             principal_type => xs_acl.ptype_db));
      END;
      /
    2. Try running the test again.
    3. Remove the ACL, because you now no longer need it. For example, assuming your user name is dba_debra:
      BEGIN
      DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE(
        host => '*',
        ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                             principal_name => 'DBA_DEBRA',
                             principal_type => xs_acl.ptype_db));
      END;
      /

If the database cannot connect with the Azure AD endpoint, even after you set the ACL policy, you will most likely need to set the HTTP_PROXY package for your database. Review the topics listed in Related Topics, depending if you are using a default Oracle Database environment or an Oracle Real Application Clusters RAC environment. Your network administrator should be able to tell you what the correct HTTP_PROXY setting should be.

Managing App Roles in Microsoft Entra ID

In Entra ID, you can create and manage app roles that will be assigned to Azure users and groups and also be mapped to Oracle Database global schemas and roles.

Creating a Microsoft Azure AD App Role

Azure AD users, groups, and applications will be assigned to the app roles.

See the Microsoft Azure article Create and assign a custom role in Azure Active Directory for detailed steps on how to create an app role. The following steps describe how to create the app role for use with an Oracle Database integration.
  1. Log in to Azure AD as an administrator who has privileges for creating app roles.
  2. Access the Oracle Database app registration that you created.
    1. Use the Directory + subscription filter to locate the Azure Active Directory tenant that contains the Oracle Database app registration.
    2. Select Azure Active Directory.
    3. Under Manage, select App registrations, and then select the Oracle Database instance that you registered earlier.
  3. Under Manage, select App roles.
  4. In the App roles page, select Create app role.
  5. In the Create app role page, enter the following information:
    • Display name is the displayed name of the role (for example, HR App Schema). You can include spaces in this name.
    • Value is the actual name of the role (for example, HR_APP). Ensure that this setting matches exactly the string that is referenced in the application's code. Do not include spaces in this name.
    • Description provides a description of the purpose of this role.
    • Do you want to enable this app role? enables you to activate the role.
  6. Click Apply.

    The app role appears in the App roles pane.

    Description of azure-app-roles-creation.png follows
Assigning Users and Groups to the Microsoft Azure AD App Role

Before Microsoft Azure AD users can have access to the Oracle Database instance, they must first be assigned to the app roles that will be mapped to Oracle Database schema users or roles.

See the Microsoft Azure article Add app roles to your application and receive them in the token for detailed steps assigning users and groups to an app role. The following steps explain how to do this for an Oracle Database integration.
  1. Log in to Azure AD as an administrator who has privileges for assigning Azure AD users and groups to app roles.
  2. In Enterprise applications, access the Oracle Database application that you registered.
    1. Use the Directory + subscription filter to locate the Azure Active Directory tenant that contains the Oracle connection.
    2. Select Azure Active Directory.
    3. Under Manage, select Enterprise applications, and then select the Oracle Database application name that you registered earlier.
  3. Under Getting Started, select Assign users and groups.
  4. Select Add user/group.
  5. In the Add assignment window, select Users and groups to display a list of users and security groups.
  6. From this list, select the users and groups that you want to add to the app role, and then click Select.
  7. In the Add assignment window, select Select a role to display a list of the app roles that you have created.
  8. Select the app role and then select Select.
  9. Click Assign.
Assigning an Application to an App Role

You can assign an Azure AD client application to a app role.

  1. Log in to Azure AD as an administrator who has privileges for assigning Azure AD users and groups to app roles.
  2. Access the app registration for the application.
  3. Under Manage, select API permissions.
  4. In the Configured permissions area, select + Add a permission.
  5. In the Request API permission pane, select the My APIs tab.
  6. Select the Oracle Database app that you want to give permission for this application to access. Then select the Application permissions option.
  7. Select the database app roles to assign to the application and then click the Add Permission box at the bottom of the screen to assign the app roles and close the dialog box. Ensure that the app roles that you just assigned appear under Configured permissions.
    Description of azure-grant-consent.png follows
  8. Select Grant admin consent for tenancy to grant consent for the tenancy users, then select Yes in the confirmation dialog box.

Enabling Azure AD External Authentication for Oracle Database

You can enable a Microsoft Azure AD external authentication with Oracle Database.

  1. Log in to the Oracle Database instance as a user who has been granted the ALTER SYSTEM system privilege.
  2. Set the IDENTITY_PROVIDER_TYPE parameter as follows:
    ALTER SYSTEM SET IDENTITY_PROVIDER_TYPE=AZURE_AD SCOPE=BOTH;
  3. Ensure that you set the IDENTITY_PROVIDER_TYPE parameter correctly.
    SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME='identity_provider_type';

    The following output should appear:

    NAME                    VALUE 
    ----------------------  ------- 
    identity_provider_type  AZURE_AD
  4. Set the IDENTITY_PROVIDER_CONFIG parameter by using the following syntax:
    ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG =
    {
       application_id_uri : string , // from registered app, to be mapped in jwt "aud" claim; 
                                     // Domain qualified to support cross tenancy resource access
       tenant_id : string,           // from tenant config
       app_id: string                // from registered resource app
    }SCOPE=BOTH;

    For example:

    ALTER SYSTEM SET IDENTITY_PROVIDER_CONFIG =
    {
      "application_id_uri" : "https://www.example.com/11aa1a11-aaaa-1111-1111-1111aa11111",
      "tenant_id" : 111a1111-a11a-111a-1a1a-1111111111a,
      "app_id" : 11aa1a11-aaaa-1111-1111-1111aa11111
    }SCOPE=BOTH;

Disabling Azure AD External Authentication for Oracle Database

To disable Azure AD External authentication for an Oracle Database instance, you must set parameters with the ALTER SYSTEM statement.

  1. Log in to the Oracle Database instance as a user who has been granted the ALTER SYSTEM system privilege.
  2. Set the identity provider parameters as follows:
    ALTER SYSTEM RESET IDENTITY_PROVIDER_CONFIG SCOPE=BOTH;
    ALTER SYSTEM RESET IDENTITY_PROVIDER_TYPE SCOPE=BOTH;

Mapping Oracle Database Schemas and Roles

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

Exclusively Mapping an Oracle Database Schema to a Microsoft Azure AD User

You can exclusively map an Oracle Database schema to a Microsoft Azure AD user.

  1. Log in to the Oracle Database instance as a user who has been granted the CREATE USER or ALTER USER system privilege.
  2. Run the CREATE USER or ALTER USER statement with the IDENTIFIED GLOBALLY AS clause specifying the Azure AD user name.
    For example, to create a new database schema user named peter_fitch and map this user to an existing Azure AD user named peter.fitch@example.com:
    CREATE USER peter_fitch IDENTIFIED GLOBALLY AS 
    'AZURE_USER=peter.fitch@example.com';
  3. Grant the CREATE SESSION privilege to the user.
    GRANT CREATE SESSION TO peter_fitch;

Mapping a Shared Oracle Schema to an App Role

In this mapping, an Oracle schema is mapped to an app role.

  1. Log in to the Oracle Database instance as a user who has the CREATE USER or ALTER USER system privilege.
  2. Run the CREATE USER or ALTER USER statement with the IDENTIFIED GLOBALLY AS clause specifying the Azure application role name.
    For example, to create a new database global user account (schema) named dba_azure and map it to an existing Azure AD application role named AZURE_DBA:
    CREATE USER dba_azure IDENTIFIED GLOBALLY AS 'AZURE_ROLE=AZURE_DBA';

Mapping an Oracle Database Global Role to an App Role

Oracle Database global roles that are mapped to Azure app roles give Azure users and applications additional privileges and roles above those that they have been granted through their login schemas.

  1. Log in to the Oracle Database instance as a user who has been granted the CREATE ROLE or ALTER ROLE system privilege
  2. Run the CREATE ROLE or ALTER ROLE statement with the IDENTIFIED GLOBALLY AS clause specifying the name of the Azure AD application role.
    For example, to create a new database global role named widget_sales_role and map it to an existing Azure AD application role named WidgetManagerGroup:
    CREATE ROLE widget_sales_role IDENTIFIED GLOBALLY AS 
    'AZURE_ROLE=WidgetManagerGroup';

Configuring Azure AD Client Connections to the Oracle Database

You can configure client connections to connect with the Azure AD registered database

About Configuring Client Connections to Azure ADs

There are numerous ways that you can configure a client to connect with an Oracle Database 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 clients version 19.16 and above 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 Database 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.

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

  • Quickstart: Configure a client application to access a web API
  • Choose the right Azure command-line tool
  • Get Azure AD tokens by using the Microsoft Authentication Library
  • Install the Azure CLI on Linux

Supported Client Drivers for Azure AD Connections

Oracle Database supports several types of client drivers for Azure AD connections.

  • JDBC-thin: Oracle Database 19.16 (July 2022), Oracle Database 21.8 (October 2022)
  • OCI (C driver): Oracle Database 19.16 (July 2022)
  • Oracle Instant Client based on OCI
  • Oracle Data Provider (core): Oracle Database 19.16, Oracle Database 21.7
  • Oracle Data Provider (unmanaged): based on OCI
  • Oracle Data Provider (managed): Oracle Database 19.16, Oracle Database 21.7
  • All other drivers built on OCI adopts the OCI compatibility

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

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

This example shows the use of the Resource Owner Password Credential (ROPC) flow with a public client. See the Microsoft Azure article Microsoft identity platform and OAuth 2.0 Resource Owner Password Credentials for detailed information about ROPC.

Figure 5-2 ROPC Operational Flow with a Public Client

Description of Figure 5-2 follows
  1. The Azure user requests an Azure AD access token for the database in PowerShell and the returned token is written into a file called token at a file location.
  2. The Azure user connects to the database using / slash login. Either the sqlnet.ora or tnsnames.ora connection string tells the instant client that an Azure AD OAuth2 token is needed and to retrieve it from a specified file location. The access token is sent to the database.
  3. The database verifies that the access token came from Azure AD (using the Azure AD public key) and then checks the token for additional claims.
  4. The database finds the schema mapping (exclusive or shared) and creates the session. The database will also grant any global roles that the Azure user is also assigned to through an app role.

Registering a Client with Azure AD Application Registration

This type of registration is similar to registering Oracle Database with Azure AD app registration.

Confidential and Public Client Registration

You can register the database client with Azure as either confidential or public depending on your use case.

See the Microsoft Azure article Authentication flows and application scenarios for detailed information about authentication flows and application scenarios.

Registering a confidential client app requires that the client have a secret, in addition to the client ID. The confidential client app uses both the client ID and the secret when it makes Azure AD requests. However, in an enterprise, it is not practical for every SQL*Plus and SQLcl user to create a separate app registration with its own secret. In addition, a secret is no longer a secret when you start to share it within an organization. It is far better to just create a public client app. A public client app does not have a secret; it only has a client ID. All database tool users can use the public client ID when they connect to Azure AD to get an access token. The Azure AD user still needs to authenticate to Azure AD with their own user credential.

Registering a Database Client App with Azure AD

Creating the client app registration is similar to creating the Oracle Database instance with the Microsoft Azure AD tenancy.

  1. Log in to the Azure portal as an administrator who has Microsoft Azure AD privileges to register applications.
  2. In the Azure Active directory admin center page, from the left navigation bar, select Azure Active Directory.
  3. In the MS - App registrations page, select App registrations from the left navigation bar.
  4. Select New registration.
  5. In the Register an application page, enter the following Oracle Database instance registration information:
    • In the Name field, enter a name for the client app (for example, DatabaseClientApplication)..
    • Under Supported account types, select the account type that matches your use case.
      • Accounts in this organizational directory only (tenant_name only - Single tenant)
      • Accounts in any organizational directory (Any Azure AD directory - Multitenant)
      • Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox)
      • Personal Microsoft accounts only
  6. Under Redirect URI (optional), configure the redirect URI for the client app.
    Description of azure-redirect-uri.png follows
    • Select Public client/native (mobile & desktop), Web, or Single-page application (SPA). Choose Public client if this client app will be used by multiple users such as database administrators who need to use SQL*Plus to access the Oracle Database instance.
    • Add a redirect URI of http://localhost, unless you have another address to use. This redirect URI is needed for the authorization flow.
  7. Click Register.
    At this stage, the database client has been registered with Azure AD. Next, you must add the new client to the list of authorized client apps for the Oracle Database instance.
  8. To add the new client to this list of client apps, do the following:
    1. Make a note of the new client's Application (client) ID. This ID is in the Overview page for the app.
      Description of azure-client-id.png follows
    2. On the App registrations page, open the app registration page for the database server by selecting it from the menu.
    3. On the left side, select Expose an API.
    4. Scroll down on the main page until you see Authorized client applications.
    5. Select + to add a client application.
    6. Copy the new client's Application (client) ID to the Client Id field.
      Description of azure-auth-client-app.png follows
    7. Click Add application.

Examples of Retrieving Azure AD OAuth2 Tokens

These examples show different ways that you can retrieve Azure AD OAuth2 tokens.

Example: Using PowerShell to Get a Token Using Resource Owner Password Credentials

This example shows how to use PowerShell to get an Azure AD access token by using Resource Owner Password Credentials (ROPC).

You can retrieve the OAuth2 access token by making a REST call from PowerShell. This configuration requires several values that were generated or that you specified when you registered the Oracle Database instance with Azure AD.
  1. If necessary install the Azure Active Directory PowerShell module.
    Follow the instructions in the Microsoft article Install the Azure Az PowerShell module to download and install Azure PowerShell. It takes about 20 minutes or longer to perform the installation. You may want to set debug options for Azure PowerShell so that you can see how the installation is progressing.
  2. After the Azure PowerShell installation is complete, log in to PowerShell and then set the following variables in the order shown.
    1. $TenantDomain = "user_tenancy_domain_name"
      This value is the tenancy domain name. For example:
      $TenantDomain = "example.com"
    2. $AppClientId ="application_client_id"
      This value sets the application client ID for the database client, not the database server. This is the Application (client) ID value in the app registration's Overview pane. For example:
      $AppClientId ="111a1a1a-aa1a-1a1a-11aa-1a11111111aa"
    3. $Username = "user_name", which
      This value is the name of the Azure user who wants to access the Oracle Database instance. For example:
      $Username = "peter.fitch@example.com"
    4. Entering the user password in PowerShell scripts depends on your corporate or personal security standards. Use your own method to capture the password securely or use this example that hides the password from the command history and command line window. You should delete the password variables after they have been used. Enter these in the order shown:
      1. $securePassword = Read-Host " Enter Password" -AsSecureString
      2. $Password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePassword))
    5. $Scope = "database_app_id_uri/scope"
      This value sets the app ID URI for the database and the scope (permission) for the database, separated by a / slash. These values can be found in the database app registration Expose an API page. In the following example, https://example.com/111aa1aa-1111-1111-a1a1-1a11a111111a is the app ID URI and session:scope:connect is the scope.
      $Scope = "https://example.com/111aa1aa-1111-1111-a1a1-1a11a111111a/session:scope:connect"
    6. $requestBody = @{client_id=$AppClientId;grant_type="password";username=$Username;password=$Password;scope=$Scope;}
      This is the request body of the upcoming REST call.
    7. $OAuthResponse = Invoke-RestMethod -Method Post -Uri https://login.microsoftonline.com/$TenantDomain/oauth2/v2.0/token -Body $requestBody
      This gets the OAuth2 access token for the user.
    8. You can remove the password from the variables if you do not need them.
      • $securePassword = $null, for secure password strings
      • $Password = $null, for clear text password strings
    9. $AccessToken = $OAuthResponse.access_token | Out-File -FilePath .\token -Encoding ASCII, which writes the OAuth2 token to the current file location using ASCII encoding
  3. Optionally, because the Azure AD OAuth2 access token is a JSON Web Token (JWT) formatted token, you can view the cleartext of the encoded content by copying and pasting the token content into the website:

    https://jwt.io/

    Note the following:

    • The default PowerShell UTF16 file encoding cannot be used for the token. Use ASCII encoding as an alternative.
    • Tokens may not work cross-platform (for example, Windows to Linux or Linux to Windows), depending on encoding changes to the file when it is moved.
At this stage, the OAuth2 access token has been retrieved and stored as a file. The next step is to enable the SQL*Plus client to use the store access token and send it to the database.
Example: Using Python with Microsoft Authentication Library Using an Authorization Flow

Because this example with the Microsoft Authentication Library (MSAL) is in Python, it can be run on a variety of platforms such as PowerShell and Linux.

When multi-factor authentication is enabled for the user, an OAuth2 authorization flow is necessary for a user to add the second authentication. Because the authorization flow requires two round trips to Azure AD, it is best handled using the MSAL. See the Microsoft article Get Azure AD tokens by using the Microsoft Authentication Library for how to use a python script with MSAL. These instructions are for the Databricks service, but the scope is changed to the database App ID URI and scope instead of the Databricks scope.
  1. Bypass the steps to set up the client app registration, since you have already accomplished that step except make sure you add a Redirect URI (http://localhost) for your client app registration.
  2. Go directly to Get Azure AD tokens by using the MSAL Python library.
    You will need the Directory (tenant) ID, Client ID for the public app client, and the database App ID URI and scope. You will see a code section for scopes with directions to not modify this variable. Because this python code was written for Databricks scope, you will need to change this scope variable to the scope of your database. For example:
    scopes = ['https://example.com/1111aa1a-a1aa-1a11-11aa-1a1a11aa1111/session:connect']
  3. Modify the code to write the token to a file location.
    Use the following example code and append it to the print statements at the end. Note the extra lines to back up and restore the original stdout.
    stdout_backup = sys.stdout
    with open('token', 'w') as token_file:
        sys.stdout = token_file
        print(acquire_tokens_result['access_token'])
    
    sys.stdout = stdout_backup
Example: Using Curl with a Resource Owner Password Credential Flow

This example shows how to use the curl command against the Azure AD API uses a Resource Owner Password Credential (ROPC) flow with a public Azure AD client.

The cleartext password is part of this command so this is not so much for end-users as it is for applications. This would need to be protected.
  • Enter the following curl command:
    curl -X POST -H 'Content-Type: application/x-www-form-urlencoded' https://login.microsoftonline.com/az207oracleoutlook.onmicrosoft.com/oauth2/v2.0/token  
    -d 'client_id=571c3f0a-aa3c-4f0a-93ed-4f75748955ea' -d 'scope=https://example.com/383fe7ee-1433-4844-a2d5-5b80d811256d/session:scope:connect' 
    -d 'username=peter.fitch@example.com' -d 'password=password' -d 'grant_type=password'
The response is a JSON file with token type, scope, expiration, and then the actual token. This file will need to be parsed so only the access token is written and stored in a file.
Example: Azure CLI Using Authorization Flow

This example shows how to use the Azure CLI to retrieve an access token and then write the token to a file.

See the Microsoft Azure article Install the Azure CLI on Linux for information about installing the Azure CLI.
  1. Log in to your Azure tenancy.
    $ az login
  2. Get an access token and assign it to the token variable using the following syntax:
    token=$(az account get-access-token --resource=database_app_id_uri --query accessToken --output tsv)

    For example:

    token=$(az account get-access-token --resource=https://example.com/1111aa1a-a1aa-1a11-11aa-1a1a11aa1111 --query accessToken --output tsv)

    If you get an error saying that the Azure CLI client app ID does not have permission to access the database resource, then copy the Azure CLI client app ID from the error message and add it to the list of authorized client applications for the database resource. (Go to the database app registration in Azure AD, click Expose an API and then Add a client application).

  3. Write the token to a file.
    $ echo "$token" >> token

Configuring 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.

Use Oracle Database release 19.16 and above SQL*Plus and Instance Client. Oracle Database release 21c clients do not support the full range of features. There is no default location for the Azure AD token, so you must specify this location.
  1. Ensure that you have an Azure AD user account.
  2. Check with an Azure AD administrator or Oracle Database administrator for one of the following:
    • An application client ID that you can use to get Azure AD tokens. If you have Azure AD privileges to do so, then create your own client app registration, similar to registering the Oracle Database instance with an Azure AD tenancy.
    • You are mapped to a global schema in the database.
  3. Ensure that you are using the latest release updates for the Oracle Database client releases 19c.
    This configuration only works with the Oracle Database client release 19.16 and above. Oracle Database release 21c clients do not support the full range of features.
  4. Configure the database server and client to use a TLS connection with the ExaDB-D database server.
  5. On the client, set the following parameters in the sqlnet.ora file:
    • Check for the parameter SSL_SERVER_DN_MATCH = ON to ensure that DN matching is enabled.
    • Set the TOKEN_AUTH parameter to enable the client to use the Azure AD token. Include the TOKEN_LOCATION parameter to point to the token location. For example:
      TOKEN_AUTH=OAUTH 
      TOKEN_LOCATION="token_location" 

      Note that there is no default location. If the token is named token, then you only need to specify the file directory (for example, /test/oracle/aad-token). If the token name is different from token (for example, azure.token), then you must include this name in the path (for example, /test/oracle/aad-token/azure.token).

You can specify the TOKEN_AUTH and TOKEN_LOCATION parameters in tnsnames.ora, as well as in sqlnet.ora. The TOKEN_AUTH and TOKEN_LOCATION values in the tnsnames.ora connect strings take precedence over the sqlnet.ora settings for that connection. For example:

(description= 
  (retry_count=20)(retry_delay=3)
  (address=(protocol=tcps)(port=1522)
  (host=example.us-phoenix-1.oraclecloud.com))
  (connect_data=(service_name=aaabbbccc_exampledb_high.example.oraclecloud.com))
  (security=(ssl_server_cert_dn="CN=example.uscom-east-1.oraclecloud.com, 
     OU=Oracle BMCS US, O=Example Corporation, 
     L=Redwood City, ST=California, C=US")
  (TOKEN_AUTH=OAUTH)(TOKEN_LOCATION="/test/oracle/aad-token"))

After the connect string is updated with the TOKEN_AUTH and TOKEN_LOCATION parameters, the Azure user can log in to the Oracle Database instance by running the following command to start SQL*Plus. You can include the connect descriptor itself or use the name of the descriptor from the tnsnames.ora file.

connect /@exampledb_high

Or the user can use the connect string. For example:

connect /@(description= 
  (retry_count=20)(retry_delay=3)
  (address=(protocol=tcps)(port=1522)
  (host=example.us-phoenix-1.oraclecloud.com))
  (connect_data=(service_name=aaabbbccc_exampledb_high.example.oraclecloud.com))
  (security=(ssl_server_cert_dn="CN=example.uscom-east-1.oraclecloud.com, 
     OU=Oracle BMCS US, O=Example Corporation, 
     L=Redwood City, ST=California, C=US") (TOKEN_AUTH=OAUTH)(TOKEN_LOCATION="/test/oracle/aad-token")

The database client is already configured to get an Azure OAuth2 token because TOKEN_AUTH has already been set, either through the sqlnet.ora file or in a connect string. The database client gets the OAuth2 token and then sends the token to the Oracle Database instance.

Trace Files for Troubleshooting Oracle Database Client Connections with Azure AD

You can use trace files to troubleshoot Oracle Database client connections with Azure AD connections.

About 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.

Setting Client Tracing for Token Authentication

You can add EVENT settings to the client-side sqlnet.ora file to control client tracing.

These EVENT settings can be used for both IAM and Azure AD connections with Oracle Database.
  • Use either of the following methods:
    • Add the following settings to the client side sqlnet.ora file:
      • EVENT_25701=14 for low level tracing
      • EVENT_25701=15 for high level tracing
    • Set the environment variable EVENT_25701:
      • EVENT_25701=14 for low level tracing
      • EVENT_25701=15 for high level tracing
    Client trace files are created in the following locations:
    • Linux: $ORACLE_HOME/log/diag/clients
    • Windows: %ORACLE_HOME%\log\diag\clients

    You can use the ADR_BASE parameter in the client side sqlnet.ora to specify the directory in which tracing messages are stored. Ensure that the directory path is valid and has write permissions. Ensure that the DIAG_ADR_ENABLED parameter is not set to FALSE.

    An example of setting ADR_BASE is as follows:

    ADR_BASE=/oracle/oauth2/trace