I’ve been working at a customer site lately that has a lot of interesting security requirements. I’m obviously not getting into detail there but let’s suffice to say that it is probably the most complex such situation that I’ve come across.
One of the simpler requirements was to know who was using each and every Discoverer report that was being run. Initially I thought this would be simple enough to do through the „The Discoverer V5 EUL“ business area that comes with the installation and is intended for just such things. When looking at it, I found that they do not store the Single Sign On (SSO) username, only the Oracle database username in question. So if a user authenticates using SSO as John but is actually running the report as Benni, then only Benni gets stored and we have no idea who really ran the report/authenticated.
I spent considerable time looking for a solution on this but to no avail. I found out some interesting things about Discoverer along the way, for example that a function can be run upon login into Discoverer or when a workbook is saved. (I may blog on that later).
Anyway, there is a simple, non supported solution to this problem. I’ve implemented it and it seems to be working fine. So, to capture this information I made the following changes to the table EUL5_QPP_STATS in the EUL schema (Discoverer owner):
alter table eul5_qpp_stats add (sso_user varchar2(32) default sys_context('USERENV','CLIENT_IDENTIFIER',32))
This will cause the SSO user to be inserted into this column whenever query statistics are written to the database. Please note that Discoverer does not write to this table until the user logs out so you may not see any records in there until the user has disconnected (it is possible that the writes are done i.e. every 5 minutes). If the Discoverer connection is a regular connection (not SSO) then this column will be NULL.
I had to change the Business Area „The Discoverer V5 EUL“ as well adding the new column to the folder EUL5_QPP_STATISTICS. (Do a refresh folder and ignore all suggested changes, except the one with the new column SSO_USER) using the Discoverer Administration utility.
All that remains is to change the reports using USERNAME to use SSO_USER as well. The report used is called EUL_QUERY_STATISTICS.
If Discoverer is upgraded it is possible that these changes will be lost. In that case, the above procedure has to be repeated (unless Oracle starts doing this by default which would be nice). In any case, a simple way to capture the SSO user.