RCAC in DB2 for i, Part 1: Row Access Permissions

In a world of hackers, every data asset is vulnerable to theft or tampering. Protecting data can be costly, yet being hacked is even costlier. Enter IBM‘s row and column access control. RCAC is a big deal as businesses are looking for effective tools to control security, in particular, database security.

RCAC allows database security administrators to tighten the reigns on who can view data in the database. Even better, many of these security measures can be implemented without changing applications. Versions of DB2, Postgre SQL, Oracle, SQL Server (currently in preview with Azure v12 and SQL Server 2016), and other database engines also implement some form of row security so it is grand to have it available on the IBM i. This tip will introduce row access security.

The pre-requisites are:

1. IBM i 7.2 is required.

2. The IBM Advanced Data Security for i (5770SS1 option 47) licensed program (free) must be installed. For 7.2, this product comes on media label i72_B_GROUP3_04.iso

If this licensed program is not installed you’ll see the following ugly error:

SQL State: 560CR
Message: [SQL7056] Database support not available for reason 3.
Cause . . . . . : A required licensed program is not installed.
The reason code is 3. 1 - IBM XML Toolkit for i (5733XT2) or International Components
for Unicode (5770-SS1) is not installed. 2 - Java Developer Kit 5.0 (5770JV1), or
J2SE 5.0 32 bit (5770JV1), or J2SE 5.0 64 bit (5770JV1), or Portable App Solutions 
Environment (5770-SS1) is not installed. 3 - IBM Advanced Data Security for i 
(5770SS1 option 47) is not installed.
Recovery  . . . : Make sure the required licensed programs are correctly installed.
Try the request again.

3. Pick one or more users to be database security administrators. User profiles belonging to this role will be able to assign row data access permissions to other users, but will not be necessarily be allowed to see the data, unless configured to do so.

The Database Security Administrator

Defining a database security administrator is done using the Change Function Usage (CHGFCNUSG) IBM i OS command. The following example will assign profile QSECOFR as a database security admin. This will allow QSECOFR to assign row (and column) security restrictions. However, once row permission security has been turned on, having this authority doesn’t allow QSECOFR to see data (even with *ALLOBJ authority). QSECOFR will only be able to see data if permission is granted. This is a separation of interest (as the database security admin doesn’t necessarily need to see the data) and helps keep the system secure.

CHGFCNUSG FCNID(QIBM_DB_SECADM)
          USER(QSECOFR)
          USAGE(*ALLOWED)

Multiple user or group profiles can be assigned the database security admin privilege. The privileges can be reviewed in the green screen using the Display Function Usage (DSPFCNUSG) command:

DSPFCNUSG FCNID(QIBM_DB_SECADM)

Restricting Access To Rows In A Table

Say the sample customer data table QIWS.QCUSTCDT contains sensitive information and should ideally have granular access to rows (some users can see certain rows but not others). Further all of this should be done without changing the legacy applications that access the table. This is where row permissions can come to the rescue.

The first step to activate row access control on a table is to use the ALTER TABLE statement:

ALTER TABLE QIWS.QCUSTCDT ACTIVATE ROW ACCESS CONTROL;

Row access control can be disabled in much the same way:

ALTER TABLE QIWS.QCUSTCDT DEACTIVATE ROW ACCESS CONTROL;

Of course, the table cannot be in use when the ALTER TABLE statement is run. However, once row access control has been activated on a table, each defined security permission (shown next) can be turned on and off without requiring exclusive access to the table.

After row access control is enabled, a database security administrator can use the new CREATE PERMISSION statement to define what conditions allow access to rows in a particular table. Typically, permissions to access rows in a table are based on user or group profiles, although permission rules can also be based on time of day, column values within rows, etc.

In this example, a permission is created such that the rows in table QIWS.QCUSTCDT will only be accessible to database connections (including native I/O) made under the user profiles WEBUSER and APPUSER:

CREATE PERMISSION QCUSTCDT_QPGMR ON QIWS.QCUSTCDT 
FOR ROWS 
WHERE CURRENT_USER IN ('WEBUSER','APPUSER')
ENFORCED FOR ALL ACCESS ENABLE

