Database Security Problem-Solving and Analytical Questions

1. You have two database systems, A and B. System A contains 1,000,000 records, while System B has 300,000 records. During a security audit, you find that a total of 200,000 records are duplicated across both systems. How many unique records do you have across both databases?

2. A security measure is put in place that requires each database table to have at least two separate users with different roles for administrative access. If a database has 5 different tables, what is the minimum number of users required to ensure this security measure is met?

3. You notice irregular login patterns on your database server. Analyze the following data set of timestamps for successful and failed logins: - Successful: 2023-10-01 08:00, 2023-10-01 08:05, 2023-10-01 08:10 - Failed: 2023-10-01 08:02, 2023-10-01 08:08, 2023-10-01 08:10 What is the percentage of failed login attempts concerning the total login attempts?

4. Suppose you have a relational database with sensitive information and legacy systems that do not support encryption. Describe a strategy you would implement to secure sensitive data while maintaining application performance.

5. A database administrator discovers that the average response time for queries is increasing. Given the following data points for query response times over the last week: 50ms, 55ms, 60ms, 75ms, 80ms, and 100ms, what statistical method could you use to determine if this trend is significant, and what action should be taken based on your analysis?

6. You are given a set of user roles for a database system: Admin, User, Manager, and Guest. If the following hierarchy exists: Admin > Manager > User > Guest, how many ways can you assign these roles to a group of 4 users if each user can only have one role and each role must be assigned to exactly one user?

7. You have observed unusual query patterns in your database logs. If you notice that a particular SQL query is executed 4000 times in a day, of which 3000 are successful but 1000 lead to a database error. Calculate the error rate and discuss potential steps to investigate and mitigate this issue.

8. Considering a relational database design that includes multiple foreign keys that reference a parent table, explain how you would implement data integrity and security measures to prevent orphan records due to unauthorized access to the parent table.

9. A company wants to migrate its database to a cloud service. However, they require that their data comply with GDPR regulations. What steps should be taken to ensure that both data migration and storage in the cloud adhere to these regulations?

10. You are tasked with implementing an access control list (ACL) for a database containing sensitive personal data. If you need to restrict access based on three different criteria (role, time of access, and IP address), how would you structure these permissions, and what challenges might arise from this setup?