All the best combined: Cloud, Db2, Python, Serverless and Security

Add a cloud service ID as Db2 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 (see screenshot) and grant database privileges. Thereafter, I share Python code for connecting to Db2 using the service ID with an API key.

All this helps to reduce the set of privileges held by a user or service and hence increases cloud security.

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 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 Writer privilege for Db2. It looks like this when picking Db2.

Assign Db2 access to a service ID

Service ID details

Once you 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. Thus, I copied it.

Service ID to Db2 user

Thereafter, it is time to 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 the top 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 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):

Note that "Authentication=GSSplugin" and "SECURITY=SSL" are set. The code makes use of Pandas and a dataframe as used by some data sciene projects, but of course could also just utilize the regular DBI interface. The output is similar to this:

{'columns': ['1'], 'index': [0], 'data': [['1222']]}

Summary

In this blog I walked you through the process of setting up and then using an IBM Cloud service ID as Db2 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.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.