The IDUG Solutions Journal
Volume 7, Number 3 (Winter 2000)

Table of Contents Letter from the President of IDUG Letter from the Executive Editor of the Journal Implementing Low Level Access Control With DB2 UDB Mining the DB2 Catalog With SQL SQL: Building Block #1 Tid Bit DB2 for OS/390: Version 5 Versus Version 6 Outer Join Performance Regional User Group Listings The Seedy Underworld of DB2 UDB for OS/390 Performance Basics Redux Editorial Information/Advertisers’ Index Mission Statement/Board of Directors

Implementing Low Level Access Control With DB2 UDB

by Paul Bird

ecently, a great deal of interest has been expressed in understanding and implementing low-level access control in relational databases, specifically control over access at the row or column level. This article is meant to explain some of the issues involved in this topic and to describe how to successfully implement low-level access control within DB2 Universal Database (DB2 UDB).

Low-level access control refers to the ability to limit the data visible or modifiable by database users on a row-by-row, or column-by-column, basis. Traditionally, this type of control has been done within applications by implementing additional predicates on SQL statements or by checking data values returned to determine whether or not the row, or column, should be included in the set seen by the user. This approach has a major weakness: it requires that the applications prevent inappropriate information from being seen by the user. It works only when such applications are produced and approved by in-house personnel who understand the rules and have a vested interest in guarding the privacy and integrity of the data.

In the world of e-business, these assumptions no longer apply. Access to information in the database is being opened up to third-party applications and to applications whose SQL content is fluid, varying by user request and context. This means that the onus of enforcing low-level access control must be pushed into the database in order to ensure that the rules are effectively applied to all users and applications who are accessing the database regardless of the application used. This change has the added benefit of allowing centralized codification of access control policies that are implemented and maintained by the information owner within the same database as the information is stored.

DB2 UDB provides a rich variety of mechanisms that can be used to enforce access control policies that differ according to the user attempting the access. These mechanisms include table privileges, column privileges, views, and triggers. In combination with a user-defined access policy, the tools can be used to implement a pervasive low-level access control in a DB2 UDB database.

Although this discussion applies to all platforms (including 390 and 400) the specific examples which follow were performed on the UNIX, Windows, and OS/2 versions of DB2 UDB.

Defining an access control policy

Before looking at specific examples of how to implement low-level security, we must review what needs to be defined within an access control policy. Basically, we can divide the issue into two parts: read access control and write access control. Control of read access specifically relates to SELECT statements, while control of write access is concerned with INSERT, UPDATE, and DELETE statements. In order to be successfully implemented and maintained, an access policy needs to be clearly defined. The questions that need to be answered for either a read or a write access control policy for each row are:

1. How will a user be defined?
   • Is it a specific user ID or a class of users?
   • What will be used to identify the user trying to access the rows? For example, a user’s ID, department, or city could be used.
2. How will the access level, or levels, be uniquely identified for each user requesting access?
   • Is more than one access level possible for each user?
3. How will the access level, or levels, be uniquely identified for each row?
   • What attributes of the row will be used to indicate the access level required? For example, the supplier, the part number, or the salary could be used.
4. Are there unique access requirements for individual columns within a row? If so, how are they to be uniquely identified?
   • Can a user access all of the columns, some of the columns, or does the number of accessible columns depend on the access level of the user? If the number of columns is less than the number in the row, or varies according to the requester, how is the access level for each subset to be determined?
5. Do the read and write row sets intersect in any way?
   • Do they overlap? Is one the subset of another?

Once these elements have been clearly defined, a secure row and column level access control policy can be implemented. For example, if a company had a table containing all their employee information, then the access control policy for their employee table might look like this:

Row Access Policy

Public Access	Employee Access	Human Resources Access	Management Access
Read: No rows Write: No rows	Read: Own row Write: Own row Update	Read: All rows Write: All rows Update Insert Delete	Read: All row Write: All rows Update

Column Access Policy

