With the all the hacking stories getting a lot of news coverage, security of your DB2 system is paramount. Doing a DB2 SQL security audit periodically or before your DB2 migration has been a standard security best practice of many for countless years and many DB2 Version upgrades. These DB2 SQL security audits help eliminate any DB2 security authority anomalies from the past and makes sure those anomalies don’t get carried into your new, clean, migrated DB2 system.
One of the first DB2 SQL security audit components that should be checked is the users’ authorities against the schemas, databases, tables, and columns defined within your DB2 system. To do this, use the following SQL to identify the user ids of people GRANTed access to your various database objects and the people that have access. These security SQL queries also provide many details on the IDs that have access to your tables. The DB2 10 SQL Security audit queries can check for the access to everyone, the “PUBLIC” authorization, and can be modified to research all or any specific user ids within these DB2 security tables.
- This first DB2 security audit SQL usually comes back with no rows since most DB2 z/OS installations don’t configure their security via a schema name. Always remember to limit your DB2 security audit SQL query result sets through the “FETCH FIRST xx ROWS ONLY” and run these DB2 security audit SQL queries “WITH UR” to make sure your DB2 SQL query doesn’t lock up the DB2 catalog.
SELECT
SUBSTR(GRANTEE,1,32) AS GRANTEE,
SUBSTR(GRANTOR,1,32) AS GRANTOR,
SUBSTR(SCHEMANAME,1,32) AS SCHEMANAME,
AUTHHOWGOT, CREATEINAUTH, ALTERINAUTH,
DROPINAUTH, GRANTEDTS,
IBMREQD, GRANTEETYPE,
GRANTORTYPE,
CURRENT_TIMESTAMP AS REPORT_DAB_TIME
FROM SYSIBM.SYSSCHEMAAUTH
ORDER BY GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR;
- The descriptions of these DB2 Security catalog tables are found in the Appendix of the DB2 SQL Reference Manual. This DB2 SQL Reference Manual provides an in-depth description of every table and all of its columns. The next DB2 security audit SQL looks at the security that has been GRANTED at the database level within the system.
SELECT
SUBSTR(GRANTEE,1,32) AS GRANTEE,
SUBSTR(GRANTOR,1,32) AS GRANTOR,
NAME, TIMESTAMP, DATEGRANTED, TIMEGRANTED,
GRANTEETYPE, AUTHHOWGOT, CREATETABAUTH,
CREATETSAUTH, DBADMAUTH, DBCTRLAUTH,
DBMAINTAUTH, DISPLAYDBAUTH, DROPAUTH,
IMAGCOPYAUTH, LOADAUTH, REORGAUTH,
RECOVERDBAUTH, REPAIRAUTH, STARTDBAUTH,
STATSAUTH, STOPAUTH, IBMREQD, GRANTEDTS,
GRANTORTYPE,
CURRENT_TIMESTAMP AS REPORT_DAB_TIME
FROM SYSIBM.SYSDBAUTH
ORDER BY GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR;
- The next DB2 SQL Security audit query checks permissions GRANTed against the various DB2 tables within the overall databases.
SELECT
SUBSTR(GRANTEE,1,32) AS GRANTEE,
SUBSTR(GRANTOR,1,32) AS GRANTOR,
GRANTEETYPE, DBNAME,
SUBSTR(SCREATOR,1,32) AS SCREATOR,
SUBSTR(STNAME,1,32) AS STNAME,
SUBSTR(TCREATOR,1,32) AS TCREATOR,
SUBSTR(TTNAME,1,32) AS TTNAME,
AUTHHOWGOT, TIMESTAMP, DATEGRANTED,
TIMEGRANTED, UPDATECOLS, ALTERAUTH,
DELETEAUTH, INDEXAUTH, INSERTAUTH,
SELECTAUTH, UPDATEAUTH, IBMREQD,
SUBSTR(GRANTEELOCATION,1,32) AS GRANTEELOCATION,
SUBSTR(LOCATION,1,32) AS LOCATION,
SUBSTR(COLLID,1,32) AS COLLID,
CONTOKEN, CAPTUREAUTH, REFERENCESAUTH, REFCOLS, GRANTEDTS,
TRIGGERAUTH, GRANTORTYPE,
CURRENT_TIMESTAMP AS REPORT_DAB_TIME
FROM SYSIBM.SYSTABAUTH
ORDER BY GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR;
- The next DB2 SQL Security audit query checks permissions GRANTed against the various DB2 tables’ individual columns.
SELECT
SUBSTR(GRANTEE,1,32) AS GRANTEE,
SUBSTR(GRANTOR,1,32) AS GRANTOR,
TIMESTAMP, DATEGRANTED, TIMEGRANTED, GRANTEETYPE,
AUTHHOWGOT, ALTERBPAUTH, BINDADDAUTH, BSDSAUTH,
CREATEDBAAUTH, CREATEDBCAUTH, CREATESGAUTH, DISPLAYAUTH,
RECOVERAUTH, STOPALLAUTH, STOSPACEAUTH, SYSADMAUTH,
SYSOPRAUTH, TRACEAUTH, IBMREQD, MON1AUTH, MON2AUTH,
CREATEALIASAUTH, SYSCTRLAUTH, BINDAGENTAUTH,
ARCHIVEAUTH, CAPTURE1AUTH, CAPTURE2AUTH, GRANTEDTS,
CREATETMTABAUTH, GRANTORTYPE, DEBUGSESSIONAUTH,
EXPLAINAUTH, SQLADMAUTH, SDBADMAUTH, DATAACCESSAUTH,
ACCESSCTRLAUTH, CREATESECUREAUTH,
CURRENT_TIMESTAMP AS REPORT_DAB_TIME
FROM SYSIBM.SYSUSERAUTH
ORDER BY GRANTEE
FETCH FIRST 50 ROWS ONLY
WITH UR;
These DB2 SQL Security audit queries are only the first step to understand the various security permissions that have been GRANTed against your DB2 system and its database tables’ data. Use these DB2 SQL Security audit queries to expose user ids that shouldn’t have access to various data objects and REVOKE their DB2 access privileges appropriately.
Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:
5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits
Sign up for the IDUG DB2 Technical Conference Philadelphia, Pennsylvania this May 4-8th.
Also plan on attending my presentation 2378 “SQL Performance for a Big Data 22 Billion-Row Data Warehouse” Friday 8:00-9:00 at the IDUG conference.
For more details on any of these items go to www.idug.org.
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.
Leave a Reply