/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 10 : ORACLE SECURITY HARDENING AND DBA SECURITY AUDIT
Topics Covered
---------------
1 Oracle database hardening
2 Default account risks
3 Privileged account auditing
4 Detecting excessive privileges
5 PUBLIC grants security risk
6 Detecting ANY privileges
7 Password security enforcement
8 Detecting inactive users
9 Production DBA security audit checklist
10 Enterprise security best practices
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Oracle Database Hardening?
*******************************************************************************************/
-- Database hardening is the process of reducing security risks
-- by disabling unnecessary features, accounts, and privileges.
-- Hardening improves protection against
-- unauthorized access and attacks.
/*******************************************************************************************
QUESTION 2
Why is database hardening important in production?
*******************************************************************************************/
-- Production databases contain sensitive information such as:
-- Employee data
-- Financial data
-- Customer records
-- Payroll information
-- Hardening ensures only authorized access.
/*******************************************************************************************
QUESTION 3
What are default Oracle accounts?
*******************************************************************************************/
-- Oracle installation creates several default users.
-- Examples include:
-- SYS
-- SYSTEM
-- DBSNMP
-- OUTLN
-- XDB
-- CTXSYS
/*******************************************************************************************
QUESTION 4
How do we check default accounts?
*******************************************************************************************/
SELECT username,
account_status
FROM dba_users;
/*******************************************************************************************
QUESTION 5
Why default accounts should be locked?
*******************************************************************************************/
-- Default accounts are common attack targets.
-- Best practice:
-- Lock unused accounts.
/*******************************************************************************************
QUESTION 6
Lock unused accounts
*******************************************************************************************/
ALTER USER OUTLN ACCOUNT LOCK;
ALTER USER XDB ACCOUNT LOCK;
/*******************************************************************************************
QUESTION 7
How to detect users with DBA privileges?
*******************************************************************************************/
SELECT *
FROM dba_role_privs
WHERE granted_role='DBA';
/*******************************************************************************************
QUESTION 8
Why DBA role assignment must be restricted?
*******************************************************************************************/
-- DBA role provides unrestricted access.
-- Only trusted administrators should receive it.
/*******************************************************************************************
QUESTION 9
Detect system privileges granted to users
*******************************************************************************************/
SELECT grantee,
privilege
FROM dba_sys_privs;
/*******************************************************************************************
QUESTION 10
What are high-risk privileges?
*******************************************************************************************/
-- ANY privileges are dangerous.
-- Examples
-- SELECT ANY TABLE
-- DROP ANY TABLE
-- EXECUTE ANY PROCEDURE
-- CREATE ANY TABLE
/*******************************************************************************************
QUESTION 11
Find users with ANY privileges
*******************************************************************************************/
SELECT grantee,
privilege
FROM dba_sys_privs
WHERE privilege LIKE '%ANY%';
/*******************************************************************************************
QUESTION 12
Why ANY privileges are dangerous?
*******************************************************************************************/
-- They allow operations on ALL objects
-- regardless of ownership.
/*******************************************************************************************
QUESTION 13
Check PUBLIC grants
*******************************************************************************************/
SELECT *
FROM dba_tab_privs
WHERE grantee='PUBLIC';
/*******************************************************************************************
QUESTION 14
Why PUBLIC grants are risky?
*******************************************************************************************/
-- PUBLIC grants give privileges to
-- every user in the database.
/*******************************************************************************************
QUESTION 15
Example PUBLIC privilege risk
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO PUBLIC;
-- This exposes employee data to all users.
/*******************************************************************************************
QUESTION 16
Revoke PUBLIC access
*******************************************************************************************/
REVOKE SELECT
ON hr.employees
FROM PUBLIC;
/*******************************************************************************************
QUESTION 17
Detect inactive database users
*******************************************************************************************/
SELECT username,
account_status
FROM dba_users
WHERE account_status='OPEN';
/*******************************************************************************************
QUESTION 18
Identify accounts with expired passwords
*******************************************************************************************/
SELECT username,
account_status
FROM dba_users
WHERE account_status LIKE '%EXPIRED%';
/*******************************************************************************************
QUESTION 19
Check password policies
*******************************************************************************************/
SELECT profile,
resource_name,
limit
FROM dba_profiles
WHERE resource_type='PASSWORD';
/*******************************************************************************************
QUESTION 20
Ensure password complexity enforcement
*******************************************************************************************/
-- Profiles should enforce
-- Password expiration
-- Password reuse restriction
-- Failed login lockouts
/*******************************************************************************************
QUESTION 21
Check failed login attempts policy
*******************************************************************************************/
SELECT profile,
resource_name,
limit
FROM dba_profiles
WHERE resource_name='FAILED_LOGIN_ATTEMPTS';
/*******************************************************************************************
QUESTION 22
Check session limits
*******************************************************************************************/
SELECT profile,
resource_name,
limit
FROM dba_profiles
WHERE resource_name='SESSIONS_PER_USER';
/*******************************************************************************************
QUESTION 23
Monitor object privileges
*******************************************************************************************/
SELECT *
FROM dba_tab_privs;
/*******************************************************************************************
QUESTION 24
Monitor role privileges
*******************************************************************************************/
SELECT *
FROM role_sys_privs;
/*******************************************************************************************
QUESTION 25
Detect unused roles
*******************************************************************************************/
SELECT role
FROM dba_roles
WHERE role NOT IN
(
SELECT granted_role
FROM dba_role_privs
);
/*******************************************************************************************
QUESTION 26
Detect objects accessible by many users
*******************************************************************************************/
SELECT table_name,
COUNT(*)
FROM dba_tab_privs
GROUP BY table_name
HAVING COUNT(*) > 10;
/*******************************************************************************************
QUESTION 27
Check database auditing settings
*******************************************************************************************/
SELECT *
FROM dba_stmt_audit_opts;
/*******************************************************************************************
QUESTION 28
Example auditing sensitive table
*******************************************************************************************/
AUDIT SELECT
ON hr.employees
BY ACCESS;
/*******************************************************************************************
QUESTION 29
Disable auditing
*******************************************************************************************/
NOAUDIT SELECT
ON hr.employees;
/*******************************************************************************************
QUESTION 30
Production DBA security audit checklist
*******************************************************************************************/
-- Review DBA role assignments
-- Remove unnecessary ANY privileges
-- Revoke PUBLIC grants
-- Lock unused accounts
-- Enforce strong password policies
-- Monitor privilege usage
-- Enable auditing on sensitive tables
/*******************************************************************************************
QUESTION 31
Enterprise security best practices
*******************************************************************************************/
-- Use least privilege model
-- Use roles for privilege control
-- Avoid direct table access
-- Use PL/SQL APIs
-- Perform periodic security audits
/*******************************************************************************************
QUESTION 32
Oracle security hardening summary
*******************************************************************************************/
-- Lock unused accounts
-- Restrict DBA privileges
-- Remove PUBLIC grants
-- Monitor roles and privileges
-- Enforce password policies
/*******************************************************************************************
END OF PART 10
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.
*******************************************************************************************/