• Home
  • About
  • Log in
  • Register
Follow Us
KTEXPERTS.COM
  Upcoming Sessions  
  • Oracle
  • Oracle Apps
  • SQL Server
  • Cassandra
  • Hadoop
  • Teradata
  • DevOps
  • MySQL
  • UNIX
  • Index
    • Public_Articles
    • Private_Articles
mm

Ajay Kumar

This is Ajay Kumar having around 3+ years of experience as Oracle DBA having exposure to SQL,PL*SQL, RAC,Dataguard, RMAN,GoldenGate,MySQL DBA and Amazon Web Services. Share your knowledge. It’s a way to achieve immortality.

Follow

Share this post

Tags

  • audit a user actions
  • audting
  • standard way of audting
  • sys.aud$

Auditing in Oracle

Posted on August 2, 2018 in Oracle Auditing, Oracle DBA, Uncategorized
Post Views: 566

Auditing in Oracle.

Auditing  is used to track the occurrence of SQL statements in subsequent user sessions.

In this article will see how can dba  track all types of sql operations  (select,insert,update,delete drop,create and alter) of particular user.

Auditing is a default feature of the Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER

1
2
3
4
5
6
7
SYS> SHOW PARAMETER AUDIT
NAME                                  TYPE           VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                        string     /oraeng/app/oracle/admin/ord/adump
audit_sys_operations                   boolean     FALSE
audit_trail                            string       NONE
unified_audit_sga_queue_size           integer     1048576

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

1
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

  • none or false –  Auditing is disabled.
  • db or true –        Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
  • db,extended –   As db, but the SQL_BIND and SQL_TEXT columns are also populated.
  • xml–                     Auditing is enabled, with all audit records stored as XML format OS files.
  • xml,extended – As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
  • os–                       Auditing is enabled, with all audit records directed to the operating system’s audit trail.

To enable auditing and direct audit records to the database audit trail, we would do the following.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SYS>>ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SYS>>shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>>startup
ORACLE instance started.
Total System Global Area  1895825408 bytes
Fixed Size                 2925744 bytes
Variable Size              570428240 bytes
Database Buffers           1308622848 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SYS>>sho parameter audit
NAME                                  TYPE         VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                       string     /oraeng/app/oracle/admin/ord/adump
audit_sys_operations                  boolean     TRUE
audit_syslog_level                    string
audit_trail                           string      DB
unified_audit_sga_queue_size          integer     1048576

Now we will create user called aud_test with required privileges.

1
2
3
4
5
6
SYS>>CREATE USER aud_test IDENTIFIED BY aud_test DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
User created.
SYS>>GRANT connect TO aud_test;
Grant succeeded.
SYS>>GRANT create table, create procedure TO aud_test;
Grant succeeded.

Give permissions to audit all operations to the  AUD_TEST user.

These options audit all DDL and DML, along with some system events.

–> DDL (CREATE, ALTER & DROP of objects)
–>DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
–>SYSTEM EVENTS (LOGON, LOGOFF etc.)

1
2
3
4
5
6
SYS>>AUDIT ALL BY aud_test by access;
Audit succeeded.
SYS>>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY aud_test by access;
Audit succeeded.
SYS>>AUDIT EXECUTE PROCEDURE BY aud_test by access;
Audit succeeded.

connect to AUD_TEST  and create  a table object then perform DML operations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SYS>>conn aud_test/aud_test
Connected.
AUD_TEST>>create table test_sep(sno number,name varchar2(10));
Table created.
AUD_TEST>>insert into test_sep values(10,'AJAY');
1 row created.
AUD_TEST>>insert into test_sep values(20,'KUMAR');
1 row created.
AUD_TEST>>insert into test_sep values(30,'VINOD');
1 row created.
AUD_TEST>>commit;
Commit complete.
AUD_TEST>>select * from test_sep;
SNO     NAME
------ --------
10     AJAY
20     KUMAR
30     VINOD
AUD_TEST>>update test_sep set name='KTEXPERTS' where sno=20;
1 row updated.
AUD_TEST>>commit;
Commit complete.
AUD_TEST>>select * from test_sep;
SNO      NAME
------  ----------
10       AJAY
20       KTEXPERTS
30       VINOD

In the next section we will look at how we view the contents of the audit trail.

