IT-eye

There are several known methods to get DBA or other potential disastrous system rights due to bugs in the database. Keeping a rigid patching regime and restricting the access rights to packages (Remove those execute to publics! ) goes a long way to secure your Oracle database against these problems. Even with a good security setup misuse is always a possibility either from a new bug not yet known to yourself or a disgruntled employee. Auditing can help you catch these unknowns.

Oracle supplies several auditing mechanisms to fulfill your needs. For this entry I am interested in catching rogue GRANT DBA TO USER statements. These could be done by the appropriate users, system & sys, or via bugs in database packages such as dbms_metadata.

To catch a GRANT DBA or other potential disastrous system grant Oracle has the AUDIT statement. To enable the use of the audit statement the system parameter AUDIT_TRAIL must be set to the value DB,OS or XM(10G + ) . An AUDIT SYSTEM GRANT will then audit all system grants done in the database with the BY USER a specific user can be defined.

I am looking for potential misuse of database packages most of which are run by SYS. The AUDIT statement will, by default, not audit anything done by SYS. To enable SYS auditing the system parameter AUDIT_SYS_OPERATIONS must be TRUE and the AUDIT_TRAIL parameter must contain OS or XML. Being forced to write your entire audit trail to the OS is a good thing. If you where able to write to the DB the SYS user could just erase the SYSTEM.AUD$ table containing all the auditing records.

Having set all the above mentioned parameters doing a GRANT DBA TO EMIEL as sys will give me the following XML record.

<AuditRecord>
  <Audit_Type>4</Audit_Type>
  <Session_Id>4294967295</Session_Id>
  <StatementId>28</StatementId>
  <EntryId>27</EntryId>
  <Extended_Timestamp>2006-02-27T15:50:15.024000</Extended_Timestamp>
  <DB_User>/</DB_User>
  <Ext_Name>ITEYE\ebolwidt</Ext_Name>
  <OS_User>ITEYE\ebolwidt</OS_User>
  <Userhost>ITEYE\LAPTOP_xxx</Userhost>
  <OS_Process>3468:4036</OS_Process>
  <Instance_Number>0</Instance_Number>
  <Returncode>0</Returncode>
  <OS_Privilege>SYSDBA</OS_Privilege>
  <Sql_Text>grant dba to emiel</Sql_Text>
</AuditRecord>

This record will be located in a file per connection in the directory identified by the AUDIT_FILE_DEST parameter. To prevent a disgruntled employee, including the DBA’s, from erasing the audit records from the OS it’s a good idea to restrict access to this a directory to the security employee.

If the AUDIT_TRAIL is set to DB all statements can be found in the DBA_AUDIT_STATEMENT view. Remember that this has the potential misue of a deletion from the SYSTEM.AUD$ table to conceal actions.

Having setup the auditing of all system grants in the database it is imperative that a dedicated person is also looking at the generated audit trails! This person should not be a DBA to the system that is audited.