The next part (Part 6) of this DB2 Family Security Best Practices blog talks about beginning to audit your DB2 zOS security related configuration to understand the different user id and environment considerations/vulnerabilities. There are many different DB2 security areas that a DBA and system administrator can control, so it is best to examine and keep these as locked down as possible within your DB2 zOS environment. In addition to the database column definitions and use of steganography that I talked about before, there are many DB2 security areas to monitor.
These other important DB2 security areas are DB2 trusted context communications, DB2 security privileges related to authorities, DB2 user IDs able to execute processes, DB2 security audit areas, and user IDs that are used within your environment.
- First inspect your DB2 system to verify or understand the number of DB2 Trusted Context definitions that exist for your environment application execution. If you have access to the DB2 Catalog tables you can find all your DB2 Trusted Context in three different DB2 Catalog tables: SYSCONTEXT, SYSCONTEXTAUTHIDS and SYSCTXTTRUSTATTRS.
— CONTAINS ONE ROW FOR EACH TRUSTED CONTEXT.
SELECT *
FROM SYSIBM.SYSCONTEXT
FETCH FIRST 10 ROWS ONLY WITH UR;
— ONE ROW FOR EACH AUTHORIZATION
— ID WITH WHICH THE TRUSTED CONTEXT CAN BE USED.
SELECT *
FROM SYSIBM.SYSCONTEXTAUTHIDS
FETCH FIRST 10 ROWS ONLY WITH UR;
— CONTAINS TRUSTED CONTEXT ATTRIBUTE INFO
SELECT *
FROM SYSIBM.SYSCTXTTRUSTATTRS
FETCH FIRST 10 ROWS ONLY WITH UR;
- DB2 security is also defined over all the various databases’ plan, packages, Buffer Pools, Storage Groups and table spaces. Looking into, controlling, and minimizing the number of DB2 security authorization IDs that have access to these different DB2 components is vital. Many users having authority and being able to GRANT access to these DB2 resources is never a good thing for securing your environment. Look into these DB2 security areas with the following DB2 Catalog SQL queries and carefully eliminate any unneeded unnecessary authorizations.
— RECORDS THE PRIVILEGES THAT ARE
—HELD BY USER OVER DATABASE
SELECT *
FROM SYSIBM.SYSDBAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— RECORDS THE PRIVILEGES THAT
— ARE HELD BY USERS OVER PLAN.
SELECT *
FROM SYSIBM.SYSPLANAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— RECORDS THE PRIVILEGES THAT ARE
— HELD BY USERS OVER PACKAGES.
SELECT *
FROM SYSIBM.SYSPACKAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— PACKAGE OWNER CAN BE A ROLE
— ALSO IN DOWNERTYPE
SELECT *
FROM SYSIBM.SYSPACKDEP
FETCH FIRST 10 ROWS ONLY WITH UR;
— PLAN OWNER CAN BE A ROLE
— ALSO IN DOWNERTYPE
SELECT *
FROM SYSIBM.SYSPLANDEP
FETCH FIRST 10 ROWS ONLY WITH UR;
—SYSIBM.SYSRESAUTH RECORDS
— CREATE IN AND PACKADM ON
— PRIVILEGES FOR COL; USE PRIVILEGES
— FOR DISTINCT TYPES, BPs, SGs & TSs
SELECT *
FROM SYSIBM.SYSRESAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
- Another area to research is the DB2 security authorization IDs that have access your data. To find these various IDs, look into the various DB2 user authorization tables below. The series of DB2 Catalog tables is very obvious and again any IDs that are unnecessary should be researched and eliminated as soon as possible.
— THE SYSIBM.SYSUSERAUTH TABLE RECORDS THE
— SYSTEM PRIVILEGES THAT ARE HELD BY USERS
SELECT *
FROM SYSIBM.SYSUSERAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— THE SYSIBM.SYSTABAUTH TABLE RECORDS THE
— PRIVILEGES THAT USERS HOLD ON AND VIEWS
SELECT *
FROM SYSIBM.SYSTABAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— SECADM – ONE ROW FOR EACH ROW PERMISSION
— AND COLUMN MASK
SELECT *
FROM SYSIBM.SYSCONTROLS
FETCH FIRST 10 ROWS ONLY WITH UR;
— THE SYSIBM.SYSCOLAUTH TABLE RECORDS THE
— UPDATE OR REFERENCES PRIVILEGES THAT ARE
— HELD BY USERS ON INDIVIDUAL
— COLUMNS OF A TABLE OR VIEW
SELECT *
FROM SYSIBM.SYSCOLAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— LISTS THE DEPENDENT OBJECTS FOR EACH ROLE
SELECT *
FROM SYSIBM.SYSOBJROLEDEP
FETCH FIRST 10 ROWS ONLY WITH UR;
- As with the last SQL from the previous DB2 security section, User IDs are only part of the issue within DB2 security because DB2 also allows Groups and ROLES to be used to access objects. Make sure to research and understand the various ROLES and the IDs within those ROLES that have DB2 authorization to reference your environment’s Schema, Sequences, and parameters.
— THE SYSIBM.SYSROLES TABLE
— CONTAINS ONE ROW FOR EACH ROLE
SELECT *
FROM SYSIBM.SYSROLES
FETCH FIRST 10 ROWS ONLY WITH UR;
— THE SYSIBM.SYSSCHEMAAUTH TABLE
— CONTAINS ONE OR MORE ROWS FOR EACH
— USER THAT IS GRANTED A PRIVILEGE ON A
— PARTICULAR SCHEMA IN THE DATABASE.
SELECT *
FROM SYSIBM.SYSSCHEMAAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
— SYSIBM.SYSSEQUENCEAUTH TABLE
— RECORDS THE PRIVILEGES THAT ARE HELD
— BY USERS OVER SEQUENCES
SELECT *
FROM SYSIBM.SYSSEQUENCEAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
—CONTAINS A ROW FOR EACH PARAMETER
— OF A ROUTINE OR MULTIPLE ROWS FOR
—TABLE PARAMETERS (ONE FOR EACH
—COLUMN OF THE TABLE)
— ROUTINE CAN HAVE A ROLE IN OWNERTYPE
SELECT *
FROM SYSIBM.SYSPARMS
FETCH FIRST 10 ROWS ONLY WITH UR;
- One of the last DB2 security areas, but certainly not the least critical, is to look into the DB2 Audits that have been set up to monitor your environment. These DB2 audit areas help you identify rogue SQL or research user IDs that are executing within your different applications. Setting up a DB2 Audit is very good for understanding the various IDs that are active and which ones can be deleted within your environment.
—SECADM — CONTAINS ONE ROW FOR
— EACH AUDIT POLICY.
SELECT *
FROM SYSIBM.SYSAUDITPOLICIES
FETCH FIRST 10 ROWS ONLY WITH UR;
— CONTAINS AUDITING OPTION COLUMN
— AUDIT ALL/CHANGE/NONE
SELECT *
FROM SYSIBM.SYSTABLES
FETCH FIRST 10 ROWS ONLY WITH UR;
— CONTAINS EXTERNAL_SECURITY
— COLUMN DB2/SESSION_USER/DEFINER
SELECT *
FROM SYSIBM.SYSROUTINES
FETCH FIRST 10 ROWS ONLY WITH UR;
— CONTAINS SECURITY DETAILS ON
— SPs, UDFs & CAST FUNCTIONs
SELECT *
FROM SYSIBM.SYSROUTINEAUTH
FETCH FIRST 10 ROWS ONLY WITH UR;
All of these DB2 security SQL queries are executed WITH UR against the DB2 Catalog to avoid taking any locks. In some DB2 environments, some of these DB2 security queries will return no rows because of the specialized DB2 authorities or information requested. That is why all of the SQL statements referenced above have the FETCH FIRST 10 ROWS ONLY SQL clause to minimize the result set so that the appropriate WHERE clause for the DB2 Catalog table can be built with the proper criteria for your specific environment.
Another method for monitoring your environment’s DB2 security is to put all of these queries into a weekly job that can expose any new ROLES, User IDs or execution authorities against your environment so they can be immediately investigated and validated. Happy hunting for all those unnecessary definitions within your DB2 security environment! You will be surprised at what you’ll find.
I authored two CIO DB2 Security Audit white papers and a technical DB2 Security Audit white paper for SEGUS. Also at the link is a webinar talking about the SEGUS security and DB2 audit considerations. The How to establish a Security Audit for DB2 z/OS white paper can be found here (https://www.segus.com/media/wp-security-audit-dba-en.pdf)” and the Is 339° Security Audit Sufficient? White paper is here (https://www.segus.com/media/wp-PSI-339-security-cto-en.pdf) and the Powerpoint webinar discussion presentation with SEGUS is here (https://www.segus.com/events/webinars/db2-for-zos-security-audit-protecting-your-assets/).
DB2 development is trying to understand the new open source application projects that need connectivity to DB2. Please help out by taking the survey below to help them understand your DB2 connection requirements.
http://it.toolbox.com/blogs/db2zos/open-source-development-and-db2-connect-68529?rss=1
Support IDUG through the European IDUG conference in Dublin, Ireland November 15-20. For more details go to www.idug.org.
I will be presenting “Defending against the Legions of Doom: DB2 Security Best Practices” at the local DB2 User Groups in September in:
- Chicago, Illinois September 17th
- Agenda at www.mwdug.org
- Register at http://www.mwdug.org/2009QUARTERLY
Also I look forward to seeing everyone at this year’s IBM Insight (formerly Information on Demand-IOD conference). I will be presenting two presentation “Performance Enterprise Architectures for Analytic Design Patterns” and “Defending against the Legions of Doom: DB2 Security Best Practices.”
For more details go here (http://www-01.ibm.com/software/events/insight/).
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. Follow him on Twitter here (@DBeulke) or connect through LinkedIn here (https://www.linkedin.com/in/davebeulke).
Leave a Reply