The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SYS>>SELECT view_name FROM dba_views WHERE view_name LIKE 'DBA%AUDIT%'  ORDER BY view_name;
VIEW_NAME
--------------------------------------------------------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_MGMT_CLEANUP_JOBS
DBA_AUDIT_MGMT_CLEAN_EVENTS
DBA_AUDIT_MGMT_CONFIG_PARAMS
DBA_AUDIT_MGMT_LAST_ARCH_TS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_DV_PATCH_ADMIN_AUDIT
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_OLS_AUDIT_OPTIONS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_SA_AUDIT_OPTIONS
DBA_STMT_AUDIT_OPTS
DBA_XS_AUDIT_POLICY_OPTIONS
DBA_XS_AUDIT_TRAIL
DBA_XS_ENB_AUDIT_POLICIES
 
24 rows selected.

The most basic view of the database audit trail is provided by the DBA_AUDIT_TRAIL view, which contains a wide variety of information. The following query displays the some of the information from the database audit trail.

SELECT few of the columns from DBA_AUDIT_TRAIL Data Dictionary View.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SYS>>set pages 500 lines 500
SYS>> COLUMN username FORMAT A10
SYS>> COLUMN owner FORMAT A10
SYS>> COLUMN obj_name FORMAT A10
SYS>> COLUMN extended_timestamp FORMAT A35
SYS>> SELECT username,extended_timestamp,owner,obj_name,action_name FROM dba_audit_trail WHERE owner ='AUD_TEST' ORDER BY timestamp;
USERNAME     EXTENDED_TIMESTAMP                 OWNER       OBJ_NAME   ACTION_NAME
---------- -----------------------------------  ---------- ----------  ----------------------------
AUD_TEST    02-AUG-18 07.03.12.061572 PM +05:30  AUD_TEST    TEST_SEP  CREATE TABLE
AUD_TEST    02-AUG-18 07.03.58.052671 PM +05:30  AUD_TEST    TEST_SEP  INSERT
AUD_TEST    02-AUG-18 07.04.11.411888 PM +05:30  AUD_TEST    TEST_SEP  INSERT
AUD_TEST    02-AUG-18 07.05.03.304228 PM +05:30  AUD_TEST    TEST_SEP  INSERT
AUD_TEST    02-AUG-18 07.05.24.116901 PM +05:30  AUD_TEST    TEST_SEP  SELECT
AUD_TEST    02-AUG-18 07.06.23.669604 PM +05:30  AUD_TEST    TEST_SEP  UPDATE
AUD_TEST    02-AUG-18 07.06.30.958158 PM +05:30  AUD_TEST    TEST_SEP  SELECT

As a DBA we will come to know  which os user and from system IP address user connected to perform the transaction.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SYS>>select OS_USERNAME,USERNAME,USERHOST,OBJ_NAME,TIMESTAMP,SCN from dba_audit_trail where username='AUD_TEST';
 
OS_USERNAME  USERNAME    USERHOST        OBJ_NAME                      TIMESTAMP  SCN
----------   ---------- --------------- ------------------------------ ---------  ----------
oracle       AUD_TEST   dba20.ord.com   DBMS_APPLICATION_INFO          02-AUG-18  1750208
oracle       AUD_TEST   dba20.ord.com   DBMS_APPLICATION_INFO          02-AUG-18  1750210
oracle       AUD_TEST   dba20.ord.com   DBMS_STANDARD                  02-AUG-18  1750257
oracle       AUD_TEST   dba20.ord.com   DBMS_APPLICATION_INFO          02-AUG-18  1752203
oracle       AUD_TEST   dba20.ord.com   DBMS_APPLICATION_INFO          02-AUG-18  1752205
oracle       AUD_TEST   dba20.ord.com                                  02-AUG-18
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1750428
oracle       AUD_TEST   dba20.ord.com   DUAL                           02-AUG-18  1750202
oracle       AUD_TEST   dba20.ord.com   SQLPLUS_PRODUCT_PROFILE        02-AUG-18  1750204
oracle       AUD_TEST   dba20.ord.com   PRODUCT_PRIVS                  02-AUG-18  1750204
oracle       AUD_TEST   dba20.ord.com   SQLPLUS_PRODUCT_PROFILE        02-AUG-18  1750206
oracle       AUD_TEST   dba20.ord.com   PRODUCT_PRIVS                  02-AUG-18  1750206
oracle       AUD_TEST   dba20.ord.com   DUAL                           02-AUG-18  1750212
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1750384
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1750433
oracle       AUD_TEST   dba20.ord.com   DUAL                           02-AUG-18  1752197
oracle       AUD_TEST   dba20.ord.com   SQLPLUS_PRODUCT_PROFILE        02-AUG-18  1752199
oracle       AUD_TEST   dba20.ord.com   PRODUCT_PRIVS                  02-AUG-18  1752199
oracle       AUD_TEST   dba20.ord.com   SQLPLUS_PRODUCT_PROFILE        02-AUG-18  1752201
oracle       AUD_TEST   dba20.ord.com   PRODUCT_PRIVS                  02-AUG-18  1752201
oracle       AUD_TEST   dba20.ord.com   DUAL                           02-AUG-18  1752207
oracle       AUD_TEST   dba20.ord.com   AUD$                           02-AUG-18
oracle       AUD_TEST   dba20.ord.com   SYSTEM_PRIVILEGE_MAP           02-AUG-18
oracle       AUD_TEST   dba20.ord.com   SYSTEM_PRIVILEGE_MAP           02-AUG-18
oracle       AUD_TEST   dba20.ord.com   STMT_AUDIT_OPTION_MAP          02-AUG-18
oracle       AUD_TEST   dba20.ord.com   AUDIT_ACTIONS                  02-AUG-18
oracle       AUD_TEST   dba20.ord.com   DBA_AUDIT_TRAIL                02-AUG-18
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1752218
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1750317
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1750323
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18  1750375
oracle       AUD_TEST   dba20.ord.com                                  02-AUG-18
oracle       AUD_TEST   dba20.ord.com                                  02-AUG-18
oracle       AUD_TEST   dba20.ord.com   TEST_SEP                       02-AUG-18
oracle       AUD_TEST   dba20.ord.com                                  02-AUG-18
oracle       AUD_TEST   dba20.ord.com                                  02-AUG-18

