RCAC In DB2 For i, Part 3: Advanced Topics

In Part 1 and Part 2 of this RCAC series, I covered row and column access control (RCAC) row permissions and column masks, and demonstrated how they can be used to add an additional layer of security to your DB2 for i database without necessarily having to change legacy applications. This tip will explore the RCAC topic a little further by discussing a few advanced concepts that may affect your implementation of RCAC.

Bypassing RCAC With SET SESSION AUTHORIZATION

When the SET SESSION AUTHORIZATION statement is executed, a SQL database connection is “impersonated” to run under the identity of another user. Impersonation can allow a sly hacker to squirm around an RCAC security implementation.

For example, look what happens if I login to a 5250 session as user MIKE, run the STRSQL command, and run the following statement:

SET SESSION AUTHORIZATION = APPPROFILE

The remainder of the SQL statements I enter are run under the identity and authority of user APPPROFILE. In this case, the USER, CURRENT_USER and SESSION_USER registers are updated to reflect APPPROFILE as the user. (If a stored procedure configured to use adopted authority is invoked, CURRENT_USER will reflect the permission of the procedure’s owner instead of the user.)

Essentially, SET SESSION AUTHORIZATION will override the special registers that are used to test the user in a mask or row permission definition. While generally you won’t have users that type and execute such a SQL statement while they’re working in an application, it is possible that a hacker could log in and get authority by impersonating another user.

For green screen database connections, protecting RCAC definitions against the impersonation of a session user is as easy as making sure the SYSTEM_USER and the SESSION_USER client registers are the same:

/* Insure impersonation is not in effect */
CREATE OR REPLACE PERMISSION CORPDATA.EMPLOYEE__PREVENT_IMPERSONATION
ON CORPDATA.EMPLOYEE
FOR ROWS
WHERE SESSION_USER='APPPROFILE' AND SYSTEM_USER=SESSION_USER
ENFORCED FOR ALL ACCESS ENABLE

This permission definition ensures that only user APPPROFILE can access the EMPLOYEE master and secondarily it insures that some other user hasn’t impersonated APPPROFILE.

For remote database connections (such as OLE DB access through job QZDASOINIT), the task to protect against impersonation isn’t as easy because the SYSTEM_USER register will by default contain QUSER. If your application has the ability to reliably set the CURRENT CLIENT_USERID special register in the connection string to the name of the database connection’s user profile, then a solution like the following could work to prevent an impersonation attempt to access the data:

/* For client/server jobs, make sure the session user matches the 
CURRENT CLIENT_USERID special register  */
CREATE OR REPLACE PERMISSION CORPDATA.EMPLOYEE__PREVENT_IMPERSONATION
ON CORPDATA.EMPLOYEE
FOR ROWS
WHERE SESSION_USER='APPPROFILE'
  AND (SYSTEM_USER=SESSION_USER
   OR (SYSTEM_USER='QUSER' AND SESSION_USER=CURRENT CLIENT_USERID))
ENFORCED FOR ALL ACCESS ENABLE;

Impersonation can be difficult to account for when defining RCAC rules because in some cases there could be a legitimate reason to impersonate another user. Attempts like these examples to prevent impersonation should only be implemented when applications and administrators will never legitimately impersonate.

Securing Functions And Triggers

Triggers, user-defined scalar functions, and table functions can be used to access data protected by RCAC. But allowing custom code to access potentially protected data raises security concerns such as:

Should a trigger or function be allowed to access masked data? After all, the code might do something nefarious with the data, such as save its unmasked form in another table.

Should a function used to test whether or not a row permission should be applied have access to all rows? This is a potential problem because the function, at least temporarily, must be given access to all the row data while it’s performing the test. It too can do something nefarious with data such as load up a data queue with the “protected” rows it’s processing.

IBM‘s solution to these problems is to require a function or trigger that accesses data under the protection of RCAC to be declared as SECURED. While this “security tag” doesn’t prevent programs from doing something they shouldn’t, it does require the attention of the database admin to review the code before allowing it in production. Like other RCAC components, administrative authority (QIBM_DB_SECADM) is required to create secured database code. While the average developer may write an RCAC related function or trigger, it takes an administrator to implement it as “secured.”

Using UDFs With Row Permissions And Column Masks

Let’s take a simple example of using an external scalar function intended to filter row access permissions. Consider the following ILE CL program named RTVJOBNAME:

PGM (&JOB &JOB_NI)

