Carol provides the second part of a two-part article on the security enhancements introduced in V7R2.

Last month, I covered the auditing, password composition rule, and other miscellaneous enhancements provided in V7R2. This month, the focus is on the security enhancements associated with DB2, specifically Row and Column Access Control (RCAC).  

RCAC brings the security implementation more into line with other DB2 implementations and provides administrators with a way to filter what information is available without having to develop and manage logical files. As one might imagine from the name, there are two aspects to RCAC—what can happen at the row level and what can happen at the column level. At the row level, you can select which users (or groups) see which records. The column controls allow you to mask data or show the entire field. For example, users belonging to the HR group get to see full Social Security numbers, but everyone else sees a masked value. We'll talk more specifics in a minute, but this gives you an idea of the functions of each. If it's not obvious, let me say that this is powerful stuff. However, it does take significant planning before implementation. So the focus of this article is to help you understand the implications of and plan for the security aspects of an RCAC implementation

Prerequisites

Before you can implement RCAC, BOSS option 47 - IBM Advanced Data Security for i must be installed. This is a no-charge option, but it must be installed for you to configure or use RCAC. Next, someone must be assigned the Security Administration function usage or QIBM_DB_SECADM. That last sentence probably makes no sense, does it? Let me back up and explain. There's a function within iNavigator called Application Administration. To reach it, launch iNavigator and right-click on the system name. From there, choose Application Administration. Once that's launched, you'll see three sections of Application Administration; the one we're interested in is under the Host Applications tab. The Host section allows you to give access to perform certain functions—in this case, the ability to administer RCAC. Cursor down to the Database Security Administration line and press the Customize button. See Figure 1.

Figure 1: This is the Application Administration's Host Applications tab.

As you'll see in Figure 2, under the "Access" heading there are two checkboxes. If you check the "Default access" box, then anyone can manage RCAC. The only time I can even imagine checking this box is on a server that's truly a sandbox system and contains no production data. Otherwise, leave it unchecked. The other checkbox says "Users with all object system privilege," meaning any user with *ALLOBJ special authority. So here's the interesting thing. If you leave the *ALLOBJ box unchecked, users with *ALLOBJ will not be able to administer RCAC. Think about that for a moment: this provides you with a way to separate database access administration from your system administrators. You'll need an administrator (someone with *SECADM special authority) to give the QIBM_DB_SECADM function usage. But once you've done that, if you avoid giving QIBM_DB_SECADM to your system administrators and they're not authorized through RCAC, they will not be able to see the contents of the database file. Clearly, you'll want to monitor who has been given the QIBM_DB_SECADM function and what the settings are (that is, whether *ALLOBJ users have been given access). (As a side note: You can use the SkyView Risk Assessor product reports to see lists of each function (including QIBM_DB_SECADM) and the users assigned.)

Figure 2: Customize users' access.

Once you've given the appropriate users Database Security Administrator function, you're ready to give RCAC a try.

Understanding RCAC

RCAC is not a replacement for the traditional object-level security settings; rather, it's the second pass in the process of the system determining the user's access to the data in the file. Therefore, even if users have been granted permission through RCAC, they must first be able to pass the test of the regular IBM i authority-checking algorithm. Object-level security is in effect regardless of how the data is accessed, and the same is true for RCAC. Once RCAC is activated on a file, the row permissions and column masks are in effect for every access method—ODBC, FTP, DDM, commands such as Run Query (RUNQRY) and Update Data (UPDDTA), and via SQL statements—which is the behavior you'd want and expect. But the access you may forget to consider in your planning is access via normal application interfaces such as a green-screen menu. Because—as I stated before—once activated, RCAC is in effect regardless of how the data is being accessed. Before I discuss some specific considerations regarding this functionality, let me provide you with an example:

Imagine that you want salespeople to see sales figures only for their region. Right now, when salespeople download information into a spreadsheet, all sales figures are downloaded. To accomplish your goal, the example below can be used. Adding these permissions would enforce that users in the NORTHSALES group will see all rows where the region is set to 001 (which is how their region is identified in the database). Likewise, users in the SOUTHSALES group will see all rows where the region is set to 002. And users in the MANAGER group see all rows in the SALES file.

CREATE PERMISSION sales_info ON sales

FOR ROWS WHERE

