/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 20 : ORACLE SECURITY ATTACK SCENARIOS & DEFENSE STRATEGIES
Topics Covered
---------------
1 Privilege escalation attacks
2 Role abuse vulnerabilities
3 SQL injection through PL/SQL
4 PUBLIC privilege abuse
5 Excessive privilege risk
6 Insider threat scenarios
7 Database takeover attacks
8 Security monitoring techniques
9 Defense strategies
10 Enterprise security defense architecture
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Privilege Escalation in Oracle?
*******************************************************************************************/
-- Privilege escalation occurs when a user gains
-- higher privileges than intended.
-- This may happen due to:
-- Incorrect privilege grants
-- Misconfigured roles
-- Insecure PL/SQL procedures
/*******************************************************************************************
QUESTION 2
Example privilege escalation scenario
*******************************************************************************************/
-- User receives privilege with grant option.
GRANT SELECT
ON hr.employees
TO training_user
WITH GRANT OPTION;
-- User can now grant access to others.
GRANT SELECT
ON hr.employees
TO another_user;
/*******************************************************************************************
QUESTION 3
Defense strategy against privilege escalation
*******************************************************************************************/
-- Avoid using WITH GRANT OPTION
-- unless absolutely required.
/*******************************************************************************************
QUESTION 4
Role abuse vulnerability
*******************************************************************************************/
-- Roles may contain powerful privileges.
-- If granted incorrectly,
-- users may gain unintended access.
/*******************************************************************************************
QUESTION 5
Example role misuse
*******************************************************************************************/
GRANT DBA
TO training_user;
-- This grants full administrative privileges.
/*******************************************************************************************
QUESTION 6
Defense strategy for role abuse
*******************************************************************************************/
-- Avoid granting DBA role
-- to application users.
-- Use custom roles instead.
/*******************************************************************************************
QUESTION 7
SQL Injection through PL/SQL
*******************************************************************************************/
-- Dynamic SQL may allow attackers
-- to execute malicious queries.
/*******************************************************************************************
QUESTION 8
Example vulnerable procedure
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE insecure_proc
(
p_table_name VARCHAR2
)
AS
v_sql VARCHAR2(200);
BEGIN
v_sql := 'SELECT * FROM ' || p_table_name;
EXECUTE IMMEDIATE v_sql;
END;
/
/*******************************************************************************************
QUESTION 9
Why is this procedure dangerous?
*******************************************************************************************/
-- User could pass malicious input
-- such as
-- HR.EMPLOYEES WHERE 1=1
/*******************************************************************************************
QUESTION 10
Secure coding defense
*******************************************************************************************/
-- Avoid dynamic SQL when possible.
-- Use bind variables and validation.
/*******************************************************************************************
QUESTION 11
PUBLIC privilege abuse
*******************************************************************************************/
-- PUBLIC grants expose objects
-- to all database users.
/*******************************************************************************************
QUESTION 12
Example PUBLIC privilege
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO PUBLIC;
/*******************************************************************************************
QUESTION 13
Defense against PUBLIC privilege abuse
*******************************************************************************************/
-- Revoke unnecessary PUBLIC privileges.
REVOKE SELECT
ON hr.employees
FROM PUBLIC;
/*******************************************************************************************
QUESTION 14
Excessive privilege risk
*******************************************************************************************/
-- Users accumulate privileges over time.
-- These privileges may never be used.
/*******************************************************************************************
QUESTION 15
Detect excessive privileges
*******************************************************************************************/
SELECT grantee,
privilege
FROM dba_sys_privs;
/*******************************************************************************************
QUESTION 16
Use privilege analysis
*******************************************************************************************/
-- DBMS_PRIVILEGE_CAPTURE helps identify
-- unused privileges.
/*******************************************************************************************
QUESTION 17
Insider threat scenario
*******************************************************************************************/
-- An employee with legitimate access
-- may misuse data.
/*******************************************************************************************
QUESTION 18
Example insider attack
*******************************************************************************************/
-- Employee extracts customer financial data.
/*******************************************************************************************
QUESTION 19
Defense strategy
*******************************************************************************************/
-- Use auditing
-- Monitor access patterns
-- Restrict sensitive privileges
/*******************************************************************************************
QUESTION 20
Database takeover scenario
*******************************************************************************************/
-- Attackers obtain DBA privileges
-- and gain full database control.
/*******************************************************************************************
QUESTION 21
Defense against takeover attacks
*******************************************************************************************/
-- Restrict DBA privileges
-- Enable auditing
-- Monitor login attempts
/*******************************************************************************************
QUESTION 22
Security monitoring query
*******************************************************************************************/
SELECT dbusername,
action_name,
event_timestamp
FROM unified_audit_trail
ORDER BY event_timestamp DESC;
/*******************************************************************************************
QUESTION 23
Detect suspicious login attempts
*******************************************************************************************/
SELECT dbusername,
return_code
FROM unified_audit_trail
WHERE return_code <> 0;
/*******************************************************************************************
QUESTION 24
Fraud detection example
*******************************************************************************************/
SELECT *
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;
/*******************************************************************************************
QUESTION 25
Enterprise security defense architecture
*******************************************************************************************/
-- USERS
-- |
-- v
-- ROLES
-- |
-- v
-- SECURE APIs
-- |
-- v
-- VPD / LABEL SECURITY
-- |
-- v
-- AUDITING & MONITORING
/*******************************************************************************************
QUESTION 26
Layered security defense
*******************************************************************************************/
-- Authentication
-- Authorization
-- Row-level security
-- Auditing
-- Monitoring
/*******************************************************************************************
QUESTION 27
Database security hardening steps
*******************************************************************************************/
-- Lock unused accounts
-- Remove ANY privileges
-- Revoke PUBLIC access
-- Enable auditing
/*******************************************************************************************
QUESTION 28
Regular security audit
*******************************************************************************************/
-- DBAs should periodically review:
-- Roles
-- Privileges
-- Audit logs
-- User accounts
/*******************************************************************************************
QUESTION 29
Enterprise security monitoring tools
*******************************************************************************************/
-- Oracle Enterprise Manager
-- SIEM systems
-- Audit log monitoring
/*******************************************************************************************
QUESTION 30
Oracle security defense summary
*******************************************************************************************/
-- Apply least privilege model
-- Avoid excessive roles
-- Use secure APIs
-- Monitor database activity
-- Enable auditing mechanisms
/*******************************************************************************************
END OF PART 20
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/