Oracle 11G Database Audit Monitoring Setting Guide

Oracle 11G enables audit monitoring database

1 Create a dedicated table space for audit data

Since audit data may occupy a lot of space, it is obviously unreasonable to put the default system table space, so we should create a special table space and users to save the audit data, which is a more reasonable plan.

CREATE TABLESPACE AUDIT_TBS
  DATAFILE 
   '/data2/app/oracle/datafile/AUDIT_TBS1'SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED，
	'/data2/app/oracle/datafile/AUDIT_TBS2'SIZE 2048M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
  SEGMENT SPACE MANAGEMENT AUTO;

2 View related information

select table_name,tablespace_name from dba_tables where table_name='AUD$';

1	AUD$	SYSTEM

select COLUMN_NAME,SEGMENT_NAME,tablespace_name from dba_lobs where table_name ='AUD$';

1	SQLBIND	SYS_LOB0000000407C00040$$	SYSTEM
2	SQLTEXT	SYS_LOB0000000407C00041$$	SYSTEM

select index_name,tablespace_name from dba_indexes where table_name ='AUD$';

1	SYS_IL0000000407C00040$$	SYSTEM
2	SYS_IL0000000407C00041$$	SYSTEM

3 Move online to new table space

3.1 Clear historical audit information

Since the current database may already contain past audit information, the migration process is relatively slow. In order to solve this problem, you can first clear the existing audit information (of course, if the business allows it). The clear statement is as follows:

sqlplus / as sysdba
truncate table $;

3.2 Migrate tablespaces

sqlplus / as sysdba

alter table aud$ move tablespace AUDIT_TBS;
alter table AUD$ move lob(SQLBIND) store as SYS_LOB0000000407C00040$$ (tablespace AUDIT_TBS);
alter table AUD$ move lob(SQLTEXT) store as SYS_LOB0000000407C00041$$(tablespace AUDIT_TBS);
alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000407C00040$$ (tablespace AUDIT_TBS);
alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000407C00041$$(tablespace AUDIT_TBS);

Note: The file name similar to SYS_LOB000000407C00040$$ above is obtained by querying step 2.

3.3 Start auditing

sqlplus / as sysdb
alter system set audit_sys_operations=true scope=spfile;
alter system set audit_trail=db,extended scope=spfile;
shutdown immediate
startup

4 Some related maintenance of audit functions

All the following statements are executed under the SYS user

sqlplus / as sysdba

4.1 Audit data migration (another solution)

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS'
  );
END;
/

4.2 Automatic cleaning of audit data

Since the audit log may have a lot of data, it is necessary to clean the data regularly to ensure the database-related performance

begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval    => 24 );
end;
/

This INIT_CLEANUP process is designed to have two parameters:

• audit_trail_type: refers to the cleaning type to be set. Here is audit_trail_db_std, the standard database audit trail, that is, the aud$ table.
• default_cleanup_interval: This value indicates how many times the cleaning task is performed, 24, that is, 24 hours.

Related audit types:

• audit_trail_aud_std：The standard AUD$ audit trail in the database
• audit_trail_fga_std：The FGA_LOG$ table, for Fine Grained Auditing
• audit_trail_db_std：Both standard and FGA audit trails
• audit_trail_os：The OS audit trail
• audit_trail_xml：The XML audit trail
• audit_trail_files：Both OS and XML audit trails
• audit_trail_all：All of the above

After setting the initial cleaning parameters, when calling the cleaning task, you will find that it has not met your expectations, and no data may be cleaned up. We also need to set another process parameter SET_LAST_ARCHIVE_TIMESTAMP, which is used to tell the cleaning process to an audit archive timestamp.

This process receives three parameters:

• audit_trail_type: The audit type to clean, such as audit_trail_aud_std
• last_archive_time: The last archive time can be set manually
• rac_instance_number: In RAC, you can specify the instance number

The code is as follows

begin
   dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type  => dbms_audit_mgmt.audit_trail_aud_std,
     last_archive_time =>
        to_timestamp('2020-05-26 10:00:00','YYYY-MM-DD HH24:MI:SS'),
     rac_instance_number  => null
   );