(

VERIFY_GROUP_FOR_USER(SESSION_USER,'NORTHSALES') = 1

AND region = '001'

)

OR

(  

VERIFY_GROUP_FOR_USER (SESSION_USER,'SOUTHSALES') = 1

AND region = '002'

)

OR

(

VERIFY_GROUP_FOR_USER (SESSION_USER,'MANAGER') = 1

)

ENFORCED FOR ALL ACCESS

ENABLE;

COMMIT;

ALTER TABLE sales ACTIVATE ROW ACCESS CONTROL;

COMMIT;

Now let's look at the things you need to plan for when considering this example:

• In RCAC, it's the absence of a permission that denies access. In other words, if you don't have permission to access a row, you're inherently denied access. This is a very important concept to remember when designing permissions.
• If you add another region, CENTRAL, for example, you must remember to add another row permission for the group and any other group you add in the future that needs filtered access to the data. So consider the ongoing maintenance when designing row permissions.
• A lot of people are tempted to think of RCAC in terms of what it can do for filtering data when using interfaces such as ODBC. But as I've said several times already, once activated on a file, RCAC is effect through every interface by which the data is accessed; therefore, you must ensure all users of the application have permission to the data. In our example, if application users aren't in the NORTHSALES, SOUTHSALES, or MANAGER group, when they take an option off of the application's green-screen menu, they will have no access to the application data. What do the non-members see? You might think they'd see some type of "not authorized" message, but that's not the case. They just don't get data. It's as if the table (file) is empty. (Can you even imagine the help desk calls that would come pouring in if this happened on your production system?!) Understanding the fact that all users of the data need a row permission when implementing RCAC is another very important concept to remember when designing permissions.
• Building on the scenario we've discussed for our example, some of you will want all of your application users to have unrestricted access to all rows as long as they are within the application's interfaces—e.g., via a green-screen menu. But as previously described, access to the sales data from outside of the application (via ODBC, the RUNQRY command, etc.) must be filtered by regions and permission given only to select individuals—i.e., members of the NORTHSALES, SOUTHSALES, and MANAGER groups. To keep the application working as it does today for users coming through the green-screen menu, you can use adopted authority. Yes, adopted authority works in the RCAC phase of authority checking. If the permission is defined to check the CURRENT_USER rather than the SESSION_USER, the last profile being adopted (that is, the owner of the last program in the program stack that is configured to adopt its owner) is evaluated. In the example above, if the application programs are owned by APP_OWN and are configured to adopt (user profile is set to *OWNER), you'll want to add the following so that there is a permission that will ensure the application users continue to have full access to the data. That is, the adopted authority will provide the permission to access all rows.

OR

(

CURRENT_USER = 'APP_OWN'

)

• Row and column access controls will not take effect until the ALTER TABLE statement has been run to activate the row permission and the column masks. If you inadvertently activate the row permission but you haven't defined any permissions, you will block everyone from using the data. Note to self: have the row permissions in place prior to activating, especially on your production servers!
• There may already be logic within an application to limit the data a user can see. With row permissions, you may now be able to remove this logic. Regardless of whether you leave this logic or remove it, you'll want to consider whether adding row permissions will have any effect on how application users are used to seeing and using data. Thorough testing will be required to make sure application users continue to see the data they're supposed to see—or not see.

Programmer Considerations:

• Again, once RCAC has been activated for the file, users without permission have no access to the data and cannot take actions on the file. Now I know I've already discussed this, but I want you to consider it in a slightly different context. Think of this restriction in the context of programmers needing to fix a production data issue. Many of you use utilities that swap to a profile with elevated privileges, often a profile with *ALLOBJ special authority. Since row permissions also enforce that you must have access to read any row that you insert or update, you'll have to make sure that the profile being swapped to has row permission to all rows; otherwise, it's likely the programmers won't be able to make the update. I found this out myself. While exploring RCAC for this article, I wanted to add some more rows to a table I had created to test with. But I had already activated the permissions; therefore, even though my profile had *ALLOBJ special authority (so I had sufficient object authority to perform the task), I could not insert new rows because I didn't have the row permissions to do so.
• Production database files are often copied to other systems. When the system is a Quality Assurance (QA) or test system, the user profiles are often the same as on production. Therefore, the permissions you've added through RCAC may not have to be modified. However, once you move this data to a development system, you may have to add more or modify the permissions to allow the developers access to the data. You'll also want to consider adding column masks as you move production data to non-production systems, but I'll discuss that in the next section.

