Here’s a practice task and a short quiz on Database Security Basics to reinforce Part 11’s concepts.

📚 This is based on Part 11: Database Security Basics. If you haven’t read it yet, check that out first.

🧪 Practice Task: Setting Up User Roles and Permissions

🎯 Objective:

Create users with specific roles and test their access permissions in both SQL and MongoDB.

🔹 Part A: SQL Practice

Create two users:

reader_user with permission to only read data from a database named SchoolDB.
editor_user with permission to read and write data on the same database.

Test the permissions by running SELECT queries as both users, and attempt to insert data as reader_user (which should fail).

🔹 Part B: MongoDB Practice

Create two users in the library database:

readUser with read-only access.
writeUser with read and write access.

Using the Mongo shell or your MongoDB client, test that:

readUser can query data but cannot insert or update.
writeUser can both query and modify data.

❓ Quiz: Quick Security Check

What SQL command is used to grant specific privileges to a user?

a) CREATE USER
b) GRANT
c) REVOKE
d) ALTER USER
In MongoDB, which role allows both reading and writing to a database?

a) read
b) readWrite
c) dbAdmin
d) clusterAdmin
What is the main purpose of encryption in databases?

a) Speed up queries
b) Protect data confidentiality
c) Organize data in tables
d) Backup data automatically
Which security principle suggests giving users only the permissions they need?

a) Principle of least privilege
b) Separation of duties
c) Data masking
d) Role hierarchies

Next: answer key and explanations for this quiz

← Previous Next →

🔷 Part 11: Database Security Basics – Protecting Your Data

📍 Introduction

In today's digital world, securing your database is critical. Databases hold sensitive data — from personal details to financial records — and must be protected from unauthorized access, breaches, and attacks.

Why it matters: In 2024, over 5 billion records were exposed due to database misconfigurations, weak passwords, and unencrypted connections. Cyberattacks increasingly target databases as a high-value asset, making security a non-negotiable priority.

⚠️ Common Threats to Databases

Understanding threats helps you design better defenses. Common risks include:

SQL Injection: Malicious SQL input used to bypass authentication or access data.
Insider Threats: Employees with excessive privileges may misuse access.
Unencrypted Data: Attackers can intercept sensitive info during transfer or access backups.
Weak Passwords: Easily guessable credentials make brute-force attacks successful.

Best Practice: Use input validation, enforce least privilege principle, set strong passwords, and enable encrypted channels to reduce risk.

This part covers core database security concepts including:

User authentication and access control
Roles and privileges
Data encryption basics

You’ll see how these apply in both SQL and NoSQL systems.

🔸 1. User Authentication and Access Control

What is it?

Authentication verifies who you are — usually by username and password. Access control defines what you can do in the database.

🔹 SQL Example: Creating Users and Granting Permissions

-- Create a user
CREATE USER 'john_doe'@'localhost' IDENTIFIED BY 'securePassword123';

-- Grant privileges
GRANT SELECT, INSERT ON LibraryDB.* TO 'john_doe'@'localhost';

-- FLUSH PRIVILEGES is only needed if you modify privilege tables directly.
-- For GRANT statements, MySQL applies changes immediately.


-- Apply changes
FLUSH PRIVILEGES;

This allows john_doe to read and add data in the LibraryDB database but not delete or update.

🔹 MongoDB Example: User Roles and Authentication

use admin;

// Create user with roles
db.createUser({
  user: "alice",
  pwd: "strongPassword456",
  roles: [
    { role: "readWrite", db: "library" }
  ]
});

alice can read and write data in the library database but not perform admin tasks.

🔸 2. Roles and Privileges

Roles group permissions for easier management.
Assigning users to roles simplifies security policies.

Common SQL Roles:

Role	Typical Privileges
SELECT	Read data
INSERT	Add data
UPDATE	Modify existing data
DELETE	Remove data
DBA	Full admin privileges

MongoDB Built-in Roles:

read: Read-only access
readWrite: Read and write access
dbAdmin: Database admin operations
clusterAdmin: Cluster-wide management

🔸 3. Data Encryption

Encryption protects data at rest and in transit.

At rest: Data files and backups are encrypted.
In transit: Network communication between client and server is encrypted.

🔹 SQL Encryption Examples

Enable TLS/SSL for secure connections.
Use Transparent Data Encryption (TDE) for encrypting database files (supported in SQL Server, Oracle, MySQL Enterprise).

🔹 MongoDB Encryption

Enable TLS/SSL for client-server encryption.
Use Encrypted Storage Engine for data-at-rest encryption (MongoDB Enterprise).
Field-level encryption for sensitive fields.

🔸 4. Monitoring and Auditing

Even with strong access control and encryption, it's essential to monitor database activity for signs of misuse or unauthorized access. Auditing keeps a record of actions taken inside the database.

SQL: Use features like AUDIT logs in Oracle or SQL Server’s SQL Server Audit.
MongoDB: Enable audit logs (Enterprise only) to track user actions, authentication, and configuration changes.

Tip: Regularly review audit logs to detect suspicious activity or policy violations.

📝 Summary

Security Aspect	SQL	MongoDB / NoSQL
User Authentication	CREATE USER + GRANT	db.createUser with roles
Access Control	Privileges & Roles	Roles and privileges
Encryption	TDE, TLS/SSL	Encrypted storage, TLS/SSL
Common Best Practices	Strong passwords, least privilege, regular audits	Same, plus monitoring and backups

✅ Next Steps

In Part 12, we’ll explore Backup and Recovery — essential for protecting your data against loss and corruption.

👉 Ready to test your knowledge? Go to the practice task & Quiz for Part 11

🗣️ How secure is your current database setup? Share your thoughts or questions in the comments below!