The following procedure is based on the PCI-DSS requirements shown in Sample Logging Requirements, but you can use them as a general guide to set up access logging for any sensitive data, by adjusting them to your site security policy.
- Run the DIPACC script to create the DBC.AccLogRule macro, which must exist before initiating access logging.
For information on running DIPACC, see the Database Initialization Program (DIP) in Utilities.
- Initiate logging on tables or views that contain sensitive data:
BEGIN LOGGING DENIALS ON EACH SELECT, INSERT, UPDATE, DELETE, STATISTICS, INDEX, REFERENCES, DUMP, RESTORE, SHOW, GRANT, CREATE TRIGGER, DROP TABLE, DROP TRIGGER ON Table "Tables_Database"."Table_Name" ;
Repeat this step for each table or view that contains sensitive data.
- Initiate logging of any action taken by all users with administrative privileges:
BEGIN LOGGING ON EACH ALL BY "DBADMIN","DBC","SECADMIN","DBADMIN " ,"admin1","admin2" ;
where SECADMIN, DBADMIN, admin1, and admin2 are the database usernames of administrators.
- Initiate logging of all invalid access attempts:
BEGIN LOGGING DENIALS ON EACH ALL ;
- Initiate logging of any query that creates or deletes a system level object, that is, in the space directly owned by user DBC:
BEGIN LOGGING ON EACH INDEX, REFERENCES, ALTER PROCEDURE, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, CREATE OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE MACRO, CREATE DATABASE, CREATE USER, CREATE TRIGGER, CREATE PROCEDURE, CREATE FUNCTION, CREATE EXTERNAL PROCEDURE, CREATE AUTHORIZATION, DROP TABLE, DROP VIEW, DROP MACRO, DROP DATABASE, DROP USER, DROP TRIGGER, DROP PROCEDURE, DROP FUNCTION, DROP AUTHORIZATION ON Database "DBC" ;
- Log the initialization of the audit logs:
BEGIN LOGGING WITH TEXT ON EACH ALL ON MACRO DBC.AccLogRule;
- Initiate logging on all attempts to access audit trails:
BEGIN LOGGING ON EACH ALL ON TABLE DBC.AccessLogTbl; BEGIN LOGGING ON EACH ALL ON VIEW DBC.AccessLog; BEGIN LOGGING ON EACH ALL ON VIEW DBC.DeleteAccessLog; BEGIN LOGGING ON EACH ALL ON TABLE DBC.EventLog; BEGIN LOGGING ON EACH ALL ON VIEW DBC.LogOnOff;