💾Intro to Database Systems Unit 13 – Database Security & Access Control

Key Concepts

• Database security involves protecting the confidentiality, integrity, and availability of data stored in databases
• Access control models (discretionary, mandatory, role-based) define how users are granted or denied access to database resources
• Authentication verifies the identity of users attempting to access the database (username/password, biometrics, smart cards)
• Authorization determines what actions authenticated users are allowed to perform on database objects (read, write, execute)
• Encryption protects sensitive data by converting it into an unreadable format using cryptographic algorithms (AES, RSA)
• Auditing and monitoring track and record database activities to detect and investigate security breaches or unauthorized access attempts
• Best practices include implementing strong passwords, regularly updating software, and properly configuring database settings to minimize security risks

Database Security Basics

• Database security is crucial for protecting sensitive information and ensuring compliance with regulations (HIPAA, GDPR)
• The CIA triad (confidentiality, integrity, availability) forms the foundation of database security principles
  • Confidentiality prevents unauthorized disclosure of data
  • Integrity ensures data remains accurate and unaltered
  • Availability guarantees authorized users can access data when needed
• Threats to database security include unauthorized access, SQL injection attacks, and insider threats
• Implementing a layered security approach with multiple controls (firewalls, access control, encryption) enhances overall database security
• Regular security assessments and vulnerability scans help identify and address potential weaknesses in the database system
• Employee training on security best practices and policies plays a vital role in maintaining database security

Access Control Models

• Discretionary Access Control (DAC) allows object owners to grant or revoke access permissions to other users
  • Flexible but prone to privilege abuse and unauthorized access
• Mandatory Access Control (MAC) enforces access based on predefined security labels assigned to subjects and objects
  • Strict and centrally controlled, commonly used in military and government settings
• Role-Based Access Control (RBAC) grants access based on user roles and their associated permissions
  • Simplifies access management and aligns with organizational structure
• Attribute-Based Access Control (ABAC) uses attributes of subjects, objects, and environment to determine access decisions
  • Highly granular and dynamic, suitable for complex and diverse environments
• Rule-Based Access Control defines access rules based on conditions and actions
  • Allows for fine-grained control and can incorporate business logic
• Implementing the principle of least privilege ensures users have only the minimum necessary access to perform their tasks

Authentication Methods

• Authentication verifies the identity of users before granting access to the database
• Username and password authentication is the most common method
  • Passwords should be strong, regularly updated, and stored securely (hashed and salted)
• Two-factor authentication (2FA) adds an extra layer of security by requiring a second factor (SMS code, hardware token) in addition to the password
• Biometric authentication uses unique physical characteristics (fingerprints, facial recognition) to verify user identity
• Smart cards or security tokens can store cryptographic keys for authentication and provide tamper-resistant storage
• Single sign-on (SSO) allows users to authenticate once and access multiple applications or databases without re-entering credentials
• Implementing account lockout policies and monitoring failed login attempts helps prevent brute-force attacks

Authorization Techniques

• Authorization determines what actions authenticated users can perform on database objects
• Privileges define the specific operations (SELECT, INSERT, UPDATE, DELETE) users are allowed to execute on database objects
• Role-based authorization assigns privileges to roles, which are then granted to users
  • Simplifies privilege management and aligns with job functions
• Fine-grained authorization controls access at the row or column level using policies or views
  • Allows for granular control over sensitive data within tables
• Stored procedures can encapsulate database operations and enforce authorization by executing with the privileges of the procedure owner
• Virtual private databases (VPD) dynamically modify SQL statements to enforce row-level security based on user context
• Regularly reviewing and auditing user privileges helps identify and revoke unnecessary or excessive access rights

Encryption and Data Protection

• Encryption protects data confidentiality by converting it into an unreadable format using cryptographic algorithms
• Symmetric encryption uses the same key for both encryption and decryption (AES, DES)
  • Efficient for large amounts of data but requires secure key management
• Asymmetric encryption uses a pair of keys: public key for encryption and private key for decryption (RSA, ECC)
  • Provides secure key exchange and digital signatures but slower than symmetric encryption
• Transparent Data Encryption (TDE) automatically encrypts data at rest in the database files and backups
  • Protects against unauthorized access to database files on storage media
• Column-level encryption selectively encrypts sensitive columns within a table
  • Allows for granular protection of specific data elements
• Key management systems securely store and manage encryption keys
  • Hardware security modules (HSMs) provide tamper-resistant key storage and cryptographic operations
• Implementing secure communication channels (SSL/TLS) protects data in transit between the database and clients

Auditing and Monitoring

• Auditing involves tracking and recording database activities for security and compliance purposes
• Database audit logs capture events such as user logins, object access, and data modifications
  • Helps detect and investigate security breaches or unauthorized access attempts
• Fine-grained auditing allows for selective auditing of specific tables, columns, or user actions
  • Minimizes performance overhead and focuses on critical data and events
• Audit log analysis tools and techniques (data mining, machine learning) help identify patterns and anomalies indicative of security incidents
• Monitoring database performance metrics (CPU usage, memory consumption) can help detect potential security issues or attacks
• Implementing real-time alerts and notifications for critical security events enables prompt incident response
• Regular review and archival of audit logs is essential for compliance with regulations and forensic investigations

Best Practices and Common Pitfalls

• Implement strong and complex password policies (minimum length, mix of characters, regular updates)
• Regularly patch and update database software to address known vulnerabilities
• Limit database access to only necessary network ports and IP addresses using firewalls
• Implement the principle of least privilege, granting users only the minimum required access
• Regularly review and revoke unnecessary or outdated user accounts and privileges
• Use prepared statements or parameterized queries to prevent SQL injection attacks
• Encrypt sensitive data at rest and in transit using strong encryption algorithms and key management
• Implement multi-factor authentication for database access, especially for privileged accounts
• Regularly perform database backups and test restore procedures to ensure data recoverability
• Conduct regular security assessments and penetration testing to identify and address vulnerabilities
• Educate and train employees on database security best practices and policies
• Monitor database activity and audit logs for suspicious or unauthorized access attempts
• Avoid using default or easily guessable passwords for database accounts
• Do not store sensitive data (passwords, credit card numbers) in plain text
• Avoid excessive privileges and permissions, especially for non-administrative users
• Do not rely solely on perimeter security controls; implement defense-in-depth with multiple layers of security