Oracle Security HIPPA Archival Model Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

A Complete System

Next, we will devise a system that will provide all the features we want, at a cost that can be justified. This procedure will also create a HIPAA compliant archival model for the aud$ table.

Requirements

* The auditing data needs to be archived and purged off every month. This frequency is based on the load on the system and one month seems to be optimal.

* Ideally, three months worth of audit data should be available. Therefore, every month, the oldest month should be archived and purged. For instance, on July 31st, the data for April should be archived and purged off. Data for May, June and July should be in aud$ table.

* There will be a small period of inactivity.

* The redo and undo generation should be minimal.

* The archived data should be available for query on request. A small amount of time can be allowed between the request and the expectation for delivery of the data.

* Existing reports based on the data dictionary views such as dba_audit_session are already built. The report should not have to be modified to query the archived data.

Design

These requirements seem reasonable for a security and accountability conscious database implementation. Next, we will see how to set this up.

1. First we will decide on a naming convention for the tablespaces and tables. Since the purge and archival routine has to run every month, we will name tables with the month and year, e.g. AUD<yy><mm>, where <yy> is the two-digit year and <mm> is the two-digit month when the data is being archived. The routine run at the end of September 2003 will archive the data from June 2003 and therefore create a table named AUD0306 in tablespace AUD0306.

2. Every month we create a new tablespace, which should be placed in a low throughput and low cost filesystem, say /u200.

3. Next, we will create a table to hold the data from the table aud$ for the entire month 3 months ago, e.g. of June, if this is run in September. The table is created in NOLOGGING mode to regenerate minimal redo and undo. In this example this table is named AUD0306.

4. Then the data in the archived table should be deleted from the main aud$ table. A simple DELETE AUD$ will do; but there are two problems:

* A regular delete generates a lot of redo and undo activity.

* A regular delete also does not reset the high water mark, so the table is fragmented.

Therefore, as an alternative, we will create a separate temporary table named aud_temp on the same tablespace as the aud$ table. This table holds the records that are not present in the archived table AUD0306.

5. After this table is created, the original aud$ table is dropped.

6. The table aud_temp is then renamed to the main table aud$. The index on it, I_AUD1, is rebuilt.

7. The tablespace AUD0306 is converted to READ ONLY.

8. This tablespace is now transported to a separate medium such as a WORM device, which could be as simple as a Writable CD-ROM. The datafiles of the tablespace and the small export dump file are written to the WORM device.

9. The tablespace is then dropped.

These steps have been implemented in the code below. The code has been deliberatively broken into three parts since only the successful execution of the prior part should pave the way for the next part. If an error occurs during any part, it should be stopped.

* archive_audit_1.sh

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc

--
-- *********************************************

