Security Best Practices

Database security is the practice of protecting data from unauthorised access, accidental exposure, and malicious attack. Oracle provides a deep set of security features — privilege control, auditing, encryption, and data masking — but the features only protect data if they are configured and used correctly. Most database breaches exploit poor access control, excessive privileges, or insufficient monitoring rather than technical vulnerabilities in the software itself.

Principle of Least Privilege

The principle of least privilege means every user, application, and service account is granted only the permissions it needs to do its job — nothing more. This limits the damage an attacker can do with a compromised account and reduces the surface area for accidental data modification. In practice this means no application should connect as SYS or SYSTEM, no reporting user should have INSERT or DELETE, and no single account should own both the application schema and the application login credentials.

-- BAD: application connects as DBA -- has unrestricted access to everything
-- GRANT DBA TO app_user;   -- never do this
-- GOOD: create a dedicated application user with only what it needs
CREATE USER store_app IDENTIFIED BY "Str0ng#Pass2026";
GRANT CREATE SESSION TO store_app;
-- Grant only the specific operations the application performs
GRANT SELECT, INSERT, UPDATE ON dataplexa_store.orders    TO store_app;
GRANT SELECT, INSERT         ON dataplexa_store.customers TO store_app;
GRANT SELECT                 ON dataplexa_store.products  TO store_app;
GRANT EXECUTE ON dataplexa_store.update_category_price    TO store_app;
-- Create a read-only reporting account with access only through a view
-- The view limits which columns are exposed -- no direct table access needed
CREATE OR REPLACE VIEW dataplexa_store.orders_report_v AS
    SELECT order_id, customer_id, order_date, status, total_amount
    FROM   dataplexa_store.orders;
GRANT SELECT ON dataplexa_store.orders_report_v TO reporting_user;
-- Verify: reporting_user has no direct access to the orders table
-- SELECT * FROM dataplexa_store.orders;  -- raises ORA-00942: table or view does not exist

• Never use SYS, SYSTEM, or any DBA account as an application login — these accounts bypass many security controls and their activity is harder to audit
• Views are a powerful security tool — they can expose only specific columns, filter rows, and hide the underlying table structure entirely
• ORA-00942 (table or view does not exist) is what an unauthorised user sees when they try to access an object they have no privilege on — Oracle intentionally gives no information about whether the object exists
• Use strong passwords with mixed case, numbers, and special characters — Oracle 12c and later enforce a default password complexity profile

User Profiles and Password Policies

A profile is a named set of resource limits and password rules assigned to a user. Oracle ships with a DEFAULT profile that applies to all users unless a different one is assigned. Profiles enforce password expiry, complexity, failed login limits, and session resource limits. In production every application and user account should be on a profile with appropriate rules — relying on the DEFAULT profile's out-of-the-box settings is rarely sufficient.

-- Create a profile for application users with strict password rules
CREATE PROFILE app_user_profile LIMIT
    FAILED_LOGIN_ATTEMPTS    5          -- lock account after 5 failed logins
    PASSWORD_LOCK_TIME       1/24       -- lock for 1 hour (expressed as fraction of a day)
    PASSWORD_LIFE_TIME       90         -- password expires after 90 days
    PASSWORD_REUSE_TIME      365        -- cannot reuse a password for 365 days
    PASSWORD_REUSE_MAX       10         -- cannot reuse any of the last 10 passwords
    PASSWORD_GRACE_TIME      7          -- 7-day grace period after expiry before login is blocked
    SESSIONS_PER_USER        3          -- maximum 3 concurrent sessions per user
    IDLE_TIME                30;        -- disconnect idle sessions after 30 minutes
-- Assign the profile to a user
ALTER USER store_app PROFILE app_user_profile;
-- View the profile assigned to users
SELECT username, profile, account_status, lock_date, expiry_date
FROM   dba_users
WHERE  username IN ('STORE_APP', 'REPORTING_USER')
ORDER  BY username;

