Use Oracle's audit function to monitor suspicious operations in the database

Let's take a look at what powerful effects Oracle's auditing functions (including FGA fine-grained auditing) can bring to us.
I will use this article to show you the audit functions of Oracle. Follow me.
1. To use audit, you must first activate the audit function.
1) view the default audit-related parameter settings in the system
Sys @ ora10g> conn/as sysdba
Connected.
Sys @ ora10g> show parameter audit
NAME TYPE VALUE
--------------------------------------------------------------------
Audit_file_dest string/oracle/app/oracle/admin/ora10g/adump
Audit_sys_operations boolean FALSE
Audit_syslog_level string
Audit_trail string NONE
2) Explain the parameters listed above
(1) AUDIT_FILE_DEST = path
Indicates the path where the audit file is stored. Here, "/oracle/app/oracle/admin/ora10g/adump" is displayed"
Whether the audit function is enabled or not, this directory item records every login information as sysdba. If you are interested, you can view the information in this directory.
For example:
$ Cat ora_9915.aud
Audit file/oracle/app/oracle/admin/ora10g/adump/ora_9915.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORACLE_HOME =/oracle/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: testdb183
Release: 2.6.18-128. el5
Version: #1 SMP Wed Dec 17 11:41:38 EST 2008
Machine: x86_64
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 9915, image: oracle @ testdb183 (TNS V1-V3)
Wed Aug 26 19:24:11 2009
ACTION: 'connect'
Database user :'/'
PRIVILEGE: SYSDBA
Client user: oracle
Client terminal: pts/1
STATUS: 0
(2) audit_sys_operations
The default value is FALSE. If the audit function is enabled, set this parameter to TRUE.
(3) audit_syslog_level
Statement: Specifies an audit statement or a specific type of statement group, such as create table, truncate table, comment on table, and DELETE [FROM] TABLE.
Permission: Use AUDIT statements to specify system permissions, such as audit create any trigger.
Object: Specify the audit statement on the specified object, such as alter table on the emp table
(4) AUDIT_TRAIL = NONE | DB | OS
DB -- Record audit information to the database
OS-audit information is recorded in the Operating System File
NONE -- disable audit (default)
3) set audit_sys_operations to "TRUE" to enable audit.
Sys @ ora10g> alter system set audit_sys_operations = TRUE scope = spfile;
System altered.
4) change the parameter audit_trail to "db" and record the audit information to the database.
Sys @ ora10g> alter system set audit_trail = db scope = spfile;
System altered.
5) Note: To make these parameters take effect, you must restart the database.
Sys @ ora10g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Sys @ ora10g> startup;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2078264 bytes
Variable Size 293603784 bytes
Database Buffers 771751936 bytes
Redo Buffers 6307840 bytes
Database mounted.
Database opened.
6) Verify the modification results of some parameters. The modification is completed.
Sys @ ora10g> show parameter audit;
NAME TYPE VALUE
-------------------------------------------------------------------
Audit_file_dest string/oracle/app/oracle/admin/ora10g/adump
Audit_sys_operations boolean TRUE
Audit_syslog_level string
Audit_trail string DB
2. after the audit function is enabled, there is an interesting effect, all operations under sysdba permissions are recorded in the/oracle/app/oracle/admin/ora10g/adump Audit directory. This is why there are some expenses and risks after the audit function is enabled.
1) if we execute the following three commands under the sysdba permission User:
Sys @ ora10g> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
Session altered.
Sys @ ora10g> select * From dual;
D
-
X
Sys @ ora10g> show parameter spfile
NAME TYPE VALUE
------------------------------------------------------------------------
Spfile string/oracle/app/oracle/product/10.2.0/db_1/dbs/spfileora10g. ora
2) using the tail command, you can see the following detailed records in the corresponding trace file. You can see what SQL statements are actually executed behind the "show parameter spfile" command.
Wed Aug 26 20:04:03 2009
ACTION: 'alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss''
Database user :'/'
PRIVILEGE: SYSDBA
Client user: oracle
Client terminal: pts/2
STATUS: 0
Wed Aug 26 20:04:03 2009
ACTION: 'In in DBMS_OUTPUT.GET_LINES (: LINES,: NUMLINES); END ;'
Database user :'/'
PRIVILEGE: SYSDBA
Client user: oracle
Client terminal: pts/2
STATUS: 0
Wed Aug 26 20:04:16 2009
ACTION: 'select * From dual'
Database user :'/'
PRIVILEGE: SYSDBA
Client user: oracle
Client terminal: pts/2
STATUS: 0
Wed Aug 26 20:04:16 2009
ACTION: 'In in DBMS_OUTPUT.GET_LINES (: LINES,: NUMLINES); END ;'
Database user :'/'
PRIVILEGE: SYSDBA
Client user: oracle
Client terminal: pts/2
STATUS: 0
Wed Aug 26 20:07:21 2009
ACTION: 'select NAME NAME_COL_PLUS_SHOW_PARAM, DECODE (TYPE, 1, 'boolean', 2, 'string', 3, 'integer', 4, 'file', 5, 'number', 6, 'Big integer ', 'unknown') TYPE, DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM from v $ parameter where upper (NAME) like upper ('% spfile % ') order by NAME_COL_PLUS_SHOW_PARAM, ROWNUM'
Database user :'/'
PRIVILEGE: SYSDBA
Client user: oracle
Client terminal: pts/2
STATUS: 0
3. Demonstrate the audit results of the delete operation on the t_audit table of the sec user
1) The t_audit information of the table is as follows:
Www.bkjia.com @ ora10g> select * from t_audit order by 1;
X
----------
1
2
3
4
5
6
6 rows selected.
2) Here, only audit of the delete operation on the table t_audit is enabled.
Sec @ ora10> audit delete on t_audit;
Audit succeeded.
3) You can query the dba_obj_audit_opts view to view audit settings.
Www.bkjia.com @ ora10g> select OWNER, OBJECT_NAME, OBJECT_TYPE, DEL, INS, SEL, UPD from dba_obj_audit_opts;
OWNER OBJECT_NAME OBJECT_TYPE DEL INS SEL UPD
------------------------------------------------------------------
SEC T_AUDIT table s/S -/--/--/-
4) Try to insert data
Www.bkjia.com @ ora10g> insert into t_audit values (7 );
1 row created.
5) because the insert statement is not audited, no audit information can be obtained.
Www.bkjia.com @ ora10g> select count (*) from dba_audit_trail;
COUNT (*)
----------
0
6) Try the delete operation again.
Www.bkjia.com @ ora10g> delete from t_audit where x = 1;
1 row deleted.
7) as expected, the delete operation was captured by the database.
You can query the dba_audit_trail view or sys. the aud $ view obtains detailed audit information. This audit method can obtain rough information such as the operation time and operation user (compared with the fine-grained audit described later)
Www.bkjia.com @ ora10g> select count (*) from dba_audit_trail;
COUNT (*)
----------
1
Select * from dba_audit_trail;
Select * from sys. aud $;
4. to cancel all audit of table t_audit, you must manually complete the audit.
Sec @ ora10> noaudit all on t_audit;
Noaudit succeeded.
Query the dba_obj_audit_opts view to confirm that the audit has been canceled.
Www.bkjia.com @ ora10g> select * from dba_obj_audit_opts;
No rows selected

1 2 Next Page