Auditing using SQL table functions

Database administrators interested in monitoring table data accesses and table data modifications made by database users can audit transactions on a table by creating and using SQL table functions that modify SQL data.

Before you begin

Any table function that encapsulates SQL statements that perform a business task, such as updating an employee's personal information, can additionally include SQL statements that record, in a separate table, details about the table accesses or modifications made by the user that invoked the function. An SQL table function can even be written so that it returns a result set of table rows that were accessed or modified in the body of the table function. The returned result set of rows can be inserted into and stored in a separate table as a history of the changes made to the table.

For the list of privileges required to create and register an SQL table function, see the following statements:
  • CREATE FUNCTION (SQL Scalar, Table, or Row) statement
The definer of the SQL table function must also have authority to run the SQL statements encapsulated in the SQL table function body. Refer to the list of privileges required for each encapsulated SQL statement. To grant INSERT, UPDATE, DELETE privileges on a table to a user, see the following statement:
  • GRANT (Table, View, or Nickname Privileges) statement
The tables accessed by the SQL table function must exist prior to invocation of the SQL table function.

Example

Example 1: Auditing accesses of table data using an SQL table function

This function accesses the salary data of all employees in a department specified by input argument deptno. It also records in an audit table, named audit_table, the user ID that invoked the function, the name of the table that was read from, a description of what information was accessed, and the current time. Note that the table function is created with the keywords MODIFIES SQL DATA because it contains an INSERT statement that modifies SQL data.

  CREATE FUNCTION sal_by_dept (deptno CHAR(3))
    RETURNS TABLE (lastname VARCHAR(10),
                   firstname VARCHAR(10),
                   salary INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    NO EXTERNAL ACTION
    NOT DETERMINISTIC
    BEGIN ATOMIC
      INSERT INTO audit_table(user, table, action, time)
        VALUES (USER, 
                'EMPLOYEE',
                'Read employee salaries in department: ' || deptno,
                CURRENT_TIMESTAMP);
      RETURN
        SELECT lastname, firstname, salary
          FROM employee as E
            WHERE E.dept = deptno;
    END
Example 2: Auditing updates to table data using an SQL table function

This function updates the salary of an employee specified by updEmpNum, by the amount specified by amount, and also records in an audit table named audit_table, the user that invoked the routine, the name of the table that was modified, and the type of modification made by the user. A SELECT statement that references a data change statement (here an UPDATE statement) in the FROM clause is used to return the updated row values. Note that the table function is created with the keywords MODIFIES SQL DATA because it contains both an INSERT statement and a SELECT statement that references the data change statement, UPDATE.

 CREATE FUNCTION update_salary(updEmpNum CHAR(4), amount INTEGER)
   RETURNS TABLE (emp_lastname VARCHAR(10),
                  emp_firstname VARCHAR(10),
                  newSalary INTEGER)
   LANGUAGE SQL
   MODIFIES SQL DATA
   NO EXTERNAL ACTION
   NOT DETERMINISTIC
   BEGIN ATOMIC
     INSERT INTO audit_table(user, table, action, time)
     VALUES (USER, 
             'EMPLOYEE',
             'Update emp salary. Values: ' 
                || updEmpNum || ' ' || char(amount),
             CURRENT_TIMESTAMP);
     RETURN
       SELECT lastname, firstname, salary
         FROM FINAL TABLE(UPDATE employee 
                          SET salary = salary + amount
                          WHERE employee.empnum = updEmpNum);
   END
Example 3: Invoking an SQL table function used for auditing transactions

The following shows how a user might invoke the routine to update an employee's salary by 500 yen:

SELECT emp_lastname, emp_firstname, newsalary
  FROM TABLE(update_salary(CHAR('1136'), 500)) AS T

A result set is returned with the last name, first name, and new salary for the employee. The invoker of the function will not know that the audit record was made.

EMP_LASTNAME EMP_FIRSTNAME NEWSALARY 
------------ ------------- -----------
JONES        GWYNETH             90500

The audit table would include a new record such as the following:

USER     TABLE      ACTION                               TIME                      
-------- ---------- -----------------------------------  --------------------------
MBROOKS  EMPLOYEE   Update emp salary. Values: 1136 500  2003-07-24-21.01.38.459255
Example 4: Retrieving rows modified within the body of an SQL table function

This function updates the salary of an employee, specified by an employee number EMPNUM, by an amount specified by amount, and returns the original values of the modified row or rows to the caller. This example makes use of a SELECT statement that references a data change statement in the FROM clause. Specifying OLD TABLE within the FROM clause of this statement flags the return of the original row data from the table employee that was the target of the UPDATE statement. Using FINAL TABLE, instead of OLD TABLE, would flag the return of the row values subsequent to the update of table employee.

CREATE FUNCTION update_salary (updEmpNum CHAR(4), amount DOUBLE)
  RETURNS TABLE (empnum CHAR(4),
               emp_lastname  VARCHAR(10),
               emp_firstname VARCHAR(10),
               dept CHAR(4),   
               newsalary integer)
  LANGUAGE SQL
  MODIFIES SQL DATA
  NO EXTERNAL ACTION
  DETERMINISTIC
  BEGIN ATOMIC  
    RETURN
      SELECT empnum, lastname, firstname, dept, salary
        FROM OLD TABLE(UPDATE employee
                              SET salary = salary + amount
                                WHERE employee.empnum = updEmpNum);
  END