How to Enable SYS User Auditing in Oracle Database 11g

Enable Auditing for SYSDBA User

This article is basic of enabling auditing for SYS administrative user and Auditing ALTER SYSTEM commands How to enable AUDIT for sys administrative user.

Summery:

1. logon to database with sys user as sysdba
2. Set AUDIT_SYS_OPERATIONS parameter
3. Set AUDIT_TRAIL parameter
4. Set / Specify AUDIT_FILE_DEST Path
5. Restart Database to implement changing 
6. Important Note

Steps:

1. logon to database with sys user as sysdba

2. Set AUDIT_SYS_OPERATIONS parameter to TRUE default value of this           parameter is FALSE

SQL> show parameter AUDIT_SYS_OPERATIONS

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_sys_operations          boolean     FALSE

SQL> alter system set audit_sys_operations=true scope=spfile;

System altered.

This parameter is static means we required to restart database to take effect of this parameter and after enable parameter SYS and user auditing records will be show in database table or view like SYS.AUD$ etc.
But don’t restart database on this step we will restart at the end after set all required parameters.

3.  Set AUDIT_TRAIL parameter as per desired value. default value of this       

     parameter is none

SQL> show parameter AUDIT_TRAIL

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                             string      NONE

SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;

System altered.

4. set AUDIT_FILE_DEST = location path for audit records log file 

default value of this parameter $ORACLE_BASE/ORACLE_SID/adump

IF AUDIT_FILE_DEST parameter does not set or not point to valid location then oracle will create audit file in $ORACLE_HOME/rdbms/audit directory.

5. Restart database to Implement Changing 

 SQL> shu immediate

 Database closed.

 Database dismounted.

 ORACLE instance shut down.

 SQL> startup

 ORACLE instance started.

 Total System Global Area 1653518336 bytes

 Fixed Size                  2253784 bytes

 Variable Size             989858856 bytes

 Database Buffers          654311424 bytes

 Redo Buffers                7094272 bytes

 Database mounted.

 Database opened.

Note:

1. Make sure oracle unified auditing is disable
2. if unified auditing is enable then all audit data/record will not be showing at database table & view level like in: SYS.AUD$, SYS.DBA_COMMON_AUDIT_TRAIL, etc
3. To show record at database level please disable unified auditing and their policies
4. Please check other Post how to enable and disable Unified auditing

Keep Smile 🙂

Fine Grained Auditing in Oracle Database

 

Fine Grained Auditing in Oracle

Prerequesites:

To perform fine grained auditing

unfied auditing must be stoped

Check Unified Auditing

SQL> SELECT * FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

PARAMETER      VALUE      CON_ID

------------------- ----------------- ------------

Unified Auditing     TRUE          0

Disable Unified Auditing

shu immediate;

lsnrctl stop

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

startup 

lsnrctl start

Check Audit Policies

SQL>SELECT object_schema,object_name,policy_owner,policy_name,policy_column,sel,ins,upd,del from dba_audit_policies;

SQL>no rows selected

Check Audit Parameter

show parameter audit;

NAME                                   TYPE         VALUE

-----------------------------          -----------     ------------------------------------------------

audit_file_dest                          string        /u01/app/oracle/admin/orcl2/adump

audit_sys_operations               boolean    TRUE

audit_syslog_level                    string

audit_trail                                  string         DB, EXTENDED

unified_audit_sga_queue_size  integer      1048576

sql> conn scott/scott

SQL> select * from tab;

TNAME      TABTYPE  CLUSTERID

----------      --------------- -----------------

BONUS          TABLE

DEPT             TABLE

EMP               TABLE

SALGRADE   TABLE

SQL> select * from emp;

Create seprate user for audit record monitoring and policy creation

SQL> create user auditmntr identified by auditmntr account unlock;

Give Grants for fine grained auditing to user

SQL> grant create session to auditmntr;

SQL> grant AUDIT_ADMIN to auditmntr identified by auditmntr;

SQL> grant AUDIT_VIEWER to auditmntr identified by auditmntr; 

SQL> grant AUDIT_ADMIN,DBA to auditmntr identified by auditmntr;   --DBA grant is optional

SQL> grant select,update,insert,delete on scott.emp to auditmntr;

SQL> grant select,update,insert,delete on scott.BONUS to auditmntr;

SQL> grant select,update,insert,delete on scott.SALGRADE to auditmntr;