Column Contents	Public Access	Employee Access	Human Resources Access	Management Access
Employee Identification Number	Read: No Write: No	Read: Yes Write: No	Read: Yes Write: No	Read: Yes Write: No
Employee User ID	Read: No Write: No	Read: Yes Write: No	Read: Yes Write: No	Read: Yes Write: No
Employee Information	Read: No Write: No	Read: Yes Write: Update	Read: Yes Write: Update	Read: Yes Write: No
Employee Evaluation	Read: No Write: No	Read: No Write: No	Read: Employee rows only Write: No	Read: Yes Write: Update
Department Number	Read: No Write: No	Read: Yes Write: No	Read: Yes Write: Update	Read: Yes Write: No

This policy lays the groundwork for determining who is allowed to perform what actions against what rows or columns. To complete it, we need a concise definition of how an employee, perhaps a member of the Human Resources department or a member of the management team, is identified with the context of the database in which the table is going to be implemented. In this example, we might use the following definitions:

Employee

•	Any user whose database user ID appears in the Employee Userid column of the table is by definition an employee of the company.

Human Resources

•	Any employee who is a member of department d789 is a member of the Human Resources (HR) department; this department is viewed as the business owner of the employee information table. Members of this department will be defined as the only members of the group ‘d789’.

Management

•	Any employee who is a member of department d666 is a member of the Management team. Members of this department will be defined as the only members of the group ‘d666’.

Implementing an access control policy

Once an access policy has been created and verified, we then face the task of implementing it in a secure manner on the target database. As mentioned previously, we have a number of tools and methods at our disposal and there are often different ways to achieve the same objective. As a guiding principle, make the implementation as simple to maintain and enforce as possible. An implementation that you can easily understand is also one that you can audit and maintain with minimal effort.

Implementing read access policies

The read portion of most access policies is best implemented through judicious grants of privileges and the use of views as the external interface to the user. The first line of defense is your control of who has the SELECT privilege on the subject table. If a user does not have this privilege on the table, he/she simply cannot access the data in the table. By minimizing the granting of this privilege, as well as the granting of the ability to grant the privilege itself, you can minimize your access exposure.

DB2 provides support for both dynamic and static SQL statements. Static SQL statements are stored in the system catalog tables in an organized collection referred to as a package. When a package is created, DB2 checks that the required privileges for each static SQL statement are held by the user ID that is creating the package, referred to as the package authorization ID, or binder, of the package. To create a package, direct access to the table need only be explicitly granted to the binder of the package. Once created, other users can execute the static SQL statements in the package without holding the required privileges for that statement as long as they hold the EXECUTE privilege on the package itself. Note also that the statements acting against the table are strictly controlled since they are defined as part of the package.

For dynamic SQL statements, DB2 checks that the required privileges for each dynamic SQL statement are held by the specific user ID that is trying to execute the statement or by a group to which that ID belongs. This can be onerous if an application is widely used: either every user must be granted the same privilege or they must all belong to the same group to which the privilege has been granted. One way to restrict access to the table but still allow the flexibility that dynamic SQL statements offer is to use the DYNAMICRULES(BIND) option when creating the package used by the application. This option states that the package authorization ID will be used as the authorization ID for any dynamic SQL executed within the package. This simplifies things because, in the same manner as for static SQL statements, only the authorization ID used to create the package needs to be granted the privileges on the table.

The use of views as a primary instrument in implementing a read access policy is advantageous in that views can be structured to hide the existence of rows and/or columns as well as to modify the contents of columns. The existence of a view also allows the focal point of access privileges on the subject table to be through the view; users are granted SELECT privileges on the view and not on the subject table itself.

Expanding on the example access policy given earlier for an employee information table, we could define the required tables and data as shown in the following SQL statements. Some simplifications have been made to the scenario in order to maintain the clarity of the example.

