Auditing
is the monitoring and recording of selected user database actions. Auditing
allow you to monitor database activities, for example logon activity, database
changes etc. In standard auditing, you use initialization parameters and the
AUDIT and NOAUDIT SQL statements to audit SQL statements, privileges, and
schema objects, and network and multi-tier activities.
Standard
auditing is enabled by setting the AUDIT_TRAIL parameter and is configured with
the AUDIT/NOAUDIT commands. It can write
its records to either the SYS.AUD$ table or to OS files depending on the value
of AUDIT_TRAIL. AUDIT_TRAIL can have the
following values.
AUDIT_TRAIL={NONE
or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}
The
following list provides a description of each value:
NONE
or FALSE -> Auditing is disabled.
DB
or TRUE -> Auditing is enabled, with all audit records stored in the
database audit trial (AUD$).
DB_EXTENDED –> Same 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
–> Same 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 file specified by AUDIT_FILE_DEST.
>>Syntax
of audit command:
audit
{statement_option|privilege_option} [by user] [by {session|access}] [whenever
{successful|not successful}]
Only
the statement_option or privilege_option part is mandatory. The other clauses
are optional and enabling them allows audit be more specific.
>>Auditing
Enable in RAC DB:
Node-1
oracle@dcpdb1
[/home/oracle]$
oracle@dcpdb1
[/home/oracle]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.4.0 Production on Mon Sep 5 18:05:45 2016
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data
Mining and Real Application Testing options
SQL>
SQL>
alter system set audit_trail=DB, EXTENDED scope=spfile sid='*';
System
altered.
SQL>
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
SQL>
SQL>
SQL>
startup
ORACLE
instance started.
Total
System Global Area 8.2309E+10 bytes
Fixed
Size 2214472 bytes
Variable
Size 3.7581E+10 bytes
Database
Buffers 4.4560E+10 bytes
Redo
Buffers 165543936 bytes
Database
mounted.
Database
opened.
SQL>
SQL>
SQL>
SQL>
select open_mode from v$database;
OPEN_MODE
--------------------
READ
WRITE
SQL>
SQL>
SQL>
select inst_name from v$active_instances;
INST_NAME
--------------------------------------------------------------------------------
dcpdb1:dcpdb1
dcpdb2:dcpdb2
SQL>
SQL>
SQL>
SQL>
SQL>
show parameter audit;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
audit_file_dest string /u02/app/oracle/admin/dcpdb/adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB, EXTENDED
SQL>
SQL>
SQL>
SQL>
SQL>
audit all by bishwanath by access;
Audit
succeeded.
SQL>
audit select table,update table,insert table,delete table by bishwanath by
access;
Audit
succeeded.
SQL>
audit all privileges by bishwanath by access;
Audit
succeeded.
SQL>
audit create user,alter user,drop user by bishwanath by access;
Audit
succeeded.
SQL>
audit execute procedure by bishwanath by access;
Audit
succeeded.
SQL>
Node-2:
oracle@dcpdb2
[/home/oracle]$
oracle@dcpdb2
[/home/oracle]$ sqlplus / as sysdba
SQL*Plus:
Release 11.2.0.4.0 Production on Mon Sep 5 18:06:23 2016
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data
Mining and Real Application Testing options
SQL>
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
SQL>
SQL>
startup
ORACLE
instance started.
Total
System Global Area 8.2309E+10 bytes
Fixed
Size 2214472 bytes
Variable
Size 3.7044E+10 bytes
Database
Buffers 4.5097E+10 bytes
Redo
Buffers 165543936 bytes
Database
mounted.
Database
opened.
SQL>
SQL>
SQL>
SQL>
select open_mode from v$database;
OPEN_MODE
--------------------
READ
WRITE
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
select open_mode from v$database;
OPEN_MODE
--------------------
READ
WRITE
SQL>
SQL>
SQL>
SQL>
SQL>
select open_mode from v$database;
OPEN_MODE
--------------------
READ
WRITE
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
show parameter audit;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
audit_file_dest string /u02/app/oracle/admin/dcpdb/ad
ump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB, EXTENDED
SQL>
SQL>
SQL>
SQL>
SQL>
>>"bishwanath"
DB USER Login and activities perform.
bishwanath@dcpdb1
[/home/bishwanath]$ sqlplus
SQL*Plus:
Release 11.2.0.4.0 Production on Mon Sep 5 18:17:15 2016
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Enter
user-name: bishwanath
Enter
password:
Connected
to:
Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With
the Partitioning, Real Application Clusters, Automatic Storage Management,
OLAP,
Data
Mining and Real Application Testing options
SQL>
SQL>
select count(1) from nblt24.fbnk_stmt_entry;
COUNT(1)
----------
58247001
SQL>
SQL>
select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
dcpdb
drpdb
SQL>
>>"bishwanath"
DB USER Activities monitor from SYS user.
SQL>
SQL>
select username,to_char(timestamp,'dd/mm/rrrr hh24:mi:ss')dt,owner,obj_name,action_name,sql_text
from dba_audit_trail where username='BISHWANATH' and trunc(timestamp)=trunc(sysdate)
and action <105 order by timestamp
desc;
USERNAME DT OWNER OBJ_NAME ACTION_NAME SQL_TEXT
----------
------------------- --------------- --------------- ---------------
---------------------------------------------
BISHWANATH
05/09/2016 18:25:04 SYS V_$DATAGUARD_CO SELECT select * from v$dataguard_config
NFIG
BISHWANATH
05/09/2016 18:25:04 SYS V$DATAGUARD_CON SELECT select * from v$dataguard_config
FIG
BISHWANATH
05/09/2016 18:25:04 SYS X$KRSTDGC SELECT select * from v$dataguard_config
BISHWANATH
05/09/2016 18:17:59 NBLT24 FBNK_STMT_ENTRY SELECT select count(1) from nblt24.fbnk_stmt_entry
BISHWANATH
05/09/2016 18:17:31 SYSTEM PRODUCT_PRIVS SELECT SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS
W
HERE (UPPER('SQL*Plus') LIKE
UPPER(PRODUCT)
)
AND ((USER LIKE USERID) OR (USERID = 'PUB
LIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
BISHWANATH
05/09/2016 18:17:31 LOGON
BISHWANATH
05/09/2016 18:17:31 SYSTEM SQLPLUS_PRODUCT SELECT SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS
W
_PROFILE
HERE (UPPER('SQL*Plus') LIKE
UPPER(PRODUCT)
) AND ((USER LIKE USERID) OR (USERID
= 'PUB
LIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
BISHWANATH
05/09/2016 18:17:31 SYSTEM PRODUCT_PRIVS SELECT SELECT
ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VAL
UE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE
(UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND
(USER
LIKE USERID)
BISHWANATH
05/09/2016 18:17:31 SYSTEM SQLPLUS_PRODUCT SELECT SELECT
ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VAL
_PROFILE UE,DATE_VALUE FROM
SYSTEM.PRODUCT_PRIVS WHERE
(UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND
(USER LIKE
USERID)
BISHWANATH
05/09/2016 18:17:31 SYS DUAL SELECT SELECT USER FROM DUAL
BISHWANATH
05/09/2016 18:17:31 SYS DUAL SELECT SELECT DECODE('A','A','1','2') FROM
DUAL
11
rows selected.
SQL>
>>
Disable Auditing
The
NOAUDIT statement turns off the various audit options of Oracle. Use it to
reset statement, privilege and object audit options. A NOAUDIT statement that
sets statement and privilege audit options can include the BY USER option to
specify a list of users to limit the scope of the statement and privilege audit
options.
SQL>
NOAUDIT;
SQL>
NOAUDIT session;
SQL>
NOAUDIT session BY bishwanath, scott;
SQL>
NOAUDIT DELETE ON fbnk_account;
SQL>
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL>
NOAUDIT ALL;
SQL>
NOAUDIT ALL PRIVILEGES;
SQL>
NOAUDIT ALL ON DEFAULT;
Prudent Chartered Accountants is a leading provider of accounting, auditing and management consultancy in the Dubai, UAE. We provide accountancy and auditing services through Middle East. Our professional and qualified team has a wealth of knowledge and experience in areas such as taxation, corporate finance, litigation support, audit assurance and accounting.
ReplyDelete