SQL> grant select,update,insert,delete on scott.DEPT to auditmntr;

Note: To create fine-grained audit policies, you must be granted the AUDIT_ADMIN role or the EXECUTE privilege on the DBMS_FGA package.

Note: To view and analyze fine-grained audit data, you must be granted the AUDIT_VIEWER role.

Syntax for Creating a Fine-Grained Audit Policy

The DBMS_FGA.ADD_POLICY procedure syntax is as follows:

DBMS_FGA.ADD_POLICY(

object_schema        IN  VARCHAR2 DEFAULT NULL 

object_name            IN  VARCHAR2,

policy_name            IN  VARCHAR2, 

audit_condition        IN  VARCHAR2 DEFAULT NULL, 

audit_column           IN  VARCHAR2 DEFAULT NULL 

handler_schema      IN  VARCHAR2 DEFAULT NULL, 

handler_module       IN  VARCHAR2 DEFAULT NULL, 

enable                      IN  BOOLEAN DEFAULT TRUE, 

statement_types      IN  VARCHAR2 DEFAULT SELECT,

audit_trail                 IN  BINARY_INTEGER DEFAULT NULL, 

audit_column_opts  IN  BINARY_INTEGER DEFAULT ANY_COLUMNS, 

policy_owner           IN  VARCHAR2 DEFAULT NULL);

object_schema

specifies the schema of the object to be audited.

object_name

 specifies the name of the object to be audited.

policy_name

specifies the name of the policy to be created. Ensure that this name is unique.

audit_condition

specifies a Boolean condition in a row. NULL is allowed and acts asTRUE. See Audits of Specific Columns and Rows for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned. 

example ( audit_condition => 'EMPNO=0', audit_condition => 'DEPARTMENT_ID = 50')

audit_column

specifies one or more columns to audit, including hidden columns.If set to NULL or omitted, all columns are audited. 

These can include Oracle Label Security hidden columns or object type columns. The default, NULL, causes audit if any column is accessed or affected.

enable

enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.

statement_types

specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only. If you want to audit a MERGE operation, then set statement_types to 'INSERT,UPDATE'. The default is SELECT.

audit_trail

If you have migrated to unified auditing, then Oracle Database ignores this parameter and writes the audit records immediately to the unified audit trail. If you have migrated to unified auditing, then omit this parameter.

audit_column_opts   => DBMS_FGA.ANY_COLUMNS,

audit_column_opts   => DBMS_FGA.ALL_COLUMNS,

  

Note: If the audit_column lists more than one column, then you can use the audit_column_opts parameter to specify whether a statement is audited when the query references any column specified in the audit_column parameter or only when all columns are referenced.

Again Check Audit Policies

SQL>SELECT object_schema,object_name,policy_owner,policy_name,policy_column,sel,ins,upd,del from dba_audit_policies;

Create fine grained policy for select

Create audit policy from newly creates user

Connect to the user

conn auditmntr/auditmntr

 

BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'SCOTT',
object_name => 'emp',
policy_name => 'SCOTT_FGA_slct_Plcy',
--audit_condition => 'EMPNO=0',
audit_column => 'ENAME,JOB',
enable => true,
statement_types => 'SELECT',
audit_trail => DBMS_FGA.DB_EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;
/

select * from scott.emp;

select userhost,object_name,db_user,policy_name,sql_text from DBA_FGA_AUDIT_TRAIL;

SELECT * FROM DBA_AUDIT_POLICY_COLUMNS;

Create fine grained policy for update

SQL>BEGIN

DBMS_FGA.ADD_POLICY (

object_schema => 'SCOTT',

object_name => 'emp',

policy_name => 'SCOTT_FGA_updt_Plcy',

--audit_condition => 'ID=1',

audit_column => 'ENAME,job',

enable => true,

statement_types => 'UPDATE',

--audit_trail => DBMS_FGA.DB_EXTENDED,

audit_column_opts => DBMS_FGA.ANY_COLUMNS);

END;

/

Create Policy for SELECT,UPDATE,DELETE,INSERT

SQL> BEGIN

dbms_fga.add_policy(

object_schema   => 'SCOTT',

object_name     => 'EMP',

policy_name     => 'SCOTT_FGA_ALL_AUDIT',

--audit_condition => 'SALARY > 2500',

audit_column    => 'ENAME,JOB,MGR',

enable          => TRUE,

statement_types  => 'SELECT,UPDATE,DELETE,INSERT');