• PASSWORD_LOCK_TIME accepts fractional days — 1/24 = 1 hour, 1/48 = 30 minutes, 1 = 24 hours
• ACCOUNT_STATUS shows OPEN, LOCKED, EXPIRED, or EXPIRED(GRACE) — monitor it regularly to catch accounts that should be disabled
• The DEFAULT profile has UNLIMITED for most limits in older Oracle versions — always create and assign explicit profiles in production
• Setting IDLE_TIME disconnects sessions that have been inactive for the specified number of minutes — reduces the risk of unattended authenticated sessions being hijacked

Auditing

Oracle's Unified Auditing (introduced in Oracle 12c) records database activity to a single, tamper-resistant audit trail stored in the AUDSYS schema. You define what to audit using audit policies — named rules that capture specific actions, on specific objects, by specific users. The audit trail records who did what, when, from where, and whether it succeeded. Auditing privileged operations and sensitive table access is a compliance requirement in most regulated industries.

-- Create an audit policy that captures all logins and logouts
CREATE AUDIT POLICY logon_audit_policy
    ACTIONS LOGON, LOGOFF;
-- Audit policy for sensitive data access on the customers table
CREATE AUDIT POLICY customers_dml_policy
    ACTIONS SELECT, INSERT, UPDATE, DELETE
    ON dataplexa_store.customers;
-- Audit policy for privileged operations -- captures any use of DBA-level system privileges
CREATE AUDIT POLICY priv_ops_policy
    PRIVILEGES CREATE USER, DROP USER, GRANT ANY PRIVILEGE, ALTER SYSTEM;
-- Enable the policies
AUDIT POLICY logon_audit_policy;
AUDIT POLICY customers_dml_policy;
AUDIT POLICY priv_ops_policy;
-- Query the unified audit trail
SELECT dbusername, action_name, object_name,
       event_timestamp, return_code, unified_audit_policies
FROM   unified_audit_trail
WHERE  event_timestamp >= SYSTIMESTAMP - INTERVAL '1' HOUR
ORDER  BY event_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

• RETURN_CODE = 0 means the action succeeded — a non-zero code means it failed, which can indicate an attempted unauthorised access
• The unified audit trail is written to the AUDSYS schema — it is protected from modification by regular users and survives even if the audited schema is dropped
• Audit policies can be scoped to specific users with BY username — useful for monitoring high-risk accounts without generating excessive audit volume for all users
• In regulated environments (PCI-DSS, HIPAA, GDPR) auditing SELECT on tables containing payment cards, health records, or personal data is typically a compliance requirement

Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts data at rest — the datafiles on disk are stored encrypted so that anyone who gains physical access to the storage media cannot read the data without the encryption key. TDE is transparent to the application: data is decrypted automatically when read through Oracle and encrypted automatically when written. The encryption keys are stored in a wallet (a password-protected keystore) that must be open for the database to read encrypted data.

-- TDE setup requires SYSDBA -- run these as SYS
-- Step 1: create and open the encryption wallet
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/wallet' IDENTIFIED BY "WalletPass#2026";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "WalletPass#2026";
-- Step 2: create the master encryption key
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "WalletPass#2026" WITH BACKUP;
-- Step 3: encrypt a tablespace (encrypts all existing and future datafiles in it)
ALTER TABLESPACE users ENCRYPTION ONLINE ENCRYPT;
-- Encrypt a single column -- useful for specific sensitive columns like SSN or card numbers
ALTER TABLE dataplexa_store.customers
    MODIFY (national_id ENCRYPT USING 'AES256' NO SALT);
-- Check which tablespaces are encrypted
SELECT tablespace_name, encrypted
FROM   dba_tablespaces
WHERE  encrypted = 'YES';

• TDE protects data on disk — it does not protect against a privileged Oracle user who has normal database access; for that, use access control and auditing
• The wallet must be open for the database to read any TDE-encrypted data — if the wallet is not opened after a database restart, encrypted tablespaces are inaccessible
• Column-level encryption with NO SALT allows equality lookups on the encrypted column — SALT (the default) adds randomness that prevents equality searches but is more secure against frequency analysis
• Tablespace-level encryption is preferred over column-level for new schemas — it encrypts everything without requiring schema changes

Lesson Summary

Practice Questions

Quiz

Next up — Performance Tuning — How to identify and resolve the most common performance bottlenecks in Oracle using AWR, ASH, and SQL tuning techniques.