How DBAs Can Stop Data Leaks and Loss: Practical Strategies and Backup Practices

1. Preventing Data Leakage

User Management : Identify and lock unused accounts, verify each account’s purpose, and capture recent activity using the following query:

select b.username, a.sql_id, count(*)
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_users b
where a.user_id = b.user_id
group by b.username, a.sql_id
order by count(*);

Lock accounts that are unclaimed or suspicious.

List unlocked accounts and confirm their purpose with developers.

If no legitimate use is found, lock the account.

User Profiles : Enforce strong password policies by creating a custom verification function (e.g., VERIFY_FUNCTION_11G) using the script @?/rdbms/admin/utlpwdmg.sql. Required checks include minimum length, no username similarity, dictionary avoidance, and inclusion of numbers and letters.

Privilege Management : Apply the principle of least privilege. Grant only CONNECT, RESOURCE, and view‑creation rights to application accounts, restrict dictionary access, and consolidate permissions into roles for easier revocation.

Audit Logging : Enable auditing (AUDIT_TRAIL, AUDIT_SYS_OPERATIONS), move audit tables out of SYSTEM tablespace, store audit files on a dedicated LUN, and use NOAUDIT CREATE SESSION to pause auditing when not needed.

Vulnerability Management : Regularly apply PSU patches, especially those fixing critical security bugs, and stay informed of vendor advisories.

2. Preventing Data Loss

Host‑Level Failures : Use Oracle RAC for active‑active clustering, ensuring that if one node fails, another continues serving the same data files.

Storage Failures : Deploy Data Guard (DG) to protect against disk or controller failures. In a failover, switch the application IP to the standby database; using DNS simplifies the switch.

DG standby can run in READ‑ONLY mode to utilize resources efficiently.

Database Bugs : For archive‑log corruption, enable delayed apply on DG with:

alter database recover managed standby database delay 120 disconnect from session;

The delay is in minutes and allows the standby to lag behind the primary without data loss.

Human Error : Implement change‑control procedures: detailed change plans, peer review, avoid manual command entry, verify IPs before execution, separate production and test windows, and have rollback plans ready.

3. Backup and DR Drills

DBAs should maintain regular backups to ensure peace of mind. For large databases (tens of terabytes), split tablespaces into separate backup sets with SYSTEM and SYSAUX, and perform incremental full backups. Validate backups by restoring a single set to keep verification time reasonable.

Consider multi‑site, multi‑center backup architectures to balance cost and safety.

Q&A Highlights

Account lockouts can be caused by resource limits, profile settings, or expiration (default 180 days).

When a user is locked without password errors, check resource parameters and profile configurations.

Backup recovery can involve delayed standby, and in extreme cases, external data sources may be used for restoration.

Overall, DBAs play a critical role in both preventing data breaches and ensuring data recovery, complementing broader security teams and disaster‑recovery strategies.