— Create department table create table bigco.department   (department_id                 char(4) not null primary key,   department_info    varchar(255)); — Create employee information table create table bigco.employee    (employee_id          int not null primary key,   employee_userid    char(30) not null unique,   employee_info   varchar(255),   employee_evaluation   char(4) default ‘ ‘,   department_id   char(4) not null       references bigco.department); — Insert department information insert into bigco.department values (‘d111’, ‘Marketing’), (‘d222’,’Inventory Control’),   (‘d333’,’Accounting’), (‘d789’,’Human Resources’),   (‘d666’,’Management’); — Insert employee information insert into bigco.employee values (1, ‘HUGO’,’some fascinating tidbits’,’A111’,’d666’),   (2, ‘MARY’,’some fascinating tidbits’,’B212’,’d789’),   (3, ‘FRUITFLY’,’some fascinating tidbits’,’FFFF’,’d111’),   (4, ‘PBIRD’,’some fascinating tidbits’,’B114’,’d222’),   (86,’MAX’,’some fascinating tidbits’,’A32A’,’d333’);

Once we have the underlying tables defined, we can implement the read access policy defined previously for the employee information table through the use of views and the granting of SELECT privileges. In this situation, we can use two view definitions: one to allow employees to look at their own row, and another for the members of the management and Human Resources (HR) departments to look at all rows in the employee table.

— Create view that allows employees access to own row and some columns of employee table create view bigco.employee_access   (Employee_ID, User_ID, Employee_Information, Department_ID) as (select employee_id, employee_userid, employee_info, department_id from employee where employee_userid = USER); — Allow read access on employee view to all employees grant select on bigco.employee_access to public; — Create view that allows HR and management access to all rows create view bigco.mgmt_access   (Employee_ID, User_ID, Employee_Information, Department_ID, Employee_Evaluation) as (select employee_id, employee_userid, employee_info, department_id, — if employee is in Management dept, then only allow users in   — the Management department to see the Evaluation     case when (department_id = ‘d666’)       then case (select department_id           from bigco.employee as x where x.employee_userid = USER)         when ‘d666’ then employee_evaluation else ‘N/A’       end               else employee_evaluation     end           from bigco.employee); — Allow read access on management view to all members of Management and HR depts grant select on bigco.mgmt_access to group d666, group d789;

Depending on the view used and the user information, different results are obtained, as shown in the samples below. Note that the mgmt_access view does not hide the existence of the employee_evaluation column but it does hide, or mask out, the values of this column when the row being viewed is for a manager and the user is not a member of the management department. Also, we are guaranteed that no one can bypass this arrangement by the privileges granted on the different views. No one has select access to the base tables; only members of the ‘d789’ and ‘d666’ groups have select access on the mgmt_access view while everyone has access to the employee_access view. This particular example assumes that dynamic SQL is used to access the tables so that group authorizations are relevant. For the static SQL authorization model, no privileges would be granted to any groups, only to the specific authorization ID used to bind the package containing the SQL. A similar discrimination of privileges can be achieved for static SQL by using a distinct authorization ID for each package. For example, a package containing static SQL for use by all employees and accessing the employee_access view could be bound using one authorization ID that has SELECT privilege on that view, and then EXECUTE privilege on that package could be
granted to PUBLIC. With this approach, no employee has access to either the table or the view; they can get to the information only through the static SQL in the package.

— select by non-HR employee (PBIRD) on employee table (no access) select * from bigco.employee; SQL0551N “PBIRD” does not have the privilege to perform operation “SELECT” on object “BIGCO.EMPLOYEE”. SQLSTATE=42501. — select by management (HUGO) on employee table (no access) select * from bigco.employee; SQL0551N “HUGO” does not have the privilege to perform operation “SELECT” on object “BIGCO.EMPLOYEE”. SQLSTATE=42501. — select by non-HR employee (PBIRD) on employee_access view select employee_id, user_id, department_id from bigco.employee_access; EMPLOYEE_ID        USER_ID                                                               DEPARTMENT_ID ——————————- ——————————————— ————————————-                                      4 PBIRD                                                                         d222 1 record(s) selected.

