As I started Part 3 of this DB2 Family Security Best Practices blog, I was notified that my personal information was hacked again. That is five different times my information has been taken. So I offer this third installment on database security that will hopefully help every company secure their data.
I have quickly realized as I wrote these Top 10 DB2 Security Best Practices blogs, there are really a lot more than 10 DB2 security best practice techniques that my clients and I have been using over the years. The first two installments of the Top 10 DB2 Security Best Practices are here and then here.
Below are three more items to add to your DB2 Security Best Practices techniques. These blog items should be added to your standardized security practices within your DB2 environment for your next new project or whenever possible. These security best practices are: definition of restricted access views, implicitly hidden columns and row/column security access controls. These DB2 standard security practices have been available for many DB2 versions and releases. They are available on both DB2 LUW and DB2 z/OS and are additional security layers to prevent data thefts.
- DB2 Restricted Access Views can be good to prevent access to the sensitive credit card, social security numbers or other PII table data. For example any table with sensitive data column(s) can have a View defined over it to restrict access to the sensitive data columns. For example below: the CUSTOMER table with a social security number column and credit card information is defined. Removing all user and program direct access to the CUSTOMER base table will secure the data. By GRANTing security access only through the specialized views all access to these DB2 CUSTOMER table columns can be restricted, monitored, and controlled effectively.
CREATE TABLE BEULKE.CUSTOMER
(
FIRST_NAME CHAR (25) NOT NULL,
LAST_NAME CHAR (25) NOT NULL,
ADDRESS1 CHAR (25) NOT NULL,
ADDRESS2 CHAR (25) NOT NULL,
CITY CHAR (25) NOT NULL,
STATE_CD CHAR (2) NOT NULL,
ZIPCODE CHAR (25) NOT NULL,
PHONE DECIMAL (10,0) NOT NULL,
SSN DECIMAL (9,0) NOT NULL,
CC_NBR DECIMAL (16,0) NOT NULL,
CC_EXP DECIMAL (4,0) NOT NULL,
CC_SCD DECIMAL (3,0) NOT NULL);Defining a CUSTV1 view that removes the sensitive data columns is a great way to provide access to the non-sensitive information to all interfaces while restricting access to the sensitive data. The CUSTV1 view can then have access GRANTed to all regular application processes, user ids groups, and roles.
CREATE VIEW BEULKE.CUSTV1
AS
SELECT
FIRST_NAME,
LAST_NAME,
ADDRESS1,
ADDRESS2,
CITY,
STATE_CD,
ZIPCODE,
PHONE
FROM BEULKE.CUSTOMER ;Next, create another view, CUST_APPROVED view, with the sensitive columns. This sensitive data view access should only be GRANTed to appropriate users, groups, or roles with the security training for handling sensitive information within your company. This combination of DB2 table and view security scheme is great because it separates the authorities, and all auditors endorse the separation of access by the different views. The different views and the base table provide a good way to monitor your environment closely, guarding against any disreputable access to the wrong object.
–Grant Access to CUST_APPROVED to only special processes and users
—
CREATE VIEW BEULKE.CUST_APPROVED
AS
SELECT
FIRST_NAME, LAST_NAME, ADDRESS1, ADDRESS2,
CITY, STATE_CD, ZIPCODE, PHONE,
SSN, CC_NBR, CC_EXP, CC_SCD
FROM BEULKE.CUSTOMER; - Another way to camouflage access to sensitive columns within tables is to make them hidden with the DB2 HIDDEN IMPLICITLY keyword. For example define the CUSTOMER_HIDE table below with the extra column definition HIDDEN IMPLICITLY keyword phrase to help protect sensitive data columns.DB2 hides these HIDDEN IMPLICITLY columns from the output of any SQL inquiry using a SELECT *, returning only the regular columns defined in the table. All the HIDDEN columns need to specify defaults values so any INSERT statements will execute regardless whether the hidden columns are specified or not. Of course, there are other restrictions around these HIDDEN IMPLICITLY defined columns, so the latest version manuals are the best place to get the current considerations and restrictions. Unfortunately, HIDDEN IMPLICITLY only hides the data columns from SELECT * results sets and INSERTs without column lists, so it really doesn’t really provide any extra security, but it is a way to somewhat hide the columns from someone browsing through your DB2 tables doing a SELECT * to gather your meta data and discovering your sensitive data columns.
CREATE TABLE BEULKE.CUSTOMER_HIDE
(FIRST_NAME CHAR (25) NOT NULL,
LAST_NAME CHAR (25) NOT NULL,
ADDRESS1 CHAR (25) NOT NULL,
ADDRESS2 CHAR (25) NOT NULL,
CITY CHAR (25) NOT NULL,
STATE_CD CHAR (2) NOT NULL,
ZIPCODE CHAR (25) NOT NULL,
PHONE DECIMAL (10,0) NOT NULL,
SSN DECIMAL (9,0) NOT NULL DEFAULT 0 IMPLICITLY HIDDEN,
CC_NBR DECIMAL (16,0) NOT NULL DEFAULT 0 IMPLICITLY HIDDEN,
CC_EXP DECIMAL (4,0) NOT NULL DEFAULT 0 IMPLICITLY HIDDEN,
CC_SCD DECIMAL (3,0) NOT NULL DEFAULT 0 IMPLICITLY HIDDEN);— THIS SELECT SQL DOES NOT RETURN THE HIDDEN COLUMNS
SELECT * FROM BEULKE.CUSTOMER_HIDE;— THIS SELECT SQL STATEMENT IS THE WAY TO GET THE COLUMN VALUES
SELECT
FIRST_NAME, LAST_NAME, ADDRESS1, ADDRESS2, CITY, STATE_CD, ZIPCODE,
PHONE, SSN, CC_NBR, CC_EXP, CC_SCD
FROM BEULKE.CUSTOMER_HIDE; - Row and Column Access Control (RCAC)Row and column access control (RCAC) is fundamental for raising the level of security to protect any data within your organization. When RCAC first came out I wrote this RCAC blog that you can access here that highlighted some of the RCAC considerations.Driving deeper into RCAC there are two very critical considerations along with many other application area items to realize when using RCAC.
- RCAC is controlled through the SECADM or the SYSADM authority based on the DB2 installation panel settings.
- RCAC controls, restricts, and manages both read and write activity against your data objects.
These two considerations raise major management and implementation challenges for existing databases and applications and require deep analysis before RCAC controls are implemented. Also, if your DB2 system has not embraced the SECADM authority yet, RCAC is another reason why some shops are starting to prepare for SECADM authorities within their systems.
When defining your RCAC controls, DB2 restricts all access implicitly by creating a restrictive default row control. During all data access and SQL operations, all the RCAC controls are applied first; then any SQL predicates or other data manipulation from any interface are applied to create the result set returned. Many different RCAC controls can be defined over any combination of rows and/or columns. These RCAC definitions may restrict or make the table data columns available and the rows unique for each security user profile, group or role defined within your environment.
RCAC permissions can also impact applications because the RCAC controls are considered during INSERT, UPDATE, and DELETE operations. The RCAC controls act like the WITH CHECK OPTION table controls that restrict and guarantee that any data aligns with the RCAC controls that were put in place for the DB2 table. Since every RCAC definition and security category can cause different SQL row result sets, testing and confirmation of RCAC controls needs extensive planning and execution.
These three security practices are additional security mechanisms to help your company guard against unauthorized access and data abuse. Make sure to strengthen your security with every database table maintenance effort and all new development because the hackers are relentless and always trying to get to data—both yours and mine.
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. Follow him on Twitter here (@DBeulke) or connect through LinkedIn here (https://www.linkedin.com/in/davebeulke) .
Support IDUG through the European IDUG conference in Dublin, Ireland November 15-20. For more details go to www.idug.org.
Leave a Reply