CDP Database Administration and Security 2 — Questions and Answers
Question 1: What is SQL injection and why is it a database security concern?
- A method to insert bulk data efficiently into databases
- An attack where malicious SQL code is inserted into input fields to manipulate or access unauthorized database data (Correct answer)
- A technique for optimizing SQL queries
- A database replication method
Correct answer: An attack where malicious SQL code is inserted into input fields to manipulate or access unauthorized database data
SQL injection occurs when attackers insert malicious SQL code through user input fields, potentially allowing them to read, modify, or delete database data without authorization.
Question 2: What is database partitioning and what problem does it solve?
- Splitting a database into multiple files for backup purposes
- Dividing a large table into smaller, more manageable segments to improve query performance and manageability (Correct answer)
- Creating separate databases for each user
- Encrypting parts of a database with different keys
Correct answer: Dividing a large table into smaller, more manageable segments to improve query performance and manageability
Partitioning divides large tables into smaller segments (by range, hash, or list) so queries can scan only relevant partitions, improving performance and manageability.
Question 3: In the context of NoSQL databases, what does 'eventual consistency' mean?
- The database will eventually be shut down for maintenance
- Data updates will propagate to all replicas eventually, but replicas may temporarily serve stale data (Correct answer)
- The database schema will eventually be normalized
- All queries will eventually be optimized automatically
Correct answer: Data updates will propagate to all replicas eventually, but replicas may temporarily serve stale data
Eventual consistency means that after an update, all replicas will eventually receive it and be consistent, but in the short term, different nodes may return slightly different values.
Question 4: What is 'transparent data encryption' (TDE) in databases?
- Encrypting only specific sensitive columns in a table
- Encrypting the entire database storage files at rest so data is protected if storage media is stolen (Correct answer)
- Encrypting data during transmission over the network
- Making encryption keys visible to database administrators
Correct answer: Encrypting the entire database storage files at rest so data is protected if storage media is stolen
TDE encrypts database storage files at rest, protecting data if physical storage media is stolen or accessed without authorization, transparently to applications.
Question 5: What is the difference between a clustered and non-clustered index in SQL databases?
- Clustered indexes are faster; non-clustered are more secure
- A clustered index determines the physical sort order of table rows; a non-clustered index is a separate structure pointing to data rows (Correct answer)
- Clustered indexes are used for strings; non-clustered for numbers
- A table can have unlimited clustered indexes but only one non-clustered
Correct answer: A clustered index determines the physical sort order of table rows; a non-clustered index is a separate structure pointing to data rows
A clustered index determines the physical order rows are stored in the table (only one per table), while non-clustered indexes are separate lookup structures pointing back to the data rows.
Question 6: What is 'principle of least privilege' in database access control?
- Granting all users read access to all tables
- Granting users only the minimum permissions required to perform their job functions (Correct answer)
- Restricting database access to only privileged administrators
- Granting permissions for the least-used database objects
Correct answer: Granting users only the minimum permissions required to perform their job functions
The principle of least privilege grants each user or process only the minimum database permissions necessary for their legitimate work, limiting the damage from errors or attacks.
CDP Database Administration and Security 2
What is SQL injection and why is it a database security concern?