Db2: How to grant privileges only to app, not a user?

Question

I have a cloud app that needs to access my Db2 database. It requires both read and write access to some tables. How can I restrict access to that specific app and the usage pattern? I don't want that userid have database access outside that context.

Answer 1

Db2 has the security feature of trusted contexts. A trusted context describes attributes of a database connection. It consists of the userid and can include the application's IP address or hostname and the required level of connection security, e.g., whether SSL is mandatory.

1. Within the cloud app use a userid (authorization id) that does not have any Db2 privileges. Revoke them if they exist.
2. Create a new database role. Grant the connect privilege and the required table privileges to that role.
3. Create a trusted context for that userid from 1). Provide the app's hostname and, if you want to enforce SSL, set the encryption attribute to a value of high. Assign the role from 2) as default role.

The userid can only access the data if connected in the way defined in the trusted context object. See this example on trusted contexts for SQL code snippets and more links.