Hello,

I'm writing a simple web-interface where users may see the sessions on the
database, including
- username
- application name
- status
- which session is blocking, in case the session is in lock-wait status

Also, I need to write a little HOWTO on how to detect if one's session is
locked, and - if so - identify which other user is blocking.

I do not want users to be able to see actual SQL statements, because they
might include sensitive information like
INSERT INTO foo(sensitive_data) VALUES ('very sensitive')

I first tried joining the sysibmadm.applications and sysibmadm.lockwaits
views, projecting the needed columns. This worked fine, except that it
required rather high permissions, and wrapping it in views, table
functions or a table returning procedure didn't seem to enable the
ordinary user to access the data.

Then I discovered the sysibmadm.mon_current_sql and
sysibmadm.mon_lockwaits views. These seem to give access to the same
information as the sysibmadm.applications/sysibmadm.lockwaits views,
except that there doesn't seem to be any restrictions on them. This means
that people may actually see each other's SQL; I find it rather strange
that DB2 by default reveals potentially sensitive information - am I
misunderstanding something here?
I could try revoking PUBLICs access to sysibmadm.mon_current_sql, but I'm
afraid to do so: could there be bad side-effects of doing that?

--
Troels

By the way:

Speaking of sysibmadm.mon_current_sql's leakage of potentially sensitive
data:

Are there other built-in views/table functions/... which (out-of-the-box)
will reveal SQL statements to PUBLIC?

--
Troels

I wrote:
It turns out that there is an APAR on this:
https://www-304.ibm.com/support/entd...id=swg1IC67819

Does this mean that the problem is fixed in 9.7FP3, or is the APAR just a
note on how to work around the problem?

(I'm not 100% fluent in IBM jargon.)

--
Troels

On Sep 16, 2:19*pm, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:
An APAR (Authorised Program Analysis Report) is a description of a
problem that is due to a bug in IBM's software (and acknowledged as
such by IBM). The APAR is given a unique number to be able to track it
during its lifetime and an estimated resolution time.
After the problem is solved the support group issues a PTF (Program
Temporary Fix) that can be sned out to customers affected by the bug.
At the moment a PTF is available, the APAR will get a status 'closed'.
An individual customer can decide to immediately apply the PTF or wait
for the next FixPack(age); each FP includes a number of PTF's.
Each PTF is also sent to the development group that is working on the
next release.

That's the theory at least, now reality... ;-)

In your case according to the link you provided it looks like the APAR
IC67819 is still 'open' and no PTF is available:
APAR status = OPEN
Temporary fix = --
Submitted date = 2010-04-12
Closed date = --
Last modified dat = 2010-04-12

But the "Fix List for DB2 Version 9.7 for Linux, UNIX and Windows"
page at
http://www-01.ibm.com/support/docvie...id=swg21412438
shows the APAR is included both in FP3 and in FP2.
It looks like someone forgot to update the APAR-page ...

HTH

--
Jeroen

On Sep 16, 7:40*pm, The Boss <nlt... (AT) baasbovenbaas (DOT) demon.nl> wrote:
It seems someopne from Support has read my previous comment, as the
APAR has been closed at 2010-09-16.
Here's the description and resolution as specified on the APAR-page:

<q>
Error description

The following views under the SYSIBMADM schema should not be
viewable by public

MON_BP_UTILIZATION
MON_TBSP_UTILIZATION
MON_LOCKWAITS
MON_PKG_CACHE_SUMMARY
MON_CURRENT_SQL
MON_CURRENT_UOW
MON_SERVICE_SUBCLASS_SUMMARY
MON_WORKLOAD_SUMMARY
MON_CONNECTION_SUMMARY
MON_DB_SUMMARY

Local fix

Revoke SELECT privilege on each view from PUBLIC.
Grant SELECT privilege to only those users that need access.
By default, DBADM, SQLADM and DATAACCESS have SELECT privilege
on these views.

Fixes are available for
DB2 version 9.7 FP2:
http://www-01.ibm.com/support/docvie...id=swg24026928
DB2 version 9.7 FP3:
http://www-01.ibm.com/support/docvie...id=swg24027906

HTH

--
Jeroen

I'd suggest the following:

1) revoke access from PUBLIC for the tables in question.
2) write a stored procedure that accesses the tables and only returns the
required columns.
3) grant the users execution privilege on the SP.

Cheers,
Helmut

On 12.09.10 11:00 , Troels Arvin wrote:
No, except that users won't be able to access those tables. :-)

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/