Column Masks

Column masks do exactly as the name sounds: they allow you to mask data in a column based on some criteria. The column mask capabilities are quite flexible and powerful, but for this article I've chosen a very simple example. If users are members of the CUSTSERV group, they'll see the last four digits of the Social Security number. If not, the field will be blank.

CREATE MASK SSN_mask ON employee

   FOR COLUMN SSN RETURN

CASE

WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'CUSTSERV') = 1)

THEN 'XXX-XX-' CONCAT SUBSTR(SSN,8,4)

ELSE NULL

      END;

   ENABLE;

COMMIT;

ALTER TABLE employee ACTIVATE COLUMN ACCESS CONTROL;

Column Mask Considerations:

• Column masks are not the same as encrypting the data. The data "at rest"—that is, the data in the physical database file—is stored in cleartext. Column masks can be deactivated or removed, leaving the data fully viewable. In addition, masking the data will never allow you to pass an audit if the requirement is to encrypt the data. Column masks limit who can see values, but as a protection measure it's not as strong as encryption and will definitely not meet compliance requirements if the data must be encrypted.
• When encryption is not an option and not required, column masks are a fantastic way to reduce the exposure of sensitive data. In my opinion, they should be defined for any column that contains PII or other confidential data to reduce the risk to this data. This especially applies to test and development systems, where object-level security is not usually enforced with the same rigor as on production systems.
• However, once again, careful planning and application testing is required. For example, RPG gurus Jon Paris and Susan Gantner pointed out a couple of issues:

• If a record is retrieved and one or more columns is masked and then the record is written back to the file with the retrieved data, you will over-write the current data with the mask, losing the real data.
• No indication is provided that tells you (or a programming interface) when you’re seeing masked data or real data. This could present challenges when debugging issues.

Managing RCAC

Defining row permissions and column masks is done strictly through SQL. While there's an indication when you run Display or Edit Object Authority (DSP/EDTOBJAUT) commands that RCAC is active on a file as shown in Figure 3, there's no command that allows you to modify or even display the permissions or masks. That's strictly done through the Row Permissions and Column Masks functions in iNavigator under Database>Schema. Or you can display them by querying the QSYS2/SYSCONTROLS catalog through iNavigator's Run SQL Scripts function.

Figure 3: Display Object Authority shows that RCAC is active.

A new audit entry type, AX, has been added to log the creation, removal, enabling and disabling, etc. of row permissions and columns masks. Included in the audit entry is the SQL statement used to define the permission or mask. The new entry has also been added to the Copy Audit Journal Entry command to allow you to easily query the IBM i audit journal for these types of entries.

Save/Restore Considerations

When planning for the implementation of RCAC, another point to consider is the save/restore issues you can encounter. For example, the save operation will not work for a target release of a previous version (that is, a release prior to V7R2) if row permissions or column masks have been defined. They will have to be removed by someone granted the QIBM_DB_SECADM function to have a successful save to a previous release. Another scenario you can encounter is when you save a file with activated RCAC controls and you restore it to a V7R2 system that doesn't have Boss option 47 installed. In this case, all attempts to open the file will fail. The resolution is to install IBM Advanced Data Security for i or have someone with QIBM_DB_SECADM function deactivate the row permissions and column masks.

More Information

My discussion has focused primarily on the security aspects of RCAC, but there are others—such as performance—that you'll want to understand and consider as part of your planning. Here are some references to help you:

• Mike Cain's blog for technical tips on RCAC
• Details of the SQL syntax to implement RCAC can be found in the DB2 for i SQL Reference manual
• Details of the new AX audit journal entry can be found in Appendix F of the IBM i Security Reference manual

Summary

RCAC provides you with options for adding another layer of protection to your IBM i data beyond what regular object-level authorities provide. However, RCAC is not something that should be implemented without planning and testing for potential side effects. A thorough understanding and a lot of planning need to occur before rolling out an implementation of this new V7R2 feature.

Many thanks to Theresa Euler, Scott Forstie, and Jeff Uehling—all from IBM Rochester—for their contributions to this article.