Building an Oracle Security Health Check Toolkit
After implementing password policies, auditing, encryption, secure backups, patch management, and privilege reviews, one question remains:
How do you verify that your Oracle database is still secure six months from now?
Security hardening is not a one-time task. New users are created, privileges change, applications evolve, and configuration drift occurs over time.
A periodic security health check helps identify these changes before they become security issues.
In this article, we’ll build a practical toolkit that every Oracle DBA can use to assess the security posture of an Oracle Database 19c environment.
What Should a Security Health Check Cover?
A useful security assessment should answer questions such as:
- Are there inactive or unlocked accounts?
- Who has administrative privileges?
- Are password policies enforced?
- Is Unified Auditing enabled?
- Is TDE configured correctly?
- Are backups encrypted?
- Are unnecessary database links still present?
- Are Scheduler jobs secure?
- Is the database patched?
- Are critical security components properly configured?
The objective is not only to collect information but also to identify deviations from your organization’s security standards.
Step 1 – Verify Database Version and Patch Level
Start by confirming the database version.
SELECT banner_full FROM v$version;
Review SQL patch history.
SELECT patch_id, status, description FROM dba_registry_sqlpatch ORDER BY action_time DESC;
Ensure the database is running a supported Release Update.
Step 2 – Review Administrative Accounts
Identify users with the DBA role.
SELECT grantee FROM dba_role_privs WHERE granted_role='DBA' ORDER BY grantee;
Review password file users.
SELECT * FROM v$pwfile_users;
Every privileged account should have a documented owner and business purpose.
Step 3 – Review User Accounts
Identify locked, expired, and inactive accounts.
SELECT username, account_status, profile, last_login FROM dba_users ORDER BY username;
Pay attention to:
- Accounts that have never logged in.
- Expired accounts that remain unlocked.
- Accounts belonging to former employees or retired applications.
Step 4 – Review Password Policies
Check password parameters.
SELECT profile, resource_name, limit FROM dba_profiles WHERE resource_type='PASSWORD' ORDER BY profile, resource_name;
Confirm that:
- Password complexity is enforced.
- Failed login attempts are limited.
- Password reuse restrictions are configured.
Step 5 – Review Powerful Privileges
Identify ANY system privileges.
SELECT grantee, privilege FROM dba_sys_privs WHERE privilege LIKE '%ANY%' ORDER BY grantee;
Review object grants to PUBLIC.
SELECT owner, table_name, privilege FROM dba_tab_privs WHERE grantee='PUBLIC';
Privileges should follow the principle of least privilege.
Step 6 – Verify Unified Auditing
Confirm Unified Auditing is available.
SELECT parameter, value FROM v$option WHERE parameter='Unified Auditing';
Review enabled audit policies.
SELECT policy_name, enabled_option FROM audit_unified_enabled_policies;
Verify that key administrative actions are audited.
Step 7 – Verify TDE Configuration
Check wallet status.
SELECT wallet_type, status FROM v$encryption_wallet;
Review encrypted tablespaces.
SELECT TS#, encryptionalg FROM v$encrypted_tablespaces;
Confirm that wallet backups are included in your disaster recovery procedures.
Step 8 – Review RMAN Configuration
Connect to RMAN and review the configuration.
SHOW ALL;
Confirm:
- Control file autobackup is enabled.
- Backup encryption is configured.
- Retention policy is appropriate.
Validate recoverability on a regular schedule.
Step 9 – Review Database Links
List all database links.
SELECT owner, db_link, username, host FROM dba_db_links ORDER BY owner;
Look for:
- PUBLIC database links.
- Links using administrative accounts.
- Obsolete or undocumented links.
Step 10 – Review Scheduler Jobs
Review Scheduler jobs.
SELECT owner, job_name, enabled, state FROM dba_scheduler_jobs;
Review Scheduler credentials.
SELECT owner, credential_name FROM dba_scheduler_credentials;
Ensure external jobs are documented and use dedicated operating system accounts.
Step 11 – Review Directory Objects
Directory objects provide access to the operating system.
SELECT directory_name, directory_path FROM dba_directories;
Review directory privileges.
SELECT grantee, table_name, privilege FROM dba_tab_privs WHERE type='DIRECTORY';
Remove unused directory objects whenever possible.
Step 12 – Review Data Pump Security
Identify users with Data Pump administrative roles.
SELECT grantee, granted_role FROM dba_role_privs WHERE granted_role LIKE 'DATAPUMP%';
Verify that dump files:
- Are encrypted.
- Have restricted operating system permissions.
- Follow your retention policy.
Step 13 – Review Installed Components
Identify installed Oracle components.
SELECT comp_name, status FROM dba_registry ORDER BY comp_name;
Review optional components that may no longer be required.
Step 14 – Generate a Security Summary
The final step is to summarize your findings.
A simple report might include:
| Category | Status | Comments |
|---|---|---|
| Patch Level | PASS | RU up to date |
| Administrative Accounts | WARNING | One undocumented DBA account |
| Password Policies | PASS | Secure profile applied |
| Unified Auditing | PASS | Standard policies enabled |
| TDE | PASS | Wallet open, encrypted tablespaces configured |
| RMAN | PASS | Encrypted backups enabled |
| Database Links | WARNING | Two PUBLIC database links require review |
| Scheduler | PASS | No external jobs |
| Data Pump | PASS | Directory permissions verified |
| Optional Components | INFO | XML DB enabled for APEX |
This report provides a concise view of the database’s security posture and highlights areas requiring attention.
Automating the Health Check
As your environment grows, running individual SQL statements becomes time-consuming.
Consider creating a single SQL*Plus script that:
- Executes all health check queries.
- Formats the output consistently.
- Spools results to a timestamped report.
- Returns PASS, WARNING, or FAIL for each control.
This approach makes regular security reviews faster and easier to compare over time.
Security Checklist
Before considering your security assessment complete, verify that:
- Administrative accounts are documented.
- Password policies meet organizational standards.
- Privileges follow the principle of least privilege.
- Auditing is enabled and reviewed.
- TDE protects sensitive data.
- RMAN backups are encrypted and tested.
- Database links are reviewed.
- Scheduler jobs are validated.
- Data Pump exports are protected.
- The latest Release Update is installed.
Common Mistakes
Some recurring issues include:
Running Security Reviews Only Before Audits
Security assessments should be part of routine database administration, not just compliance exercises.
Collecting Data Without Acting on It
A report has little value if its findings are never addressed.
Ignoring Low-Risk Findings
Several small issues can combine into a significant security exposure.
Not Tracking Changes Over Time
Comparing reports from different months often reveals configuration drift that would otherwise go unnoticed.
Conclusion
A security health check is more than a collection of SQL queries. It is a structured process for verifying that the controls you implemented are still working as intended.
Regular reviews help detect privilege creep, configuration drift, forgotten accounts, and outdated security settings before they become serious problems.
Google+
Facebook
