Guidelines for writing field procedures that mask data

Field procedures can be used to mask data for certain users or environments when data is decoded. Field procedures that mask data must be coded to handle special situations to ensure data is not corrupted.

The following special situations must be handled by a field procedure that masks data:

Field-decoding
- Masking must only be performed for field-decoding. It must not be performed for field-encoding. If masking was performed for field-encoding, the masked data would be stored in the table and the actual value would be lost.
- In some cases, system code needs to copy data internally (the data is not being returned to the user in these cases). For example, in some cases, RGZPFM, ALTER TABLE, and CRTDUPOBJ must copy data internally. Likewise, data passed internally to triggers must not be masked. During these operations, when the data is read, field-decoding will occur and when the data is written, field-encoding will occur. If masking is performed in these cases during field-decoding, the mask data will then be written and the actual data will be lost.
  To prevent corruption, the ninth parameter to the field procedure indicates whether this is a system operation where masking must not be performed. It is critical that the field procedure be written to check this parameter during field-decoding and if the parameter indicates that masking must not be performed, the field procedure must not mask regardless of the user or environment.
Field-encoding
- For native update and insert operations, the field procedure must be able to identify when masked data is being passed to the field procedure and take special actions. For example, a field procedure might be written to mask a credit card number column. That same user may be authorized to read and update the table through an RPG application that performs READ and UPDATE operations. When the READ is performed, the credit card number is masked to prevent the user from seeing it, but when the user performs the UPDATE, the masked data will be passed back to database on the UPDATE operation and the field procedure will be called to encode the data. If the field procedure does not recognize that the value being passed is masked, the masked data would be encoded and stored in the table and the original value in the row would be corrupted with an encoded masked data.
  To prevent corruption, the field procedure must recognize on field-encoding that the data is masked. Instead of encoding the data, the field procedure must return a warning SQLSTATE value of ’09501’ in the seventh parameter.
  - For an UPDATE operation, ’09501’ indicates to DB2® that the current value for the column should be used.
  - For an INSERT operation, ’09501’ indicates to DB2 that the default value should be used for the associated column value.

Query Considerations: There are several considerations that apply to queries that reference a column of a table that has a field procedure that masks data:

Depending on how the optimizer implements a query, the same query may return different rows and values for different users or environments. This will occur in cases where optimizer must decode the data in order to perform comparisons or evaluate expressions in a query. If masking is performed for one user but not for another user, the result of the decode operation will be very different, so the resulting rows and values can also be quite different for the two users.
For example, assume that a field procedure returns (decodes) data for user profile MAIN without masking and returns (decodes) data for user profile QUSER with masking. An application contains the following query:
```
SELECT * FROM orders WHERE cardnum = '112233'
```
By default, the optimizer will try to implement the search condition (logically) as follows:
```
WHERE cardnum = FieldProc ENCODE('112233')
```
This is the best performing implementation since it allows DB2 to compare the encoded version of the constant ’112233’ with the encoded version of the CARDNUM values that are stored in the orders table. Since the optimizer did not decode the data to perform the comparison, the query will return the same rows for the MAIN and QUSER user profiles. The only difference will be that QUSER will see masked values in the result rows for the CARDNUM column.
The implementation of queries that reference a field procedure column can be controlled by the QAQQINI FIELDPROC_ENCODED_COMPARISON option. The default value for this option is *ALLOW_EQUAL. This option enables the optimizer to implement the comparison using the encoded values.
In the previous example, if the FIELDPROC_ENCODED_COMPARISON option was changed to *NONE, the query would return different rows for the two users. When the value is *NONE, an equal comparison will be implemented internally by DB2 as follows:
```
WHERE FieldProc DECODE(cardnum)='112233'
```
In this case, DB2 has to decode the CARDNUM values for every row in the table to compare against the original constant '112233'. This means that the comparison for the MAIN user profile will compare the decoded and unmasked card number values (112233, 332211, etc) to ’112233’. The MAIN user profile will find the orders associated with the specified card number (112233). However, the query will not return any rows for the QUSER user profile. That is because the comparison for QUSER will be comparing the masked value of the card numbers (****33, ****11, etc) with the constant ’112233’.
For more information on how the QAQQINI FIELDPROC_ENCODED_COMPARISON option affects field procedures see the Database Performance and Query Optimization topic in the Information Center.
REFRESH of a materialized query table is affected by the QAQQINI FIELDPROC_ENCODED_COMPARISON option. If the materialized query table references a column with a field procedure that masks, it is imperative that the REFRESH of the MQT be issued by a user that is allowed to see unmasked data. Otherwise, the results in the MQT will be incorrect for all users.
CREATE TABLE LIKE, CREATE TABLE AS, DECLARE GLOBAL TEMPORARY TABLE LIKE, or DECLARE GLOBAL TEMPORARY TABLE AS are affected by the QAQQINI FIELDPROC_ENCODED_COMPARISON option. If the statements are issued by a user that is not allowed to see unmasked data, the resulting table will contain masked data.
OPNQRYF and Query/400 are not affected by the QAQQINI FIELDPROC_ENCODED_COMPARISON option. The optimizer always processes by decoding values (similar to a FIELDPROC_ENCODED_COMPARISON option of *NONE).
Select/omit DDS-created logical files are also not affected by the QAQQINI FIELDPROC_ENCODED_COMPARISON option. The logical file is processed by decoding values (similar to a FIELDPROC_ENCODED_COMPARISON option of *NONE).

Best Practices: There are two QAQQINI options that are strongly recommended for use if you have field procedures that mask data:

FIELDPROC_ENCODED_COMPARISON: The default option for FIELDPROC_ENCODED_COMPARISON is *ALLOW_EQUAL which works very well for field procedures that do not mask data. If field procedures are used that do mask data, however, *NONE is the most secure and recommended option.
CACHE_RESULTS: The default option for CACHE_RESULTS is *SYSTEM. In many cases, this option works well. However, if field procedures that mask data are used, you should specify *JOB for CACHE_RESULTS.

Since these two options can affect the behavior of field procedures that mask data, it is also important to ensure that only authorized users be allowed to specify new or different QAQQINI options:

CHGQRYA command
Verify that only authorized users can execute the CHGQRYA command. By default only users with job control (*JOBCTL) special authority or have the QIBM_DB_SQLADM function usage are authorized to the CHGQRYA command.
QUSRSYS/QAQQINI file
Verify that only authorized users can create the QUSRSYS/QAQQINI file or update it if it already exists. By default *PUBLIC has *USE authority to QUSRSYS which is not be enough authority to create a new QUSRSYS.QAQQINI file.