What should I audit?
Submitted by Spokey on Thu, 2008-07-31 18:23.
Under: Ask Spokey
--- Cut here ---
At those customers who are subject to regulatory compliance of some form or another, I always get asked about which operations need to be audited. There is no short answer to this, but courtesy of Jonathan Leffler, I have a script that a) does what Jonathan and I both agree is a bare minimum and b) is very easily modified with your specific requirements. It is posted below and I have also tried to attach it (which is a first for me!)
--- Cut here ---
#!/bin/ksh
#
# @(#)$Id: mkaudit.sh,v 1.2 2008/05/09 16:45:57 jleffler Exp $
#
# Create standardized Informix Audit Mnemonic Groupings
# _audit: Audit operations
# _crud: Insert, Delete, Update, Select (CRUD = Create, Read, Update, Delete)
# _dba: DBA operations (create/drop database)
# _dbsa: Database System Administration operations
# _ddl: Basic DDL operations
# _dml: Basic DML operations
# _domain: Domain operations (non-operational)
# _ius: IDS extended operations (IUS)
# _lbac: LBAC operations
# _onutils: OnLine utilities
# _optical: Optical operations
# _perms: Permissions (not LBAC)
list_opcodes()
{
sed -e 's/[ ][ ]*[-:]\([a-z][a-z]*\)[-:][ ][ ]*/ \1 /g' -e '/^[ ]*$/d' <<EOF |
BGTX :ddl: BEGIN WORK
CMTX :ddl: COMMIT WORK
RLTX :ddl: ROLLBACK WORK
ACTB :ddl: Access Table
CLDB :ddl: CLOSE DATABASE
OPDB :ddl: DATABASE
ULTB :ddl: Unlock Table
LKTB :ddl: Lock Table
STSN :ddl: Start Session
ADCK :dbsa: Add chunk
ADLG :dbsa: Add log
ALFR :ddl: ALTER FRAGMENT
ALIX :ddl: ALTER INDEX
ALLC :lbac: ALTER Security Label Component
ALME :ius: Alter Access Method
ALOC :ius: ALTER Operator Class
ALSQ :ddl: ALTER SEQUENCE
ALTB :ddl: ALTER TABLE
CRAM :audit: Create Audit Mask
DRAM :audit: Drop audit mask
UPAM :audit: Update Audit Mask
CRDB :dba: CREATE DATABASE
DRDB :dba: DROP DATABASE
CRAG :ius: CREATE AGGREGATE
CRBS :dbsa: CREATE storage space
CRBT :ius: CREATE opaque (binary) type
CRCT :ius: CREATE CAST
CRDS :dbsa: CREATE dbspace
CRDT :ius: CREATE DISTINCT TYPE
CRIX :ddl: CREATE INDEX
CRLB :lbac: CREATE LABEL
CRLC :lbac: CREATE LABEL COMPONENT
CRME :ius: CREATE Access Method
CROC :ius: CREATE Operator Class
CRPL :lbac: CREATE POLICY
CRPT :dml: Encryption or Decryption function
CRRL :perms: CREATE ROLE
CRRT :ius: CREATE named ROW TYPE
CRSN :ddl: CREATE SYNONYM
CRSP :ddl: CREATE STORED PROCEDURE
CRSQ :ddl: CREATE SEQUENCE
CRTB :ddl: CREATE TABLE
CRTR :ddl: CREATE TRIGGER
CRVW :ddl: CREATE VIEW
CRXD :ius: CREATE XA Data Source
CRXT :ius: CREATE XA Data Source Type
DLRW :crud: DELETE ROW
DNCK :dbsa: Down Chunk - offline
DNDM :dbsa: Disable disk mirroring
DRAG :ius: DROP AGGREGATE
DRBS :dbsa: DROP storage space
DRCK :dbsa: Drop chunk
DRCT :ius: DROP CAST
DRDS :dbsa: DROP dbspace
DRIX :ddl: DROP INDEX
DRLB :lbac: DROP LABEL
DRLC :lbac: DROP LABEL COMPONENT
DRLG :dbsa: Drop transaction log (why no create?)
DRME :ius: DROP Access Method
DROC :ius: DROP Operator Class
DRPL :lbac: DROP POLICY
DRRL :perms: DROP ROLE
DRRT :ius: DROP ROW TYPE
DRSN :ddl: DROP SYNONYM
DRSP :ddl: DROP STORED PROCEDURE
DRSQ :ddl: DROP SEQUENCE
DRTB :ddl: DROP TABLE
DRTR :ddl: DROP TRIGGER
DRTY :ius: DROP TYPE
DRVW :ddl: DROP VIEW
DRXD :ius: DROP XA Data Source
DRXT :ius: DROP XA Data Source Type
EXSP :dml: EXECUTE PROCEDURE
GRDB :perms: GRANT DB privilege
GRDR :perms: GRANT DEFAULT ROLE
GRFR :perms: GRANT FRAGMENT
GRLB :lbac: GRANT SECURITY LABEL
GRRL :perms: GRANT ROLE
GRSA :lbac: GRANT DBSECADM
GRSS :lbac: GRANT SETSESSIONAUTH
GRTB :perms: GRANT table permissions
GRXM :lbac: GRANT EXEMPTION
INRW :crud: INSERT ROW
LGDB :dbsa: Change database log mode
LSAM :audit: List Audit Mask
LSDB :ddl: List databases
MDLG :dbsa: Modify transaction logging
ONAU :onutils: ON-Audit
ONBR :onutils: ON-BAR
ONCH :onutils: ON-Check
ONIN :onutils: ON-Init
ONLG :onutils: ON-Log
ONLO :onutils: ON-Load
ONMN :onutils: ON-Monitor
ONMO :onutils: ON-Mode
ONPA :onutils: ON-Params
ONPL :onutils: ON-Pload
ONSP :onutils: ON-Spaces
ONST :onutils: ON-Stat
ONTP :onutils: ON-Tape
ONUL :onutils: ON-Unload
RDRW :crud: READ ROW
RLOP :optical: Release optical cluster
RMCK :dbsa: Clear mirrored chunks
RNDB :dba: Rename database
RNDS :dbsa: Rename dbspace
RNIX :ddl: Rename index
RNLB :lbac: Rename label
RNLC :lbac: Rename label component
RNPL :lbac: Rename policy
RNSQ :ddl: Rename sequence
RNTC :ddl: Rename table/column
RSOP :optical: Reserve optical cluster
RVDB :perms: Revoke Database Privileges
RVDR :perms: Revoke Default Role
RVFR :perms: Revoke Fragment
RVLB :lbac: Revoke Label
RVRL :perms: Revoke Role
RVSA :lbac: Revoke DBSECADM
RVSS :lbac: Revoke SETSESSIONAUTH
RVTB :perms: Revoke table privileges
RVXM :lbac: Revoke exemption
SCSP :dml: SYSTEM command in Stored Procedure
STCN :ddl: SET CONSTRAINT
STCO :dml: SET COLLATION
STDF :dml: SET DEBUG FILE
STDP :perms: SET DATABASE PASSWORD
STDS :dml: SET DATASKIP
STEP :dml: SET ENCRYPTION PASSWORD
STEV :dml: SET ENVIRONMENT
STEX :dml: SET EXPLAIN
STIL :dml: SET ISOLATION
STLM :dml: SET LOCK MODE
STNC :dml: SET NO COLLATION
STOM :ddl: SET object mode
STOP :ddl: STOP VIOLATIONS
STPR :dml: SET PDQPRIORITY
STRL :perms: SET ROLE
STRS :dba: SET RESIDENT
STRT :ddl: START VIOLATIONS
STSA :perms: SET SESSION AUTHORIZATION
STSC :dbsa: SET STATEMENT CACHE
STTX :dml: SET TRANSACTION
SVXD :ddl: SAVE EXTERNAL DIRECTIVES
TCTB :ddl: Truncate table
ALOP :optical: ALTER Optical Cluster
CROP :optical: CREATE OPTICAL CLUSTER
DROP :optical: DROP OPTICAL CLUSTER
TMOP :optical: Optical timeout
UPCK :dbsa: UP Chunk
UPDM :dbsa: Enable Disk Mirroring
UPRW :crud: UPDATE row
USSP :ddl: UPDATE STATISTICS - Stored procedure
USTB :ddl: UPDATE STATISTICS - Table
DRDM :domain: DROP DOMAIN
CRDM :domain: CREATE domain
EOF
sort +1 -2 +0 -1
}
groups=$(list_opcodes | awk '{print $2}' | uniq)
for group in $groups
do
opcodes=$(list_opcodes | grep " $group " | awk '{print $1 "," }')
opcodes=$(echo $opcodes | sed -e 's/ //g' -e 's/,$//')
echo onaudit -a -u _$group -e +$opcodes
done--- Cut here ---
| Attachment | Size |
|---|---|
| mkaudit.ksh_.txt | 6.26 KB |
Possible reclassifications
Submitted by Jonathan Leffler on Thu, 2008-07-31 19:15.
As ever, the classifications in the script can be debated. The ones that immediately spring to mind are BEGIN WORK, COMMIT WORK, and ROLLBACK WORK, which are 'ddl' in the script I sent to Spokey, but could perhaps be better treated as 'dml'. There could be endless discussions - feel free to reclassify to suit your needs. Do notify us of any major mistakes.
Thanks.
Jonathan Leffler
