Database Security Code Challenges & Tips

1. Write a SQL query to find all users in the `users` table who have not logged in for the past 90 days.

2. Create a SQL script that adds a new user role `read_only` to the `user_roles` table and grants SELECT permissions on all tables in the `sales` schema to that role.

3. Write a SQL statement that revokes all privileges from a user `test_user` on the `employees` table.

4. Write a stored procedure that logs all failed login attempts to a `failed_logins` table, capturing the username, timestamp, and reason for failure.

5. Create a SQL function that checks if a password meets certain complexity requirements (at least one uppercase letter, one lowercase letter, one number, and one special character) and returns true or false.

6. Write a query to identify any users in the `employees` table who have been granted more privileges than necessary based on their job role defined in the `roles` table.

7. Design a trigger that prevents the deletion of any records from the `sensitive_data` table if the current user is not an admin.

8. Create a SQL script to encrypt a specific column, `ssn`, in the `customers` table using a given encryption key.

9. Develop a SQL query that lists all active database sessions along with their IP addresses, hostnames, and the queries being executed, filtering for sessions that have been active for longer than 10 minutes.

10. Write a SQL statement to create an audit table called `table_audit` that tracks changes to a table `orders`, including insertions, updates, and deletions, with columns for the operation type, timestamp, and user.