Securing Individual Database Fields

The AS/400 database does not directly support field-level security for a file; that is, there's no direct way to protect an individual field from unauthorized access while leaving all others unprotected. At most, you can give users *READ authority (to read records), *ADD (to add records), *UPD (to update records) or *DLT (to delete records), but this is a blanket authority that covers the entire record-not a specific field.

However, there's a circumvention to protect a particular field in a physical file: create a logical file that contains a subset of the fields in the physical file. For example, picture an Employee Master file. You want anyone to be able to read the nonsensitive part of the record, such as employee number, name, and the department number. You don't want anyone outside of Human Resources to be able to read the rest of the fields, such as hourly pay rate and deductions. 1 shows the physical file and 2 the logical file. The logical file only "sees" the employee number, name and department number-but not the rest of the information, which is confidential.

However, there's a circumvention to protect a particular field in a physical file: create a logical file that contains a subset of the fields in the physical file. For example, picture an Employee Master file. You want anyone to be able to read the nonsensitive part of the record, such as employee number, name, and the department number. You don't want anyone outside of Human Resources to be able to read the rest of the fields, such as hourly pay rate and deductions. Figure 1 shows the physical file and Figure 2 the logical file. The logical file only "sees" the employee number, name and department number-but not the rest of the information, which is confidential.

Now consider which users would be given access to the limited information. Give them all *OBJOPR authority to the logical file. This gives the users the authority to open the logical file and use the data authorities of the physical file on which the logical is based (the Employee Master).

Then give the users the desired data rights to the physical file. If you only want them capable of reading the data, give them *READ authority to the physical file. Don't give them *OBJOPR authority to the physical file, though. Because they don't have *OBJOPR authority, they cannot open the physical file itself, thus protecting the confidential fields.

You can use the same technique to allow some users to change certain fields but not others. Again, create a logical file with *OBJOPR authority to the users, and give *CHANGE authority (but not *OBJOPR) to the physical file.

TechTalk: Securing Individual Database Fields

Figure 1 Employee Master Physical File

 ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A R EMREC A EMEMPL 5P 0 TEXT('Employee number') A EMNAME 30A TEXT('Employee name') A EMADR1 30A TEXT('Address line 1') A EMADR2 30A TEXT('Address line 2') A EMCITY 25A TEXT('City') A EMSTAT 2A TEXT('State') A EMZIP 9P 0 TEXT('Zip code') A EMDEPT 4P 0 TEXT('Department number') A EMHIRE 8P 0 TEXT('Hire date, YYYYMMDD') A EMHPAY 5P 2 TEXT('Hourly pay rate') A K EMEMPL ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 
 
 TechTalk: Securing Individual Database Fields
 
 Figure 2 Employee Master Logical File for Restricted Access
 
 ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 A R EMREC PFILE(EMPMST) A EMEMPL A EMNAME A EMDEPT A K EMEMPL ... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7