Database Authentication using Azure AD Token #JoelKallmanDay

Oracle Database supports modern ways of authentication using some of the cloud directories – OCI and Azure. I’ve tested Azure AD and set up authentication-only integration of an Oracle Autonomous DB (always free tier) with Azure AD (no special subscriptions as well).

The documentation:

Use Azure Active Directory (Azure AD) with Autonomous Database
Authenticating and Authorizing Microsoft Azure Active Directory Users for Oracle Databases
Multi-Cloud Security – Oracle Database and Azure Active Directory integration

As always, it seems a bit strange initially and becomes more clear the more you read and try things in a test. Yet there are a few issues, primarily because all the docs I’ve read set up authorization (role assignment) as well which I didn’t want. I’d like to have an AD user mapped to a DB user, and allow AD user to connect to DB as the DB user using Azure identity. Plus make it work using CLI commands rather than UI.
On a high level, the process should be like this:

• register database as an Azure application
• register application which will be connecting to the database as an Azure application
• add a permission to connect to the database using registered application
• assign AD user to the Azure application
• configure database with certain details about Azure application registration
• add a database user identified globally as Azure user
• set up client application for connection to the database using token
• get an Azure access token for Azure application
• connect to the database using the token

Detailed steps:

1. A bit of setup and cleanup. I create an AD user az_user1 for testing, and use oracle-db-test as part of the DB URI in Azure. Note that the documentation doesn’t mention a URI can be any string, and suggest to use the application ID.

# get default domain and use it for db app uri
DEFAULT_DOMAIN=$(az rest -m GET -u "https://graph.microsoft.com/v1.0/domains?$select=id" | jq -r '.value[0].id')
DB_URI=https://$DEFAULT_DOMAIN/oracle-db-test

# new user name
AZ_USR_NAME=az_user1
AZ_USR_UPN=$AZ_USR_NAME@$DEFAULT_DOMAIN

TENANT_ID=$(az account show | jq -r '.homeTenantId')

# delete app registration "Oracle DB Test"
az ad app delete --id $(az ad app list --output table | grep "Oracle DB Test" | awk '{print $4}') 2>/dev/null

# delete user if needed
# az ad user delete --id $(az ad user list --display-name az_user1 -o json | jq '.[0].id')

2. Create App registration “Oracle DB Test” for a database. The most important here is to include upn – User Principal Name – into the Token configuration of the App registration. Oracle DB uses upn to find AD user in Azure when a direct user-to-user mapping is in use. If upn is missing in the Token, DB won’t be able to log you in.
   A weird thing is a Service Principal for the App registration. If you create App registration in the UI, an Enterprise Application with the same name is created behind the scene for you. This doesn’t happen when you create an App registration via CLI, and EA appears only after you create a Service Principal for the App registration.

echo '{"accessToken": [{
        "additionalProperties": [
          "include_externally_authenticated_upn_without_hash"
        ],
        "essential": false,
        "name": "upn",
        "source": null
      }]}' > db-claims.json

# create db registration
DB_APP_REG=$(az ad app create --display-name "Oracle DB Test" \
    --enable-access-token-issuance true \
    --identifier-uris $DB_URI \
    --sign-in-audience AzureADandPersonalMicrosoftAccount \
    --optional-claims db-claims.json)

DB_APP_ID=$(echo $DB_APP_REG | jq -r '.appId')
DB_ID=$(echo $DB_APP_REG | jq -r '.id')

# create service principal - needed for the enterprise application to appear (wtf?)
SP_ID=$(az ad sp create --id $DB_APP_ID | jq -r '.id')

3. Next item is to create a scope session:scope:connect for connection and to assign application permission. The scope name can also be different from the recommended. There is no appropriate CLI command to create a scope, and REST API is the only choice here. 04b07795-8ddb-461a-bbee-02f9e1bf7b46 is ID of the MS Azure CLI – it’s already registered Azure application which will be used to get a database access token. The documentation says you need to register your client application as well, but in reality if you only use Azure CLI, database client registration is not required. This may change when/if a client application – for example, SQL Developer – will be able to transparently get an Azure token for you directly from Azure. Then it will be necessary to also register the client and assign it permission for the connection scope.

