How to Identify and Stop application or block user Hacking in Oracle Database

Leave a reply

How to Detect and Prevent Oracle Database Hacking

In today’s data-driven world, databases are a prime target for hackers. Oracle databases, being widely used in enterprises, are not immune to such attacks. If you suspect hacking in your Oracle database, taking immediate action is critical to protect your data and trace the malicious activity. In this guide, we’ll explore how to identify suspicious activity, stop it in its tracks, and trace the source. We’ll also cover long-term strategies to prevent future breaches.

Step 1: Identify Suspicious Activity

The first step is to detect the signs of hacking. Oracle provides several tools and views that allow administrators to track active sessions and recent database activities.

Check Active Sessions

Use the V$SESSION view to identify all active sessions in the database. Look for unusual users, programs, or machines:

SELECT SID, SERIAL#, USERNAME, PROGRAM, MACHINE, MODULE, ACTION, STATUS, LOGON_TIME
FROM V$SESSION
WHERE STATUS = 'ACTIVE';
  • USERNAME: The database user performing the activity.
  • PROGRAM: The application or tool being used (e.g., SQL*Plus, JDBC driver, etc.).
  • MACHINE: The host machine making the connection.
  • MODULE and ACTION: The specific application module and action being executed.

Audit Recent Changes

Query the DBA_AUDIT_TRAIL view to check for unauthorized data manipulation (DML) or structural changes (DDL):

SELECT USERNAME, OS_USERNAME, USERHOST, ACTION_NAME, TIMESTAMP, SQL_TEXT
FROM DBA_AUDIT_TRAIL
WHERE ACTION_NAME IN ('INSERT', 'UPDATE', 'DELETE', 'ALTER', 'CREATE', 'DROP')
ORDER BY TIMESTAMP DESC;

This query will show who made changes, what they changed, and when they changed it.

Find Recently Updated Rows

To identify specific rows that were updated, use the ORA_ROWSCN (row-level SCN timestamp):

SELECT ROWID, ORA_ROWSCN, *
FROM schema_name.table_name
ORDER BY ORA_ROWSCN DESC
FETCH FIRST 10 ROWS ONLY;

This is particularly helpful if you suspect unauthorized updates to specific tables.

Step 2: Trace the Suspicious Session

Once you’ve identified a suspicious session, you can trace its activities.

Find SQL Executed by the Session

Use the V$SQL and V$SESSION views to trace SQL queries executed by the session:

SELECT S.SID, S.SERIAL#, S.USERNAME, S.MODULE, Q.SQL_ID, Q.SQL_TEXT
FROM V$SESSION S
JOIN V$SQL Q ON S.SQL_ID = Q.SQL_ID
WHERE S.SID = <suspicious_session_sid>;

Historical Activity

Check the V$SQLAREA view to get a history of SQL executed by a user:

SELECT SQL_ID, SQL_TEXT, PARSING_USER_ID, EXECUTIONS, LAST_LOAD_TIME
FROM V$SQLAREA
WHERE PARSING_USER_ID = (SELECT USER_ID FROM DBA_USERS WHERE USERNAME = '<username>');

Step 3: Stop the Malicious Activity

Once you’ve identified the source of the hacking attempt, take immediate steps to stop it.

Kill the Session

Terminate the session causing harm using the following command:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

If the session persists, use:

ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' POST_TRANSACTION IMMEDIATE;

Lock the User Account

Prevent the malicious user from reconnecting by locking their account:

ALTER USER <username> ACCOUNT LOCK;

Step 4: Block Further Access

Use a Firewall to Block Suspicious IPs

If the malicious activity originates from a specific IP address, block it at the server’s firewall:

sudo iptables -A INPUT -s <suspicious_ip> -p tcp --dport 1521 -j DROP

Restrict Database Access via Listener

Edit the sqlnet.ora file to restrict access to trusted IPs:

tcp.validnode_checking = YES
tcp.invited_nodes = (127.0.0.1, <trusted_ip1>, <trusted_ip2>)
tcp.excluded_nodes = (<suspicious_ip>)

Restart the Oracle listener to apply changes:

lsnrctl stop
lsnrctl start

Step 5: Enable Detailed Auditing

Enable Oracle’s auditing features to log suspicious activity in detail.

Enable Unified Auditing

Track all actions by a specific user:

AUDIT ALL BY <username> BY SESSION;
AUDIT INSERT, UPDATE, DELETE ON schema_name.table_name;

Monitor Suspicious Connections

Use the V$SESSION_CONNECT_INFO view to monitor client IPs:

SELECT CLIENT_IP, USERNAME, STATUS, LOGON_TIME
FROM V$SESSION_CONNECT_INFO;

Step 6: Trace the Source of the Application

Generate an ASH Report

Oracle’s Active Session History (ASH) can help analyze historical session activity:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

Select the timeframe and filter by the suspicious session to get insights into its behavior.

Enable SQL Trace

Enable tracing for the suspicious session:

EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(SID => <sid>, SERIAL# => <serial#>);

Disable tracing after collecting enough data:

EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SID => <sid>, SERIAL# => <serial#>);

Trace files will be available in the USER_DUMP_DEST directory for analysis.

Step 7: Long-Term Measures to Prevent Future Attacks

Enforce Strong Password Policies

Set strict password rules for all database users:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 60;
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX 10;

Follow the Principle of Least Privilege

Limit user privileges to only what is necessary:

REVOKE CREATE ANY TABLE, DROP ANY TABLE FROM <username>;

Regularly Review Logs

Monitor logs in DBA_AUDIT_TRAIL and DBA_SYS_PRIVS to detect unusual activity.

Apply Security Patches

Keep your Oracle database updated with the latest Critical Patch Updates (CPUs).

Conclusion

Preventing and responding to hacking attempts in Oracle databases requires vigilance, quick action, and a robust security strategy. By following the steps outlined in this guide, you can identify suspicious activity, stop malicious users, and secure your database from future attacks. Remember to regularly review your database’s security posture and stay updated with Oracle’s latest best practices.

Leave a Reply