# -------------------------------------------------------
# Filename    : archive_audit_1.sh
# Description : First part of the AUD$ archival and purge
#               process.
# Parameters  : 1. 2-digit Year of the archival process
#               2. 2-digit Month of the process
# Important:
#    Change the variables AUDIT_TS_NAME and
#    ARC_FILE_SYSTEM, SYS_PASSWORD to suit to your site.
# -------------------------------------------------------
#
# Site dependent variables
AUDIT_TS_NAME=SYSTEM
ARC_FILE_SYSTEM=/u201
SYS_PASSWORD=manager
#
ARC_YEAR=$1
ARC_MONTH=$2
ARC_TS_NAME=AUD$ARC_YEAR$ARC_MONTH
ARC_TABLE_NAME=AUD$ARC_YEAR$ARC_MONTH
#
sqlplus /nolog << EOF1
whenever sqlerror exit
connect sys/$SYS_PASSWORD as sysdba
create tablespace $ARC_TS_NAME
datafile '$ARC_FILE_SYSTEM/${ARC_TS_NAME}_01.dbf'
size 10M
autoextend on next 10M
extent management local
uniform size 256K
/
REM
prompt AudArch: Created Tablespace $ARC_TS_NAME
create table $ARC_TABLE_NAME
tablespace $ARC_TS_NAME
nologging
as
select * from AUD$
where timestamp# between
      to_date('$ARC_YEAR$ARC_MONTH’,’RRMM’)
and
add_months(
    to_date('$ARC_YEAR$ARC_MONTH',’RRMM'),1)
/
prompt AudArch: Created Table $ARC_TABLE_NAME
create table AUD_TEMP
tablespace $AUDIT_TS_NAME
nologging
as
select * from AUD$
where timestamp# >
  add_months(
     to_date('$ARC_YEAR$ARC_MONTH',’RRMM'),1)
/
prompt AudArch: Created Temporary AUD$ table
EOF1
#
Echo Only if all the steps were successful,
Echo execute the second part archive_audit_2.sh
#
# End of Script

After completion of this part examine the log file and see if all the steps were successfully executed. Only after you have verified that the first part executed successfully should you move on to the next part, archive_audit_2.sh.

*
archive_audit_2.sh

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

# -------------------------------------------------------
# Filename    : archive_audit_2.sh
# Description : Second part of the AUD$ archival and
#               purge process.
# Parameters  : 1. 2-digit Year of the archival process
#               2. 2-digit Month of the process
# Assumption  : A file .sys_passwd in this directory
#               containing the password of SYS.
# Important:
#    Change the variables AUDIT_TS_NAME, AUDIT_INDEX_TS
#    and ARC_FILE_SYSTEM, SYS_PASSWORD to suit to
#    your site.
# -------------------------------------------------------
#
# Site dependent variables
AUDIT_TS_NAME=SYSTEM
AUDIT_INDEX_TS=SYSTEM
ARC_FILE_SYSTEM=/u200
SYS_PASSWORD=manager
#
ARC_YEAR=$1
ARC_MONTH=$2
ARC_TS_NAME=AUD$ARC_YEAR$ARC_MONTH
ARC_TABLE_NAME=AUD$ARC_YEAR$ARC_MONTH
#
sqlplus /nolog << EOF1
whenever sqlerror exit
connect sys/$SYS_PASSWORD as sysdba
drop table AUD$
/
prompt AudArch: Dropped Table AUD$
REM
rename AUD_TEMP to AUD$
/
prompt AudArch: Renamed Table AUD_TEMP to AUD$
REM
create index i_aud1
on aud$ (sessionid, ses$tid)
tablespace $AUDIT_INDEX_TS
nologging
/
prompt AudArch: Created Index I_AUD1
REM
alter tablespace $ARC_TS_NAME readonly
/
prompt AudArch: Tablespace $ARC_TS_NAME made Read Only
EOF1
#
# Export the tablespace as transportable
# Note the password of SYS is passed from a file. This
# eliminates the chance of the password being visible
# by a ps –aef command
#
exp transport_tablespace=Y tablespaces=\($ARC_TS_NAME\)
file=$ARC_FILE_SYSTEM/tts_${ARC_TS_NAME}.dmp < .sys_passwd
#
echo AudArch: Exported Tablesapce as Transportable
echo Files $ARC_FILE_SYSTEM/tts_${ARC_TS_NAME}.dmp and
$ARC_FILE_SYSTEM/${ARCH_TS_NAME}_01.dbf should be put together and form a set.
echo Only after this part is over successfully, run the
echo Part 3 of the process.
#
# End of Script

At this stage the tablespace can be dropped.

* archive_audit_3.sh

--**********************************************
--
--   Copyright © 2003 by Rampant TechPress Inc.
--
--   Free for non-commercial use.
--   For commercial licensing, e-mail info@rampant.cc
--
-- *********************************************

# -------------------------------------------------------
# Filename    : archive_audit_3.sh
# Description : Third part of the AUD$ archival and
#               purge process.
# Parameters  : 1. 2-digit Year of the archival process
#               2. 2-digit Month of the process
# Assumption  : A file .sys_passwd in this directory
#               containing the password of SYS.
# Important:
#    Change the variable SYS_PASSWORD to suit to
#    your site.
# -------------------------------------------------------
#
# Site dependent variables
SYS_PASSWORD=manager
#
ARC_YEAR=$1
ARC_MONTH=$2
ARC_TS_NAME=AUD$ARC_YEAR$ARC_MONTH
sqlplus /nolog << EOF1
whenever sqlerror exit
connect sys/$SYS_PASSWD as sysdba
drop tablespace $ARC_TS_NAME
including contents
and datafiles /* Only in 9i */
/
prompt AudArch: Dropped Tablespace $ARC_TS_NAME
EOF1
echo Archival Complete

Retrieval

When the archived data needs to be retrieved, this process makes it very easy. Since the data was initially exported as transportable, all that needs to be done while importing is to mount the WORM device (or the filesystem containing the archived tablespace) and plug the tablespace into the database using import. Since the tablespace has been named after the year and the month, they will be always unique.

After the tablespace is imported, the table AUD0309, say, will be available. A view named aud$_combined may be created using:

create view aud$combined
as
select * from aud$
union all
select * from aud0309
/

A public synonym aud$ can then be created for this new view:

create public synonym aud$ for sys.aud$_combined
/

Now all the reports developed against the data dictionary views will pick up data from this table as well.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.