END ;

/  

To Check Audit Record

DBA_COMMON_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

SYS.FGA_LOG$

Enable the Policy

DBMS_FGA.ENABLE_POLICY(

object_schema VARCHAR2,

object_name VARCHAR2,

policy_name VARCHAR2,

enable BOOLEAN);

Disable the Policy

DBMS_FGA.DISABLE_POLICY(

object_schema VARCHAR2,

object_name VARCHAR2,

policy_name VARCHAR2 );

Drop the policy

Note:To drop policies first we have to disable policy then drop it

DBMS_FGA.DROP_POLICY(

object_schema VARCHAR2,

object_name VARCHAR2,

policy_name VARCHAR2 );

Oracle Enterprise Manager Cloud Control 12c Installation on Linux

Install Oracle Enterprise Manager Installation 12.1.0.5 on Linux

STEP 1- Create a database that will be used as repository database. Here I have created a database OEMDB in RAC. So it has two instances OEMDB1 and OEMDB2. Unlock the sysman user and keep the password of your choice.

STEP 2 - Downlaod the Enterprise Manager Cloud Control 12c Release 1 (12.1.0.5) (x86_64) software from oracle.

STEP 3 - If you have performed a default database installation you will need to deconfigure Enterprise Manager Database Control. Run the following command as the "oracle" user.

STEP 4 - Make the following initialization parameter changes and restart the database.

STEP 5 - Make a directory to hold the Middleware installation and for Agent. Copy the downloaded zipped file to the server and Unzip the Cloud Control media, then start the installation by running the "runInstller" script.

STEP 6 - If you wish to receive support information, enter the required details, or uncheck the security updates checkbox and click the "Next" button. Click the "Yes" button the subsequent warning dialog.

STEP 7 - If you wish to check for updates, enter the required details, or check the "Skip" option and click the "Next" button.

STEP 8 - If you have performed the prerequisites as 

described, the installation on linux x86_64 should pass all prerequisite checks. On linux x86_64 there is one failure due "ulimit".This can be ignored by pressing the "Ignore" button, followed by the "Next" button.

STEP 9 - Select the "Create a new Enterprise Manager System" and "Simple" options, enter the middleware home and click the "Next" button.

STEP 10 - Enter the administrator password and database repository details, then click the "Next" button.

STEP 11 -  Ignore the error by clicking "yes" button

STEP 12 - If you are happy with the review information, click the "Install" button.

STEP 13 -  Wait while the installation and configuration take place.

STEP 14 - When prompted, run the root scripts, then click the "OK" button.


STEP 15 - Make note of the URLs, then click the "Close" button to exit the installer. A copy of this information is available in the "/u01/app/oracle/Middleware/oms/install/setupinfo.txt" file

CONVERTING PHYSICAL STANDBY TO SNAPSHOT STANDBY DATABASE

Oracle 11gR2 Convert Physical Standby Database To SNAPSHOT STANDBY DATABASE

Overview:

Part # 1. Convert Physical standby to Snapshot

Part # 2. Revert Snapshot Standby to Physical Standby

Part # 3.  Convert Physical RAC Standby Database to snapshot / Revert to Physical Standby

Summery:

"SNAPSHOT STANDBY allows the standby database to be opened in read-write mode.

When switched back into standby mode, all changes made whilst in read-write mode are lost.

Converting physical standby Database to READ/WRITE mode Snapshot Standby and Revert to Physical standby either single instance or RAC Nodes.

Step 1: Check if Flashback is enabled. If not, enable in mount state in Physical Standby database

i)             SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
-------------------------------------------------------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area/
db_recovery_file_dest_size big integer 5G

ii)            ii) SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> Shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down. 

iv) SQL> startup mount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 162887636 bytes
Database Buffers 41943040 bytes
Redo Buffers 4096000 bytes
Database mounted. 

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

FLASHBACK
---------
YES

Step 2 : Cancel recovery on Standby Database

SQL> alter database recover managed standby database cancel;
Database altered.

Step 3 : Converting Physical Standby database to Snapshot Standby database

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database altered.

Step 4: Shutdown the Standby Database Normal

SQL> shut immediate
ORACLE instance shut down.

Step 5: Startup Standby database Normal

