Row and Column Access Control
An Overlooked Security Feature For IBM i?

When version 7.2 of the IBM i operating system was introduced a very powerful security function was added: Row and Column Access Control (RCAC). In short, this capability allows columns (fields) in a database file to be masked or hidden from various users as well as allowing rows (records) in a database file to be hidden from various users.

This capability is not a substitute for good ‘object-level’ security, rather it is another tool available to help you achieve your security requirements.

Prior to version 7.2 of the operating system this capability could be implemented with:

  • SQL Views
  • Encryption with SQL field procedures

With SQL views you would have to administer many views for different categories of users and you would have to find and modify every application interface to use those views.

With encryption, you would have to write or purchase your own encryption routines and encryption can result in modifications to the database.

The benefits of RCAC are:

  •  Compared to SQL views and encryption it is much easier to set up.
  •  No need to worry about finding and modifying application programs. Whether using an RPG program, a query or ODBC masked columns will be masked and omitted rows will be omitted.

Let’s look at an example; The Credit card column (CREDCARD) in the following table will be masked in different ways for different users.

The following two examples show how different masking can be implemented for different users.

In the above two examples masking is different for user GLYNNUS3 than it is for user QSECOFR. Masking is even in place for user QSECOFR, the system’s most powerful user; how good is that?

How is RCAC for column masking implemented? The following screen-shots show how just two SQL statements can get you started.

The above statement does the following:

  • Creates a mask called CRED1on the table CREDITFILE in library GLYNNTEST.
  • The mask is created on column CREDCARD.
  • The SQL function VERIFY_GROUP_FOR_USER will check the user profile on the incoming access request.
  • For user GLYNNUS2 the credit card column will be totally visible.
  • For user GLYNNUS2 the first 8 characters of the credit card will be masked with ‘X’
  • For users in the GLYNNGRP group profile the whole credit card information will be masked with ‘X’
  • For none of the above users (catch-all) the credit card field will be replaced with ‘PUBLICXXXXXXXXXX’

The following statement activates the mask:

As already stated RCAC can also be used for row selection; the ability to determine which records particular users can work with.

As with masking, row selection can be achieved in releases prior to 7.2. usually involving something like the creation of SQL views. Again, this would mean modifications to application code or working practices to use the correct views; a very time-consuming and complicated process.

Using the example table from the first screen-shot we use RCAC to implement row selection on users GLYNNUS3 and QSECOFR.

As you can see different users see different rows. This also applies to updates as well; if you can’t see a row you can’t update it. How is RCAC for row selection implemented? The following screen-shots show how just two SQL statements can get you started.

The above statement does the following:

Creates a permission called PERMCREDIT on the table CREDITFILE in library GLYNNTEST, the SQL function VERIFY_GROUP_FOR_USER will check the user profile on the incoming access request, for user GLYNNUS3 all rows will be allowed except for any where the customer number is ‘00001, For user QSECOFR all rows will be allowed except for any where the customer number is ‘00002’

The following statement activates the permission:

There are a few things to be aware of when using RCAC:

  • Licensed program 5770-SS1 option 47 – IBM Advanced Data Security for i needs to be installed. This is a no-charge option.
  • The user administering RCAC must have QIBM_DB_SECADM ‘function usage’

Function usages, as the name suggests, allow permitted users to perform certain tasks or functions on the IBM i.

 The user given this function usage can even have *EXCLUDE authority to the object so that they cannot work with the data in the database table.

QIBM_DB_SECADM is covered in one of Chilli’s other Newsletters.

IBM i 7.2 will be going out of support in 2021, the positive is that if you upgrade to IBM i 7.3 or above you can receive all of the features mentioned in this article in addition to new security enhancements have been added since the release of IBM i 7.2, many of which will be covered in future editions of our newsletter.

Would you like to know more?

If you would like to see a live demonstration of RCAC for IBM i email us info@chilli-it.co.uk or call on 0300 124 0992. Alternatively, we have another great read from our technical consultant Glynn Jones. In this article he provides you with a simple step by step guide, showing you how to set up roles and segregation of duties on IBM i DB2. 

Chilli IT help organisations bridge the IBM skills  gap by providing technical support, professional services and hardware and software upgrades to organisations across the world.

Useful Links

Call us today on 0300 124 0992