Step-by-Step Guide to Configure Unified Auditing in Oracle 12c
Unified Auditing is a powerful feature introduced in Oracle 12c to simplify and consolidate auditing processes. It combines traditional auditing, fine-grained auditing, and database activity monitoring into a single framework, offering improved performance and ease of management. This blog provides a step-by-step guide to enable and configure Unified Auditing in Oracle 12c.
login as: oracle
oracle@apexdb2’s password:
┌──────────────────────────────────────────────────────────────────────┐
│ • MobaXterm Personal Edition v22.0 • │
│ (SSH client, X server and network tools) │
│ │
│ → SSH session to oracle@apexdb2.cao.local │
│ • Direct SSH : v │
│ • SSH compression : v │
│ • SSH-browser : v │
│ • X11-forwarding : v (remote display is forwarded through SSH) │
│ │
│ → For more info, ctrl+click on help or visit our website. │
└──────────────────────────────────────────────────────────────────────┘
Last login: Wed Aug 7 17:20:38 2024 from gateway
[oracle@apexdb2 ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 17:35:42 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> shut immediate
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3701
Additional information: -60214929
Process ID: 0
Session ID: 0 Serial number: 0
SQL> Disconnected
[oracle@apexdb2 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 07-AUG-2024 17:35:57
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apexdb2.cao.local)(PORT=1521)))
The command completed successfully
[oracle@apexdb2 ~]$ cd /u01/app/oracle/
admin/ audit/ cfgtoollogs/ checkpoints/ datafiles/ diag/ fra/ oradiag_oracle/ product/ tfa/
[oracle@apexdb2 ~]$ cd /u01/app/oracle/
admin/ audit/ cfgtoollogs/ checkpoints/ datafiles/ diag/ fra/ oradiag_oracle/ product/ tfa/
[oracle@apexdb2 ~]$ cd /u01/app/oracle/product/12
12.2.0/ 12c/
[oracle@apexdb2 ~]$ cd /u01/app/oracle/product/12c/db_1/
install/ response/ rpm/ sshsetup/ stage/
(reverse-i-search)`m’: cd /u01/app/oracle/product/12.2.0/db_1/network/ad^Cn/
[oracle@apexdb2 ~]$ cd $ORACLE_HOME/rdbms/lib
[oracle@apexdb2 lib]$ make -f ins_rdbms.mk uniaud_on ioracle
/usr/bin/ar d /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/libknlopt.a /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/kzaiang.o
chmod 755 /u01/app/oracle/product/12.2.0/db_1//bin
– Linking Oracle
rm -f /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/oracle
/u01/app/oracle/product/12.2.0/db_1//bin/orald -o /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/oracle -m64 -z noexecstack -Wl,–disable-new-dtags -L/u01/app/oracle/product/12.2.0/db_1//rdbms/lib/ -L/u01/app/oracle/product/12.2.0/db_1//lib/ -L/u01/app/oracle/product/12.2.0/db_1//lib/stubs/ -Wl,-E /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/opimai.o /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/ssoraed.o /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/ttcsoi.o -Wl,–whole-archive -lperfsrv12 -Wl,–no-whole-archive /u01/app/oracle/product/12.2.0/db_1//lib/nautab.o /u01/app/oracle/product/12.2.0/db_1//lib/naeet.o /u01/app/oracle/product/12.2.0/db_1//lib/naect.o /u01/app/oracle/product/12.2.0/db_1//lib/naedhs.o /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/config.o -ldmext -lserver12 -lodm12 -lofs -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo “-loraolap12” ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -ldmext -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.2.0/db_1//lib/libavserver12.a ] ; then echo “-lavserver12” ; else echo “-lavstub12”; fi` `if [ -f /u01/app/oracle/product/12.2.0/db_1//lib/libavclient12.a ] ; then echo “-lavclient12” ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /u01/app/oracle/product/12.2.0/db_1//lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12.2.0/db_1//lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12.2.0/db_1//lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12.2.0/db_1//lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/libknlopt.a | grep “kxmnsd.o” > /dev/null 2>&1 ; then echo ” ” ; else echo “-lordsdo12 -lserver12”; fi` -L/u01/app/oracle/product/12.2.0/db_1//ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -lgeneric12 -lorazip -loraz -llzopro5 -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons -lfthread12 `cat /u01/app/oracle/product/12.2.0/db_1//lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.2.0/db_1//lib -lm `cat /u01/app/oracle/product/12.2.0/db_1//lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/12.2.0/db_1//lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
test ! -f /u01/app/oracle/product/12.2.0/db_1//bin/oracle || (\
mv -f /u01/app/oracle/product/12.2.0/db_1//bin/oracle /u01/app/oracle/product/12.2.0/db_1//bin/oracleO &&\
chmod 600 /u01/app/oracle/product/12.2.0/db_1//bin/oracleO )
mv /u01/app/oracle/product/12.2.0/db_1//rdbms/lib/oracle /u01/app/oracle/product/12.2.0/db_1//bin/oracle
chmod 6751 /u01/app/oracle/product/12.2.0/db_1//bin/oracle
[oracle@apexdb2 lib]$ cd
[oracle@apexdb2 ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 17:39:04 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1.0033E+10 bytes
Fixed Size 12169944 bytes
Variable Size 2046823720 bytes
Database Buffers 7952400384 bytes
Redo Buffers 21381120 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@apexdb2 ~]$ lsnrctl start
LSNRCTL for Linux: Version 12.2.0.1.0 – Production on 07-AUG-2024 17:39:25
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0/db_1//bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 12.2.0.1.0 – Production
System parameter file is /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/apexdb2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apexdb2.cao.local)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=apexdb2.cao.local)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 – Production
Start Date 07-AUG-2024 17:39:25
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/apexdb2/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=apexdb2.cao.local)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@apexdb2 ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 17:39:34 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select * from v$option where parameter =’Unified Auditing’;
PARAMETER
—————————————————————-
VALUE CON_ID
—————————————————————- ———-
Unified Auditing
TRUE 0
SQL>
SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES;
POLICY_NAME
——————————————————————————–
ORA_CIS_RECOMMENDATIONS
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_DATABASE_PARAMETER
ORA_DV_AUDPOL2
ORA_RAS_SESSION_MGMT
ORA_ACCOUNT_MGMT
ORA_DV_AUDPOL
ORA_SECURECONFIG
9 rows selected.
SQL> ed
Wrote file afiedt.buf
SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_ENABLED_POLICIES
POLICY_NAME
——————————————————————————–
ORA_LOGON_FAILURES
ORA_SECURECONFIG
SQL> select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME=’ORA_SECURECONFIg’;
no rows selected
SQL> select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME=’ORA_SECURECONFIG’;
AUDIT_OPTION
——————————————————————————–
LOGMINING
TRANSLATE ANY SQL
EXEMPT REDACTION POLICY
PURGE DBA_RECYCLEBIN
ADMINISTER KEY MANAGEMENT
DROP ANY SQL TRANSLATION PROFILE
ALTER ANY SQL TRANSLATION PROFILE
CREATE ANY SQL TRANSLATION PROFILE
CREATE SQL TRANSLATION PROFILE
CREATE EXTERNAL JOB
CREATE ANY JOB
AUDIT_OPTION
——————————————————————————–
GRANT ANY OBJECT PRIVILEGE
EXEMPT ACCESS POLICY
CREATE ANY LIBRARY
GRANT ANY PRIVILEGE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
ALTER DATABASE
GRANT ANY ROLE
DROP PUBLIC SYNONYM
CREATE PUBLIC SYNONYM
AUDIT_OPTION
——————————————————————————–
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY TABLE
DROP USER
BECOME USER
CREATE USER
AUDIT SYSTEM
ALTER SYSTEM
CREATE DATABASE LINK
DROP DATABASE LINK
ALTER USER
AUDIT_OPTION
——————————————————————————–
CREATE ROLE
DROP ROLE
SET ROLE
CREATE PROFILE
DROP PROFILE
ALTER PROFILE
ALTER ROLE
CREATE DIRECTORY
DROP DIRECTORY
ALTER DATABASE LINK
CREATE PLUGGABLE DATABASE
AUDIT_OPTION
——————————————————————————–
ALTER PLUGGABLE DATABASE
DROP PLUGGABLE DATABASE
EXECUTE
EXECUTE
48 rows selected.
SQL> [oracle@apexdb2 ~]$
[oracle@apexdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 17:51:03 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> alter user HR identified by HR123 account unlock;
User altered.
SQL> conn HR
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> drop directory EMP;
Directory dropped.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@apexdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 17:52:49 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> set lines 299
SQL> col SQL_TEXT for a23
SQL> col action_name for a18
SQL> col UNIFIED_AUDIT_POLICIES for a23
SQL>
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES,EVENT_TIMESTAMP from unified_AUDIT_trail where DBUSERNAME=’SCOTT’ and EVENT_TIMESTAMP > sysdate -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
LOGON ORA_LOGON_FAILURES 07-AUG-24 05.50.56.925492 PM
DROP DIRECTORY drop directory EMP ORA_SECURECONFIG 07-AUG-24 05.52.39.462777 PM
SQL>
SQL> create audit policy HR_CASE ACTIONS CREATE TABLE, INSERT ON HR.MY_TABLE,TRUNCATE TABLE, SELECt ON HR.APGCIMAST;
Audit policy created.
SQL> set lines 299
SQL> col POLICY_NAME for a23
SQL> col AUDIT_OPTION for a12
SQL> col AUDIT_CONDITION for a12
SQL> col OBJECT_SCHEMA for a23
SQL> col OBJECT_NAME for a14
SQL>
SQL> select POLICY_NAME,AUDIT_OPTION,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME from AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME=’HR_CASE’;
POLICY_NAME AUDIT_OPTION AUDIT_CONDIT OBJECT_SCHEMA OBJECT_NAME
———————– ———— ———— ———————– ————–
HR_CASE CREATE TABLE NONE NONE NONE
HR_CASE TRUNCATE TAB NONE NONE NONE
LE
HR_CASE INSERT NONE HR MY_TABLE
HR_CASE SELECT NONE HR APGCIMAST
SQL>
SQL> SELECT DISTINCT POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME=’HR_CASE’;
no rows selected
SQL> AUDIT POLICY HR_CASE;
Audit succeeded.
SQL> —ENABLED THE POLICY—HR_CASE—
SQL>
SQL> SELECT DISTINCT POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME=’HR_CASE’;
POLICY_NAME
———————–
HR_CASE
SQL>
SQL> ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
User altered.
SQL> CONN HR
Enter password:
Connected.
SQL>
SQL> CREATE TABLE WITNESS(ID NUMBER(10))
SQL> /
Table created.
1* insert into witness values (1)
SQL> /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> CREATE TABLE WITNESS_for_del(ID NUMBER(10))
2 /
Table created.
SQL> commit
2 /
Commit complete.
SQL> insert into witness_for_del values (1);
1 row created.
SQL> ed
Wrote file afiedt.buf
1* insert into witness_for_del values (22)
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1* insert into witness_for_del values (3)
SQL> /
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table witness_for_del;
Table truncated.
1* select count(*) from apgcimast
SQL> /
COUNT(*)
———-
156570
SQL> commit;
Commit complete.
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES, EVENT_TIMESTAMP FROM UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME=’HR’ AND EVENT_TIMESTAMP > SYSDATE -1/24;
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
CREATE TABLE HR_CASE 07-AUG-24 06.53.12.812406 PM
CREATE TABLE CREATE TABLE WITNESS(ID HR_CASE 07-AUG-24 06.54.42.318623 PM
NUMBER(10))
CREATE TABLE CREATE TABLE WITNESS_fo HR_CASE 07-AUG-24 06.57.14.526073 PM
r_del(ID NUMBER(10))
TRUNCATE TABLE truncate table witness_ HR_CASE 07-AUG-24 06.59.45.664988 PM
for_del
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.01.52.386428 PM
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
alues (07082024)
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.02.07.536052 PM
alues (070824)
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.04.18.397142 PM
alues (07)
INSERT insert into MY_TABLE ( HR_CASE 07-AUG-24 07.05.56.955979 PM
NO) values (070824)
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
INSERT insert into MY_TABLE ( HR_CASE 07-AUG-24 07.06.47.354599 PM
NO) values (7824)
INSERT insert into MY_TABLE(S HR_CASE 07-AUG-24 07.07.31.192670 PM
ER_NO) values (7824)
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.09.38.181183 PM
alues(1234, 123, 425, 6
78)
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.13.27.041326 PM
alues(1234, 123, 425, 6
78, 30-09-2024,(C), 202
308, 1)
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.14.21.101032 PM
alues(1234, 123, 425, 6
78, (NULL), 30-09-2024,
(C), 20230
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.14.55.547935 PM
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
alues(1234, 123, 425, 6
78, (NULL), 30-09-2024,
202308, 1
INSERT insert into MY_TABLE v HR_CASE 07-AUG-24 07.17.44.079159 PM
alues(1234, 123, 425, 6
78, (NULL), (NULL), (nu
ll), 202308
SELECT select count(*) from ap HR_CASE 07-AUG-24 07.18.57.086282 PM
gcimast
ACTION_NAME SQL_TEXT UNIFIED_AUDIT_POLICIES EVENT_TIMESTAMP
—————— ———————– ———————– —————————————————————————
LOGON ORA_LOGON_FAILURES 07-AUG-24 07.10.15.724497 PM
17 rows selected.
SQL> ED
Wrote file afiedt.buf
1* select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES, EVENT_TIMESTAMP FROM UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME=’HR’ AND EVENT_TIMESTAMP > SYSDATE -1/24
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME=’HR_CASE’;
USER_NAME POLICY_NAME ENABLED
——————————————————————————————————————————– ———————– ——-
ALL USERS HR_CASE BY
SQL> –EXCLUDE ONE USER FROM THE POLICY;
SQL>
SQL> –CHANGE AUDIT CONDITION–DISABLE AND ENABLE WITH OTHER CONDITION–
SQL>
SQL>
SQL> NOAUDIT POLICY HR_CASE;
Noaudit succeeded.
SQL> AUDIT POLICY HR_CASE EXCEPT SCOTT;
Audit succeeded.
SQL> select USER_NAME,POLICY_NAME,ENABLED_OPT FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME=’HR_CASE’;
USER_NAME
——————————————————————————–
POLICY_NAME
——————————————————————————–
ENABLED
——-
SCOTT
HR_CASE
EXCEPT
SQL> CONN SCOTT
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> [oracle@apexdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 19:39:27 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> conn SCOTT
Enter password:
Connected.
SQL>
SQL>
SQL> create table for_test (id number);
Table created.
SQL> commit;
Commit complete.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
[oracle@apexdb2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 7 19:40:23 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES, EVENT_TIMESTAMP FROM UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME=’HR’ AND EVENT_TIMESTAMP > SYSDATE -1/24;
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
CREATE TABLE
HR_CASE
07-AUG-24 06.53.12.812406 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
CREATE TABLE
CREATE TABLE WITNESS(ID NUMBER(10))
HR_CASE
07-AUG-24 06.54.42.318623 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
CREATE TABLE
CREATE TABLE WITNESS_for_del(ID NUMBER(10))
HR_CASE
07-AUG-24 06.57.14.526073 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values (07082024)
HR_CASE
07-AUG-24 07.01.52.386428 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values (070824)
HR_CASE
07-AUG-24 07.02.07.536052 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values (07)
HR_CASE
07-AUG-24 07.04.18.397142 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE (NO) values (070824)
HR_CASE
07-AUG-24 07.05.56.955979 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE (NO) values (7824)
HR_CASE
07-AUG-24 07.06.47.354599 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE(NO) values (7824)
HR_CASE
07-AUG-24 07.07.31.192670 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values(1234, 123, 425, 678)
HR_CASE
07-AUG-24 07.09.38.181183 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values(1234, 123, 425, 678, 30-09-2024,(C), 202308, 1)
HR_CASE
07-AUG-24 07.13.27.041326 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values(1234, 123, 425, 678, (NULL), 30-09-2024, (C), 20230
HR_CASE
07-AUG-24 07.14.21.101032 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values(1234, 123, 425, 678, (NULL), 30-09-2024, 202308, 1
HR_CASE
07-AUG-24 07.14.55.547935 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
INSERT
insert into MY_TABLE values(1234, 123, 425, 678, (NULL), (NULL), (null), 202308
HR_CASE
07-AUG-24 07.17.44.079159 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
SELECT
select count(*) from apgcimast
HR_CASE
07-AUG-24 07.18.57.086282 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
TRUNCATE TABLE
truncate table witness_for_del
HR_CASE
07-AUG-24 06.59.45.664988 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
AUDIT
AUDIT POLICY HR_CASE EXCEPT SCOTT
07-AUG-24 07.34.54.226853 PM
ACTION_NAME
—————————————————————-
SQL_TEXT
——————————————————————————–
UNIFIED_AUDIT_POLICIES
——————————————————————————–
EVENT_TIMESTAMP
—————————————————————————
LOGON
ORA_LOGON_FAILURES
07-AUG-24 07.10.15.724497 PM
18 rows selected.