The CREATE PERMISSION statement’s WHERE clause specifies the rule(s) that determine the circumstances under which rows are accessible. When other user profiles run a query or DML statement against this table, zero rows will be returned or affected. No security related error messages will be thrown when data is queried that allows legacy applications to continue to function. Note that permissions can only be assigned to a table, they cannot be assigned to a view, alias, or a temporary table.

Since user profile based rules can be difficult to maintain, group profile membership can also be tested:

CREATE PERMISSION QIWS.QCUSTCDT_QPGMR 
ON QIWS.QCUSTCDT
FOR ROWS WHERE                
(VERIFY_GROUP_FOR_USER(CURRENT_USER, 'QPGMR') = 1)
ENFORCED FOR ALL ACCESS ENABLE 

The VERIFY_GROUP_FOR_USER function (which incidentally only allows the use of one of three special registers: SESSION_USER, USER, or CURRENT_USER) is used in this instance to test whether the current user is a member of the QPGMR group profile. This function returns a one if the requested user is a member of the specified group(s), or a zero if not a member. SESSION_USER and USER report the same value, namely the user profile connected to DB2. CURRENT_USER is similar with the exception that it returns a current adopted authority profile. Choosing which user special register to pass depends on whether or not adopted authority should affect the permission. The SYSTEM_USER special register (synonymous with the job user) cannot be used with this function.

VERIFY_GROUP_FOR_USER also allows multiple group memberships to be tested simultaneously. In this example, users belonging to the QPGMR or APPPROFILE groups can access all rows:

CREATE OR REPLACE PERMISSION QIWS.QCUSTCDT_GROUPS 
ON QIWS.QCUSTCDT
FOR ROWS WHERE  
( VERIFY_GROUP_FOR_USER(CURRENT_USER, 'QPGMR','APPPROFILE') = 1 ) 
ENFORCED FOR ALL ACCESS ENABLE 

Multiple permissions can be assigned to the same table. For example, if it’s acceptable for any user profile to review the customer entries for the “small potatoes” customers (where the credit limit is less than or equal to 1000), then the following permission can also be applied:

CREATE OR REPLACE PERMISSION QIWS.QCUSTCDT_LOW_CREDIT
ON QIWS.QCUSTCDT 
FOR ROWS WHERE CDTLMT<=1000
ENFORCED FOR ALL ACCESS ENABLE

Now, all user profiles with authority to read the data will be able to see all rows where the credit limit (CDTLMT) is less than or equal to $1,000.

When rows are accessed by DB2, all row permission definitions on a table are evaluated (with an OR operator) so that rows are accessible whenever one or more of the rules grant a specific user (or other criteria) permission. Alternatively all conditions can be combined in a WHERE:

CREATE OR REPLACE PERMISSION QIWS.QCUSTCDT_ACCESS
ON QIWS.QCUSTCDT
FOR ROWS WHERE
CURRENT_USER IN ('WEBUSER','APPUSER') 
OR ROWS WHERE CDTLMT<=1000
ENFORCED FOR ALL ACCESS ENABLE 

However, having separate permission definitions does allow the ease of granularly enabling or disabling permissions. For instance, if the QPGMR profile rule should only be allowed permission during month end or certain maintenance windows, it is easy to enable or disable the permission at will:

CREATE PERMISSION QCUSTCDT_QPGMR ON QIWS.QCUSTCDT
FOR ROWS WHERE
( VERIFY_GROUP_FOR_USER(CURRENT_USER, 'QPGMR') = 1 ) 
ENFORCED FOR ALL ACCESS DISABLE

As evidenced in these examples, row permissions can also be granted on conditions other than user or group profiles. Within table ADVWORKS.CUSTOMER, customers are classified as stores or individuals in the customer type column. A permission can be created to allow users to always access rows for “individual” customers and store customers that were updated in the past 30 days:

CREATE OR REPLACE PERMISSION ADVWORKS.CUSTOMER_INDIVIDUALS
ON ADVWORKS.CUSTOMER
FOR ROWS WHERE MODIFIEDDATE>=CURRENT_DATE - 30 DAYS 
            OR CUSTOMERTYPE='I'
