This is powerful stuff. You must understand the implications of and plan for the security aspects of an RCAC implementation.

This article is adapted from IBM i Security Administration and Compliance: Second Edition, chapter 6.

Row and Column Access Control (RCAC) was introduced in V7R2 and brings the security implementation more in line with other DB2 implementations, providing administrators 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 section is to help you understand the implications of and plan for the security aspects of an RCAC implementation.

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 RUNQRY (Run Query) and UPDDTA (Update Data) 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 once activated on a file, RCAC is in 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 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 (e.g., via ODBC, the RUNQRY command) must be filtered by regions and permission given only to select individuals—that is, 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.

 

Carol Woodbury is IBM i Security SME and Senior Advisor to Kisco Systems, a firm focused on providing IBM i security solutions. Carol has over 30 years’ experience with IBM i security, starting her career as Security Team Leader and Chief Engineering Manager for iSeries Security at IBM in Rochester, MN. Since leaving IBM, she has co-founded two companies: SkyView Partners and DXR Security. Her practical experience and her intimate knowledge of the system combine for a unique viewpoint and experience level that cannot be matched.

Carol is known worldwide as an author and award-winning speaker on security technology, specializing in IBM i security topics. She has written seven books on IBM i security, including her two current books, IBM i Security Administration and Compliance, 3^rd Edition and Mastering IBM i Security, A Modern, Step-by-Step Approach. Carol has been named an IBM Champion since 2018 and holds her CISSP and CRISC security certifications.

---

MC Press books written by Carol Woodbury available now on the MC Press Bookstore.

		IBM i Security Administration and Compliance: Third Edition Don't miss the newest edition by the industry’s #1 IBM i security expert. List Price $71.95 Now On Sale
		Mastering IBM i Security Get the must-have guide by the industry’s #1 security authority. List Price $49.95 Now On Sale