Database Security and Auditing Quiz

Database Security

• Database security involves legal, ethical, policy, and technical dimensions
• Primarily aims to prevent unauthorized access to sensitive data
• Common threats include loss of integrity, availability, and confidentiality
• Strategies for protecting databases include access control, inference control, flow control, and data encryption
• Database Administrators (DBAs) are responsible for granting privileges, ensuring overall system security, and managing user accounts
• Access control manages user accounts, login verification, and audit trail creation
• Discretionary Access Control (DAC) and Mandatory Access Control (MAC) are two types of access controls

Access Control

• Objective: Only authorized users can access the database.
• How it works (in databases): Establishment of rules and permissions for authorized users for data access and modification.
• Methods: Use of user IDs and passwords to manage permissions.
• Example: University database where professors can modify grades and course materials but students can only view them.

Inference Control

• Definition: Prevents sensitive information extraction (inference) from publicly available data.
• Why is it important: Protects private data even when some parts are public.
• Example: Publishing salary data of an organization without individual names. Risk of individuals inferring salaries based on other publicly available data. Inference control masks or aggregates data to prevent salary inference.

Flow Control

• Definition: Prevents unauthorized access from reaching unauthorized users.
• Measures: Setting rules and restrictions on data paths to limit data flow to authorized users.
• Methods: Encrypting data to prevent unauthorized viewing and encryption of documents stored in a shared memory system.
• The Importance of flow control: Ensures sensitive data remains within authorized pathways.

Data Encryption

• Definition: Method to protect sensitive data (like credit card information) by transforming it into an unreadable format.
• Data transformation: Converting data using an encryption algorithm.
• Decryption: Conversion of encrypted data back to its original form using a decryption key.
• Importance: Protects sensitive information during transmission or storage, preventing theft or unauthorized access.
• Example: Encrypting credit card information during online transactions.

Types of Database Security Mechanisms

• Discretionary Security Mechanisms (DSM): Flexible, and controlled by administrators. Allows assignment of specific access rights to users.
• Example: Professors can alter course grades but students can only see. It is administered by DBAs who give and take away these privileges.
• Mandatory Security Mechanisms (MSM): Strict security mechanisms, enforced by predefined rules based on data sensitivity and user clearance levels. Example: Classified government information only accessible to users with matching or higher clearance levels.

Database Security and the DBA

• Definition of DBA: Central authority responsible for managing the database system.
• Key Responsibilities: Assign privileges to users based on roles, classify data and users based on sensitivity, enabling secure data handling and access at appropriate levels

Access Control, User Accounts, and Database Audits

• DBA manages security through access control, user accounts, and database audits.
• User account creation: DBAs create unique IDs and passwords to manage access.
• User logins: Users log in with their IDs and passwords.
• DBMS verification: The DBMS verifies login credentials for accurate access.
• Database audits: System logging of every user action, useful for security and error diagnosis.

Discretionary Access Control

• Based on the granting/revoking of privileges on an object.
• Command: GRANT and REVOKE statements.

Mandatory Access Control

• A stringent security measure classifying data and users.
• Data and users are labeled with security levels.
• Users can access data only if their security level matches or is higher than the data's security level.

Comparing Discretionary and Mandatory Access Control

Advantages of Discretionary Access Control (DAC):

• Flexible and customizable. Administrators can control access easily. • Suitable for diverse application needs.

Disadvantages of Discretionary Access Control (DAC):

• Security risk: Easily susceptible to attacks if security measures are weak or misused.

Advantages of Mandatory Access Control (MAC):

• Ensures high protection. • Strict about who can access data and how. • Suitable in environments where data security is critical

Disadvantages of Mandatory Access Control (MAC):

• Rigid and strict structure. • Not ideal for dynamic environments with frequently changing access requirements.

Database Performance Tuning and Query Optimization

• The goal of database performance tuning is to minimize the time it takes retrieve data.
• Database tuning is a series of activities and procedures to reduce response time.
• Optimising performance by ensuring components are operating at optimum levels. minimising bottlenecks to enhance overall performance.
• Activities can be divided into client-side SQL tuning and server-side DBMS tuning.

DBMS Architecture

• Purpose: Efficient database management through various processes.
• Components: Client-side processes (like SQL queries and user processes), server-side processes handling requests, receiving and responding to SQL queries, scheduling, and managing locks and optimizations.
• Includes SQL cache, data cache, I/O operations and database storage (like table spaces and data files).

Query Processing

• Process: The DBMS parses a query, selects an execution plan, executes the query, and fetches data for the client.
• Phases: Parsing (analysing SQL query to select best execution plan), execution (the DBMS runs the query), Fetching (DBMS retrieves the needed data to return to client).
• SQL tuning: Improving the efficiency of SQL to speed up processes.

Query Processing Bottlenecks

• Definition: Delays in query execution due to CPU, RAM, hard disk, network, and application code issues.

Indexes and Query Optimization

• Speed up data retrieval.
• Ordered lists of values (with pointers) associated with data records..
• Indexes scans improve performance over table scans.
• Understanding data sparsity (the number of different values in a column) helps determine when an index improves performance.

Optimizer Choices

• Two primary modes for query optimizers are rule-based optimizers that use pre-defined rules to establish the best execution paths or cost-based optimizers that scrutinize the data and choose the most efficient query execution plan.

Database Administration

• Focuses on the Data-Information-Decision Making cycle, which includes data collection, analysis, information processing, knowledge creation, decision making and actions.
• Dirty data relates to errors/inaccuracies in datasets. Data quality focuses on improving this accuracy, validity and timeliness to enable reliable decision-making. Data profiling software examines and analyses data to ensure quality. Master data management software combines and ensures all systems have up-to-date and accurate data needed for smooth operations.
• Role and placement of Database Administrators (DBAs), which cover staff positions (consultative strategies), or line positions (implementation & enforcement roles for company-wide data policies).
• DBA skills needed include management skills and technical skills in effectively managing databases.
• Tools used by DBAs, like monitoring tools for performance evaluation, performance tools for database improvement, and backup tools for data security.
• A data dictionary acts as a centralized repository for database object descriptions. This includes tables, columns, indices and relationships; this can come as an integrated component of the DBMS, or standalone one.