Ugenz Academy/Tutorial

Database Recovery and Security

Learn database recovery techniques, transaction failure handling, backup strategies, database security, encryption, access control, and SQL injection prevention.

19 min read3,664 words

Reading progress

Keep learning

Database Recovery and Security

1. Introduction

Database Recovery and Security are important components of Database Management Systems (DBMS).

A database stores valuable information such as student records, banking transactions, employee details, customer information, and business data. This information must remain available, accurate, and protected.

Two major responsibilities of a DBMS are:

  1. Database Recovery: Restoring the database to a correct and consistent state after failures.
  2. Database Security: Protecting the database from unauthorized access, modification, disclosure, and destruction.

Example

Consider a banking database containing customer account information.

  • If the computer suddenly loses power during a money transfer, database recovery helps restore a consistent state.
  • If an unauthorized person attempts to access customer records, database security mechanisms help prevent unauthorized access.

Both recovery and security are necessary for reliable database management.


2. What You Will Learn

This unit covers the following topics:

  1. Introduction to Database Recovery
  2. Deferred and Immediate Database Updates
  3. Shadow Paging
  4. Backup and Recovery Techniques
  5. Database Security
  6. Database Encryption
  7. Access Control
  8. SQL Injection Prevention

3. Database Recovery

Database recovery is the process of restoring a database to a correct and consistent state after a failure.

Failures may occur because of:

  • Power failure.
  • Hardware malfunction.
  • Software errors.
  • Operating system crashes.
  • Transaction errors.
  • Disk failures.
  • Human mistakes.
  • Natural disasters.
  • Cyberattacks.

A DBMS uses recovery mechanisms to minimize data loss and maintain database consistency.

Objectives of Database Recovery

  1. Restore the database after failure.
  2. Maintain transaction atomicity.
  3. Preserve committed transactions.
  4. Undo incomplete transactions when necessary.
  5. Redo committed changes when necessary.
  6. Reduce data loss.
  7. Maintain database consistency.

4. Types of Database Failures

Database failures are commonly classified into several categories.

4.1 Transaction Failure

A transaction failure occurs when an individual transaction cannot complete successfully.

Possible reasons:

  • Invalid input.
  • Constraint violation.
  • Arithmetic error.
  • Application error.
  • Deadlock victim selection.
  • Explicit rollback.

Example:

T1: BEGIN UPDATE Account SET balance = balance - 500 WHERE account_id = 101 ERROR OCCURS ROLLBACK

The transaction is rolled back to prevent an incomplete update.

4.2 System Crash

A system crash occurs when the DBMS or operating system stops unexpectedly.

Possible reasons:

  • Power outage.
  • Operating system failure.
  • Hardware malfunction.
  • System crash.

Volatile memory contents may be lost, but data stored on non-volatile storage may remain available.

The recovery system uses transaction logs and other mechanisms to restore database consistency.

4.3 Media Failure

Media failure occurs when the physical storage containing database files becomes damaged or unavailable.

Examples:

  • Hard disk failure.
  • SSD failure.
  • Storage corruption.
  • Physical damage to storage devices.

Recovery generally requires restoring backups and applying available transaction logs.

4.4 Communication Failure

Communication failure occurs when a network or communication link fails during a database operation.

This is particularly important in distributed database systems.

Example:

A banking application sends a transfer request to a remote database server, but the network connection fails before the application receives confirmation.

The application may not know whether the transaction committed. It must check the transaction outcome rather than blindly repeating the operation.

4.5 Human Error

Human errors include:

  • Accidentally deleting records.
  • Running an incorrect UPDATE statement.
  • Dropping an important table.
  • Modifying incorrect data.
  • Changing database configurations improperly.

Backups, point-in-time recovery, permissions, and auditing help reduce the impact of human errors.


5. Database Recovery Techniques

Different recovery techniques are used depending on the type of failure and DBMS implementation.

Important techniques include:

  1. Log-based recovery.
  2. Deferred database modification.
  3. Immediate database modification.
  4. Shadow paging.
  5. Backup and restore.
  6. Checkpointing.

These techniques are explained in the following lessons.


6. Database Transaction Log

A transaction log is a record of database operations used to support recovery.

It stores information about transactions and their changes.