DCL &JOB    *CHAR 10
DCL &JOB_NI *CHAR 2

RTVJOBA    JOB(&JOB)
CHGVAR     VAR(&JOB_NI) VALUE(X'0000')

ENDPGM

This function is intended for use as an external SQL function to retrieve the current job name. It accepts zero inputs and returns one output (the unqualified job name). The function definition looks something like this:

CREATE OR REPLACE FUNCTION CORPDATA.RTVJOBNAME()
RETURNS CHAR(10)
LANGUAGE CL
EXTERNAL NAME 'CORPDATA/RTVJOBNAME'
NO EXTERNAL ACTION
DETERMINISTIC
PARAMETER STYLE SQL
NO SQL
NOT FENCED
SECURED

The SECURED attribute is set so that the function can be included in a row permission definition. Though function RTVJOBNAME doesn’t accept any inputs, it is still required by DB2 to be defined as secured.

Because of the SECURED attribute, I had to specify the qualified program name in EXTERNAL NAME, otherwise the function creation would fail with:

SQL State: 55019
Vendor Code: -7009
Message: [SQL7009] RTVJOBNAME in CORPDATA not valid for operation.
Cause . . . . . : The reason code is 47.
Reason codes are: . . . 47 -- Specify the name of an existing ILE *PGM
or *SRVPGM with an SQL associated space that can be updated with the
attributes of the new function.

I believe the reason for this behavior is because DB2 wants to lock in which specific external program is secured. Leaving the program resolution to the library list leaves the door open for a hacker to slip in a duplicate program higher up in the library list in order to gain access to protected data.

Now say that you want to deny JDBC, ODBC, .NET, etc., connections from accessing the CORPDATA.EMPLOYEE table under all circumstances. The following permission definition will use the RTVJOBNAME() external function to make sure that the QZDASOINIT and QSQSRVR jobs cannot access rows in the table.

ALTER TABLE CORPDATA.EMPLOYEE ACTIVATE ROW ACCESS CONTROL

CREATE OR REPLACE PERMISSION CORPDATA.EMPLOYEE_DENY_REMOTE
ON CORPDATA.EMPLOYEE
FOR ROWS WHERE  
CORPDATA.RTVJOBNAME() NOT IN ('QZDASOINIT','QSQSRVR') 
ENFORCED FOR ALL ACCESS ENABLE 

Alternatively, instead of denying all rows to remote access users, suppose the security requirement is to only (and always) hide the salary information from remote users. A column mask could be used to conceal the salary data from remote users using tools such as Excel and ODBC:

ALTER TABLE CORPDATA.EMPLOYEE ACTIVATE COLUMN ACCESS CONTROL

CREATE OR REPLACE MASK CORPDATA.EMPLOYEE__SALARY 
ON CORPDATA.EMPLOYEE
FOR COLUMN SALARY
RETURN
CASE WHEN CORPDATA.RTVJOBNAME() NOT IN ('QZDASOINIT','QSQSRVR')
     THEN SALARY ELSE NULL END
ENABLE

Now let’s consider a different function that can accept potentially secure input. Scalar function EARNS_BIG_MONEY accepts a salary input and determines whether or not an employee is a top earner. The function is defined as follows:

CREATE FUNCTION CORPDATA.EARNS_BIG_MONEY(SALARY DEC(9,2))
RETURNS CHAR(1)
DETERMINISTIC
NO EXTERNAL ACTION
SECURED
BEGIN
    RETURN CASE WHEN SALARY>=125000
           THEN 'Y' ELSE 'N' END;
END

Now we’ll pass the masked column into the function (and assume that the sample mask CORPDATA.EMPLOYEE__SALARY is in effect):

SELECT e.*,CORPDATA.EARNS_BIG_MONEY(SALARY)
  FROM CORPDATA.EMPLOYEE e;

When the column mask is masking the data (via an ODBC connection running under job QZDASOINIT, for example), the SALARY column is passed to the function as a NULL value. Therefore, every function invocation returns ‘N’. However, when the query is run from the green screen, the function is passed the unmasked salary value and the function will alternately return a value of ‘Y’ or ‘N’ depending on the employee’s salary. In other words, the column mask rule is applied by the database engine before passing the data to the function.

However, if the secured function EARNS_BIG_MONEY is used in a row permission, it will be passed the unmasked salary data in order to determine if the row is accessible. For instance, say profile MY_USER is the only user allowed to view or change the employee master for top earners. The following permission will allow this scenario:

