**Database Administration & Security

Database access control dictates who can access what data and how. There are several models, each offering different levels of security and flexibility.

• Discretionary Access Control (DAC): The owner of an object (e.g., table) determines who can access it. This provides flexibility but can be vulnerable if owners grant excessive permissions. Example: A user creates a table and then grants SELECT access to another user using the GRANT statement in SQL.

-- User creates a table
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(255), salary DECIMAL(10,2));

-- User grants SELECT access to another user
GRANT SELECT ON employees TO 'another_user'@'localhost';

• Mandatory Access Control (MAC): Access is determined by security labels associated with both data objects and users. The operating system, rather than the user, controls the access. This is generally more secure but less flexible. Common in high-security environments, such as military or government databases. The security labels are handled by the database itself, often through specialized security modules.

• Role-Based Access Control (RBAC): Users are assigned to roles, and permissions are granted to roles. This simplifies permission management, making it easier to manage and audit access rights. RBAC is the most common model used in modern databases.

  • Example: Creating a 'finance_manager' role and granting it select, insert, update and delete access to a 'transactions' table:
    ```sql
    -- Create a role
    CREATE ROLE finance_manager;

  -- Grant permissions to the role
  GRANT SELECT, INSERT, UPDATE, DELETE ON transactions TO finance_manager;

  -- Assign a user to the role
  GRANT finance_manager TO user_finance;
  ```

Database systems provide granular control over user access. Roles are collections of permissions. Permissions specify what actions users (or roles) can perform (e.g., SELECT, INSERT, UPDATE, DELETE). Privileges define the specific resources users can access (e.g., a particular table or view).

• Creating Roles: Use the CREATE ROLE statement.
• Granting Permissions: Use the GRANT statement, specifying the permission, the object, and the user/role.
• Revoking Permissions: Use the REVOKE statement to remove permissions.

• Understanding Privilege Levels: Database systems support several levels of privileges including database-level, schema-level, and table/object-level privileges, offering fine-grained access control.

  Example: Granting a user the ability to select from a specific table, but not update:
  ```sql
  -- Grant SELECT privilege
  GRANT SELECT ON customers TO 'read_user'@'localhost';

  -- Revoke UPDATE privilege
  REVOKE UPDATE ON customers FROM 'read_user'@'localhost';
  ```

Databases are prime targets for attacks. It is critical to understand and mitigate common vulnerabilities.

• SQL Injection (SQLi): Attackers inject malicious SQL code into input fields to gain unauthorized access or manipulate data. Mitigation involves using parameterized queries or prepared statements, input validation, and escaping user-supplied input.

  • Example: A vulnerable query and a parameterized query (safe):
    ```sql
    -- Vulnerable (SQL Injection possible)
    SELECT * FROM users WHERE username = '" + userInput + "' AND password = '" + passwordInput + "';

    -- Parameterized query (Safe)
    -- (Database connection object).prepareStatement("SELECT * FROM users WHERE username = ? AND password = ?");
    ```
    * Cross-Site Scripting (XSS): While primarily a web application vulnerability, improper sanitization of database content displayed on a website can lead to XSS attacks. Mitigation includes output encoding.
    * Weak Passwords and Authentication: Using strong, unique passwords and implementing multi-factor authentication are crucial. Database administrators should enforce password complexity policies and regularly review user accounts.
    * Data Leakage: Sensitive data must be protected using appropriate security measures. Proper data masking and tokenization techniques should be employed to protect sensitive information.
    * Insufficient Logging and Monitoring: Implement comprehensive logging to detect and respond to security incidents. Regularly review logs for suspicious activities. Tools that analyze logs and alert on anomalies are critical.

Encryption protects data confidentiality. Encryption is the process of converting readable data into an unreadable format to prevent unauthorized access. The key is to know who the authorized parties are, and to determine how they can use the keys.

• Data at Rest Encryption: Encrypting data stored in the database. Methods include full database encryption, table-level encryption, or column-level encryption. Most database systems provide built-in encryption features.
  • Example (Column-level encryption using AES with a key stored outside the database – concept only, actual syntax varies by database):
    sql -- (Conceptual, syntax varies) ALTER TABLE customers MODIFY COLUMN credit_card_number VARBINARY(255) ENCRYPTED WITH AES_256 USING KEY 'external_key';
• Data in Transit Encryption: Encrypting data as it travels between the application and the database. This is typically achieved using Transport Layer Security (TLS) or Secure Sockets Layer (SSL).
  • Example: Configuring an SSL connection in the database connection string.
    -- Example Connection String (conceptual): jdbc:mysql://your_db_host:3306/your_db_name?useSSL=true&verifyServerCertificate=false&trustServerCertificate=true