SQL> Startup
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2166288 bytes
Variable Size 2852127216 bytes
Database Buffers 1308622848 bytes
Redo Buffers 12652544 bytes
Database mounted.
Database opened.

Step 6: Check Database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE
-------------------------------------- ---------- ------- -------------------------------
ORCL READ WRITE NONE SNAPSHOT STANDB

Part # 2:

Revert Snapshot Standby to Physical Standby 

Step 1: Check for current database role

SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

Step 2 : Shutdown Snapshot Standby Database

SQL> shut immediate

Step 3 : Startup in mount mode

SQL> Startup mount;

Step 5: Converting Snapshot Standby to Physical Standby Database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Step 6: Shutdown the database

SQL> shut immediate

SQL> startup mount;

Step 8 :Start Recovery on Standby Database

  

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 9 : Check Status Now 

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

 "IF STANDBY DATABASE IS RAC THEN FOLLOW BELOW STEPS"

Part # 3.  Convert Physical RAC Standby Database to snapshot / Revert to Physical Standby

 How to kill Inactive sessions in Oracle Database?

Sometimes there are so many inactive sessions available in the database. Due to these inactive sessions, and database responds slow performance so we need to kill them using the below steps.

Step 1. Find total session details.

SQL> select status,count(*) from v$session group by status;

Step 2. Find sid & serial# for inactive sessions.

SQL> SELECT sid, serial#, status, username FROM v$session where status='INACTIVE';

Step 3. Kill session using below command.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 
If you have multiple sessions for killing, you must prepare the script.
Script for all sessions:
SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;
The script only for Inactive sessions:
SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session 
     where status='INACTIVE';                                                  
The above queries will write the queries for you to kill Inactive sessions 
to makes your task very easy.


SET LINESIZE 250
COL USERNAME FOR A12
COL OBJECT_NAME FOR A10
COL SID FOR A20
COL LOCK_MODE FOR A10
COL CTIME FOR A30
COL MACHINE FOR A25
SELECT S.USERNAME,
SUBSTR(O.NAME, 1, 15) OBJECT_NAME,
DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID) SID,
DECODE(L.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', 
TO_CHAR(L.LMODE)) LOCK_MODE,
  TRUNC(L.CTIME / 3600) || ':' || TRUNC(MOD(L.CTIME, 3600) / 60) || ':' || MOD(L.CTIME, 60) CTIME,
  S.STATUS,
  S.MACHINE,
  S.SQL_ID,
  Q.SQL_TEXT
FROM (SELECT /*+ NO_MERGE */(3-LEVEL) LV,
  INST_ID,
  SID,
  TYPE,
  LMODE,
  CTIME
  FROM (SELECT /*+ NO_MERGE */A.INST_ID,
  A.SID,
  A.TYPE,
  A.LMODE,
  A.REQUEST,
  CASE
  WHEN REQUEST = 0 THEN ID1
  END ID1,
  CASE
  WHEN REQUEST > 0 THEN ID1
  END ID3,
  A.CTIME
  FROM GV$LOCK A
  WHERE A.TYPE <> 'MR') START WITH REQUEST > 0 CONNECT BY PRIOR ID3 = ID1) L,
  GV$SESSION S,
  GV$PROCESS P,
  SYS.OBJ$ O,
  GV$SQL Q
WHERE L.SID = S.SID
AND L.INST_ID = S.INST_ID
AND S.INST_ID = P.INST_ID(+)
AND S.PADDR = P.ADDR(+)
AND S.ROW_WAIT_OBJ# = O.OBJ#(+)
AND L.CTIME >= 1
AND S.SQL_ID = Q.SQL_ID(+)
GROUP BY DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID),
 S.INST_ID, S.USERNAME, O.NAME, L.TYPE, L.LMODE, L.CTIME, S.STATUS,
 S.MACHINE,S.SQL_ID,Q.SQL_TEXT;

Identify SERIAL# of Holder: 
To identify the Serial# against the SID of Holder:

select action, sid, serial#, seconds_in_wait , status , client_identifier, 
logon_time , module  from v$session where sid=<SID of Holder>;

Example:
select action, sid, serial#, seconds_in_wait , status , client_identifier, 
logon_time , module  from v$session where sid=321;

Kill Database Lock:

alter system kill session '<SID>,<SERIAL#>';

alter system kill session '321,6868';