CREATE PERMISSION CORPDATA.EMPLOYEE__TOP_EARNERS
ON CORPDATA.EMPLOYEE
FOR ROWS 
WHERE SESSION_USER='MYUSER' 
  AND CORPDATA.EARNS_BIG_MONEY(SALARY)='Y'
ENFORCED FOR ALL ACCESS ENABLE;

If the EARNS_BIG_MONEY function didn’t include “SECURED” while masked column SALARY was passed as an argument, you’d get this feedback from DB2 when attempting to create the row permission:

Message: [SQ20473] Column SALARY cannot be used as an argument of function
EARNS_BIG_MONEY.
Cause . . . . . : Function EARNS_BIG_MONEY in CORPDATA is not secure and
column SALARY has an active and enabled column mask. An input argument of
a function that is not secure must not reference a column with a mask that
is enabled on a table with active column access control.
Recovery  . . . : Alter function EARNS_BIG_MONEY in CORPDATA to be SECURE.

Note that DB2 only requires the outermost function to be “secured” when defining a row permission. For example, if function EARNS_BIG_MONEY calls other unsecured functions, DB2 doesn’t concern itself with the secured attribute of the nested functions.

Using Triggers With Tables Protected By RCAC

If you intend to enable RCAC for a table with existing triggers, the triggers will need to be recreated and declared SECURED. For example, say you enable row permissions on table ADVWORKS.SALES_ORDER_HEADER and this table has an existing trigger named TRG_SALESORDERHEADER_INSERT that is not secured. Attempting to enable RCAC evokes this nastygram from DB2:

Message ID . . . . . . :   SQ20469

Message . . . . :   Access control on table SALES00002 in ADVWORKS is not
  valid.

Cause . . . . . :   Row or column access control for table SALES00002 in
  ADVWORKS either cannot be activated or is not valid.  The reason code is 37.
   Reason codes are:
    37 -- A trigger, TRG_SALESORDERHEADER_INSERT in ADVWORKS, is defined for
  the table and the trigger is not defined as secured or is a read trigger.

This is another one of those unfortunate (but necessary) circumstances that requires some application tinkering prior to implementing RCAC. Thankfully, the tinkering is minimal and as long as the source hasn’t been obfuscated, the fix can be as simple as generating the SQL for the trigger, adding the secured keyword and then re-creating the trigger.

Like functions, triggers must be tagged as “secured” because they can access unprotected data. To demonstrate, assume table EMPLOYEE has a column mask defined for column SSN. Review this simple trigger that sends a message (using SNDMSG) when an employee row is updated, even if the user should be seeing a masked value:

CREATE OR REPLACE TRIGGER CORPDATA.EMPLOYEE__AFTER_UPDATE
AFTER UPDATE ON CORPDATA.EMPLOYEE
REFERENCING 
  NEW ROW AS NEW 
  OLD ROW AS OLD
FOR EACH ROW MODE DB2ROW
SECURED
SET OPTION COMMIT=*NONE
BEGIN
-- Demo requires removal of FK constraint
-- ALTER TABLE CORPDATA.EMPLOYEE DROP CONSTRAINT RED;
DECLARE @SQL VARCHAR(100);

SET @SQL='SNDMSG (''Old/New '||DIGITS(COALESCE(OLD.SSN,1))||'/'||
DIGITS(COALESCE(NEW.SSN,1))||''') TOUSR('||SESSION_USER||')';
CALL QSYS2.QCMDEXC(@SQL);

END

When running the following statement in a scenario when the SSN column mask is active:

UPDATE CORPDATA.EMPLOYEE
   SET SSN=988007114
 WHERE EMPNO='000160';

The trigger will send a message showing the unmasked column values of the “before” SSN value and the “after” value. This example simply illustrates that triggers can access unmasked RCAC data; something every security administrator should be concerned with.

One other important note about RCAC and triggers is that read triggers cannot be used with row permissions and column masks.

Plugging The Holes In The Dike

In summary, RCAC provides a simple and effective way to limit access to entire rows and specific columns of data. However, issues like impersonation and programmatic access to data can open up a few extra holes in the security dike provided by RCAC. If necessary, impersonation attempts can be denied and database code capable of bypassing masks and permissions should be reviewed by the security admin and marked as SECURED. It is the responsibility of the database administrator to carefully review each of these potential holes and plug them before a security leak is sprung.

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.

RELATED STORIES

RCAC in DB2 For i, Part 2: Column Masks

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