Dev.to

Security Triggers in PL/SQL

When implementing security triggers in your PL/SQL, you should focus on the following three critical areas: 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; / 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; / 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.

Feb 24, 2025 - 10:39

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

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;
/

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;
/

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.