Introduction
I have recently activated the Db2 Audit facility on one of our client’s production databases with the purpose of collecting the user login information.
But, when I looked at the recorded data later on, I realised that each audit event had three different “user” IDs recorded, all according to the description in the Db2 Knowledge Centre:
Furthermore, some audit events contained the exact same data in all three columns, some contained different values (in some of those columns) and some others contained NULL values (again, in some of those columns).
So, what does each of those columns represent?
And when are they populated with data, and when not?
Which is which?
Authorization ID
This ID is probably the easiest of all three to understand what it represents – the actual username used to connect to a database (together with an appropriate password). In the following example, the Authorization ID would be set to the value “myuser”:
db2 connect to DBNAME user myuser using mypassword
Database Connection Information
Database server = DB2/LINUXX8664 11.5.9.0
SQL authorization ID = MYUSER
Local database alias = DBNAME
Generally, the Authorization ID value may have different meanings, according to the context in which it is used, as explained in this Db2 Knowledge Centre (KC) article.
The initial value used to connect to the database is also called the System Authorization ID and is saved in the SYSTEM_USER special register. It cannot be changed for the duration of the DB connection, and can be queried with the following command:
db2 "values SYSTEM_USER"
1
---------------------------------
DB2INST1
1 record(s) selected.
The values used for other contexts may be different from case to case, or their value can be changed, like for example the Session Authorization ID, via the SET SESSION AUTHORIZATION statement.
In my experience, this column is always populated in the Audit report (category “Validate”).
User ID
The Db2 KC has scant information on the true meaning of the User ID value. In other words, I wasn’t able to figure out at all what it represents by searching the Db2 KC (to that matter, if anyone has a good link, please share!). In addition, googling produced no meaningful results as well (unless I missed something crucial here too?!). So, rather desperately, I asked IBM Support for an explanation and soon learned that the User ID value will basically always be equal to the Authorization ID value.
OK, if User ID is always equal to the Authorization ID, then why do we need it anyway?
Anyone knows the answer, please let me know!
Execution ID
The Execution ID value can be queried via the MON_GET_CONNECTION table function, as described in the Db2 KC. If we follow the link for the Execution ID monitor element from that page, we will learn the following about its meaning:
The ID that the user specified when logging in to the operating system. This ID is distinct from auth_id, which the user specifies when connecting to the database.
So, the Execution ID is equal to the underlying operating system user ID, from where the database connection was established. And it doesn’t have to be the same as the auth_id!
And, with a bit more help from the IBM Support, I learned that this parameter may not always be populated, mostly in cases where the connection is remote, and the remote user is irrelevant. Such as in the ODBC/JDBC connections. The Audit (“Validate”) report will reflect this fact.
In my experience, this is exactly what happens – the column is populated for some Audit records, but NULL for other ones.
Messy Summary
Are you feeling as confused about these Db2 User IDs as I am?
Could all this have been done in a better way (i.e. giving more meaningful names to the user ID parameters; dropping the duplicates…)?
Probably, but it is what it is, and we have to live with it – and know which is which 😊
Finally, here’s a link to a useful IBM Support article that confirms some of the statements I made in this short tech note, maybe it will be of value to you guys as well!