end;

/

After executing the previous program, you can find relevant information in the DBA_AUDIT_MGMT_LAST_ARCH_TS view.

Once you have archive time, you can execute the real cleanup program.

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        =>  dbms_audit_mgmt.audit_trail_aud_std,
   use_last_arch_timestamp => TRUE
  );
end;
/

• audit_trail_type represents the audit type for cleaning.
• use_last_arch_timestamp => TRUE means the last archive time. If false, all audit information of audit_trail_aud_std type will be cleaned.

The above is a manual cleaning method. After setting the cleaning time point, the cleaning process will be run. Next, see how to do automatic cleaning:

The database automatic task will be used to implement it. The first task will automatically generate a cleanup point:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
                   DBMS_AUDIT_MGMT.audit_trail_all,LAST_ARCHIVE_TIME => sysdate-1); END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
    enabled    =>  TRUE,
    comments   => 'Create an archive timestamp'
  );
END;

/

The second task is automatically cleaned according to the time point:

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.audit_trail_all,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
    AUDIT_TRAIL_PURGE_NAME     => 'DAILY_AUDIT_PURGE_JOB',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/

Delete DAILY_AUDIT_PURGE_JOB and DAILY_AUDIT_ARCHIVE_TIMESTAMP

exec dbms_scheduler.drop_job( job_name =>  'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP' , force => TRUE);
exec SYS.DBMS_AUDIT_MGMT.DROP_PURGE_JOB( AUDIT_TRAIL_PURGE_NAME => 'DAILY_AUDIT_PURGE_JOB');

5 Assign audit permissions to other users

sqlplus / as sysdba
grant select on $ to dataaly;           -----Give new users a query audit information
grant select on sys.dba_fga_audit_trail to dataaly; ----Give new users a query audit information

6 Query all audit types that can be set

select user_name,audit_option,success,failure from dba_stmt_audit_opts
union
select USER_NAME,privilege,success,failure from dba_priv_audit_opts;

7 The following table summarizes the different types of audits in Oracle databases.

The following sections describe how DBA manages auditing of system and object permissions. When a certain granularity is required, DBAs can use fine-grained audits to monitor access to certain rows or columns in a table, not just whether or not the table is accessed.

Main parameters related to audit

SQL>show parameter audit

audit_file_dest
audit_sys_operations
audit_trail

audit_sys_operations：

• Default is false. When set to true, all operations of sys users (including users logged in as sysdba, sysoper) will be recorded, and the audit trail will not be written in audIn the table, this is very easy to understand. If the database has not started auD, this is very easy to understand. If the database has not started auDsurfacemiddle，thisindivualverygoodreasonuntie，likefruitnumberaccording toLibraryreturnnot yetstartmoveaudIf not available, then connection information like conn /as sysdba can only be recorded elsewhere. If it is a Windows platform, the audti trail will be recorded in the event management of Windows, and if it is a Linux/Unix platform, it will be recorded in the file specified by the audit_file_dest parameter.

audit_trail：

• None: It is the default value, no auditing is done;
• DB: Record audit trail in the audit related table of the database, such as aud$, and the audit results are only connection information;
• DB,Extended: In this way, in addition to the connection information, the audit results also include specific statements executed at that time;
• OS: record the audit trail in the operating system file, and the file name is specified by the audit_file_dest parameter;

7.1 Statement Audit

All types of audits use the audit command to turn on audits and the noaudit command to turn off audits. For statement auditing, the format of the audit command looks like this:

AUDIT sql_statement_clause BY {SESSION | ACCESS} WHENEVER [NOT] SUCCESSFUL;

sql_statement_clause contains many different pieces of information, such as the type of SQL statement you want to audit and the person you are auditing.

In addition, it is desirable to audit each action (by access) or only audit once (by session). By session default.

Sometimes an action that hopes for successful auditing: no statement that generates an error message. For these statements, add whenever successful. Sometimes only cares about whether the command using audit statements fails, the reason for the failure is a permission violation, the use of space in the table space or a syntax error. For these cases, use whenever not successful.