— select by management (HUGO) on employee_access view select employee_id, user_id, department_id from bigco.employee_access; EMPLOYEE_ID        USER_ID                                                               DEPARTMENT_ID ——————————- ——————————————— ————————————-                                      1 HUGO                                                                         d666 1 record(s) selected. — select by non-HR employee (PBIRD) on mgmt_access view (no access) select employee_id, user_id, employee_evaluation from bigco.mgmt_access; SQL0551N “PBIRD” does not have the privilege to perform operation “SELECT” on object “BIGCO.MGMT_ACCESS”. SQLSTATE=42501. — select by HR employee (MARY) on mgmt_access view (management evaluations are hidden) select employee_id, user_id, employee_evaluation from bigco.mgmt_access; EMPLOYEE_ID        USER_ID                                                      EMPLOYEE EVALUATION ——————————- ——————————————— ————————————-                                      1 HUGO                                                                        N/A                                      2 MARY                                                                        B212                                      3 FRUITFLY                                                                 FFFF                                      4 PBIRD                                                                        B114                                      86 MAX                                                                        A32A 5 record(s) selected.

— select by management on mgmt_access view (all evaluations shown) select employee_id, user_id, employee_evaluation from bigco.mgmt_access; select employee_id, user_id, employee_evaluation from bigco.mgmt_access; EMPLOYEE_ID        USER_ID                                                        EMPLOYEE EVALUATION ——————————- ——————————————— ————————————-                                      1 HUGO                                                                        A111                                      2 MARY                                                                        B212                                      3 FRUITFLY                                                                 FFFF                                      4 PBIRD                                                                        B114                                      86 MAX                                                                        A32A 5 record(s) selected.

Implementing Write Access Policies

As with read access, the control of granting write privileges (that is, INSERT, UPDATE, or DELETE privileges) on the subject table is the ultimate defense. If a user does not have the privilege, then that user cannot perform the operation. Of course, it is not realistic to assume that you can avoid granting write access to a table that needs to be modified. If you must grant a user a write privilege on the table, it is best to minimize the extent of the grant as much as possible. For example, when granting UPDATE privilege, only grant it on those columns that are required if a subset of columns is all that needs to be updated. Once again, as with read privileges, you also have the choice of using either the static SQL or the dynamic SQL authorization models provided by DB2 to control who has what privilege and in what context.

The use of views as the primary write access point is also recommended whenever possible. You can avoid granting direct privileges on the subject table by granting the user the write privilege on the view instead. This allows a consistent external access point to be maintained from the user’s perspective while controlling direct access on the subject table from both a privilege and a table content and structure perspective. The use of the WITH CHECK OPTION clause on the view definition ensures that any write access attempted through the view will match the criteria of the view itself. The WITH CHECK OPTION on a view definition ensures that the specific write access attempted on the view must be logically consistent with rows that could have been read through the view. That is, the rows being modified must be rows that would have been visible to the user requesting the modification through the view definition. This applies to all INSERT, UPDATE, and DELETE operations. This is particularly powerful when the set of rows defined for a user by the read and write access policies are identical, because all access for that user can be enforced simply through the view definition and the privileges granted on the view without any additional enforcement mechanisms required. The following example shows how the previous view definitions, given earlier during the discussion on how we could implement read access control, can be modified to include the WITH CHECK OPTION clause.

— Create view that allows employees access to own row and some columns of employee table create view bigco.employee_access   (Employee_ID, User_ID, Employee_Information, Department_ID) as (select employee_id, employee_userid, employee_info, department_id from employee where employee_userid = USER); with check option; — Create view that allows HR and management access to all rows create view bigco.mgmt_access   (Employee_ID, User_ID, Employee_Information, Department_ID, Employee_Evaluation) as (select employee_id, employee_userid, employee_info, department_id,     case when (department_id = ‘d666’)       then case (select department_id           from bigco.employee as x where x.employee_userid = USER)         when ‘d666’ then employee_evaluation else ‘N/A’       end               else employee_evaluation     end           from bigco.employee); with check option;

With these new modified view definitions, we can now grant the privileges required to implement most of the write portion of the previously defined access
control policy (shown below).

— Allow update on employee_info to all on employee_access view grant update(employee_information) on bigco.employee_access to public; — Allow update on employee_information on mgmt_access view to HR grant update(department_id, employee_information) on bigco.mgmt_access to group d789; — Allow delete on mgmt_access view to HR grant delete on bigco.mgmt_access to group d789;