ENFORCED FOR ALL ACCESS ENABLE

As an additional benefit, global variables can be used in a permission definition to limit data access to an arbitrary number of days:

CREATE OR REPLACE VARIABLE ADVWORKS.MODIFIEDDAYS INT DEFAULT 7

CREATE OR REPLACE PERMISSION ADVWORKS.CUSTOMER_INDIVIDUALS   
ON ADVWORKS.CUSTOMER
FOR ROWS WHERE MODIFIEDDATE>=CURRENT_DATE -
               ADVWORKS.MODIFIEDDAYS DAYS
            OR CUSTOMERTYPE='I'

By controlling the content of the global variable, an application can exert dynamic control over the accessible rows. However, this creates the old dilemma of having a potential security hole vs. application versatility. But there should generally be some circumstance where the appropriate user can see all the rows.

*ALLOBJ Doesn’t Apply

Remember, a user profile with the database security admin function does not automatically have access to data protected by row permissions. For example, if I run the following query under QSECOFR when table QIWS.QCUSTCDT is limited by a single row permission that checks whether the current user is WEB_USER or APP_USER (as shown in the example above), the query returns zero rows by default, even though QSECOFR has *ALLOBJ special authority:

SELECT * FROM QIWS.QCUSTCDT

Investigating Problems With Row Permissions Enabled

It’s important to instruct developers and application users about row permissions. Imagine the help desk passing a complaint to a developer about two ERP reports (one based on native RPG I/O and one based on SQL) that no longer match (and that the underlying cause is row permission restrictions differences due to the RPG and SQL programs being run under different users). Since row permissions do not operate at the application level, if RCAC isn’t considered, the developer could spend quite a bit of time trying to figure out why the data no longer matches.

There are a couple of ways to find out about row permissions defined on a partition:

1. In the System i Navigator’s databases node, drill down into the desired schema and then inspect the entries under the “row permissions” node.

2. SYSCONTROLS catalog view (a CONTROL_TYPE of ‘R’ indicates row permission).

Row Permissions And DML

While the effect of row permissions has only been shown using a SELECT statement, the permission also applies to data modification language (DML) statements including INSERT, MERGE, UPDATE and DELETE. For example, assuming a user is accessing rows under the single permission QCUSTCDT_LOW_CREDIT shown above, the user can only access and manipulate rows where the credit limit is less than or equal to $1,000. If the user attempts to insert a row where the credit limit exceeds $1,000, then DB2 will throw this error:

Message ID . . . . . . :   SQ20471 

Message . . . . :   INSERT or UPDATE does not satisfy row permissions.

Cause . . . . . :   Row access control is enforced for QCUSTCDT in QIWS.
  Consequently, all attempts to insert or update rows in that table are
  checked to ensure that the resulting rows conform to the row permissions
  defined for the table.  The INSERT or UPDATE could not be done because a
  resulting row did not satisfy one or more row permissions for QCUSTCDT in
  QIWS.
Recovery  . . . :   Change the data being inserted or updated so that it
  conforms to the rules defined for the row permissions.  

This scenario presents an example where an application may need to be modified to handle a new error condition.

RCAC and Other Security

Implementing RCAC (in particular row permissions) is not a substitute for securing applications and database objects in a responsible way. The drudgery of normal database and application permissions still apply. Row permissions are a way of further limiting users to a slice of the table data, but you still need to control whether or not a user should be able to view or change the data at all.

Performance

Note that the implementation of row permissions can potentially cause performance problems if DB2 cannot get a good handle on estimating accurate row counts due to row permission definitions. The permission functions should be defined carefully and their impact on performance evaluated.

Conclusion

Row permissions provide an effective way to protect your data without necessarily having to modify your applications. Because this security is handled in the database engine, there is no evading it. It doesn’t matter if data is requested using native I/O, Query/400 or SQL access via ODBC, JDBC, OLE DB, etc. In future tips, I’ll discuss using triggers and functions with row permissions and implementing column masks.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

REFERENCES

Row and Column Access Control Support in IBM DB2 for i (Redbook)

CREATE PERMISSION