Auditing at Schema Level in Oracle Database 11gR2
Posted by Mir Sayeed Hassan on December 23rd, 2017
Auditing at Schema Level in Oracle Database 11gR2
DDL Auditing at Schema Level
Try to perform the below tested & verified auditing at schema level in Oracle Database 11gr2
A DDL event occurs when a DDL statement is run in specific schema level such as Create, Alter, Drop, Rename, truncate etc
SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance; INSTANCE_NAME VERSION DATABASE_STATUS ---------------- ----------------- ------------------------------------------- testdb 11.2.0.4.0 ACTIVE
SQL> show parameter audit NAME TYPE VALUE ------------------------------------------------------------------------------- audit_file_dest string /u01/app/oracle/admin/prim/adump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB
Create the test user as “AUDIT_MIR”:
SQL> conn sys/sys as sysdba Connected.
SQL> create user audit_mir identified by maudit; User created.
SQL> grant connect, resource to audit_mir; Grant succeeded.
Create the Audit table as per your requirement for the schema to audit as SYS Login shown below:
SQL> CREATE TABLE audit_sch_ddl_level_mir( d date, OSUSER varchar2(255), CURRENT_USER varchar2(255), HOST varchar2(255), TERMINAL varchar2(255), owner varchar2(30), type varchar2(30), name varchar2(30), sysevent varchar2(30), sql_txt varchar2(4000)) / Table created. NOTE: If you need to include more information, refer below view & select as per your requirement
SQL> desc dba_audit_trail;
Create the TRIGGER to enable for the require schema level (Ex: “Audit_mir”) in our database as shown below:
SQL> create or replace trigger audit_mir_trig after ddl on audit_mir.schema
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent=' ') --- If Null it will be audit all DDL Command
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into auditnull_level(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/
Trigger created. — Therefore the trigger is created for the specific user “AUDIT_MIR”
SQL> conn audit_mir/maudit; Connected.
SQL> sho user USER is "AUDIT_MIR"
SQL> create table audit1(eno number(10)); Table created.
SQL> create table audit2(eno number(10)); Table created.
SQL> insert into audit1 values(1); 1 row created.
SQL> / 1 row created.
SQL> truncate table audit1; Table truncated.
SQL> alter table audit2 add name varchar2(20); Table altered.
SQL> alter table audit2 rename column name to ename; Table altered.
SQL> alter table audit2 drop column ename; Table altered.
SQL> drop table audit2; Table dropped.
SQL> commit; Commit complete.
SQL> conn sys/sys as sysdba Connected. set linesize 800 set pagesize 45 column d format a10 column OSUSER format a20 column HOST format a30 column TERMINAL format a40 column OWNER format a50 column TYPE format a50 column NAME format a60 column SYSEVENT format a70 column SQL_TXT format a80
SQL> select * from audit_sch_ddl_level_mir; D OSUSER CURRENT_USER HOST TERMINAL OWNER TYPE NAME SYSEVENT SQL_TXT ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT1 CREATE create table audit1(eno number(10)) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT2 CREATE create table audit2(eno number(10)) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT1 TRUNCATE truncate table audit1 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT2 ALTER alter table audit2 add name varchar2(20) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT2 ALTER alter table audit2 rename column name to ename 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT2 ALTER alter table audit2 drop column ename 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDNULL TABLE AUDIT2 DROP drop table audit2 7 rows selected.
====Hence auditing at schema level is tested & verified in our test database====
DDL Auditing at Schema Level by excluding the specific DDL Command like “TRUNCATE”
Fallow the below tested & verified step by step procedure:
SQL> sho user USER is "SYS"
SQL> CREATE TABLE audit_trunc_level( d date, OSUSER varchar2(255), CURRENT_USER varchar2(255), HOST varchar2(255), TERMINAL varchar2(255), owner varchar2(30), type varchar2(30), name varchar2(30), sysevent varchar2(30), sql_txt varchar2(4000)) / Table created.
SQL> create user auditmir_trunc identified by maudit; User created.
SQL> grant connect,resource to auditmir_trunc; Grant succeeded.
SQL> create or replace trigger audit_schtrunc_trig after ddl on auditmir_trunc.schema
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent='TRUNCATE') ----- This will exclude the DDL Truncate command in this operation
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into auditcreate_level (d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/
Trigger created.
SQL> connect auditmir_trunc/maudit; Connected
SQL> create table aud_test1(eno number(10)); Table created.
SQL> insert into aud_test1 values(1); ---- 1 row created.
SQL> commit; Commit complete.
SQL> select * from aud_test1; ENO ---------- 1 1 1
SQL> create table aud_test2(eno number(10)); Table created.
SQL> truncate table aud_test1; --- As you can see we have truncated but it will not be auditing in our scenario Table truncated.
SQL> drop table aud_test2; Table dropped.
SQL> create table AUD_TEST3(eno number(10)); Table created.
SQL> alter table AUD_TEST3 add ename varchar2(20); Table altered.
SQL> alter table AUD_TEST3 drop column ename; Table altered.
SQL> alter table AUD_TEST3 rename column eno to empno; Table altered.
SQL> commit; Commit complete.
SQL> conn sys/sys as sysdba Connected.
set linesize 800 set pagesize 45 column d format a10 column OSUSER format a20 column HOST format a30 column TERMINAL format a40 column OWNER format a50 column TYPE format a50 column NAME format a60 column SYSEVENT format a70 column SQL_TXT format a80 SQL> select * from auditmir_trunc; D OSUSER CURRENT_USER HOST TERMINAL OWNER TYPE NAME SYSEVENT SQL_TXT ------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------ 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST1 CREATE create table aud_test1(eno number(10)) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST2 CREATE create table aud_test2(eno number(10)) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST2 DROP drop table aud_test2 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST3 CREATE create table AUD_TEST3(eno number(10)) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST3 ALTER alter table AUD_TEST3 add ename varchar2(20) 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST3 ALTER alter table AUD_TEST3 drop column ename 23-DEC-17 oracle SYS oracleTestServer.psp.local pts/2 AUDITSCH_T TABLE AUD_TEST3 ALTER alter table AUD_TEST3 rename column eno to empno 7 rows selected.
Note:
DDL Audit with excluding the truncate command in the above scenario & Similarly we can perform any specific DDL Command as per your requirement in database
=======Hence tested & verified in our test db========