Oracle Audit setup

--env: oracle 19.8 militenant Environment

1. view audit record
SQL> show parameter audit_trail

NAME                       TYPE                     VALUE
------------------------- ----------------------  ------------------------------
audit_trail                string                   DB

SQL> select * from unified_audit_trail order by event_timestamp desc

2.auditing the DBA Role in a Multitenant Environment

SQL> create audit policy role_dba_audit_pol roles dba container = all;
Audit policy created.

SQL> audit policy role_dba_audit_pol;
Audit succeeded.

3. pluggable database
  create pluggable database inodb
    admin user user1 identified by 123456 roles=(connect)
    file_name_convert=('/u01/app/oracle/oradata/LEE/pdbseed/','/u01/app/oracle/oradata/LEE/inodb/');
    
SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL>  create pluggable database inodb
    admin user user1 identified by 123456 roles=(connect)
    file_name_convert=('/u01/app/oracle/oradata/LEE/pdbseed/','/u01/app/oracle/oradata/LEE/inodb/');  2    3

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
         4 INODB                          MOUNTED

SQL> alter pluggable database inodb open;
Pluggable database altered.

grant connect to 使用者名稱;--允許使用者連線資料庫
grant resource to 使用者名稱;--允許使用者建立相關的資料庫物件，如表，序列等。

--查詢有審計記錄
SQL> select * from unified_audit_trail order by event_timestamp desc

4. 登入審計
SQL> audit session;
Audit succeeded.

SQL> audit session whenever successful;
Audit succeeded.

SQL> audit session whenever not successful;
Audit succeeded.

SQL> audit select any table whenever successful;
Audit succeeded.


--檢視審計記錄
 select * from sys.aud$;
 
--audit_trail=os時，
--開啟會話審計，輸出同DB引數的內容一致，只是會儲存在audit_file_dest目錄下面

--注意：19g後(包括)後，dba_audit_trail.sql_text記錄了具體的sql語句，可以檢視，
--並且audit_trail必須=db,extended,否則不能看到audit.

使用審計功能，需要對資料庫初始化引數AUDIT_TRAIL進行設定，其引數值可以為：
none
Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.

os
Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.

db
Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.
If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

db, extended
Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified. When standard auditing is used with DB, EXTENDED, then virtual private database (VPD) predicates and policy names are also populated in the SYS.AUD$ table.
If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

xml
Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

xml, extended
Performs all actions of AUDIT_TRAIL=xml, and includes SQL text and SQL bind information in the audit trail.

--物件審計記錄在表中
SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;
System altered.

--物件審計記錄在檔案
SQL> alter system set AUDIT_TRAIL=os scope=spfile;
System altered.

5.操作審計
5.1 開啟使用者user1建表操作審計
SQL> alter session set container=inodb;
Session altered.

SQL> audit create table by user1 by access container=current;
Audit succeeded.

5.2 開啟對使用者user1的test表插入語句進行審計
SQL> audit insert on user1.test;
Audit succeeded.


--插入資料時報錯
One error saving changes to table "USER1"."TEST":
Row 1: ORA-01950: no privileges on tablespace 'SYSTEM'
ORA-06512: at line 1

--初始大小100M，每次自動擴充套件5M，最大擴充套件到2048M
SQL> create tablespace leel datafile '/u01/app/oracle/oradata/LEE/inodb/leel01.dbf' size 100M autoextend on next 5M maxsize 2048M;
Tablespace created.

SQL> alter user user1 default tablespace leel;
User altered.

SQL> drop table user1.test;

SQL> create table user1.test (
       id number (10),
       name varchar2 (10),
       inst_date date)
      tablespace leel;
      
--無法插入資料
SQL> select * from dba_sys_privs where grantee='USER1';
no rows selected
SQL> alter user user1 quota unlimited on leel;
User altered.
SQL> grant unlimited tablespace to user1;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee='USER1';
GRANTEE      PRIVILEGE              ADMIN COMMON INHERI
----------   -----------------      ------ ------ ------
USER1        UNLIMITED TABLESPACE   NO     NO     NO


  select * from sys.aud$;
  select * from dba_audit_object     --audit_trail=db時物件審計記錄
  select * from dba_priv_audit_opts  --許可權審計情況
 
  --審計記錄檔案示例
Fri Nov 11 09:18:14 2022 +08:00
LENGTH : '332'
ACTION :[20] 'select '1' from dual'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[5] 'l1xxxx'
CLIENT TERMINAL:[7] 'unknown'
STATUS:[1] '0'
DBID:[9] '952652103'
SESSIONID:[10] '4294967295'
USERHOST:[10] 'Lxx03-1xxx'
CLIENT ADDRESS:[57] '(ADDRESS=(PROTOCOL=tcp)(HOST=10.x.x.x)(PORT=57050))'
ACTION NUMBER:[1] '3'


SQL> audit drop any table by user1 by access container=current;
Audit succeeded.

  select * from sys.aud$ order by ntimestamp# desc;
  select * from dba_audit_object order by timestamp desc;
  select * from dba_priv_audit_opts
  select * from DBA_OBJ_AUDIT_OPTS