Database logins auditing in oracle

Database login auditing in Oracle involves tracking and monitoring user login activities to ensure database security and compliance with policies and regulations. Oracle provides several ways to audit login activities, including:

1. Standard Auditing
2. Unified Auditing
3. Fine-Grained Auditing (FGA)

1. Standard Auditing

Standard auditing involves setting audit options that can monitor various database activities, including user logins.

Enabling Standard Auditing

First, ensure auditing is enabled. You can check and set the audit_trail parameter to enable standard auditing.

SHOW PARAMETER audit_trail;

-- To enable auditing, if it's not already enabled
ALTER SYSTEM SET audit_trail = DB, EXTENDED SCOPE = SPFILE;
-- Restart the database to apply this change

Auditing User Logins

To audit successful and unsuccessful logins, use the following statements:

-- Audit all successful and unsuccessful logins
AUDIT SESSION;

Viewing Audit Records

Audit records can be viewed in the DBA_AUDIT_SESSION view.

SELECT
  USERNAME,
  USERHOST,
  TERMINAL,
  TIMESTAMP,
  ACTION_NAME,
  RETURNCODE
FROM
  DBA_AUDIT_SESSION;

2. Unified Auditing

Unified Auditing consolidates all auditing into a single, comprehensive auditing framework introduced in Oracle 12c.

Enabling Unified Auditing

Unified Auditing is enabled by default in Oracle 12c and later versions. You can check if Unified Auditing is enabled by running:

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

Creating an Audit Policy

Create an audit policy to monitor login activities.

-- Create an audit policy for logins
CREATE AUDIT POLICY login_audit_policy
  ACTIONS LOGON;

-- Enable the audit policy
AUDIT POLICY login_audit_policy;

Viewing Audit Records

Unified Audit records can be viewed in the UNIFIED_AUDIT_TRAIL view.

SELECT
  DBUSERNAME,
  USERHOST,
  ACTION_NAME,
  EVENT_TIMESTAMP,
  RETURN_CODE
FROM
  UNIFIED_AUDIT_TRAIL
WHERE
  ACTION_NAME = 'LOGON';

3. Fine-Grained Auditing (FGA)

FGA allows for more granular auditing, including specific conditions for auditing login activities.

Creating an FGA Policy

Here is an example of setting up FGA to audit logins to a specific table:

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'HR',
    object_name     => 'EMPLOYEES',
    policy_name     => 'audit_logins_policy',
    audit_condition => NULL,
    audit_column    => NULL,
    handler_schema  => NULL,
    handler_module  => NULL,
    enable          => TRUE
  );
END;

Viewing FGA Audit Records

FGA audit records can be viewed in the DBA_FGA_AUDIT_TRAIL view.

SELECT
  USERNAME,
  OBJECT_NAME,
  POLICY_NAME,
  SQL_TEXT,
  STATEMENT_TYPE,
  EXTENDED_TIMESTAMP
FROM
  DBA_FGA_AUDIT_TRAIL;

Example: Comprehensive Audit Policy

Below is an example of how you might set up a comprehensive audit policy using Unified Auditing to cover login attempts.

Create and Enable Unified Audit Policy

-- Create an audit policy to track logon and logoff activities
CREATE AUDIT POLICY comprehensive_login_policy
  ACTIONS LOGON, LOGOFF;

-- Enable the audit policy
AUDIT POLICY comprehensive_login_policy;

Query the Audit Trail

To see the results of your audit policy:

SELECT  DBUSERNAME,
  USERHOST,
  ACTION_NAME,
  EVENT_TIMESTAMP,
  RETURN_CODE
FROM
  UNIFIED_AUDIT_TRAIL
WHERE
  ACTION_NAME IN ('LOGON', 'LOGOFF');

Summary

• Standard Auditing: Use AUDIT SESSION to track login attempts and view results in DBA_AUDIT_SESSION.
• Unified Auditing: Create audit policies and view results in UNIFIED_AUDIT_TRAIL.
• Fine-Grained Auditing (FGA): Set up specific conditions for auditing and view results in DBA_FGA_AUDIT_TRAIL.

By implementing these auditing techniques, you can monitor database login activities effectively, ensuring that unauthorized access is detected and compliance requirements are met.