SQL - Practical Security and Access Control Exercises
Security and Access Control Exercises with solutions [20 exercises with solution]
1. Granting SELECT Permission on a Table
Write a SQL query to grant SELECT permission on a table to a user.
2. Revoking INSERT Permission on a Table
Write a SQL query to revoke INSERT permission on a table from a user.
3. Granting EXECUTE Permission on a Stored Procedure
Write a SQL query to grant EXECUTE permission on a stored procedure to a user.
4. Implementing Row-Level Security (RLS)
Write a SQL query to implement row-level security on a table.
5. Implementing Column-Level Security (CLS)
Write a SQL query to implement column-level security using views.
6. Creating a Database Role and Assigning Permissions
Write a SQL query to create a database role and assign permissions to it.
7. Enabling Data Masking for Sensitive Columns
Write a SQL query to enable dynamic data masking on a sensitive column.
8. Auditing user Access to Sensitive Data
Write a SQL query to audit user access to sensitive data using triggers.
9. Restricting Schema Modifications with DDL Triggers
Write a SQL query to restrict schema modifications using DDL triggers.
10. Encrypting a Database Column
Write a SQL query to encrypt a sensitive column using Transparent Data Encryption (TDE).
11. Creating a Login and Mapping It to a user
Write a SQL query to create a login and map it to a database user.
12. Denying DELETE Permission on a Table
Write a SQL query to deny DELETE permission on a table to a user.
13. Using Certificates for Database Authentication
Write a SQL query to create and use a certificate for database authentication.
14. Enforcing Password Policies for SQL Logins
Write a SQL query to enforce password policies for SQL logins.
15. Granting Ownership of a Schema to a User
Write a SQL query to grant ownership of a schema to a user.
16. Implementing Role-Based Access Control (RBAC)
Write a SQL query to implement role-based access control (RBAC) for a database.
17. Auditing Failed Login Attempts
Write a SQL query to audit failed login attempts using SQL Server Audit.
18. Enabling Always Encrypted for Sensitive Data
Write a SQL query to enable always Encrypted for a sensitive column.
19. Implementing Multi-Factor Authentication (MFA)
Write a SQL query to configure multi-factor authentication (MFA) for database access.
20. Revoking All Permissions from a User
Write a SQL query to revoke all permissions from a user.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics