Module IV
Database Security
THREATS, DEFENSE, PL/SQL, AND LOCKING
Security and Integrity Threats
Common Security Threats
• Unauthorized Access
Accessing data without proper credentials.
• SQL Injection
Malicious input to execute unintended SQL commands.
• Privilege Abuse
Misuse of granted permissions by authorized users.
• Data Tampering
Unauthorized modification of data.
• Malware Attacks
Viruses or scripts targeting the database system.
• Loss of Data Integrity
Due to bugs, crashes, or unauthorized changes.
Data Integrity Threats
• Invalid Data Entries
Input values that violate business rules.
• Inconsistent Data Updates
Conflicts during concurrent transactions.
• Failure of Integrity Constraints
Violation of primary key, foreign key, or check constraints.
• Security and integrity threats can compromise confidentiality,
accuracy, and trustworthiness of data.
Defense Mechanisms
✅ 1. Authentication
• Verifies user identity.
• Methods: Passwords, Biometrics, Multi-Factor Authentication (MFA)
✅ 2. Authorization
• Controls access to data based on roles/permissions.
• Example: Role-Based Access Control (RBAC)
✅ 3. Encryption
• Protects data from unauthorized reading.
• Types:
• At-Rest Encryption: Stored data
• In-Transit Encryption: Data during transfer (e.g., TLS/SSL)
✅ 4. Auditing and Logging
• Tracks user activities and changes.
• Useful for forensics, compliance, and anomaly detection.
✅ 5. Database Firewalls
• Protects against:
• SQL injection
• DoS attacks
• Filters unauthorized or abnormal queries.
✅ 6. Backup & Recovery Systems
• Ensures data can be restored after:
• Hardware failure
• Human error
• Cyberattack
Goal: Protect confidentiality, integrity, and availability of
data.
Statistical Database Auditing &
Control
Statistical Databases (SDBs)
•Provide aggregate data only (e.g., AVG, SUM, COUNT).
•Used in applications like census, healthcare, and finance.
•Designed to protect individual records.
Auditing in Statistical DBs
• Monitors query behavior over time.
• Detects inference attacks:
• Attempts to deduce private data from statistical queries.
Control Methods to Prevent Data
Leakage
🔸 1. Query Size Restrictions
•Block queries with too few records (e.g., WHERE age = 25 and only one record
exists).
🔸 2. Randomized Perturbation
•Add small random noise to results to obscure exact values.
🔸 3. Query-Set-Size Control
•Limit access based on how many times or how narrowly users can filter data.
Security Issues – Granting &
Revoking Privileges
SQL Privilege Commands:
GRANT SELECT, INSERT ON employees TO user1;
REVOKE INSERT ON employees FROM user1;
Security Issues:
•Granting too many privileges
•Public access to sensitive tables
•Users re-granting privileges (use WITH GRANT OPTION
cautiously)
Introduction to Statistical
Database Security
• Focuses on protecting aggregate data while allowing
statistical queries.
• Challenge: Preventing inference attacks (e.g., deducing
individual data from statistics).
Techniques:
• Noise addition
• Query-set-size control
• Differential privacy (modern approach)
PL/SQL Security Features
🔐 PL/SQL Supports:
• Definer's rights vs. Invoker's rights
• Privilege escalation control
• Secure packages and procedures
PL/SQL Security Capabilities
1. Definer’s Rights vs. Invoker’s Rights
• Definer’s Rights (Default)
• Code runs with the privileges of the creator.
• Useful when accessing secured internal logic.
• Invoker’s Rights
• Code runs with the privileges of the caller.
• Useful for shared utility code across users.
2. Privilege Escalation Control
• Prevent users from using procedures to gain access to
unauthorized data.
• Restrict access using:
• Role-based privileges
• Access control lists (ACLs)
3. Secure Packages and Procedures
• Hide business logic using wrapped PL/SQL code (obfuscation).
• Restrict execution and modification rights with:
GRANT EXECUTE ON package_name TO user;
Locks in PL/SQL
Locks Prevent:
•Dirty reads
•Lost updates
•Inconsistent retrieval
Types of Locks:
•Implicit Locks: Automatically acquired by DBMS during DML
•Explicit Locks: Acquired manually using LOCK TABLE, SELECT ... FOR UPDATE
Lock Levels:
•Row-level
•Table-level
Explicit LockingBEGIN
SELECT salary INTO :salary FROM employees
WHERE employee_id = 101 FOR UPDATE;
END;
Oracle’s Named Exception
Handlers
What Are Exception Handlers?
• Handle runtime errors during PL/SQL execution.
• Improve program stability and error reporting.
Predefined Exceptions in Oracle
Exception Name Trigger Condition
NO_DATA_FOUND SELECT returns no rows
TOO_MANY_ROWS SELECT returns more than one row
ZERO_DIVIDE Division by zero attempted
Example: Using Predefined
Exceptions
BEGIN
SELECT salary INTO v_salary FROM employees WHERE emp_id =
100;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple employees found.');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero error.');
END;
Why Use Them?
• Avoids abrupt termination of code.
• Provides custom error messages.
• Maintains control flow even when issues occur.
THANK
YOU
