--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