All about oracle database auditing

As your application and database grow, more and more users get connected to the database, and it becomes difficult to manage all the privileges that individual user has granted. So, there is a feature provided by the oracle called AUDITING.

Users with DBA privileges can do a lot of things with the database, it is important to make sure that users with DBA privileges should not harm the database by any means, in order to do it, the security team or database admin can enable different levels of auditing.

Before enabling any auditing, you need to consider the fact that, this is put some extra effort into the database, so try to push it on a minimal basis.

 

Auditing SYSDBA activity

Based on the parameter AUDIT_SYS_OPERATIONS value, SYSDBA activity is tracked at the operating system level audit trail file. If AUDIT_SYS_OPERATIONS is set to TRUE then every statement executed by the user connected as “as sysdba” or “as sysopr”  is audited. Location of audit trail file can be set and monitored by parameter AUDIT_FILE_DEST

 

Database Auditing

Database auditing is controlled using the parameter AUDIT_TRAIL parameter, there are different values associated with it.

 

NONE (FALSE) – database auditing is disabled

OS – auditing will be recorded at the OS level audit trail and location is controlled by audit_file_dest

DB- auditing will be recorded at database table SYS.AUD$

DB_EXTENDED – saves at database level but includes the SQL statements with bind variables

XML- auditing is done at OS level, formatted with XML tag

XML_EXTENDED- formatted as XML tags, includes SQL statements with bind variables

 

Database auditing can be configured by AUDIT commands,

For example

 SQL> audit update any table; 

Audit succeeded. 

SQL> audit select any table by session; 

Audit succeeded. 

SQL>

 Assume, that few users have “update any table” privilege granted, this can be used to harm the database as well apart from regular work.  In order to record what tables are being updated, you can simply turn on auditing for the same.

By default, auditing will generate one row for each auditing violation.

BY SESSION- one record for each session does not matter how many times it violates (DEFAULT)

BY ACCESS- one record for every violation.

 

Auditing can be enabled on specific objects as well, for example

SQL> audit select on SYS.DBA_USERS whenever successful; 

Audit succeeded. 

SQL> 

This statement generates a record for every successful insertion for the table SYS.DBA_USERS

WHENEVER SUCCESSFUL – records only when the insertion is successful

WHENEVER NOT SUCCESSFUL – records only when the insertion is failed

By default- both conditions are recorded.

 

When AUDIT_TRAIL is set as OS or XML, one can check audit records at OS level audit trail file and when AUDIT_TRAIL is set as DB or a similar one, you can fetch records using SYS.AUD$ or you can use the DBA_AUDIT_TRAIL view. There are around 50 columns available to view in the DBA_AUDIT_TRAIL view.

There are a couple of subset views of DBA_AUDIT_TRAIL and can  be used to narrow down the results

DBA_AUDIT_OBJECT,

DBA_AUDIT_STATEMENT

DBA_AUDIT_SESSION

 

Auditing WITH triggers

Auditing enabled using the audit command will only have a single record for each statement, but it will not have the exact statement used to insert the record. Sometimes you may need to watch out for statements executed,

Database triggers will help to capture the statement based on the condition defined. Assume, there is an update trigger defined on the table, you try to update the table, it will simply generate an audit record and put the row in another table defined in trigger.

 

Fine-Grained Auditing (FGA)

Till now, we have discussed auditing at database level or table level, what if you want to capture auditing for only specific rows in table or views, FGA can help you to achieve this.

FGA can be configured through package DBMS_FGA and add FGA audit policy, need to use ADD_POLICY procedure.

To view records, you need to use the DBA_FGA_AUDIT_TRAIL view, generally FGA auditing can be enabled on rows on which data is critical such as salary or budget or revenue.

DBMS_FGA has a lot of procedures and can be used to add, drop, enable policy, disable policy.

 

SQL> desc dbms_FGA

PROCEDURE ADD_POLICY

PROCEDURE DISABLE_POLICY

PROCEDURE DROP_POLICY

PROCEDURE ENABLE_POLICY 

This is all about the database auditing theory part.

Read More

Create User in Oracle Database Pre-12c and 12c

This post will give you brief details about how to create user in oracle database in 10g,11g and 12c

Pre-12c Database (Application to all below 12c ):

creating user is normal and important activity is DBA’s life. below commands are described to create user in databases under 12c.

Granting create session is mandatory as it will allow you to connect to database, assume username is xyz and password is abc123

create user xyz identified by abc123 
default tablespace users
temporary tablespace temp
profile default 
password expire;
grant create session to xyz;

Here

USERS is default tablespace

TEMP is temporary tablespace

“password expire” option will allow user to change the password while first login.

DEFAULT is profile assigned to user

User Creation in 12c Database:

In 12c database we have concept container database and pluggable databases, we should be clear with our idea whether we need to create user in container to pluggable db

In order to create user in container database i.e. user having access to all pluggable databases we need to use c## prefix with username. For example

create user c##xyz identified by abc123;

grant create session to c##xyz;

For creating user under any pluggable database we need to set container database using alter session command, Suppose pluggable database is PDB1

Command to set pluggable database

alter session set container=PDB1;

create user xyz identified by abc123;

grant create session to xyz;

Changing Default ,Temporary Tablespace, Granting Role,Privileges

We can simply perform these operation using alter user command in order to give more access to user as per application requirement

In case we don’t mention anything during create user command oracle will assign default and temporary tablespace as per database’s default settings

Changing users default and temp tablespace

alter user xyz default tablespace <tablespace_name>;

alter user xyz temporary tablespace <temporary tablespace name>;

In case we need to create objects under this user then we are suppose to grant quota on tablespace this can be done using below command

alter user xyz quota unlimited on <tablespace_name>;

granting Roles and Privileges

grant <role_name> to xyz

grant select,insert,update,delete on <table_name> to xyz ;

Verification of user creation  and grants

Select username, profile,account_status from dba_users where username='XYZ';

select grantee,PRIVILEGE from dba_sys_privs where grantee='XYZ';

select grantee,granted_role from dba_role_privs where grantee='XYZ';

Read More