Row and Column Access Controls
As I have talked about in previous weeks, another reason to request additional DBA resources are all the security configuration and settings involved with DB2 10 row and column access security. The setup, configuration and monitoring of these row and column access control settings encompass all aspects of your DB2 system and applications.
Setting up the row and column access controls is only done by the SECADM authority id or RACF group level. The row and column access controls are defined separately. Row access is set up through permissions and then enabled by activating them. This is done with the CREATE PERMISSION control DDL and through the ACTIVATE ROW ACCESS statement on a particular table. These statements and their different parameters define the security access row permission criteria.
The column access is created through the CREATE MASK column DDL along with the ACTIVATE COLUMN ACCESS CONTROL clause. By defining the individual columns, the security level required and the partial or complete column masking for that security level, multiple security levels and mask types can be implemented over any table.
It is important to think of row and column controls being added to every access. The permissions criteria and data masking is done for every SQL statement, Trigger, UDFs, Views and all other types of data retrieval. This impacts everyone including the SECADM within the application and overall system. Also additional security steps, such as validating and defining them as SECURE is necessary for some of these other access types that are affected by these row and column access controls.
DB2 Security Impacts Performance
In addition these row and column access controls will impact every application access path and its overall performance. So the row and column access controls need very thorough analysis and understanding to make sure their implementation is optimized. DB2 considers predicates from row permissions in the access path selection. Since the SECADM authority is responsible for defining the row and column access controls they must be able to research the performance impact these new security definitions might have on application performance.
In addition to all the security aspects of row and column access controls their setup and implementation will most likely need to be done during a quiet time or outage within your application environment. When the row and column access controls are defined, all packages and the dynamic statement cache entries that reference that controlled table are invalidated.
Another consideration is that row access control and multilevel security are mutually exclusive. If you already have multilevel security label columns to enforce security on a table, that table cannot have row controls activated. Also if row and column controls are activated securing access, multilevel security cannot be ALTERed on to the table. So plan the security methodology for your environment carefully.
Happy Holidays everyone and thank you all for making 2010 a wonderful year and all the best for 2011.
See you all soon at the Austin, TX DB2 User Group on January 18th and in Houston on the 19th.
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).
Leave a Reply