Hi All!
I'm looking for an effective way to write a query set that helps me to
check security issues as follows:
1) Database Version
Not sure if it's relevant. Depending on DB2 version, the catalog will
change? A DBA, once told me
that there are no equivalents in MVS DB2 for SYSCAT.INDEXAUTH and
SYSCAT.PASSTHRUAUTH, this is why I'm asking. Is it relevant?
2) DB2 Instances;
3) Databases within each instance;
4) Schemas on each database
It's relevant for security purposes?
5) Tables on each database;
6) All userids that can access data, or potentially have access to it
on each instance,database,schema and table;
7) Userids auths ( S,I,U,D,G) over instance, database, schema and
tables;
Wondering if only auths over the tables are enough, or checking the
whole thing will give me a better picture? Worried if I miss something
here I can lost some chain effect.
8) Last but not least, even if a userid does not have access to the
database through SO - prevented to login - but it still exists as a
database userid, can this userid still used for access the objects
that he have auths via tcp?

I found bunches of queries such as the one below, but I really
appreciate a robust and concise help, and mostly informing if I am
missing something.

Query:
SELECT
SUBSTR(TCREATOR,1,15) AS OWNER,
SUBSTR(TTNAME,1,25) AS NAME,
SUBSTR(GRANTEE,1,10) AS GRANTEE,
SUBSTR(SELECTAUTH,1,1) AS S,
SUBSTR(INSERTAUTH,1,1) AS I,
SUBSTR(UPDATEAUTH,1,1) AS U,
SUBSTR(DELETEAUTH,1,1) AS D,
SUBSTR(GRANTOR,1,8) AS GRANTOR,
GRANTEDTS AS GRANTDATE,
DATEGRANTED AS DATE,
TIMEGRANTED AS TIME
FROM SYSIBM.SYSTABAUTH

Query Results::
OWNER NAME GRANTEE S I U D
GRANTOR GRANTDATE DATE TIME
--------------- ------------------------- ---------- - - - -
-------- -------------------------- ------ --------
BLA TABLE1 USER1 Y Y Y Y
BLA 2005-06-05-11.00.23.455151 050605 11002345
BLA TABLE1 BLA G G G G
DB2ADM1 2002-10-14-17.24.55.660900 021014 17245566

Thanks for your help!
Guto.

"Guto" <gutomore (AT) gmail (DOT) com> wrote

DB2 for LUW, DB2 for z/OS, and DB2 for iSeries are three different
"products" (not versions). There are a lot of similarities in their system
catalogs, but there are also a lot of differences also. Also, there terms
"instance, database, etc" mean different things (or sometimes don't exist)
among these 3 products.

Within these 3 products the catalog tables (or views) may change from one
release to the next (but usually not very much).

On Sep 21, 4:50*am, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
Thanks Mark, so what the DBA told me about the DB2 on MVS makes sense
though.
Any tips on the queries?
Cheers!

On Sep 21, 3:54*pm, Guto <gutom... (AT) gmail (DOT) com> wrote:
The documentation for LUW catalog views can be found here:
http://publib.boulder.ibm.com/infoce.../r0011297.html

You'll find plenty related to security. Don't forget the new concept
of roles, that will make you queries that much more interesting :-)

--
Frederik

On Sep 21, 12:34*pm, Frederik Engelen <engelenfrede... (AT) gmail (DOT) com>
wrote:

Ow boy!

This is sort of a RTFM answer ...
anyone interested in give us a User Authority class ?
I'm used to Informix and Sybase databases where have a (sys)master
database where you can check some of these stuff, so this is why is a
little confused to me.
Meantime, I'm RTFM!

Thanks Frederik!