DB2 Users, Security, Auditing
Users & Privileges
Check CONNECT privilege for all users :
db2 "select varchar(grantee,10) grantee,connectauth from syscat.dbauth order by grantee,connectauth with ur"
Check implicit/explicit privileges for AUTHID on DATABASE :
db2 "SELECT varchar(AUTHORITY,40) PRIVILEGE, D_USER, D_GROUP, D_PUBLIC, ROLE_USER, ROLE_GROUP, ROLE_PUBLIC, D_ROLE FROM TABLE(SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID (UPPER('MWUSRDBT'), 'U') ) AS T ORDER BY AUTHORITY"
Check EXPLICIT privileges for AUTHID on OBJECTS :
db2 "SELECT varchar(AUTHID,10) ID, varchar(OBJECTSCHEMA,20) SCHEMA, varchar(OBJECTNAME,30) OBJECT, OBJECTTYPE, privilege FROM SYSIBMADM.PRIVILEGES where AUTHID = UPPER('MWUSRDBT') order by object, privilege with ur"
Generate SQL to check privileges from all *AUTH tables :
db2 -x "select 'select * from ' || tabschema || '.' || tabname || ' where grantee like ''%COV%'';' from syscat.tables where tabschema = 'SYSCAT' and tabname like '%AUTH' with ur"
Grant Connect to all users in syscat.dbauth :
for user in `db2 -x "select rtrim(grantee) from syscat.dbauth where granteetype = 'U' and grantee <> current schema order by grantee with ur"`; do db2 grant connect on database to user ${user}; done
Grant select to all tables of specific schema :
db2 "select 'grant select on '|| tabschema || '.'|| tabname ||' to aprime;' from syscat.tables where tabschema = '<SCHEMA>'"
Grant all to some users :
db2 -x "select 'grant select,insert,update,delete on ' || tabschema || '.' || tabname || ' to user demarini;' from syscat.tables where tabschema not like 'SYS%' order by 1" > grant_all_demarini_2.sql
Revoke all from some users :
db2 -x "select 'revoke all on ' || tabschema || '.' || tabname || ' from user ' || grantee || ';' from syscat.tabauth where grantee in ('PADOVANOV','TAIOCCHIA','COVATO','ZOPPIF','DEMARINI','GABRIELI') order by 1" > revoke_all
Grant to user with delimited special characters :
db2 -x "select 'db2 \"grant select on table ' || tabschema || '.' || tabname || ' to user \\\"SVC-QVW7\\\"\"' from syscat.tables where tabschema = 'DB2INST1'" db2 "grant connect on database to user \"SVC-QVW7\""
Grant usage on default workload (when DB is created in restrictive mode ) ! :
db2 grant usage on <DEFAULTWORKLOADNAME> to user <USER>
Security plugin
List user IDs :
db2 "select varchar(userid,15),pw_expire_date,account_status,failed_logins,created from db2auth.users"
Auditing
Clean old archived logs :
find . -xdev -name "db2audit.db.MEDIAW_P.log*" -type f -mtime +365 -print
find . -xdev -name "mw_prod_db2audit.instance.log.0*" -type f -mtime +365 -print
find . -xdev -name "db2audit.db.MEDIAW_P.log*" -type f -mtime +365 -exec rm {} \;
find . -xdev -name "mw_prod_db2audit.instance.log.0*" -type f -mtime +365 -exec rm {} \;
find . -xdev -name "db2audit.db.*" -type f -mtime +365 -print
find . -xdev -name "db2audit.db.*" -type f -mtime +365 -exec rm {} \;
Configure instance auditing :
db2audit stop db2audit configure reset db2audit configure scope audit status both, checking status both, context status none, objmaint status both, secmaint status both, sysadmin status both db2audit configure datapath "</path/to/audit/>" archivepath "</path/to/audit/archive>" db2audit start
Configure database auditing :
connect to <DBNAME>; create audit policy <POLICYNAME> categories AUDIT STATUS BOTH, OBJMAINT STATUS BOTH, SECMAINT STATUS BOTH, SYSADMIN STATUS BOTH ERROR TYPE NORMAL; commit; audit database using policy <POLICYNAME>; commit; connect reset;
Script to flush and archive the audit logs :
#!/bin/bash . ~/.bashrc #or #!/usr/bin/ksh . ~/.profile db2audit flush # Archive database level audit logs (each database) db2audit archive database <DBNAME> # Archive instance level audit log db2audit archive
Crontab entries for audit flush :
00 00 * * * </path/to/scripts>/db2audit_dumplog.sh >> </path/to/audit>/db2audit_dumplog.log
Extract the audit files :
db2audit extract delasc to /tmp/archive/extr from path /tmp/archive files db2audit*
Extract and load audit data into tables
HC
CREATEIN :
db2 "revoke CREATEIN on schema SYSTOOLS from PUBLIC" db2 "revoke CREATEIN on schema SYSPUBLIC from PUBLIC" db2 "revoke CREATEIN on schema SQLJ from PUBLIC" db2 "revoke CREATEIN on schema NULLID from PUBLIC"
SELECT on system views :
db2 "revoke select on table SYSCAT.COLAUTH from PUBLIC" db2 "revoke select on table SYSCAT.DBAUTH from PUBLIC" db2 "revoke select on table SYSCAT.INDEXAUTH from PUBLIC" db2 "revoke select on table SYSCAT.LIBRARYAUTH from PUBLIC" db2 "revoke select on table SYSCAT.PACKAGEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.PASSTHRUAUTH from PUBLIC" db2 "revoke select on table SYSCAT.ROLEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.ROUTINEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.SCHEMAAUTH from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYLABELACCESS from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYPOLICYEXEMPTIONS from PUBLIC" db2 "revoke select on table SYSCAT.SEQUENCEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.SURROGATEAUTHIDS from PUBLIC" db2 "revoke select on table SYSCAT.TABAUTH from PUBLIC" db2 "revoke select on table SYSCAT.TBSPACEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.VARIABLEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.WORKLOADAUTH from PUBLIC" db2 "revoke select on table SYSCAT.XSROBJECTAUTH from PUBLIC" db2 "revoke select on table SYSIBMADM.AUTHORIZATIONIDS from PUBLIC" db2 "revoke select on table SYSIBMADM.OBJECTOWNERS from PUBLIC" db2 "revoke select on table SYSIBMADM.PRIVILEGES from PUBLIC" db2 "revoke select on table SYSCAT.MODULEAUTH from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYLABELCOMPONENTELEMENTS from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYLABELCOMPONENTS from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYLABELS from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYPOLICIES from PUBLIC" db2 "revoke select on table SYSCAT.SECURITYPOLICYCOMPONENTRULES from PUBLIC" db2 "revoke select on table SYSIBM.SQLCOLPRIVILEGES from PUBLIC" db2 "revoke select on table SYSIBM.SQLTABLEPRIVILEGES from PUBLIC" db2 "revoke select on table SYSIBM.SYSCOLAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSDBAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSINDEXAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSLIBRARYAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSPASSTHRUAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSPLANAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSROLEAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSROUTINEAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSSCHEMAAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYLABELACCESS from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYPOLICYEXEMPTIONS from PUBLIC" db2 "revoke select on table SYSIBM.SYSSEQUENCEAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSSURROGATEAUTHIDS from PUBLIC" db2 "revoke select on table SYSIBM.SYSTABAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSTBSPACEAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSUSERAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSVARIABLEAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSWORKLOADAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSXSROBJECTAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSMODULEAUTH from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYLABELCOMPONENTELEMENTS from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYLABELCOMPONENTS from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYLABELS from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYPOLICIES from PUBLIC" db2 "revoke select on table SYSIBM.SYSSECURITYPOLICYCOMPONENTRULES from PUBLIC"
IMPLSCHEMAAUTH :
db2 "revoke implicit_schema on database from public"
Encryption
Export libraries and bin
Create STASH file with master key :
gsk8capicmd_64 -keydb -create -db "${HOME}/ibm_dba/security/`hostname`_`whoami`_keystore.p12" -pw "heslo!klesl0" -strong -type pkcs12 -stash
Create master key :
/opt/ibm/db2/V11.1/gskit/bin//gsk8capicmd_64 -secretkey -create -db /home/db2v11/ibm_dba/security/dbavm_db2v11_keystore.p12 -stashed -label "MK_dbatools" -size "16"
Create encrypted database :
db2 create database dbatools on /app/db2/db2v11/data/ dbpath on /app/db2/db2v11/dbpath/ restrictive encrypt cipher aes key length 256 master key label "MK_dbatools"
Update the DBM with location of the created keystore :
db2 update dbm cfg using KEYSTORE_TYPE PKCS12 KEYSTORE_LOCATION /home/db2v11/ibm_dba/security/dbavm_db2v11_keystore.p12
Check encryption options for DB :
db2 "SELECT * FROM TABLE (SYSPROC.ADMIN_GET_ENCRYPTION_INFO())"