Home | Previous Page | Next Page   Managing Databases > Granting and Limiting Access to Your Database > Using SPL Routines to Control Access to Data >

Monitoring Changes to Data

When you use SPL routines, you can create a record of changes made to a database. You can record changes that a particular user makes, or you can make a record each time a change is made.

You can monitor all the changes a single user makes to the database. Channel all changes through SPL routines that keep track of changes that each user makes. If you want to record each time the user acctclrk modifies the database, set up the database with the following privileges:

Write an SPL routine similar to the following example, which uses a customer number the user provides to update a table. If the user happens to be acctclrk, a record of the deletion is put in the file updates.

UNIX Only
CREATE DBA PROCEDURE delete_customer(cnum INT)

DEFINE username CHAR(8);

DELETE FROM customer 
   WHERE customer_num = cnum;

IF username = 'acctclrk' THEN
   SYSTEM 'echo Delete from customer by acctclrk >>
/mis/records/updates' ;
END IF
END PROCEDURE; 

To monitor all the deletions made through the procedure, remove the IF statement and make the SYSTEM statement more general. The following procedure changes the previous routine to record all deletions:

CREATE DBA PROCEDURE delete_customer(cnum INT)

DEFINE username CHAR(8);
LET username = USER ;
DELETE FROM tbname WHERE customer_num = cnum;

SYSTEM
 'echo Deletion made from customer table, by '||username
||'>>/hr/records/deletes';

END PROCEDURE; 
End of UNIX Only
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]