Increase Information Security for Db2 on IBM Cloud

3 min read

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:

Assign Db2 access to a service ID.

Assign Db2 access to a service ID.

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:

Service ID details.

Service ID details.

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.

Add a cloud service ID as a Db2 user.

Add a cloud service ID as a Db2 user.

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): 

def db2test(iamKey,hostname):
    connstr="DATABASE=BLUDB;Authentication=GSSplugin;HOSTNAME={};PORT=50001;PROTOCOL=TCPIP;SECURITY=SSL;APIKEY={};".format(hostname,iamKey)
    conn1 = dbi.connect(connstr)
    # Quick test with Pandas and dataframe
    df = pd.read_sql("select count(*) from syscat.tables", conn1)
    return df.to_json(orient='split')

 The full source is available in this Gist. Note that Authentication=GSSplugin and 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:

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

Summary

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.

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

Be the first to hear about news, product updates, and innovation from IBM Cloud