Tue, Dec
2 New Articles

Security Patrol: V7R2 Security Enhancements, Part 2: Implementing Row and Column Access Controls

IBM i (OS/400, i5/OS)
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times


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 RCACwhat 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


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 functionsin 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 methodODBC, FTP, DDM, commands such as Run Query (RUNQRY) and Update Data (UPDDTA), and via SQL statementswhich 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. Becauseas I stated beforeonce 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




AND region = '001'





AND region = '002'











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 interfacese.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 individualsi.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.





  • 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 seeor 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










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 fileis 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 otherssuch as performancethat 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


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 Uehlingall from IBM Rochesterfor their contributions to this article.


Carol Woodbury


Carol Woodbury is President and CTO of DXR Security and has over 30 years’ experience with IBM i Security. She started 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 current company - DXR Security - specializes in penetration testing for IBM i. Her practical experience together with her intimate knowledge of the system combine for a unique viewpoint and experience level that cannot be matched.

Carol is known world-wide 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. 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 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

IBM i Security Administration and Compliance: Second Edition IBM i Security Administration and Compliance: Second Edition
Get the must-have guide by the industry’s #1 security authority.
List Price $71.95

Now On Sale

IBM i Security Administration and Compliance IBM i Security Administration and Compliance
For beginners to veterans, this is the definitive security resource.
List Price $69.95

Now On Sale



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: