Have you ever encountered a situation where a user needed to use an application that displayed all of the rows in a file, but the user really should be restricted from seeing certain rows in that file?
For example, a clinic with multiple doctors has a database of all the clinic’s patients and an application that displays the contents to the clinic’s doctors. Before HIPAA, it was OK for all doctors at the clinic to see the information for all patients. With the advent of HIPAA, doctors are now limited to seeing the information for only his or her patients.
Until the new DB2 row/column data security enhancements in IBM i release 7.2, enforcing the HIPAA requirement would have required a change to the application. Now this limitation can be implemented by system, security, or database administrators using DB2 for i’s new Row Permission function — with no changes required in the application.
Even better, if a user who is authorized to the file queries the database directly instead of using the application, they are also limited to seeing only the entries for their own patients. This is just one example of how useful these enhancements can be.
Dubbed RCAC for Row Column Access Control, the new set of functions adds another layer of access control after the existing object level security is applied. Therefore, if a user is not authorized at the object level, the RCAC functions are moot. RCAC can only be used to further filter the results available to a specific user or group with object level permissions.
Mask Column Data
We’ve talked about one example for limiting the rows that an authorized user can see. However, you can also mask column data.
One really useful example for a lot of IT shops is the problem of developers needing to use the contents of the production databases for development and testing. I’ll use an HR application using an employee database for this example.
The production data includes employee social security numbers (SSN). Developers need a copy of the production database in order to test an enhancement to the application. In the past the development organization would just copy the production file and run their testing with that. This meant that the developers could see everyone’s SSN.
With column masking, the security administrator can mask the contents of the SSN column so that, for example, the first seven characters of the SSN are ‘999-99-‘ with the last four numbers being the actual last four numbers of the SSN for the user(s) that copy the file. (Note: the entire field or any length substring can be masked.) When a developer copies the file with CRTDUPOBJ, for example, the SSNs in the duplicated object will all start with ’999-99-XXXX’ (where “XXXX” represents the actual last four digits of the SSN). No need to write an application that scrubs the contents of the data in the SSN column of the file.
Other RCAC Functions
IBM tends to talk about RCAC as a single entity, but I think of it as a collection of functions. For starters, the column function is not really access control, but the ability to mask (partially or entirely) the contents of specific columns. RCAC also includes new built-in global variables, and a SQL function intended to be used when defining row and column controls. There’s even a new user-defined special authority (also known as Function Usage) that allows separation of duties. Collectively these are described as the new RCAC function.
Getting Started with RCAC
So how do you start using RCAC in your shop? The first thing to know is that the new function is delivered as an optionally installable part of the base OS – BOSS option 47 IBM Advanced Data Security for i. This option must be installed in order to create Row Permissions, Column Masks, or open a file or SQL cursor containing an enabled mask or permission.
Next, Permissions and Masks are managed via SQL statements. They must first be created and then they must be enabled in order for the system to apply them to access attempts. Once a permission or mask is enabled for a table, it is implicitly enforced for any operation on that table including:
- SQL access
- Native access (COBOL, RPG, C, etc.)
- IBM Query for i, DB2 for IBM i Query Manager, Open Query File (OPNQRYF)
- Read, insert, update, delete, operations
- CL commands (MOV, CRTDUPOBJ, REN, CPYF, SAV/RST, etc.)
You should also know that certain operations will fail if a row or column control is defined for a file. These operations are:
- Open of a multi-format logical file with formats > 1
- Open done through the QQQQRY API
- Adding a read trigger
A user must be granted QIBM_DB_SECADM (new in 7.2) function usage ID (which I refer to as user defined special authority) in order to create, delete, enable, and disable row permissions or column masks. The cool thing about this is that the person that is granted this privilege does not need to be authorized to the file at the object level. Therefore, the person that manages permissions and masks doesn’t have to be able to see or modify the data in the file! This provides a greater level of separation of duties than has been available in previous releases.
VERIFY_GROUP_FOR_USER
Before getting to examples of creating and enabling permissions and masks you also need to know about a critical new SQL function, VERIFY_GROUP_FOR_USER(<username>, <groupname>). This function tests if the specified user is a member of the specified group.
Session variables are often used with this SQL function. “SESSION_USER” corresponds to the current user in the job. “CURRENT_USER” corresponds to the most recently adopted user profile in the job (IBM was forced to use CURRENT_USER rather than ADOPTED_USER because of SQL naming standards. Rest assured, however, CURRENT_USER really means the most recently adopted user profile.)
If you have an application that adopts multiple user profiles, only the most recently adopted user profile in the call stack is used. For example if a person (PERS1) runs a program (PGM1) that adopts USERXYZ and then calls PGM2 that adopts USER123, which calls PGM3 that accesses a table with row or column controls defined, SESSION_USER will be set to PERS1 and CURRENT_USER will be set to USER123. The authority or group membership of USERXYZ cannot be accessed in a row or column control definition.
How to Define RCAC Permissions and Masks
So how does someone who has been granted QIBM_DB_SECADM define a permission or a mask? Here are examples of each:
Example 1 – Column Masking
We have a file, EMPLOYEE, which contains a field SSN. People who are members of the group PAYROLL are allowed to access the full social security number. People who are members of the MANAGERS group are only allowed to see the last four digits of the SSN. All others are not allowed to see anything related to social security numbers.
Here is the SQL statement that defines this control on the SSN field of the EMPLOYEE file.
CREATE MASK SSN_MASK ON EMPLOYEE
FOR COLUM SSN RETURN
……CASE
………WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, ‘PAYROLL’) = 1)
……………THEN SSN
………WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER, ‘MANAGERS’) = 1)
……………THEN ‘999-99-‘ CONCAT SUBSTR(SSN, 8, 4)
………ELSE NULL
……END
ENABLE;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN LEVEL ACCESS CONTROL
Example 2 – Row Permission
The EMPLOYEE file also has a column for each employee named MGR. This is the username of the employee’s manager. Managers can see information only for the employees they manage. In this example,
CREATE PERMISION MGR_EMP_ACCESS
ON EMPLOYEE FOR ROWS
WHERE
……VERIFY_GROUP_FOR_USER(SESSION_USER, ‘MANAGERS’) = 1 AND
……SESSION_USER = MGR
ENFORCED FOR ALL ACCESS
ENABLE;
ALTER TABLE EMPLOYEE ACTIVATE ROW LEVEL ACCESS CONTROL
Note: “ENFORCED FOR ALL ACCESS” means that the control is enforced on selects, inserts, and updates.
Example 3 – Disabling Column Masking
Assume that you want to create a column mask to scrub the SSN column for all rows when the production EMPLOYEE file is copied by a developer. However, you don’t want this mask to be applied during day-to-day production processing. You can define the following column mask and disable it.
CREATE MASK SSN_MASK ON EMPLOYEE
FOR COLUM SSN RETURN
……‘999-99-‘ CONCAT SUBSTR(SSN, 8, 4)
DISABLE;
ALTER TABLE EMPLOYEE ACTIVATE COLUMN LEVEL ACCESS CONTROL
Just prior to a developer copying the file, you could issue the following ALTER MASK statement:
ALTER MASK SSN_MASK ENABLE
without affecting any other enabled row permissions or column masks.
Just after the developer copies the file, you can again disable just the SSN column mask with:
ALTER MASK SSN_MASK DISABLE
New Built-In Global Variables
Several new built-in global variables were added in 7.2 also. The ones I think might prove most useful for security are CLIENT_IPADDR, CLIENT_HOST, and ROUTINE_TYPE. For example, one might select rows to return based on whether the client is issuing the SQL request from a specific workstation. Or mask columns based on whether the routine type is stored procedure or an SQL function.
SUMMARY
I think I have just started to grasp the potential of the new RCAC capabilities in release 7.2. While I can already identify a number of real world uses, I believe there will be a lot more once system, security, and database administrators begin using RCAC.
Let me know if you have additional examples where RCAC can be used. You can comment on this blog post or send me an email. I’m looking forward to hearing your ideas!
