- ERP System: Oracle
- MarkView Version: All
- Oracle Database 10g gives you the option of sending audit records to the database audit trail or your operating system's audit trail, when the operating system is capable of receiving them.
- The audit trail for database administrators, for example, is typically written to a secure location in the operating system.
- Writing audit trails to the operating system provides a way for a separate auditor who is root on the operating system to hold all DBAs (who don't have root access) accountable for their actions.
- These options, added to the broad selection of audit options and customizable triggers or stored procedures, give you the flexibility to implement an auditing scheme that suits your specific business needs.
Steps to Enable Database Auditing
- Confirm if auditing has been enabled in database by executing the following command. This command should be executed as sysdba. Please see note below how to connect as sysdba.
SHOW PARAMETER AUDIT
- The command should provide results similar to the following output. If audit_trail is set to NONE this indicates auditing has not been enabled.
SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
-------------------------- ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
- Stop Oracle Apps: as appmgr01- execute:
- Stop the MarkView OC4J modules: as oraias01 - execute:
- Modify the initvis01.ora file. This file will be located under the path of /appl01/vis01db/10.2.0/dbs/
- Uncomment the audit_trail parameter in the file and set it to a value of db and then save the file.
- Stop the database as the sysdba user by executing the following command.
- Start the database base as the sysdba user by executing the following command.
- Confirm if the auditing parameter has been enabled by executing the following command. If auditing has been enabled audit_trail will be set to db.
SQL> SHOW PARAMETER AUDIT
- Create audit policy of table/ procedure. As sysdba execute the following command.
This audit policy will be created for the table name AP_MC_INVOICE_DISTS
--Create audit policy
object_schema => 'AP',
object_name => 'AP_MC_INVOICE_DISTS',
policy_name => 'AP_MC_INVOICE_DISTS_AUDIT',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
- Start Apps by executing the following command as appmgr01
- Start the MarkView OC4J modules as oraias01.
- To view the audit history of the transactions performed against the object, please execute the following SQL statement as sysdba.
SQL > select * from dba_fga_audit_trail;
- Connect as sysdba
$ sysdba.sh sh
- The following article describes auditing in 10gR2 in further detail:
Author: Shawn Abedi
Created On: 7/31/2019
Based on original CRM article 4772