All the information regarding AUDITING stored in sys.aud$.

Simple we can  truncate those information by using TRUNCATE command.

1
2
3
4
SYS>>truncate table sys.aud$;
Table truncated.
SYS>> SELECT username,extended_timestamp,owner,obj_name,action_name FROM dba_audit_trail WHERE owner ='AUD_TEST' ORDER BY timestamp;
no rows selected

In above query the result is no rows selected  because we truncated info from sys.aud$.

 

Thank you ………..

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (7 votes, average: 5.00 out of 5)
Loading...

You might also like...

  • RENAMING INSTANCE

  • Renaming the Oracle Database.

  • Controlfile Multiplexing and De-Multiplexing In Oracle12c

    Controlfile Multiplexing and De-Multiplexing In Oracle12c

Previous Post: INTRODUCTION TO AWS CLOUDFORMATION
Next Post: Fine-Grained Auditing (FGA)

Add Comment Cancel reply

Telegram Group

Please join our Telegram group for future session .

Click here to join Telegram .

Advertisement

SUBSCRIBE NOW


Get Tech articles and other opportunities all into your inbox.

  • Popular
  • Latest
  • Tags
  • DevOps : Maven introduction

    DevOps : Maven introduction

    September 18, 2017
  • Hadoop : What is BIG DATA?

    Hadoop : What is BIG DATA?

    September 25, 2017
  • Oracle : REDOLOG FILE MANAGEMENT

    Oracle : REDOLOG FILE MANAGEMENT

    October 16, 2017
  • Oracle_Apps: Oracle E-Business Suite Architecture Overview for Apps DBA’s

    November 17, 2016
  • Oracle : DATA MASKING (12c)

    Oracle : DATA MASKING (12c)

    September 19, 2017
  • RENAMING INSTANCE

    February 15, 2019
  • Renaming the Oracle Database.

    February 15, 2019
  • Controlfile Multiplexing and De-Multiplexing In Oracle12c

    Controlfile Multiplexing and De-Multiplexing In Oracle12c

    February 15, 2019
  • Different Block Size of ORACLE

    February 13, 2019
  • Storage Management in Oracle -3

    Storage Management in Oracle -3

    February 12, 2019

Tags

12c top n queries about MySQL Adding ASM Disks bigdata big data cassandra cassandra key components cassandra key structures clonning user in oracle DATA DISTRIBUTION AND PRIMARY INDEX Devops Maven Introduction dmts featured Gossip in Cassandra hadoop hadoop what is big data How To Kill runaway processes After Terminating Concurrent Request information_schema InnoDB Invisible columns in oracle lmts Multitenant Database MyISAM MySQL NoSQL oracle 12c masking oracle database link Oracle Database Parameter Oracle Data Guard Oracle Dataguard oracle dba oracle history commands oracle performance tuning Oracle Server architecture shell scripting for DBA SQL caching Steps before patching GI and RDBMS home storage engines in mYSql Teradata Architecture TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION teradata TYPES OF PRIMARY INDEXES Transportable tablespace undo UNIX vprocs and the number of AMPs