The combination of view definitions and privileges will effectively control the behaviors of the different users accessing the employee information. The following examples show the results of various attempts to make modifications to the data through both the employee_access and mgmt_access views. Note that because no one has yet been granted any privileges on the base tables, any attempt to access those tables by any employee from any department will fail.

— employee_access examples — Attempt by regular employee (PBIRD) to update employee_information update bigco.employee_access set employee_information = ‘A different tidbit’; DB20000I The SQL command completed successfully. — Note that only PBIRD employee_information updated — Attempt by regular employee (PBIRD) to update employee_information for MARY update bigco.employee_access set employee_information = ‘A different tidbit too’          where user_id = ‘MARY’; SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 — Attempt by regular employee (PBIRD) to update employee_id update bigco.employee_access set employee_id = 77; SQL0551N “PBIRD” does not have the privilege to perform operation “UPDATE” on object “BIGCO.EMPLOYEE_ACCESS”. SQLSTATE=42501 — Attempt by HR employee (MARY) to update employee_information for MAX update bigco.employee_access set employee_information = ‘An exciting tidbit’ where user_id = ‘MAX’; SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000 — Attempt by HR employee (MARY) to insert a new employee insert into bigco.employee_access values (99, ‘BARBARA’, ‘some fascinating tidbits’, ‘d111’); SQL0551N “MARY” does not have the privilege to perform operation “INSERT” on object “BIGCO.EMPLOYEE_ACCESS”. SQLSTATE=42501 — Attempt by HR employee (MARY) to delete an employee delete from bigco.employee_access where user_id = ‘MAX’; SQL0551N “MARY” does not have the privilege to perform operation “DELETE” on object “BIGCO.EMPLOYEE_ACCESS”. SQLSTATE=42501

At this point, we have implemented almost all of the access control policy that we defined earlier. The two remaining pieces are the ability of HR employees to insert new employees into the employee table and the ability of management to update the employee_evaluation column of the employee table. Because of the definition used in the mgmt_access view, the employee_evaluation column in this view is not updatable because it is constructed from the result of a case expression. In this case, we need to implement an alternative method of providing and controlling the specified access.

In addition to the need to update non-updatable columns in views, there are other scenarios where alternative methods of providing write access control outside of the traditional view approach are needed. These other scenarios include:

•	read-only views (where the view definition is such that it does not allow inserts or modifications through the view)
•	those cases where the set of rows that can be read by a user is not identical to the set that the user can modify

These situations require that users have a different method of modifying the data from that of reading it. While the philosophy of restricting direct privileges on a table could still be adhered to by creating a new view with a different definition that allowed for inserts and updates, this new view could only be used for write access by the user. The reason is that its definition could not simultaneously support the read access
control. For this type of view, the user would only be granted write privileges but not read privileges on the view. Some administrators may prefer the more concise approach of, in certain limited cases, providing users with direct INSERT, UPDATE, or DELETE privileges on a table. Regardless of the approach taken with respect to the granting of write privileges, the enforcement of the write access control policy needs to be done at the target table itself. In these cases, the appropriate use of INSERT, UPDATE, or DELETE triggers on the table can provide the enforcement of the required write access control policy.. The basic mechanism that can be used within a trigger is similar to the one used in a view definition: the WHEN clause in the definition of the trigger is used to determine if an access policy violation has occurred for the type of trigger or, at the very least, determine if further analysis of the attempt is required before allowing it to proceed. The body of the trigger performs the final determination if a violation has occurred, if still required, and then takes action to prevent the access. For ease of maintenance in the event of future changes, the trigger definitions should be kept as clean and
simple as possible, with adequate comments. Note that these policy enforcement triggers will be in effect and will guard the subject table against all attempted write access, whether the attempt is made directly against the table or indirectly through a view.