For most categories of audit methods, if you really want to audit all types of table access or any permissions for a certain user, you can specify all instead of a single statement type or object.

Table 1 lists the types of statements that can be audited and contains a brief description of the relevant statements in each category. If all is specified, any statements in that list are audited. However, the statement types in Table 2 do not belong to the all category when auditing is enabled; they must be specified explicitly in the audit command.

Table 1 Auditable statements included in the ALL category

Table 2 Explicitly specified statement types

Notice:

Starting from Oracle Database 11g, columns SQL_TEXT and SQL_BIND in DBA_AUDIT_TRAIL are populated only if the initial parameter AUDIT_TRAIL is set to DB_EXTENDED. By default, the value of AUDIT_TRAIL is DB.

SQL> audit index by dataaly;
Audit succeeded.

To close the audit of KSHELTON on the table, you can use the noaudit command as follows:

SQL> noaudit index by dataaly;
Noaudit succeeded.

It may also be desirable to audit successful and unsuccessful logins in the usual way, which requires two audit commands:

SQL> audit session whenever successful;
Audit succeeded.
SQL> audit session whenever not successful;
Audit succeeded.

7.2 Permission Audit

Audit system permissions have the same basic syntax as statement auditing, but audit system permissions are specified in sql_stateme nt_clause, not in statements.

For example, you might want to grant ALTER TABLESPACE permission to all DBAs, but you want to generate an audit record when this happens. The command to enable auditing for such permissions looks similar to statement auditing:

SQL> audit alter tablespace by access whenever successful;
Audit succeeded.

Each time you successfully use the ALTER TABLESPACE permission, a line of content is added to $.

System administrators who use SYSDBA and SYSOPER permissions or connect to the database as SYS users can take advantage of special audits. To enable this additional audit level, the initial parameter AUDIT_SYS_OPERATIONS can be set to TRUE.

This audit record is sent to the same location as the operating system audit record. Therefore, this location is related to the operating system. All SQL statements executed when one of these permissions is used, as well as any SQL statements executed as user SYS, are sent to the operating system audit location.

7.3 Mode Object Audit

Audit access to various schema objects looks similar to statement audits and permission audits:

AUDIT schema_object_clause BY {SESSION | ACCESS}
WHENEVER [NOT] SUCCESSFUL;

schema_object_clause specifies the type of object accessed and the object to be accessed. You can audit 14 different types of operation on a specific object, which are listed in the following table.

If you want all insert and update commands on the audit table regardless of who is updating, you can use the audit command like this every time the action occurs:

SQL> audit insert, update on  by access whenever successful;
Audit successful.

7.4 Fine-grained audit

Starting with Oracle9i, auditing has become more focused on one aspect and more precise by introducing fine-grained object audits, or FGA. Implement FGA by a PL/SQL package called DBMS_FGA.

Using standard audits, it is easy to discover which objects are accessed and who are accessed, but it is impossible to know which rows or columns are accessed. Fine-grained auditing solves this problem, which not only specifies predicates (or where clauses) for rows that need to be accessed, but also specifies columns accessed in the table. By auditing access to tables only when certain rows and columns are accessed, the number of audit table entries can be greatly reduced.

The package DBMS_FGA has 4 procedures:

User TAMARA usually visits the table every day to find the employee's email address. System administrators suspect that TAMARA is viewing managers’ salary information, so they set up an FGA strategy to audit any manager’s access to the SALARY column:

begin
dbms_fga.add_policy(
object_schema =>   'HR',
object_name =>     'EMPLOYEES',
policy_name =>     'SAL_SELECT_AUDIT',
audit_condition => 'instr(job_id,''_MAN'') > 0',
audit_column =>    'SALARY'
);
end;

The audit records for fine-grained audits can be accessed using the data dictionary view DBA_FGA_AUDIT_TRAIL. If you generally need to view standard audit lines and fine-grained audit lines, the data dictionary view DBA_COMMON_AUDIT_TRAIL combines rows in both audit types.