Categories

  • Big Data Hadoop (4)
  • Cassandra (12)
    • Cassandra Architecture (6)
    • NoSQL (5)
  • DevOps (3)
    • Maven (1)
  • MySQL (30)
  • MySQL DBA (21)
    • MySQL Architecture (8)
  • Oracle Apps DBA (8)
  • Oracle DBA (132)
    • 12c New Features (13)
    • Goldengate Oracle (1)
    • Load Test (2)
    • Oracle Adhoc SQL Scripts (5)
    • Oracle Architecture (9)
    • Oracle Archivelogs (2)
    • ORACLE ASM (4)
    • Oracle Auditing (2)
    • Oracle Backups (5)
    • Oracle Cloud (2)
    • Oracle Dataguard (9)
    • ORACLE EXPORTS (5)
    • Oracle Fragmentation (1)
    • Oracle Metadata (6)
    • Oracle Networking (1)
    • Oracle Partitioning (3)
    • ORACLE RAC (3)
    • Oracle SQL Loader (1)
    • Oracle_Flashback (1)
    • Oracle_Stats (1)
    • Oracle_Tablespace (5)
    • OS Commands for DBAs (3)
    • Patching (1)
    • Performance Monitoring (9)
    • PFILE AND SPFILE (1)
    • Production Support (14)
    • User Management (1)
  • Public_Articles (10)
  • SQL Server DBA (19)
    • SQL Server – Production Support (16)
    • SQL Server Performance Tuning (2)
    • SQL Server Troubleshooting (2)
  • Teradata (11)
  • Teradata DBA (9)
    • Teradata Architecture (6)
  • Uncategorized (37)
  • UNIX (18)

Recent Comments

  • Ajay Kumar on Oracle installation on AWS Cloud
  • ajay001 on ORACLE DATABASE CREATION (MANUAL)
  • Ajay Kumar on ORACLE DATABASE CREATION (MANUAL)
  • Ajay Kumar on Startup and Shutdown modes in Oracle
  • Swetha on Teradata : TYPES OF PRIMARY INDEXES (UPI AND NUPI)

Archives

  • February 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • January 2018
  • December 2017
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • May 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016

Tags

12c top n queries about MySQL Adding ASM Disks bigdata big data cassandra cassandra key components cassandra key structures clonning user in oracle DATA DISTRIBUTION AND PRIMARY INDEX Devops Maven Introduction dmts featured Gossip in Cassandra hadoop hadoop what is big data How To Kill runaway processes After Terminating Concurrent Request information_schema InnoDB Invisible columns in oracle lmts Multitenant Database MyISAM MySQL NoSQL oracle 12c masking oracle database link Oracle Database Parameter Oracle Data Guard Oracle Dataguard oracle dba oracle history commands oracle performance tuning Oracle Server architecture shell scripting for DBA SQL caching Steps before patching GI and RDBMS home storage engines in mYSql Teradata Architecture TERADATA BASIC TERMINOLOGIES AND SPACE ALLOCATION teradata TYPES OF PRIMARY INDEXES Transportable tablespace undo UNIX vprocs and the number of AMPs

About

This is Vinod Kumar having around 14+ years of experience as Oracle DBA having exposure to RAC,Dataguard,RMAN,GoldenGate,DB Upgrades,Hadoop. Looking forward to sharpen my skill set and adapt new things. Worked for various industries like Banking, financial, Telecom, Product based companies and retail etc.

  • I believe that Knowledge sharing is Knowledge Gaining.
  • Working Hard for Something We Don't Care about is called STRESS.
  • Working Hard for Something we LOVE is called PASSION.

Recent Posts

  • RENAMING INSTANCE

  • Renaming the Oracle Database.

  • Controlfile Multiplexing and De-Multiplexing In Oracle12c

    Controlfile Multiplexing and De-Multiplexing In Oracle12c

  • Different Block Size of ORACLE

  • Storage Management in Oracle -3

    Storage Management in Oracle -3

Archives

  • February 2019 (12)
  • January 2019 (18)
  • December 2018 (16)
  • November 2018 (11)
  • October 2018 (19)
  • September 2018 (14)
  • August 2018 (7)
  • July 2018 (9)
  • June 2018 (11)
  • May 2018 (1)
  • April 2018 (1)
  • March 2018 (5)
  • February 2018 (10)
  • January 2018 (5)
  • December 2017 (4)
  • November 2017 (4)
  • October 2017 (7)
  • September 2017 (5)
  • August 2017 (7)
  • July 2017 (7)
  • June 2017 (1)
  • May 2017 (1)
  • March 2017 (6)
  • February 2017 (3)
  • January 2017 (2)
  • December 2016 (8)
  • November 2016 (35)
  • October 2016 (31)

Get Updates

Subscribe to our newsletter to receive breaking news by email.

Follow us

Website Visitors


Locations of visitors to this page

  • About
  • Privacy Statement
COPYRIGHT © 2017 - KTEXPERTS.COM Back To Top