I mentioned in my previous blog that many customer use-cases today are shaped around multi-cloud in which Oracle has been heavily investing. I also hinted that there is more coming for Autonomous Database on Shared Exadata Infrastructure (ADB-S) on this topic. This is still just the beginning, but as promised, I’d like to introduce another interesting and cool capability that will help you establish the bridge between your Autonomous Database and your resources that reside in Microsoft Azure. Many of you loved when we introduced OCI resource principal and Amazon ARN support for Autonomous Database to help you access your resources in OCI or AWS easily, without having to create or store individual credentials in the database. To expand on that capability, ADB-S now supports Azure service principal authentication with the same simplicity and ease of use.
Azure service principal is based on the same concept as OCI resource principal or Amazon ARN. To explain it in more Azure-friendly language, an entity (e.g. ADB-S) that wants to access any resources governed by an Azure tenancy must be represented by a security principal. If this entity is a user, the authentication method is called user principal; on the other hand, if it’s an application then it’s called service principal. In other words, which resources an entity can access and what it can do with them are dictated by this principal object and its role assignments.
Instead of boring you with more words, I’d like to demonstrate this feature with a simple use-case. In fact, it’s the same use-case that I used in my OCI resource principal blog, in which I loaded some data into my ADB-S instance from Object Storage via the OCI resource principal. I will repeat it here with the exception that my data source will be in Azure blob storage instead. Here’re the steps that I will follow:
- Enable Azure service principal authentication in ADB-S
- Provide Azure application consent
- Assign necessary roles for the application to access the Azure resources
- Load data from Azure blob storage via Azure service principal
Enable Azure service principal authentication in ADB-S
First, I’m going to enable the Azure service principal authentication in my ADB-S instance, and I need my Azure Active Directory (AD) tenant ID for this so that my ADB-S instance can be properly associated with my Azure tenancy and registered as an authorized application in Azure (see How to find your Azure Active Directory tenant ID if you don’t know it):
SQL> BEGIN
2 DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(
3 provider => 'AZURE',
4 params => JSON_OBJECT('azure_tenantid' value '74***-****-****-****-****67'));
5 END;
6 /
PL/SQL procedure successfully completed.
You can also specify the username parameter in this procedure if you are enabling this authentication for a user other than ADMIN. I omitted it since I will be working with the ADMIN user in my demo.
Provide Azure application consent
Since ADB-S will be accessing the resources in my Azure tenancy on my behalf, I need to follow the Azure AD application consent procedure (see Understanding Azure AD application consent experiences for more details). To put it simply, when I enabled the Azure service principal, ADB-S created some metadata about my Azure tenancy to register itself as an application on the Azure side and I (as an Azure tenancy admin) need to give this application the necessary permission to access resources in my Azure tenancy.
Let’s first take a look at the metadata I was referring to above. For this, I will query the CLOUD_INTEGRATIONS view:
SQL> SELECT param_name, param_value FROM CLOUD_INTEGRATIONS; PARAM_NAME PARAM_VALUE ------------------ ------------------------------------------------------------------------------ azure_tenantid 74***-****-****-****-****67 azure_consent_url https://login.microsoftonline.com/'74***-****-****-****_****67'/oauth2/v2.0/***&scope=User.read azure_app_name ADBS_APP_OCID1.AUTONOMOUSDATABASE.OC1.IAD.******VFA
To fulfill the consent request for our application, I’ll go to URL pointed by the azure_consent_url parameter and accept the request:
Assign necessary roles for the application to access the Azure resources
At this point, I’m almost ready to use Azure service principal in my ADB-S instance. As the step of my configuration, I’ll add the necessary members and access control (IAM) role(s) to my Azure storage account. In other words, I need to add my application as an authorized member and grant it the needed role(s) to access Azure blob storage.
To add a role, I’ll navigate to Azure console home -> Storage accounts -> <myStorageAccountname> -> Access Control (IAM) -> Add -> Add Role Assignment -> Role and search for “blob storage”. Since I only need read access, I will choose the “Storage Blob Data Reader” role.
To add my application as a member, I’ll navigate to Members (right next to Role tab on the same page) -> Select members and copy/paste the the value of azure_app_name (that we obtained in the previous step) in the search box. Once everything looks good, I can hit “Review + assign”:
Load data from Azure blob storage via Azure service principal
Now that I’m all set, I can go ahead and load some data from my Azure blob storage (please note AZURE$PA as the credential_name in DBMS_CLOUD.COPY_DATA procedure):
SQL> CREATE TABLE CHANNELS
2 (channel_id CHAR(1),
3 channel_desc VARCHAR2(20),
4 channel_class VARCHAR2(20)
5 );
Table CHANNELS created.
SQL> BEGIN
2 DBMS_CLOUD.COPY_DATA(
3 table_name =>'CHANNELS',
4 credential_name =>'AZURE$PA',
5 file_uri_list =>'https://ctuzlastorage.blob.core.windows.net/ctuzlabucket/chan_v3.dat',
6 format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true')
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from channels;
COUNT(*)
___________
10
That’s it! I told you it was simple. Just to recap, many cloud providers including OCI offer principal-based authentication today, which eliminates the need for users to create and store individual credentials or access key/tokens in the database. ADB-S supporting the Azure service principal authentication is yet another milestone for our and our customers’ multi-cloud efforts. I know I said it before but I will say it again, more to come!