How to use a service ID with an API key as a Db2 database user.
Earlier this year, I wrote about how to use an API key or access token to connect to Db2 (on Cloud). Today, I am going to show you how to set up a service ID (technical user) on IBM Cloud, assign it a Db2 user ID, and grant database privileges. I'll then share Python code for connecting to Db2 using the service ID with an API key for authentication.
All this helps to reduce the set of privileges held by a user or service ID and, therefore, increases information security.
Create an IBM Cloud service ID
A service ID on IBM Cloud identifies a service or an application. It is similar to how a user ID identifies a user and can be compared to a "technical user." In my blog on using Key Protect as a Vault, I briefly explained how service IDs are used for Cloud Functions (see the section "Security configuration"). In some situations, like when creating a new IAM namespace for Cloud Functions, a service ID is automatically created. But you can also create them manually. That's what I did for this blog.
Once created, you can assign access (privileges) to the service ID. I needed to select IAM services, then select Db2 (or Db2 Warehouse) from the list of services, and assign the Writer privilege for Db2. It looks like this when picking Db2:
Once you've assigned access, you should create an API key for the service ID. Copy it to a safe place or download it.
On the page with the service ID, click on Details. It brings up a small info window showing when the ID was created and modified and its ID. That ID, starting with ServiceId, is needed to configure Db2. Make sure to copy it:
Give a service ID to a Db2 user
You should now configure a new user in Db2 (Warehouse) on Cloud. This involves adding the service ID as an IBMid-based user and then granting the necessary database privileges.
For the first step, in the Db2 console, I had to go to Settings and Manage Users. There, I added a new database user—"db2user1"—based on the service ID as shown in this screenshot.
To grant database privileges, I used the SQL editor. Depending on the project setup, this could be as simple as doing nothing because the new user has IMPLICIT_SCHEMA authority. If that authority has been revoked, it could be to grant schema- or table-level access.
Use the API key for Db2 connection in Python
With the setup in place, the last step is to verify the service ID can indeed connect to Db2 using an API key. Using the Python driver for IBM databases and passing in the previously created API key and the hostname of my Db2 Warehouse instance, the following function returns the number of tables in the system catalog. It can run either as standalone Python script or can be deployed as IBM Cloud Functions action (serverless):
The full source is available in this Gist. Note that
SECURITY=SSL are set to allow the use of the API key for authentication. The code makes use of Pandas and a dataframe as done by many data science projects. Of course, it is possible to just utilize the DBI API. The generated output is similar to this:
In this blog post, I walked you through the process of setting up and then using an IBM Cloud service ID as a Db2 on Cloud user. It combines cloud and database security concepts to better scope access and enhance information security. Note that both IBM Cloud IAM (Identity and Access Management) and Db2 support the use of roles. They are useful to simplify security management for a larger set of users and should be applied on top of what was shown in this blog.