Top 10 DB2 Family Security Best Practices Part 2

Below is Part 2 of the Top 10 DB2 Family Security Best Practices.  All the DB2 security-minded DBAs and system administrators should verify their DB2 security configuration along with their DB2 security authority definitions as soon as possible.  The first five parts of the Top 10 DB2 Family Security Best Practices that I talked about before are here I thought I could get all of these done in one blog but there is too much to take care of when it comes to DB2 security.

6. The sixth DB2 security best practice is to REVOKE PUBLIC DB2 security privileges against all aspects of your system, DB2 instances, DB2 databases, and table objects within your overall DB2 system.  The PUBLIC access to some database objects needs to be reviewed to make sure it is acceptable to your overall security schemes and DB2 security audits.  Care needs to be taken with all authorizations but especially with any PUBLIC access that could be later exploited to access other components within your DB2 environment.
   
   Within DB2 LUW, it is a good habit to tune up your DB2 security with revoking the PUBLIC access to the vital components.  Within your default DB2 system installations and common configuration there are many components with PUBLIC access that can be used to compromise your system.  These components include, but are not limited to, the ability to add processes through the BINDADD and CONNECT to your system, or CREATETAB within an already defined database.  These examples and others are highlighted below.
   
   

   REVOKE BINDADD ON DATABASE FROM PUBLIC;
   REVOKE CREATETAB ON DATABASE FROM PUBLIC;
   REVOKE CONNECT ON DATABASE FROM PUBLIC;
   REVOKE IMPLICIT_SCHEMA ON DATABASE FROM PUBLIC;

   
   
   Next in the PUBLIC DB2 security realm there are a number of DB2 security privileges that DBAs and system administrators have been implicitly or automatically GRANTing to PUBLIC when they create new application databases.  These extra DB2 security privileges can no longer be allowed to expose your system/data and need to be cleaned up before they are used against your company and database environment.Two of the most important DB2 security authorities that need to be revoked are the ACCESSCTRL and DATAACCESS.  Even though both can no longer be GRANTed to PUBLIC, the ACCESSCTRL authority level provides administrative authority to issue the GRANT statements against most of your database objects.  Also, there are a number of other administrator-type authorities below that should be cleaned up.

   REVOKE ACCESSCTRL ON DATABASE FROM GROUP PUBLIC;
   REVOKE DATAACCESS ON DATABASE FROM GROUP PUBLIC;
   REVOKE DBADM ON DATABASE FROM GROUP PUBLIC;

   REVOKE ALTERIN ON SCHEMA <my schema name> FROM PUBLIC;
   REVOKE CREATEIN ON SCHEMA <my schema name> FROM PUBLIC;
   REVOKE DROPIN ON SCHEMA <my schema name> FROM PUBLIC;

   REVOKE EXTERNALROUTINE ON DATABASE FROM GROUP PUBLIC ;
   REVOKE LOAD ON DATABASE FROM GROUP PUBLIC ;
   REVOKE NOFENCE ON DATABASE FROM GROUP PUBLIC ;
   REVOKE QUIESCECONNECT ON DATABASE FROM GROUP PUBLIC ;
   REVOKE LIBRARYADM ON DATABASE FROM GROUP PUBLIC ;
   REVOKE SECURITYADM ON DATABASE FROM GROUP PUBLIC ;
   REVOKE SQLADM ON DATABASE FROM GROUP PUBLIC ;
   REVOKE WLMADM ON DATABASE FROM GROUP PUBLIC ;
   REVOKE EXPLAIN ON DATABASE FROM GROUP PUBLIC ;
   REVOKE CREATESECURE ON DATABASE FROM GROUP PUBLIC;

   
   
   The SQL statement below can help you generate SQL statements which will REVOKE PUBLIC access to your DB2 Catalog Tables and other various application database tables.  It is important to REVOKE PUBLIC access to your DB2 Catalog so that people don’t discover the meta-data about your DB2 system or tables to use for hacking or targeting.  Based on your version of DB2 LUW, the SQL statement below should be used to generate REVOKE statements against your various system and DB2 Catalog objects such as SYSCAT, SYSIBM, SYSIBMADM, and SYSTOOLS.  Change the TABSCHEMA LIKE predicate appropriately to produce the large list of REVOKE statements to protect your system.

   SELECT ‘REVOKE SELECT ON ‘|| TABSCHEMA||’.’||TABNAME||’ FROM PUBLIC; ‘
   FROM SYSCAT.TABLES
   — Start with SYSCAT%, SYSIBM%, SYSIBMADM%, SYSTOOLS% for your TABSCHEMA
   WHERE TABSCHEMA LIKE ‘table schema name’
   ORDER BY TABSCHEMA, TABNAME
   WITH UR;

   
   
   Make sure to REVOKE the usage of your table spaces from the PUBLIC:

   REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC;

   
   
   Make sure to REVOKE the SELECT privileges from your monitoring tables.  This is important because these tables contain many different types of information, sometimes SQL as well as other details about the workloads running in your environment.  Again this information should only be  accessed by authorized users to minimize the security exposures.

   REVOKE SELECT ON MON_DB_SUMMARY from public
   REVOKE SELECT ON MON_CONNECTION_SUMMARY from public
   REVOKE SELECT ON MON_WORKLOAD_SUMMARY from public
   REVOKE SELECT ON MON_SERVICE_SUBCLASS_SUMMARY from public
   REVOKE SELECT ON MON_CURRENT_UOW from public
   REVOKE SELECT ON MON_CURRENT_SQL from public
   REVOKE SELECT ON MON_PKG_CACHE_SUMMARY from public
   REVOKE SELECT ON MON_LOCKWAITS from public
   REVOKE SELECT ON MON_TBSP_UTILIZATION from public
   REVOKE SELECT ON MON_BP_UTILIZATION from public

   ***NOTE*** Analysis and testing should be done to determine the impact of any DB2 security REVOKE within your DB2 environment prior to implementation.  Remember authorizations cascade and so do the REVOKE authorities within your system.  Before revoking access make sure to analyze and test the operations of your system, its databases, the various utilities, and your application operations.  DB2 security is very complex and inter-dependent.  One of the best ways to analyze the REVOKE impacts is by using a Redirected Restore of your DB2 system to a test environment where all the REVOKE statement cascades and impacts can be thoroughly tested and realized.
   
   Another DB2 security REVOKE testing scenario can be analyzed and started in your test environment.  Once the full impact of your DB2 Security REVOKEs are realized, they can be migrated into your other environments and finally your production environments.  Be careful! Properly managed DB2 security is critical to protect your environment and to maintain an operational environment that is critical to your company’s bottom line.

Also I will be on The DB2Night Show z/OS Edition #Z60: SQL Performance for a Big Data 22 Billion Row Data Warehouse.

This presentation discusses the design, architecture, performance, and complex SQL issues encountered while building and reporting using a big data analytics DW system. Through this presentation, you will learn 30 SQL tuning tips, the real life issues, agile SQL considerations, and SQL design solutions for building a data warehouse of 22+ billion rows in six months.
Sign up here.

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/).

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

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.