With IBM i 7.2, DB2 Gives You More!

Today (Monday April 28) is the announcement of the long awaited IBM i version 7 release 2. And if you've been following along, you know this also means that the fully integrated DB2 for i moves forward as well.

Now, before illuminating a new powerful database capability residing in the best OS for business, let me remind you that over the past couple years, the good folks who develop DB2 for i have been enhancing the database capabilities within 7.1 too. These enhancements are known as "technology refreshes" or TRs. The latest iteration, TR8 was announced a few weeks ago - check it out here.

A Data Centric Attitude

For years now, my team has been going around the world educating clients and solution providers on the importance of designing and developing business applications with a data centric attitude. That is to say, employing sound relational database techniques, set-at-a-time principles and information management best practices. After all, this is how to get the most out of DB2 for i, now and in the future.

Architecturally speaking, we want to see applications that exhibit flexibility, extensibility and scalability. And obviously, business users want solutions in a timely and cost effective manner.

Utilizing modern tools and modern methods is the fastest and most efficient way to achieve this goal. Whether creating a new application or re-engineering an existing one, progressive techniques and using SQL are the keys to unlocking the valuable features and functions residing in the database management system woven into IBM i.

You Need Governance and Control 

As I engage clients in various settings, one topic that always seems to pop up is the need for governance and control. Unless you have been keeping your eyes closed and ears plugged, you realize that data is big. More accurately: storing, analyzing and exploiting data in ways that bring new insight and understanding is all the rage.

As the appetite for data increases, so does the importance of governance and control. Determining who is able to see what data is the responsibility of the data owner, and it is a fundamental aspect of designing with integrity and deploying with confidence. It might also keep you out of the spotlight.

In the old days of AS/400, nearly all of the data access was controlled through the high level language 5250 programs that sat at the heart of online transactional systems. To get to a program and ultimately to the data, the user had to sign on with a valid user profile and corresponding password. If successful, a menu was presented - their menu. The list of items on that menu dictated what the user could do and not do; what data they had access to, and did not have access to. In many cases, the underlying database objects were not secured and not restricted through object level security.  Why should they be? The only way to access the object was through the program, and the only way to call the program was through the menu.

Given the openness of DB2 for i, I trust you now realize that there are many different ways for a user to gain access to data outside of the old style menu based applications. For example, tools and applications that connect via ODBC and JDBC interfaces abound. The need to properly secure database objects directly via the powerful IBM i security features is a must. If I walk into your shop and try to access your physical files with SQuireL, will I gain access?  If you don't know, or are not sure, we need to talk. Seriously.

Unfortunately, granting or revoking rights to the data seems to be an all or nothing proposition. If a user has access to the table, they have access to all the rows. If a user does not have access to the table, they have access to no rows. What if different departments all need access to the same table, but each department must be restricted to a subset of rows and columns that they are authorized to see? Is there a way to allow each group of users access to only their respective data sets?

Vistas Grande

Within the science and art of relational database there exists the ability to provide a logical "view" of the data. A VIEW is a virtual table, and as such, the database engineer can define and publish various views of the same data. This technique can be used to provide different groups of users with different sets of rows that they can "see". The trick is to ensure that the user is only accessing their particular VIEW and no other.

For a relative small and stable set of different users, creating and utilizing VIEWs is an elegant and acceptable way to control access to data, whether it be a particular set of rows or a subset of columns.

The VIEW is also very useful when needing to transform data and/or hide complexity, such as join syntax or aggregation specifications. But what if the different groups of users is relatively large and dynamic?  What if there are many different applications and database interfaces in use? Implementing a comprehensive and grand set of views can be problematic and time consuming.

Behold, Another Powerful Tool in the Kit

Through the use of data centric (not application centric) techniques, the row and column data that is returned to the requester can be controlled and governed by a set of policies defined and implemented within DB2 for i. These policies cannot be bypassed or circumvented by the requester, and they are in effect regardless of interface.

The new capability delivered with 7.2 is known as Row and Column Access Control or RCAC.

RCAC provides fine grained access control and is complementary to the ever present object level security (i.e. table privilages). With the new row and column access control feature of DB2 for i, the database engineer, in partnership with the data owner can ensure that users see only the data that is required for their work, and return result sets that match their level of authorization. This can (and should) also include allowing the database engineer to design and implement the policies, but restricting he or she from the actual data the policies control. In other words, just because you implemented the database security mechanism, it doesn't mean you have access to all the data.

Some of the advantages of RCAC are:

• No database user is inherently exempted from the row and column access control policies.
   
• Table data is protected regardless of how the table is accessed.
   
• No application changes are required to take advantage of this additional layer of data security.
   
• Both rows and/or columns can be controlled through simple or complex logic - including the ability to mask what data is projected.
   
• Groups of users can share the same policy - or not.
   
• The implementation of the policies is part of the DB2 data access layer itself.

 

Seek to Understand, Then Plan and Test

Like any advanced technique, deep understanding, proper planning, and adequate testing are essential for success. This includes the use of quality assurance, as well as performance and scalability exercises that serve to demonstrate all of your requirements are being met. Proofs of concepts and proofs of technology are highly recommended. These projects can be accomplished in Rochester, Minnesota by the way.

To further assist you with understanding and insight, the DB2 for i Center of Excellence team will be partnering with the ITSO in June and July to author a "redpaper". The document will cover more details on successfully implementing RCAC. Stay tuned for that.

In the mean time, if you are interested in getting more value out of IBM i data centric technology immediately, please reach out, we are here to help.

And finally, check out Kent Milligan's excellent overview of what's new in DB2 for i - including more technical details on row column access control. The presentation can be found here.