Security Triggers in PL/SQL

When implementing security triggers in your PL/SQL, you should focus on the following three critical areas:

---

1. Access Control & Restriction

Prevent unauthorized users from modifying or deleting critical records.

Ensure only specific roles can perform sensitive operations.

Key Fields:

USER (current database user)

ROLE (user's assigned role)

ACTION (INSERT, UPDATE, DELETE)

Example: Prevent Non-Admin Users from Deleting Cargo Data

CREATE OR REPLACE TRIGGER prevent_deletion
BEFORE DELETE ON cargo_shipments
FOR EACH ROW
BEGIN
IF USER NOT IN ('ADMIN_ROLE', 'DBA') THEN
RAISE_APPLICATION_ERROR(-20002, 'Unauthorized deletion attempt detected.');
END IF;
END;
/

---

1. Audit Logging for Compliance & Monitoring

Track changes made to important tables.

Maintain an audit trail for who, what, when of modifications.

Key Fields:

user_name (who made the change)

operation_type (INSERT, UPDATE, DELETE)

timestamp (when the change happened)

Example: Log All Updates on Cargo Shipments

CREATE OR REPLACE TRIGGER log_updates
AFTER UPDATE ON cargo_shipments
FOR EACH ROW
BEGIN
INSERT INTO audit_log (user_name, operation_type, table_name, timestamp)
VALUES (USER, 'UPDATE', 'cargo_shipments', SYSTIMESTAMP);
END;
/

---

1. Data Integrity & Business Rule Enforcement

Ensure critical fields maintain integrity and comply with business rules.

Prevent unauthorized updates to sensitive financial or customer data.

Key Fields:

OLD_VALUE (previous value)

NEW_VALUE (updated value)

condition_check (ensures valid changes)

Example: Prevent Employees from Modifying Their Own Salary

CREATE OR REPLACE TRIGGER prevent_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary <> :OLD.salary AND USER = :OLD.employee_username THEN
RAISE_APPLICATION_ERROR(-20003, 'You cannot modify your own salary.');
END IF;
END;
/

---

Final Takeaway

Whenever you're working with security triggers, make sure to implement at least:
✅ Access Control – Restrict actions based on user roles.
✅ Audit Logging – Track and log database modifications.
✅ Data Integrity Enforcement – Enforce business rules and prevent unauthorized modifications.