A number of options are available when deciding how to implement an access policy through a trigger. First, it is necessary to decide on the approach to be used when an incorrect, or restricted, access is attempted. Should an error be returned or should the restricted access attempt be nullified and processing allowed to continue? An error can be returned from within a trigger through use of the SIGNAL SQLSTATE statement. The signaling of an error within a trigger will cause the statement that prompted the offense to be rolled back and all changes made by the statement will be removed from the database. In some cases, where a large number of rows are being modified, it may be desirable to simply eliminate the row causing the error scenario and allow the processing of the statement to complete successfully. The technique needed to mask out the bad row in order to allow execution to continue varies depending on the type of trigger; possible nullifying actions are discussed with each type of trigger in subsequent text. In general, however, access violations should be dealt with by issuing an error to the user versus attempting to nullify the results of the access without a visible error. Triggers allow you much more flexibility when dealing with access attempts than views do. Because of this, when deciding how to handle failed access attempts found within policy enforcement triggers, one should consider whether or not the offense should be recorded somewhere for audit purposes and possible future follow-up activities.

To implement access control for update actions that cannot be implemented through the WITH CHECK OPTION clause on a view, the definition on the subject table of a row-level UPDATE trigger based on the NO CASCADE BEFORE type is recommended. A trigger of this type is evaluated for each row affected by an UPDATE statement before the update is applied to that row. The trigger evaluation for an update trigger can be fine-tuned to be sensitive only to the update of specific columns within the table; this is done in order to minimize the impact of the evaluation as much as possible. In order to nullify any UPDATE access violation in this type of trigger, you can substitute the old values of the row for the new values before letting the update action proceed. The new and old rows can be made available within the trigger body through use of the REFERENCING clause.

Going back to our example for a moment, in order to implement the ability of management to update the
employee_evaluation column of the employee table, we could implement an UPDATE trigger similar to the one shown in the following example. This one will nullify an invalid update by an HR employee. However, in this case we do not really need to use a trigger because the actual enforcement of the access control policy is really done by the explicit grant of the UPDATE privilege to the management group on the employee_evaluation column of the employee table. The UPDATE trigger definition given is actually only ensuring that the value being submitted is in the valid set of evaluation values. While this is not a policy enforcement trigger, the definition and the nullifying action of this trigger are identical to the ones that would be used by a policy enforcement trigger. The WHEN clause definition would be different since it would be used to detect access control violations rather than erroneous updated values.

— Allow update of employee_evaluation on employee table by management grant update(employee_evaluation) on bigco.employee to group d666 — Create an update trigger on bigco.employee to enforce update access control — so that only management can update employee_evaluation create trigger update_control no cascade before update of employee_evaluation on   bigco.employee referencing old as old new as new for each row mode db2sql — if invalid employee_evaluation value when (new.employee_evaluation not in (‘A111’,’B212’,’FFFF’,’B114’,’A32A’)) begin atomic     — replace new value with current value and allow processing to continue set new.employee_evaluation = old.employee_evaluation;   end

Some examples of how this implementation would react to different update attempts are shown in the following sample output:

— Attempted update by HR employee (MARY) update bigco.employee set employee_evaluation = ‘FFFF’ where employee_id = 4; SQL0551N “MARY” does not have the privilege to perform operation “UPDATE” on object “BIGCO.EMPLOYEE”. SQLSTATE=42501 — Attempted update of all records by manager (HUGO) to an invalid value update bigco.employee set employee_evaluation = ‘jjjj’; DB20000I The SQL command completed successfully. — Results of update attempt read by manager (HUGO) select employee_evaluation from bigco.mgmt_access EMPLOYEE_EVALUATION —————————- A111 B212 FFFF B114   4 record(s) selected. — Attempted update of all records by manager (HUGO) to valid value update bigco.employee set employee_evaluation = ‘FFFF’; DB20000I The SQL command completed successfully. — Results of update attempt read by manager (HUGO) select employee_evaluation from bigco.mgmt_access EMPLOYEE_EVALUATION —————————- FFFF FFFF FFFF FFFF   4 record(s) selected.

To implement a policy enforcement trigger to control insert access attempts, you can define a row-level INSERT trigger that would check for invalid access attempts. If an error is going to be reported upon detection of an access violation, it is recommended that you use a NO CASCADE BEFORE trigger for performance reasons. Nullifying an insert action without raising an error to the user is problematic: the only method is to delete the newly inserted row afterwards through use of a row-level trigger based on the AFTER trigger type. The use of an AFTER trigger to nullify the insert does not prevent other AFTER triggers from being fired; thus, not all of the changes to the database may be nullified if other row-level AFTER triggers exist.