Continue to look at the example, user TAMARA runs the following two SQL queries:

SQL> select employee_id, first_name, last_name, email from 
2     where employee_id = 114;
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL
----------- ------------------ ---------------------   --------------
114    Den                   Raphaely                  DRAPHEAL
1 row selected.
SQL> select employee_id, first_name, last_name, salary from 
2     where employee_id = 114;
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- ------------------ -----------------------   ----------
114    Den                   Raphaely                       11000
1 row selected.

The first query accesses manager information, but does not access the SALARY column. The second query is the same as the first one, but accesses the SALARY column, thus triggering the FGA policy, resulting in a row in the audit trail:

SQL> select to_char(timestamp,'mm/dd/yy hh24:mi') timestamp,
2      object_schema, object_name, policy_name, statement_type
3  from dba_fga_audit_trail
4  where db_user = 'TAMARA';
TIMESTAMP        OBJECT_SCHEMA  OBJECT_NAME     POLICY_NAME       STATEMENT_TYPE
--------------  -------------  -------------  ---------------- --------------
08/12/07 18:07  HR               EMPLOYEES       SAL_SELECT_AUDIT SELECT
1 row selected.

Because fine-grained access control was established in the VPD examples earlier in this chapter to prevent unauthorized access to the SALARY column, it is necessary to double check the policy function to ensure that the SALARY information is still correctly limited. Fine-grained audits, as well as standard audits, are great ways to ensure that authorization strategies are properly established first.

7.5 Audit-related data dictionary view

The following table contains the data dictionary view related to auditing.

7.6 Protection Audit Trail

Audit tracking itself needs to be protected, especially if non-system users must access table $. The built-in role DELETE_ANY_CATALOG is a way for non-SYS users to access audit trails (e.g., archive and intercept audit trails to ensure that it does not affect the space requirements of other objects in the SYS tablespace).

To establish an audit of the audit trail itself, connect to the database as SYSDBA and run the following command:

SQL> audit all on $ by access;
Audit succeeded.

Now, all target tablesThe actions of the actions, including select, insert, update and delete, are recorded in the actions of the actions of the actions of the words, including select, insert, update and delete, are recorded in theofmovedo，Bagincludeselect、insert、updateanddelete，AllrememberrecordexistSYS.AUDIn itself. However, you might ask if someone deletes the Identity TableWhat happens at this time when the audit records of the interview? At this time, the rows in the table will be deleted, but another row will be inserted to record the deletion of the row. Therefore, there are always some audit records for access, what happens at this time? At this time, the rows in the table will be deleted, but another row will be inserted to record the deletion of the row. Therefore, there are always some targetsvisitaskofReviewcountrememberrecord，thishourmeetinghairbornVariedIs it？thishourWilldeleteremovesurfacemiddleofOK，butcatchPutInsertenterOtheroneOK，rememberrecordOKofdeleteremove。becausethis，totalyesliveexistonesomeNeedlerightSYS.AUDproof of (intentional or accidental) activity. Additionally, if AUDIT_SYS _OPERATIONS is set to True, any sessions that use as sysdba, as sysoper, or itself will be logged into the operating system audit location, which even the Oracle DBA may not be able to access. Therefore, there are many suitable security measures to ensure that all permissions in the database are recorded, as well as any attempt to hide that activity.

7.7 Enable Enhanced Audit

Starting with Oracle Database 11g, the Database Configuration Assistant (DBCA) is easy to enable default (enhanced) auditing. While there is some system overhead for recording audit information, compatibility requirements (e.g., compatibility requirements stipulated in the Sarbanes-Oxley Act) require strict monitoring of all business operations, including security-related operations in the database.

Default auditing can be configured using DBCA when creating a database or after the database has been created. If you have changed a lot of audit settings and want to reset the audit options to baseline values, it is useful to configure default audits using DBCA after the database has been created.

In addition to setting the value of the initial parameter AUDIT_TRAIL to DB, the default audit setting also audits the audit role command itself. In addition, in the Oracle Enterprise Manager Audit Settings page on the Audited Privileges tab, you can view the default audit permissions.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.