DATABASE MANGEMENT

Part 1

1.What are users, profiles  & roles?

2. How does a schema affect security?

3. Create a new "test_database'.

4. show where the .mdf and .ldf files are found

5. What does recovery model refer to and how is it related to security?

6. What is the SA account and how can it be used to compromise database security?

7. What are server roles?

8. What is the difference between SQL Server authentication modes?

9. What is the risk of unchecking the enforce password policy box?

10. What is the difference between SQL Server authentication modes?

11.If you create a new user using SQL Server authentication what will happen when you try to connect to SQL Server?

12. How do you solve that problem?

13. What roles are available to a user by default? How is this a security concern?

14. What is a schema?

15. Change to SQL Server authentication. Create a login

16. Create a user

17. Create a schema

18. Allow the new user to write to and read from tables but not to create or delete them.

19. Create two instances of SSMS and connect to the database using different logins

20. What happens when you attempt to create a new table in each instance?

Part 2:

Watch this group of five videos on Transactions in SQL.

1. What are transactions?

2. Why do we use transactions?

3. What is an ACID transaction?

4. What are the different types of locks available in SQL?.

5. What are the different isolation levels?

6. Why would we use each different isolation level.

7. What are the commands needed to create a transaction?

8. Create a transaction in your ToDo database. 

9. Show a failed transaction.

10. Show a successful transaction.

Part3:

1. What is transparent data encryption?

2. what problem's does it resolve?

3. What versions of SQL Server is it available in? 

4. What does it mean that TDE is certificate compliant?

5. What happens when the certificate is expired?

6. What are the limits of TDE encryption?

7. Are all databases encrypted with the same key?

8. How can you recover the database if you lose the keys? 

SQLServer TDE video

MySQL TDE reference.