See End Users in Db2
Using Trusted Context
Dean Compher
29 April 2019
Many organizations would like to know which individual users are doing what in their Db2 databases, but because the application server uses one generic user id to make all connections, user information is not available within the database. From the database side all you can see are the connections, but the user id is the same on all of them. Using he Db2 Trusted Context feature you can easily get that information into your database and then you can monitor the activity of various user ids to see who is doing what and see who is consuming the most resources. Once you’ve done that you can give different authorities to different users, but that is not necessary. In this article I’ll present a way of using Trusted Context that takes very little effort to implement and allows you to capture what end users are doing.
Trusted Context has been around a very long time, and you can do many things with it to improve the security of your database. This is especially important if you want to monitor and potentially control access to your most sensitive information. You can read more about the many interesting things you can do with Trusted Context in the knowledge center. Unfortunately, I don’t see it used very frequently, so in this article I show a way to get started seeing who is using your database that requires minimal changes to your system.
Trusted Context works by making a few changes to the way the client connects to Db2 and adding an object in Db2. In our discussion we will assume that the connections are being made from an application server such as WebSphere Application Server or Tomcat that opens several connections to the database, using those connections to fulfil user requests. Frequently this is done using the Db2 JDBC or ODBC driver. To allow a trusted connection, the application server can just add a property to the database connection string when connecting to the database that will allow the user to be switched as transactions are run for different people. Then when queries are run on a connection for a user, a property is specified that passes a user id to Db2. When a different user uses that connection you just switch that property. The act of switching users is very light weight. It does not cause the connection to be re-established. I’ll provide more detail later and some examples.
However, before you try to make any trusted connections, you first must create a Trusted Context object in the database. This object allows you a lot of flexibility as to what users can do when a trusted connection is made. However, my example will show the minimum that is needed to allow trusted connections to be made so you can see the end user.
create trusted context ctx1
based upon connection using system authid app_id
attributes (address '172.18.24.3',
address '192.168.0.8',
address '172.17.0.1')
default role app_server_role
with use for PUBLIC without authentication
enable
There are additional options for the CREAT TRUSTED CONTEXT command that you can read about, but here I will highlight what my example is doing:
· Name this Trusted Context object “ctx1”
· app_id is the user id expected to make the initial connection. In our case it is the user id that the application server uses to connect to Db2. If you had multiple different application servers connecting with different id’s then you would create multiple trusted context objects.
· The only time that the connection will be promoted to trusted is if it is coming from one of the IP addresses shown. (Domains names can be used instead IP addresses.) This means that if app_id connects from one of these addresses then it will be allowed to switch the user. If app_id connects from a different id, it will still be allowed to connect, but its connection will not be promoted and cannot do things like switching the user.
· In this case, the app_id user and any users to which it switches will operate with any authorities they already had plus the authorities granted to the app_server_role. So, before you create the Trusted Context, it is a good idea to determine all of the authorities that the app_id user has and grant all of those to the role named here. In addition, make sure to grant connect to this role so that you don’t have to define any users to the database that might come through the trusted context. I’ll show an example of creating a role below.
· By specifying “PUBLIC” in the “with use for” clause you allow the connection to be switched to any user you like. You can specify individual user names here instead of public, but if you do that, then you must remember to recreate the Trusted Context anytime a new user is added to the application server. This is also where you could give different levels of authorities to individual users if you like.
· By specifying “without authentication” means that you don’t need to create the end users’ ids in the database server for use in trusted context. It is also more efficient since “with authentication” causes extra overhead on Db2 every time the connection switches users.
· enable enables the trusted context. If it causes problems, trusted contexts can easily be dropped or disabled.
Since the thrust of my article is making trusted context easily work for a system already in place, I created this trusted context in such a way, that you don’t have to know any of the user ids used to connect to the application server. Each connection can be switched to any user by the application server and that user can run with the authorities defined in the app_server_role. You do not have to define any users on the database server or give them any specific privileges when all you want to do is track the end users.
Here is the role that I created and authorized for this article. It assumes that the only tables that the app_id user had access were the employee and department tables. Again make sure to grant connect to the role, or you will get an error when trying to switch connections:
create role app_server_role;
grant connect on database to role app_server_role;
grant select on db2inst1.department to role app_server_role;
grant select on db2inst1.employee to role app_server_role;
As I noted earlier, you do not need to change your application to use Trusted Context if your connections are established by your web server through your JDBC, ODBC or other driver. You merely set a property on each connection when it is opened and switch the user as different users use the connection. The establishing a trusted connection page describes this at high level and shows the APIs involved for widely used languages. JDBC is probably the most widely used so I have provided the link to a more detailed example of using Trusted Context for that driver. Here is page of code examples for other languages. You will need to drill down to find the Trusted Context examples. I’m currently learning Python, so I’ll show some examples using Python instead of a web server since I don’t have easy access to one.
To demonstrate connecting with a trusted connection and then switching connections I ran the following Python script after creating the role and Trusted Context shown above. In it you will see that I connect using the app_id user id and then switch the connection to a user called db2dean and then one called db2mary. Before each of the user id switches, I have the script pause for several seconds so that I can show the connection from the database’s perspective. I’ll show that in more detail later.
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
import ibm_db
import time
#
# ## Define initial property that tells Db2 that I'm opening this
# ## connection as a trusted connection.
options = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE}
# ## Define properties needed to switch the connection to db2dean or db2mary
tc_db2dean = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "db2dean" }
tc_db2mary = {ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE,
ibm_db.SQL_ATTR_TRUSTED_CONTEXT_USERID: "db2mary" }
# Define a select statement
query="select count(*) from db2inst1.employee"
# Connect to the database with the trusted context option
conn = ibm_db.connect("DATABASE=SAMPLE;HOSTNAME=192.168.0.8;PORT=50000; + \
PROTOCOL=TCPIP;UID=app_id;PWD=passw0rd;", "", "",options)
if conn:
print("** Pausing to allow list apps before switch to db2dean **")
time.sleep(10)
print("## Proceeding ##")
# Switch the connection to user "db2dean" as defined above in
# the tc_db2dean string
ibm_db.set_option(conn, tc_db2dean, 1)
# Execute the query on employee and print the result
stmt=ibm_db.exec_immediate(conn, query)
result = ibm_db.fetch_both(stmt)
print ("Number of employee rows selected by db2dean = "+ str(result[0]))
stmt=ibm_db.exec_immediate(conn, "commit")
print("** Pausing to allow list apps before switch to db2mary **")
time.sleep(10)
print("## Proceeding ##")
# Switch the connection to user "db2mary" as defined above in
# the tc_db2mary string
ibm_db.set_option(conn, tc_db2mary, 1)
stmt=ibm_db.exec_immediate(conn, query)
result = ibm_db.fetch_both(stmt)
print ("Number of employee rows selected by db2mary = "+ str(result[0]))
stmt=ibm_db.exec_immediate(conn, "commit")
# pause again so that I can list applications before closing
# the connection.
time.sleep(10)
ibm_db.close(conn)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
After defining a number of strings in variables, the script connects to the database using the ibm_db.connect function. As you can see, I’m connecting with the app_id user. The way Db2 knows that I want to make a trusted connection is because I specify the variable options that I defined earlier as “ibm_db.SQL_ATTR_USE_TRUSTED_CONTEXT: ibm_db.SQL_TRUE”. That is the only thing that distinguished a trusted connection request from any other connection request in Python. If I didn’t want a trusted connection, then I would make the same function call and just not specify the last parameter.
After starting the script and getting to the first “sleep” command, here is what was displayed on the output of my terminal window where I was executing the script:
$ python3 trust.py
** Pausing to allow list apps before switch to db2dean **
While the script was sleeping, I ran a “db2 list application” on the Linux server where Db2 is running. As you can see the user APP_ID is connected
Auth Id Application Appl. Application Id
Name Handle
-------- -------------- ---------- -----------------------------
APP_ID python3 44 192.168.0.8.51361.190214172855
DB2INST1 db2jcc_applica 22 172.17.0.1.48086.190214172255
DB2INST1 db2jcc_applica 21 172.17.0.1.48084.190214172254
Some seconds later the sleep ended and the ibm_db.set_option(conn, tc_db2dean, 1)function was called to switch the user to db2dean in the “conn” connection handle. Note that I defined the tc_db2dean variable earlier in the script. Using the string just tells Db2 that I want to continue using trusted context and to whom I wish the user to be while running any subsequent SQL through this connection. Here is the script output and the list applications at the time the script was sleeping the second time:
$ python3 trust.py
** Pausing to allow list apps before switch to db2dean **
## Proceeding ##
Number of employee rows selected by db2dean = 42
** Pausing to allow list apps before switch to db2mary **
Auth Id Application Appl. Application Id
Name Handle
-------- -------------- ---------- ------------------------------
DB2DEAN python3 44 192.168.0.8.51361.190214172855
DB2INST1 db2jcc_applica 22 172.17.0.1.48086.190214172255
DB2INST1 db2jcc_applica 21 172.17.0.1.48084.190214172254
Finally, the script reached the final sleep step, and here are the outputs at that point after calling the command to switch the user to db2mary:
$ python3 trust.py
** Pausing to allow list apps before switch to db2dean **
## Proceeding ##
Number of employee rows selected by db2dean = 42
** Pausing to allow list apps before switch to db2mary **
## Proceeding ##
Number of employee rows selected by db2mary = 42
Auth Id Application Appl. Application Id
Name Handle
-------- -------------- ---------- ------------------------------
DB2MARY python3 44 192.168.0.8.51361.190214172855
DB2INST1 db2jcc_applica 22 172.17.0.1.48086.190214172255
DB2INST1 db2jcc_applica 21 172.17.0.1.48084.190214172254
As you can see from the Db2 side, the user id started at app_id then changed to db2dean and finally to db2mary, but the application handle and application id remained the same. This demonstrates that a new connection was not established, but you can now keep an eye on who is actually doing what in your database and later give different authorities to different users if you like. You should note that you must commit or roll back your work, or the user id may not be switched. This is the case even if you are only selecting data. It took me quite a while to figure out why my users were sometimes not being switched, but adding commands to commit my work after each query fixed that. You can only switch users between transactions (units of work).
There are a few things worth noting about the users in this example:
· I’m using a Linux machine that doesn’t have LDAP, and users are just defined locally to the OS. So, to initially connect directly to my database using the app_id user id, I had to define it on the server along with a password.
· The user ids db2dean and db2mary are not defined on my Linux machine, so they would not be able to connect directly to my database. They can only become the user through the app_id connection.
· No authorities at all are granted to db2dean or db2 in my database. So even if they were able to connect to the database they would not be able to query the employee table or any other object in the database. You should make sure that PUBLIC does not have any rights in the database, whether you are using trusted context or not.
· The users db2dean and db2mary were able to query the employee table only through the trusted context when using the application, but you should note that they were doing that before and their use was actually anonymous because everyone’s connection was done with the app_id user. Now you can know which user is doing what.
As I noted earlier there are examples of using trusted context for many programming languages in the knowledge center, but Python is not one of them. However, there are many Python Db2 examples on github. Among them is a robust Trusted Context example.
***
If you think of other interesting ways to use Trusted Context, please post them on my Facebook Page or my db2Dean and Friends Community along with any other comments you may have.