Home / Database / Security & Administration

Database Security & Administration

Overview

Database Security & Administration involves protecting database systems from unauthorized access, ensuring data confidentiality, integrity, and availability. Effective security measures are essential for protecting sensitive information and maintaining system reliability.

Database administration encompasses day-to-day management tasks, maintenance, backup and recovery, user management, and security enforcement.

Database Security Fundamentals

1. Authentication

Authentication verifies the identity of users attempting to access the database.

Authentication Methods
Methods:
- Username/password authentication
- Integrated Windows authentication
- Certificate-based authentication
- Multi-factor authentication (MFA)
- Database-level vs OS-level authentication

2. Authorization

Authorization determines what authenticated users are allowed to do. It's enforced through privileges and permissions.

3. Access Control

Controlling who can access what data and operations:

User and Role Management

Creating Users

Create Database User
-- Create user
CREATE USER 'app_user'@'localhost' 
IDENTIFIED BY 'strong_password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE ON company_db.* 
TO 'app_user'@'localhost';

Roles

Roles group privileges together for easier management:

Role Management
-- Create role
CREATE ROLE 'sales_team';

-- Grant privileges to role
GRANT SELECT, INSERT ON Orders TO 'sales_team';
GRANT SELECT ON Products TO 'sales_team';

-- Assign role to user
GRANT 'sales_team' TO 'john_doe';

Privileges and Permissions

Database privileges control what actions users can perform:

Object-Level Privileges

Database-Level Privileges

System-Level Privileges

Principle of Least Privilege

Users should be granted only the minimum privileges necessary to perform their tasks:

Least Privilege Example
Application User:
- Needs to: Read and write to specific tables
- Should have: SELECT, INSERT, UPDATE on those tables only
- Should NOT have: DROP, DELETE, or admin privileges

Read-only User:
- Needs to: Generate reports
- Should have: SELECT on relevant tables/views only
- Should NOT have: INSERT, UPDATE, DELETE

Data Encryption

1. Encryption at Rest

Encrypting data stored on disk to protect against unauthorized physical access:

2. Encryption in Transit

Encrypting data during transmission between client and database server:

SQL Injection Prevention

SQL Injection is a security vulnerability where malicious SQL code is inserted into queries.

Prevention Techniques

SQL Injection Prevention
❌ VULNERABLE (String concatenation):
query = "SELECT * FROM Users WHERE Username = '" + username + "'";

✅ SAFE (Parameterized query):
query = "SELECT * FROM Users WHERE Username = ?";
params = [username];
execute(query, params);

Backup and Recovery

1. Backup Types

Full Backup

Complete copy of entire database at a point in time.

Incremental Backup

Backs up only changes since last backup.

Differential Backup

Backs up changes since last full backup.

Transaction Log Backup

Backs up transaction log for point-in-time recovery.

2. Backup Strategy

Database Administration Tasks

1. Daily Tasks

2. Weekly Tasks

3. Monthly Tasks

Auditing and Logging

Track database activities for security and compliance:

Disaster Recovery

Plan for recovering from catastrophic failures:

Security Best Practices

Common Security Threats

Compliance and Regulations

Database security must comply with regulations:

Next Steps

Review fundamental database concepts in the Database Introduction, or explore design principles in Database Design Process.

← Performance