A log may contain:

  • Transaction identifier.
  • Data item identifier.
  • Previous value.
  • New value.
  • Transaction start information.
  • Commit information.
  • Abort information.

Example

Suppose transaction T1 changes an account balance from 1000 to 800.

A simplified log may look like:

Log RecordInformation
1<START T1>
2<T1, Account_A, 1000, 800>
3<COMMIT T1>

The previous value (1000) can support undo operations.

The new value (800) can support redo operations.

Important: This is a simplified educational log format. Actual DBMS transaction logs use implementation-specific structures and may include additional information.


7. Undo and Redo Operations

Two important recovery operations are Undo and Redo.

Undo

Undo reverses the effects of a transaction.

It is generally used when a transaction has not committed successfully and its changes must be removed.

Example:

Old balance = 1000 New balance = 800

Undo restores:

Balance = 1000

Redo

Redo reapplies the effects of a committed transaction when necessary.

Example:

Old balance = 1000 New balance = 800

Redo ensures:

Balance = 800

Difference Between Undo and Redo

UndoRedo
Reverses a transaction's changesReapplies a transaction's changes
Restores previous valuesApplies new values
Commonly used for incomplete transactionsCommonly used for committed transactions whose changes need to be reapplied
Supports atomicitySupports durability

A recovery algorithm may use both Undo and Redo depending on its logging and storage strategy.


8. Checkpointing

Checkpointing is a recovery technique in which the DBMS records a known recovery position in its log.

It helps reduce the amount of log information that must be examined during recovery.

Example

Transaction Log: START T1 UPDATE A COMMIT T1 START T2 UPDATE B CHECKPOINT START T3 UPDATE C COMMIT T3 SYSTEM CRASH

During recovery, the DBMS can use checkpoint information to identify which transactions and log records need to be considered.

A checkpoint does not necessarily mean that every dirty page has been written to disk. The exact checkpoint behavior depends on the DBMS recovery architecture.

Advantages

  1. Reduces recovery time.
  2. Helps organize transaction logs.
  3. Makes crash recovery more efficient.
  4. Limits the amount of log scanning in suitable recovery systems.

9. Database Recovery and ACID Properties

Database recovery supports important ACID properties.

ACID PropertyRecovery Contribution
AtomicityIncomplete transactions can be undone
ConsistencyDatabase constraints and rules are preserved
IsolationRecovery works with transaction concurrency mechanisms
DurabilityCommitted changes can be restored after failures

For example, if a banking transfer is interrupted, recovery mechanisms help ensure that the transfer is not left in an inconsistent partial state.


10. Database Recovery in Real-World Applications

ApplicationRecovery Requirement
BankingPreserve account balances and transaction history
E-commerceRecover orders and payment records
Hospital ManagementProtect patient and treatment records
College ManagementRestore student and examination information
Railway ReservationRecover booking and payment information
Inventory ManagementRestore stock and order information

11. Database Security

Database security refers to the techniques and policies used to protect databases against unauthorized access, modification, disclosure, and destruction.

It ensures that only authorized users and applications can perform permitted database operations.

Objectives of Database Security

  1. Protect confidential information.
  2. Prevent unauthorized access.
  3. Maintain data integrity.
  4. Ensure data availability.
  5. Protect against data theft.
  6. Prevent unauthorized modifications.
  7. Monitor database activities.
  8. Support regulatory and organizational requirements.

Example

A college database may contain:

  • Student names.
  • Roll numbers.
  • Addresses.
  • Examination marks.
  • Attendance records.
  • Fee details.

A student should generally be allowed to view permitted personal information but should not be allowed to modify examination marks or access every student's private records.

Database security enforces these restrictions.


12. Common Database Security Threats

12.1 Unauthorized Access

Occurs when a person accesses a database without proper permission.

12.2 SQL Injection

Occurs when untrusted input is improperly included in SQL statements, allowing an attacker to alter the intended query.

12.3 Privilege Abuse

Occurs when an authorized user misuses the permissions granted to their account.

12.4 Data Leakage

Occurs when confidential information is exposed to unauthorized people.

12.5 Malware and Ransomware

Malicious software may steal, encrypt, modify, or destroy database files.

12.6 Insider Threats

An employee or authorized user may intentionally or accidentally misuse access privileges.

12.7 Denial of Service

An attacker may overload database resources or prevent legitimate users from accessing database services.


13. Database Security Mechanisms

Common database security mechanisms include:

  1. Authentication.
  2. Authorization.
  3. Access control.
  4. Encryption.
  5. Auditing and monitoring.
  6. Backup and recovery.
  7. Network security.
  8. Secure application development.

Authentication

Authentication verifies the identity of a user.

Common methods include:

  • Username and password.
  • Multi-factor authentication.
  • Digital certificates.
  • External identity providers.

Authorization

Authorization determines which actions an authenticated user is permitted to perform.

For example:

  • A student can view their permitted records.
  • A teacher can update assigned marks.
  • An administrator can manage database users and system settings.

Authentication answers:

Who are you?

Authorization answers:

What are you allowed to do?


14. Database Encryption

Encryption converts readable information into an encoded form using cryptographic algorithms.

It helps protect data from unauthorized disclosure.

Types of Database Encryption

1. Encryption at Rest

Protects data stored on storage devices.

Examples:

  • Database storage encryption.
  • Disk encryption.
  • Encrypted backups.

2. Encryption in Transit

Protects data while it travels between applications and database servers.

Example:

TLS-protected database connections.

3. Column-Level Encryption

Encrypts selected sensitive database columns.

Example:

Encrypting a confidential identification number stored in a customer table.

4. Application-Level Encryption

An application encrypts sensitive data before storing it in the database.

Encryption Example

Conceptually:

Original Data: Account Number = 123456789 Encryption: Plaintext → Encryption Algorithm → Ciphertext Stored Data: Encrypted Ciphertext

Authorized applications can decrypt the data using the appropriate key.

Encryption vs Hashing

EncryptionHashing
Reversible with the correct keyDesigned to be one-way
Used to protect data confidentialityUsed for verification and integrity-related purposes
Requires encryption/decryption keysUses a hash function
Example: AESExample: SHA-256

Password Security: Passwords should generally be stored using a dedicated password-hashing algorithm such as Argon2id, bcrypt, or scrypt, with appropriate salts and parameters. Ordinary reversible encryption or a fast general-purpose hash alone is not suitable for password storage.


15. Access Control in DBMS

Access control determines which users can access database resources and what operations they can perform.

Common permissions include:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CREATE
  • ALTER
  • DROP
  • EXECUTE

SQL GRANT Command

The GRANT command provides privileges to database users.

Example:

GRANT SELECT, INSERT ON Student TO teacher_user;

This grants the user permission to read and insert records in the Student table, subject to DBMS-specific privilege rules.

SQL REVOKE Command

The REVOKE command removes previously granted privileges.

Example:

REVOKE INSERT ON Student FROM teacher_user;

This removes the INSERT privilege from the user.

Role-Based Access Control (RBAC)

Role-Based Access Control assigns permissions to roles rather than managing every user's permissions independently.

Example:

Database | ├── Admin Role | ├── Create Tables | ├── Update Records | └── Manage Users | ├── Teacher Role | ├── View Students | └── Update Assigned Marks | └── Student Role └── View Own Records

Users receive permissions through their assigned roles.

Advantages of RBAC

  1. Simplifies permission management.
  2. Reduces repeated privilege assignment.
  3. Supports organizational access policies.
  4. Makes user administration easier.
  5. Helps enforce least privilege.

16. Principle of Least Privilege

The principle of least privilege means that users and applications should receive only the permissions necessary to perform their tasks.

Example:

A reporting application that only displays student records should generally receive SELECT permission rather than full database administrator privileges.

Benefits:

  • Reduces unauthorized modifications.
  • Limits damage from compromised accounts.
  • Improves security.
  • Reduces unnecessary access.

17. Database Auditing

Database auditing is the process of recording and reviewing database activities.

It helps identify suspicious behavior and investigate security incidents.

Auditing may record:

  • User login attempts.
  • Data access.
  • Data modification.
  • Privilege changes.
  • Failed authentication.
  • Administrative operations.

Example Audit Table

CREATE TABLE Audit_Log ( log_id INT PRIMARY KEY, username VARCHAR(100), action_name VARCHAR(100), action_time TIMESTAMP );

A database or application can record important events in an audit system.

Note: Production auditing should consider tamper resistance, retention policies, access restrictions, and sensitive-data protection.


18. SQL Injection Prevention

SQL Injection is a security vulnerability that occurs when an application constructs SQL queries by directly combining untrusted input with SQL code.

An attacker may manipulate the input to change the intended query.

Vulnerable SQL Example

Suppose an application uses:

SELECT * FROM Users WHERE username = 'INPUT' AND password = 'INPUT';

If the application directly concatenates user input into a query, an attacker may provide specially crafted input that changes the SQL logic.

For example, an unsafe application might construct:

SELECT * FROM Users WHERE username = 'admin' -- ' AND password = 'anything';

In SQL dialects where -- begins a comment, the rest of the query may be ignored, potentially bypassing the intended password condition.

This example illustrates why directly inserting untrusted input into SQL statements is dangerous.

18.1 Prepared Statements

Prepared statements separate SQL code from user-supplied values.

Instead of building SQL by string concatenation, the application sends the SQL structure and parameter values separately.

Example using parameter placeholders:

SELECT * FROM Users WHERE username = ? AND password = ?;

The application binds user input as parameter values.

The placeholder syntax depends on the database driver and programming language. PostgreSQL drivers commonly use $1, $2, while other drivers use ? or named parameters.

18.2 Parameterized Query Example in Python

Using a Python database interface with ? placeholders:

import sqlite3 connection = sqlite3.connect("college.db") username = input("Enter username: ") password = input("Enter password: ") query = """ SELECT * FROM Users WHERE username = ? AND password = ? """ cursor = connection.cursor() cursor.execute(query, (username, password)) user = cursor.fetchone() if user: print("User found") else: print("Invalid credentials") connection.close()

The input values are treated as data rather than executable SQL syntax.

Important: This example demonstrates parameterized SQL only. Production authentication must store securely hashed passwords and verify them using an appropriate password-hashing library rather than comparing plaintext passwords.

18.3 Additional SQL Injection Prevention Methods

  1. Use parameterized queries.
  2. Avoid dynamic SQL string concatenation with untrusted input.
  3. Validate input according to expected data types and formats.
  4. Apply least-privilege database accounts.
  5. Avoid exposing detailed database errors to users.
  6. Keep database software and drivers updated.
  7. Use secure stored procedure practices.
  8. Perform security testing.
  9. Monitor suspicious database activity.

18.4 Parameterized Queries vs String Concatenation

Parameterized QueriesString Concatenation
Separates SQL code from dataCombines SQL code and user input
Helps prevent SQL injectionCan introduce SQL injection vulnerabilities
Recommended approachAvoid for untrusted input
Supported by common database driversRequires careful handling and is error-prone

19. Database Recovery vs Database Security

Database RecoveryDatabase Security
Restores data after failuresProtects data from unauthorized activities
Focuses on availability and consistencyFocuses on confidentiality, integrity, and availability
Uses logs, backups, and recovery algorithmsUses authentication, authorization, and encryption
Handles crashes and transaction failuresHandles attacks and unauthorized access
Uses undo, redo, and restore operationsUses access controls, auditing, and secure queries

20. Best Practices for Database Recovery and Security

  1. Create regular database backups.
  2. Test backup restoration periodically.
  3. Maintain transaction logs.
  4. Use appropriate recovery and checkpoint mechanisms.
  5. Protect database administrator accounts.
  6. Use strong authentication.
  7. Apply least-privilege permissions.
  8. Encrypt sensitive data.
  9. Use TLS for database connections.
  10. Use parameterized queries.
  11. Monitor and audit database activities.
  12. Keep database software updated.
  13. Restrict unnecessary network access.
  14. Prepare a disaster recovery plan.
  15. Test recovery procedures before an actual failure occurs.

21. Summary

  • Database recovery restores a database after failures.
  • Common failures include transaction, system, media, communication, and human errors.
  • Transaction logs record database operations for recovery.
  • Undo reverses changes, while redo reapplies changes.
  • Checkpointing helps reduce recovery time.
  • Deferred and immediate update techniques are used in log-based recovery.
  • Shadow paging is another recovery technique.
  • Backups help restore databases after storage failures or data loss.
  • Database security protects information from unauthorized access and modification.
  • Authentication verifies user identity.
  • Authorization determines user permissions.
  • Encryption protects sensitive information.
  • Access control manages database privileges.
  • Auditing records important database activities.
  • SQL injection occurs when untrusted input changes the intended SQL query.
  • Prepared statements and parameterized queries help prevent SQL injection.

22. Practice Questions

Short Answer Questions

  1. What is database recovery?
  2. Why is database recovery important?
  3. Define transaction failure.
  4. What is a system crash?
  5. What is media failure?
  6. What is a transaction log?
  7. Differentiate between Undo and Redo.
  8. What is checkpointing?
  9. How does recovery support ACID properties?
  10. What is database security?
  11. Define authentication and authorization.
  12. What is database encryption?
  13. Differentiate between encryption and hashing.
  14. What is access control?
  15. Explain Role-Based Access Control.
  16. What is the principle of least privilege?
  17. What is database auditing?
  18. Define SQL injection.
  19. What are prepared statements?
  20. How can SQL injection be prevented?

Long Answer Questions

  1. Explain database recovery and its objectives.
  2. Describe different types of database failures with examples.
  3. Explain transaction logs and their role in recovery.
  4. Discuss Undo, Redo, and checkpointing techniques.
  5. Explain the importance of database recovery in banking systems.
  6. Discuss common database security threats.
  7. Explain different database encryption techniques.
  8. Describe authentication, authorization, and access control.
  9. Explain Role-Based Access Control with a diagram.
  10. What is SQL injection? Explain its causes and prevention methods.
  11. Discuss database security best practices.
  12. Differentiate between database recovery and database security.

Practical Questions

1. Identify the failure type:

A database server suddenly loses power while transactions are running.

2. Identify the recovery operation:

A transaction updates a balance from 1000 to 800 but fails before committing. The DBMS restores the previous value.

3. Write SQL statements to:

  • Grant SELECT permission on the Student table to teacher_user.
  • Revoke SELECT permission from teacher_user.

4. Identify the security vulnerability:

An application creates SQL statements by directly concatenating a username and password supplied by a user.

5. Explain the security principle:

A reporting application is given SELECT permission but not UPDATE or DELETE permission.


23. Multiple Choice Questions (MCQs)

1. What is the main purpose of database recovery?

A. Increase database size
B. Restore the database after failure
C. Create database users
D. Encrypt all tables

2. Which failure occurs when a hard disk becomes damaged?

A. Transaction failure
B. Media failure
C. Logical query failure
D. Authentication failure

3. Which operation reverses the effects of a transaction?

A. Redo
B. Undo
C. Commit
D. Checkpoint

4. Which operation reapplies committed changes during recovery?

A. Undo
B. Redo
C. Rollback
D. Delete

5. What is the purpose of checkpointing?

A. Create user accounts
B. Reduce recovery work
C. Delete transaction logs immediately
D. Encrypt database tables

6. Which component records transaction operations for recovery?

A. Transaction log
B. User table
C. Index
D. View

7. What does database security primarily protect?

A. Only database size
B. Database information and resources
C. Only SQL syntax
D. Only table names

8. Which process verifies a user's identity?

A. Authorization
B. Authentication
C. Recovery
D. Indexing

9. Which process determines what an authenticated user can do?

A. Authentication
B. Authorization
C. Checkpointing
D. Hashing

10. Which command grants database privileges?

A. REVOKE
B. GRANT
C. DELETE
D. DROP

11. Which command removes previously granted privileges?

A. INSERT
B. UPDATE
C. REVOKE
D. CREATE

12. Which encryption type protects data while it travels over a network?

A. Encryption at rest
B. Encryption in transit
C. Column deletion
D. Data normalization

13. Which method is recommended for storing passwords?

A. Plaintext storage
B. Base64 encoding
C. Dedicated password hashing
D. Reversible encryption only

14. What is SQL injection?

A. A database backup method
B. An attack that manipulates SQL queries through untrusted input
C. A database indexing technique
D. A normalization method

15. Which technique helps prevent SQL injection?

A. String concatenation
B. Parameterized queries
C. Removing all database indexes
D. Disabling transaction logs

MCQ Answer Key

QuestionAnswer
1B
2B
3B
4B
5B
6A
7B
8B
9B
10B
11C
12B
13C
14B
15B

Keep learning. Keep building. Keep growing.

© 2026 Ugenz Academy