Db2 auditory

[BeatrixKiddo]

Hello, is there a guideline or check list about Db2 auditing?

 

[sathyarams]

What do you want to audit ?

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[BeatrixKiddo]

Profiles, security access configuration, superdbas, etc.

 

[BeatrixKiddo]

Found some notes about the topic,


DB2 Query SQL
The commands, tables and column definitions should be up-to-date but I would suggest a check, especially if you get spurious results. I would suggest a sanity check of the output (i.e. take some data you know exists and ensure that it appears on the relevant output).

To retrieve all user IDs (not plans) with DBADM authority and whether they hold this privilege ‘with grant option’ or not

SELECT GRANTEE, DBADMAUTH FROM SYSIBM.SYSDBAUTH WHERE GRANTEETYPE = ' ' AND WHERE DBADMAUTH <> ' ';

(If GRANTEETYPE is blank, the value of GRANTEE is an ID that has been granted a privilege)

OR

SELECT DISTINCT GRANTEE FROM SYSIBM.SYSDBAUTH WHERE GRANTEETYPE = ' ' AND DBADMAUTH IN (‘G’,’Y’) ;


Retrieve all users with SYSADM authority
SELECT GRANTEE, GRANTEETYPE, SYSADMAUTH FROM SYSIBM.SYSUSERAUTH WHERE SYSADMAUTH <>' ';
OR
SELECT GRANTEE FROM SYSIBM.SYSUSERAUTH WHERE SYSADMAUTH IN (‘G’,’Y’);
(If GRANTEETYPE is blank, the value of GRANTEE is an ID that has been granted a privilege)

Find all users with SYSCTRL authority
Repeat the above query for SYSADM but substituting ‘WHERE SYSCTRLAUTH’ for ‘WHERE SYSADMAUTH’

To retrieve all system privileges held by users, enter the following SQL (If GRANTEETYPE is blank, the value of GRANTEE is an ID that has been granted a privilege):-

SELECT GRANTEE, GRANTEETYPE, SYSADMAUTH FROM SYSIBM.SYSUSERAUTH WHERE SYSADMAUTH <>' ';
? (Repeat for SYSCTRLAUTH, SYSOPRAUTH, BSDSAUTH, CREATEDBAAUTH, CREATEDBCAUTH, CREATESGAUTH, BINDADDAUTH, ALTERBPAUTH, BINDAGENTAUTH, ARCHIVEAUTH, RECOVERAUTH, STOPALLAUTH, STOSPACEAUTH, TRACEAUTH, DISPLAYAUTH)


Contd..
To list the privileges held by users over databases, enter the following SQL :-

SELECT GRANTEE, DBADMAUTH FROM SYSIBM.SYSDBAUTH WHERE DBADMAUTH <> ' ';
? (Repeat for DBCTRLAUTH, DBMAINTAUTH, CREATETABAUTH, CREATETSAUTH, DISPLAYDBAUTH, DROPAUTH, IMAGCOPYAUTH, LOADAUTH, REORGAUTH, RECOVERDBAUTH, REPAIRAUTH, STARTDBAUTH, STATSAUTH, STOPAUTH)