SCOPE_ID=$(uuidgen)

OAUTH_SCOPE='{
"api": {
    "oauth2PermissionScopes": [
      {
        "adminConsentDescription": "DB connection",
        "adminConsentDisplayName": "DB connection",
        "id": "'$SCOPE_ID'",
        "isEnabled": true,
        "type": "User",
        "userConsentDescription": null,
        "userConsentDisplayName": null,
        "value": "session:scope:connect"
      }
    ],
    "requestedAccessTokenVersion": 2
  }
}'

# appId of the az cli
OAUTH_AZ_CLI='{
"api": {
    "preAuthorizedApplications": [
      {
        "appId": "04b07795-8ddb-461a-bbee-02f9e1bf7b46",
        "delegatedPermissionIds": [
          "'$SCOPE_ID'"
        ]
      }
    ],
    "requestedAccessTokenVersion": 2
  }
}'

# add a scope and permission to use it for az cli
az rest --method PATCH --uri "https://graph.microsoft.com/v1.0/applications/$DB_ID" --headers Content-Type=application/json --body "$OAUTH_SCOPE"
az rest --method PATCH --uri "https://graph.microsoft.com/v1.0/applications/$DB_ID" --headers Content-Type=application/json --body "$OAUTH_AZ_CLI"

#to check the json, use:
#az rest --method GET --uri "https://graph.microsoft.com/v1.0/applications/$DB_ID" -o json

4. Create an Azure AD test user, and assign Owner role for default subscription. I’m sure there’s a better way to make it work in a real environment, but for my testing it is enough. You may use an existing user as well; I just wanted to have the ability to re-run my simple code.

# create a test user
AZ_USR_PWD=$(uuidgen)
AZ_USR=$(az ad user create --display-name az_user1 --user-principal-name $AZ_USR_UPN --password $AZ_USR_PWD)
AZ_USR_ID=$(echo $AZ_USR | jq -r '.id')

az role assignment create --assignee $AZ_USR_UPN --role Owner --scope /subscriptions/$(az account show -o json | jq -r '.id')

5. Now it’s necessary to assign test user to the Enterprise Application “Oracle DB Test”. Unfortunately there is no CLI command to do so, and the REST API is only available under certain subscription level. I don’t want to pay for it so assignment is done via Azure Portal: App registrations -> Oracle DB Test -> App roles -> How do I assign App roles -> Enterprise Applications -> Oracle DB Test (Enterprise Application) -> Users and groups -> Add user/group -> Add az_user1 with Default Access. The UI and the structure of App registration/Enterprise application looks very weird.
6. At this point Azure config is ready. Oracle DB config is rather simple. Run the code below in the same shell to get what needs to be run in Oracle ADB under ADMIN to enable Azure AD integration & create a DB user:

echo "
BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_EXTERNAL_AUTHENTICATION(
      type   => 'AZURE_AD',
      params => JSON_OBJECT('tenant_id' VALUE '$TENANT_ID',
                            'application_id' VALUE '$DB_APP_ID',
                            'application_id_uri' VALUE '$DB_URI'),
      force => TRUE
  );
END;
/
create user az_user1 identified globally as 'AZURE_USER=$AZ_USR_UPN';
-- choose appropriate permissions instead
grant pdb_dba to az_user1;
"

7. I use SQL Developer as a DB client. The connection setup is similar to the usual cloud database access: you download a cloud wallet, and then use Cloud Wallet as the Connection Type in SQL Developer. For an Azure AD authentication change Authentication Type to OS; and on the Advanced tab add two properties: oracle.jdbc.tokenAuthentication=OAUTH and oracle.jdbc.tokenLocation=/path/to/token
8. Get a token & test the connection in SQL Developer

az login
az account get-access-token --resource=$DB_URI --query accessToken --output tsv > /path/to/token

If the token is incorrect, unavailable, or expired, there will be an error pointing you in the right direction. If the upn is missing in the token, there will be ORA-1017 error. You can ensure that upn is in the token by pasting token content in jwt.io and checking human readable token in a json format.
That’s it! The whole process is relatively simple yet Azure design makes it more complex than it should be.