The final missing piece of our example implementation of the access control policy defined at the beginning of this article is the ability of HR employees to insert new employees into the employee table. As with the update ability, this is best and most easily implemented as a simple grant of the INSERT privilege on the employee table to the HR department group ID (that is, d789). This is the recommended way. However, for the sake of discussion, this ability could also be implemented in the following manner, where the INSERT privilege is granted to PUBLIC and a row-level INSERT trigger is used to detect access violations.

— Allow insert on employee table by everyone grant insert on bigco.employee to public — Create an insert trigger on bigco.employee to enforce insert access control — so that only HR employees can insert new employees create trigger insert_control no cascade before insert on bigco.employee   for each row mode db2sql — if user is not a member of the HR department   when (USER not in (select employee_userid     from bigco.employee where department_id = ‘d789’))   begin atomic     — signal an error signal sqlstate ‘75001’ (‘Access denied’);   end

Some examples of how this implementation would react to different insert attempts are shown in the following sample output:

— Attempt by a regular employee (PBIRD) to insert a new employee insert into bigco.employee values (99, ‘BARBARA’, ‘some fascinating tidbits’, ‘FFFF’, ‘d111’); SQL0438N Application raised error with diagnostic text: “Access denied”. SQLSTATE=75001 — Attempt by a manager (HUGO) to insert a new employee insert into bigco.employee values (99, ‘BARBARA’, ‘some fascinating tidbits’, ‘FFFF’, ‘d111’); SQL0438N Application raised error with diagnostic text: “Access denied”. SQLSTATE=75001 — Attempt by an HR employee (MARY) to insert a new employee insert into bigco.employee values (99, ‘BARBARA’, ‘some fascinating tidbits’, ‘FFFF’, ‘d111’); DB20000I The SQL command completed successfully.

And finally, to enforce delete access control policies through a trigger, you again define a row-level DELETE trigger that would check for invalid access attempts. If an access violation error is going to be reported, it is recommended that you use a NO CASCADE BEFORE trigger for performance reasons. Nullifying a delete action creates the same issues that arise when trying to nullify an invalid insert access: the only method is to reinsert the deleted row into the table afterwards through use of a row-level trigger based on the AFTER trigger type. As before, this does not prevent other AFTER triggers from being fired; thus, not all of the changes to the database may be nullified if other row-level AFTER triggers exist.

Summary

After reading this article and trying out your own variations on the example implementation provided, you should have a good basic understanding of the various methods available to you within DB2 Universal Database to provide secure and reliable access control at both a row and a column level.

To ensure a successful implementation:

1.	Create a clear definition of your access control policy for read and write access.
2.	Remember that privileges are your first and best line of defense!
3.	Avoid granting privileges on the tables themselves (where possible).
4.	Grant the minimum set of privileges required to the minimum number of people (use column level privileges where appropriate).
5.	Determine whether the dynamic or static SQL authorization model, or some hybrid, is best for you.
6.	Use views to implement read access control policies for both rows and columns.
7.	Use views with the WITH CHECK OPTION to implement write access control policies (where possible).
8.	Use row-level BEFORE triggers to enforce write access control policies in those situations where views are not suitable.

Other factors that you need to consider when implementing an access control policy include:

•	Document it! Do this not only on paper but in the SQL used to define views and triggers.
•	Keep it as clean and simple to read and maintain as possible.
•	Consider whether you need to supplement the DB2 Audit facilities with your own (that is, when implementing policy enforcement triggers).

For further information on the various options and methods available to you from DB2 in the area of authorizations, view definitions, or trigger definitions, see the DB2 UDB online reference material provided with the product, as well as the IBM Data Management Web site (www.ibm.com/software/data/db2). In particular, the SQL Reference Manual is highly recommended!

---

About the author

Paul Bird is a senior technical manager in the DB2 Universal Database development group at IBM’s Toronto Laboratory;
he has been a member of its DB2 team since 1992. He may
be reached via email at pbird@ca.ibm.com.