DB2 for i has a fantastic feature that is fairly new, Row and Column Access Control, or RCAC, for ease.
This feature has been around since 7.2 of IBM i in April 2014, but doesn’t get a lot of exposure, so hence my interpretation of how it works.
It is a method of controlling security to certain rows and columns in your database, which protects data without having to modify applications – Fantastic, I don’t have to revisit all our applications!
So how did we use features like this before IBM implemented this feature?
Normally, there would be some bespoke applications performing this feature, for instance a RPG program would check user access & only show authorised data.
Which works perfectly well if the only database access was through the RPG application, but what about ODBC/JDBC access? What about PHP access etc …
Other methods we have come across included using views/logical files with record and column selection.
Not now, we can do away with all that and let the database take the strain!
RCAC bullet points are:
- Controls data access across all interfaces & all types of users
- Moving security to the database layer, making it easier to build controls
- Expects you to have sound object level security in place (Use Authority Collection?)
- Provides an effective way to protect data without having to modify applications
- Can be used with SQL or DDS tables
- Cannot be used on a DDM or program described file
As I’ve already said, we must be running IBM i version 7.2 or higher and have the optional part of 5770SS1 Option 47 – Advanced Data Security for i installed. There is no optional charge for this from IBM.
The figure below shows the licensed program option.
There are three ways to implement, and manage, RCAC.
- Green screen
- SQL Scripts
- System i Navigator (Ops Nav)
- Navigator for i
In this article, I’ll be showing the SQL statements, but System i Navigator and Navigator for I products can be used if you do not have a working knowledge of SQL.
RCAC involves three stages:
- Implementation of responsibilities and roles
- Force row permissions on the database
- Guard columns by defining column masks
Implementation & Roles
The first step is to select a user to be the database security administrator, or DBA.
This user will be able to implement row permissions and column masking. Only those users with the QIBM_DB_SECADM function can administer/manage RCAC rules.
The user you defined in this role will not be allowed to see the data, unless configured to do so
Use the green screen Functional Usage commands to achieve this.
These commands are as follows:
- Work with Functional Usage – WRKFCNUSG
- Display Functional Usage – DSPFCNUSG
- Change Function Usage – CHGFCNUSG
The Work with Functional Usage command is shown below.
The functional ID we need to add our administrator to is the QIBM_DB_SECADM.
The figure below shows we have three administrators for RCAC.
As the figure shows, use group profiles to make life easier.
We can also run a quick SQL statement to show all our administrators.
You define & enable row permissions & column masks using SQL statements, Navigator for i, or iNav.
CREATE PERMISSION – This SQL function defines rules that are automatically applied by the database to determine which rows are visible.
CREATE MASK – This SQL function defines rules that are automatically applied when the target column is selected.
Enabling Row Access & Column Masking
The first stage before we restrict rows & columns is to change the table to allow it to use RCAC.
This is achieved by executing the following SQL ALTER TABLE statement.
This SQL statement does not change the file format record ID so no need to recompile all your programs.
Row access permissions are search conditions that describes which rows can be accessed
For example, a manager can see only the rows that represent his department, or the Ops Department cannot see any records for managers, or above.
Rows can be restricted by any of the following methods
- By User Profile
- By Group Profile
- Time of day
- Values in the rows
- Etc …
Please note that permissions cannot be assigned to a view, alias nor a temporary table.
Let us start with a quick example, where a group of users have access to all rows, whereas everyone else cannot see any rows.
The figure above shows the data base will allow access to all rows for anyone that is a member of the Formaserve group profile.
The ENFORCED FOR ALL ACCESS statement ensures that control is enforced on Selects, Inserts, & Updates.
Everyone else is excluded.
Running the SQL Statement in the previous slide, gives us two Row Permissions on the Projects table. The one we created an another, a default row permission.
Let me now show you an example where we authorise users to their country data, by using their group profile.
This example is checking the column Country in our Sales table, then only showing the rows where they are a member of a group profile. If they are a member of the FormaServe group profile they will see all records.
We can now see this working.
Firstly, as a FormaServe group profile.
Now running the same SQL statement, the HR_UK Group Profile sees only United Kingdom records.
The HR_FR Group Profile sees only the French records.
And lastly, HR_US Group Profile sees only US records.
Column masks increase security by protecting sensitive information in a column. For example, you may wish to mask any of the following:
- Social security numbers
- Credit card details
- Sensitive dates, DOB etc.
Please note – Column masking is not to be confused with DB2 encryption on a column.
An example of creating a SQL statement to mask rows is achieved by the following statement.
The database manager will check each row, then if the user is part of the FormaServe group it will show the salary column (EMSAL), if they are not a zero will be shown for the salary.
Let me show you an example of using column masking for a column on our employee table that holds National Insurance Number details.
To explain, all users of the FormaServe group will see the full NI number, any members of the HR group profile will be shown only the last 3 characters. And the rest will just see xxx-xxx-xxx.
Just a quick word about deleting records, showing a problem I faced.
I was signed on with a profile that used the group profile of HR_UK. They can only see (1,285 records.
Then running the SQL statement below.
How many records do you think were deleted?
The whole table!
Thinking this cannot be right, I restored the data to my SALES table and run the command again.
This time I got.
Just as I was expecting first time!
Time to open a PMR with IBM, who are looking into the matter. The screen shot below shows the two statements deleting a different number of rows.
I’ll keep you informed when I get an answer to this problem.
I’m sure these features will become standard within our database in time.
Luckily, I haven’t had to develop an application that has these restrictions, testing must be an absolute nightmare.
I’m looking forward to the next round of the i-UG user group meetings this year in the UK. Hopefully we can meet up at the main event of the year at our local football stadium. Full details can be found on the iUG site here.
Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems.