Database Security, Recovery, and Introduction to NoSQL

TL;DR

You'll learn how to keep your database safe from unauthorized access, how to get your data back if something goes wrong, and explore a different way of storing data with NoSQL databases. These are critical aspects for any robust database system.

1. The Mental Model

Think of your database as a highly valuable vault. You need strong locks and alarms (security), a plan to rebuild it if it's ever damaged (recovery), and sometimes, a completely different type of vault might be better for certain treasures (NoSQL).

2. The Core Material

Database Security

Photo by panumas nikhomkhai on Pexels

Database security is all about protecting your data from unauthorized access, modification, or destruction. It involves several layers of defense.

• Authentication: Verifies who you are. This usually involves usernames and passwords, but can also include two-factor authentication or biometric scans.
• Authorization: Determines what you're allowed to do once you're authenticated. This is managed through permissions and roles. For instance, an admin role might have full access, while a viewer role can only read data.
• Encryption: Scrambles your data so it's unreadable to anyone without the decryption key. This is crucial for data both "at rest" (stored on disk) and "in transit" (moving across a network).
• Auditing: Keeps a log of who did what, when, and where. This helps detect suspicious activity and provides a trail for forensics if a breach occurs.

Database Recovery

Photo by panumas nikhomkhai on Pexels

No matter how good your security, things can go wrong: hardware failures, software bugs, human error, or even natural disasters. Database recovery is the process of restoring the database to a consistent and correct state after a failure.

The core principle of recovery is backup and restore. You regularly save copies of your data (backups) and, if a failure occurs, you use a backup to bring the database back online.

• Transaction Logs (Journals): Most database systems maintain a transaction log. Every change made to the database is first recorded in this log. If the system crashes, the log can be used to ROLLBACK incomplete transactions (undoing changes) or REDO completed transactions that hadn't yet been written to the main data files. This ensures ACID properties, especially atomicity and durability.
• Recovery Techniques:
  • Deferred Update: Changes are initially written only to the log. The actual database is updated only after a transaction commits. If a crash happens before commit, no changes are lost in the database.
  • Immediate Update: Changes are written to both the log and the database immediately. If a crash happens, the log is used to undo incomplete transactions.
  • Shadow Paging: Instead of updating data in place, a new copy of the page is made and updated. If the transaction commits, the old page is replaced by the new one. If it aborts, the new page is simply discarded.

Introduction to NoSQL

Photo by Miguel Á. Padriñán on Pexels

Traditional relational databases (SQL databases) excel with structured data and ACID compliance. However, for certain use cases, like handling massive amounts of unstructured data, rapidly changing data schemas, or needing extreme scalability, NoSQL ("Not only SQL") databases offer alternatives.

NoSQL databases often relax some of the ACID properties (especially consistency) in favor of BASE properties:
* Basically Available: The system is guaranteed to be available for queries.
* Soft state: The state of the system may change over time, even without input.
* Eventual consistency: All updates will eventually propagate through the system, leading to a consistent state, but there might be a delay.

There are four main types of NoSQL databases:

graph TD
    A["NoSQL Databases"] --> B["Key-Value Store"]
    B --> B1("Examples: Redis, DynamoDB")
    B --> B2("Simple key-value pairs, fast reads/writes.")

    A --> C["Document Database"]
    C --> C1("Examples: MongoDB, Couchbase")
    C --> C2("Stores data as JSON-like documents, flexible schema.")

    A --> D["Column-Family Store"]
    D --> D1("Examples: Cassandra, HBase")
    D --> D2("Stores data in columns, optimized for wide rows.")

    A --> E["Graph Database"]
    E --> E1("Examples: Neo4j, ArangoDB")
    E --> E2("Stores data as nodes and edges, great for relationships.")

Each NoSQL type is optimized for different data models and access patterns, providing flexibility beyond the relational model.

3. Worked Example

Let's imagine you have a web application storing user data in a MongoDB (a Document Database).

A user, "Alice," updates her profile. Here's a simplified look at the security, recovery, and NoSQL aspects:

Security:
1. Authentication: Alice logs in using her username and password. The application verifies these credentials.
2. Authorization: Once logged in, Alice's role (user) is checked. This role allows her to UPDATE her own profile but not other users' profiles.
3. Encryption: Her password (and sensitive data like credit card numbers, if stored) would be encrypted at rest in MongoDB and encrypted in transit (using HTTPS) when she sends it to the server.
4. Auditing: The database system logs that "Alice updated her profile at 2023-10-26 10:30:00 UTC."

Recovery:
1. Backup: Your operations team performs daily backups of the MongoDB database, storing them off-site.
2. Transaction Log (Oplog in MongoDB): MongoDB maintains an "oplog" which is a special capped collection that keeps a rolling record of all operations that modify the data.
3. Failure Scenario: A server crash occurs after Alice updates her profile, but before the changes are fully written to a stable part of the disk.
4. Recovery: When MongoDB restarts, it uses the oplog to replay any committed operations that might not have been fully synced to disk, ensuring Alice's update is preserved. If the crash was severe and data files were corrupted, you'd restore from the latest daily backup and then apply the oplog from the backup time up to the crash time to recover most recent changes.

NoSQL (MongoDB specific):
Alice's profile is stored as a single JSON-like document in a users collection:

{
  "_id": "user123",
  "username": "alice_smith",
  "email": "alice@example.com",
  "preferences": {
    "theme": "dark",
    "notifications": true
  },
  "address": {
    "street": "123 Main St",
    "city": "Anytown",
    "zip": "12345"
  },
  "lastLogin": ISODate("2023-10-26T10:30:00Z")
}

If Alice decides to add a new field, like marketingConsent: true, you don't need to alter a table schema. You simply update her document:

db.users.updateOne(
  { "_id": "user123" },
  { "$set": { "marketingConsent": true } }
)

This schema flexibility is a key advantage of document databases like MongoDB.

4. Key Takeaways

• Database security involves layers like authentication, authorization, encryption, and auditing to protect data.
• Database recovery relies on backups and transaction logs to restore data after failures, ensuring durability.
• NoSQL databases offer alternatives to relational models, optimized for different data types and scalability needs.
• The four main NoSQL types are Key-Value, Document, Column-Family, and Graph, each with distinct advantages.
• NoSQL databases often prioritize availability and eventual consistency over strict ACID properties (like full immediate consistency).

Common Mistakes to Avoid:
- Relying solely on network firewalls for security: Many breaches happen from within, so internal database security is crucial.
- Not testing your recovery plan: A backup isn't useful if you can't restore from it reliably.
- Choosing NoSQL just because it's trendy: Understand its trade-offs; SQL is still excellent for many problems.
- **Ignoring authorization: ** Granting overly broad permissions can lead to significant security vulnerabilities.

5. Now Try It

Think about a simple online store database. Design a basic authorization scheme. List at least three distinct roles (e.g., customer, manager, guest) and for each role, specify what database actions (e.g., READ products, CREATE orders, UPDATE prices, DELETE users) they should be allowed to perform.

What success looks like: A clear, concise list showing each role and its specific, justified permissions.