Database Security

/* Q1. As an administrator of your SQL Server, write a DDL statement to create a new SQL Server login called 'shark' with a password 'Mid-Atlantic'. Do not enforce the Windows password policies on this login. */ /* Q2. Do a little research to find what GRANT statement needs to execute if we allow the above 'shark' login to create another login called 'bluewhale with a password 'All-oceans'. */ /* Q3. Assuming a user is already connected to a SQL Server using a login named 'Anna', what statement does this user need to execute in order to switch the execution context from 'user1' to another, say, 'Jack', in order to run scripts or statements based on 'Jack'? What statement is needed to switch the execution context back to 'Anna'? */ --To switch the execution context from login 'Anna' to another login 'Jack': --To switch the execution context from 'Jack' back to 'Anna':

/* Q4. Assuming the login 'Anna' was already created in your server but it has no permissions yet to use the Clearwater database. What statement will you execute to make 'Anna' be a user named 'assistant' of Clearwater in your server? *Do not forget to set first the database context to Clearwater by a USE statement. If you forget, this new user 'assistant' will be added to the current database you are in. */ /* Q5. Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server, write one GRANT statement to perform two tasks: (1) allow this user to create new tables in Clearwater, and (2) allow this user to grant the same permission (i.e., creating new tables in Clearwater) to other users. */ /* Q6. Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server, write a statement to make this user a member of 'db_datareader' database role of ClearWater. */

/* Q7. Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server, write a statement to allow this user to update UnitPrice and UnitsInStock values of Products table. */ /* Q8. (8 points) Assuming the login 'Anna' is the user 'assistant' of the Clearwater database in your server, write a REVOKE statement to disallow this user to read the Address and Phone values of Suppliers. After the above REVOKE statement is executed, is user 'assistant' still able to read the Address and Phone values of Suppliers? Why? If REVOKE statement does not work for this purpose, what statement should be used? Hint: p.562-3 */ --(2 points) the REVOKE statement: --(4 points) Can 'assistant' still read Address and Phone of Suppliers after the REVOKE is done? Why? --(2 points) What statement other than REVOKE is needed?

/* Q9. Assuming you are currently working on a query editing page or session of Clearwater database in your SQL Server using your own login, what statement do you need to execute in order to switch the execution context from yours to the 'assistant' user, that is, pretending you are the 'assistant' user of Clearwater? What statement is needed to switch the execution context back to your own? */ --To switch the execution context from yours to the 'assistant' user: --To switch the execution context from the 'assistant' user back to your own:

/* Q10. In Q5, you have granted the 'assistant' user a permission of CREATE TABLE in the Clearwater database and also allowed this user to grant the same permission to other users of this database. A big concern and risk of doing so is the 'assistant' user and everyone who was granted this permission can keep granting to more and more other users, which may become out of your expectation eventually. To stop and prevent the 'assistant' user and all who were granted such permission from granting to even more other users, what statement will you execute? */