Database Security /* Q1. As an administrator of your SQL Server, write a DDL sta

Question

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?
*/

Explanation / Answer

SOLUTION:-

(1):- SQL Server login that uses SQL Server Authentication via Transact-SQL method.In Query Editor window we will enter the following Transact-SQL command given below:-

CREATE LOGIN <shark> WITH PASSWORD = '<Mid-Atlantic>' ; GO

(2):-

mysql> CREATE USER 'bluewhale'@ '%' IDENTIFIED BY ' All-oceans';

mysql> GRANT ALL PRIVILEGES ON *.* TO ' bluewhale '@'%'

    ->     WITH GRANT OPTION;

In the above statement, we can connect 'bluewhale' with any host via '%' wildcard. Here GRANT statement is used to install exclusive privileges to 'bluewhale' in order to make suitable alterations in the prime grant tables by the server.

(3):-

Let user1 is already connected with Anna

CREATE USER user2 FOR LOGIN Jack;

GO

--setting permissions on user2 to user1

--so that user1 will set the execution context to user2.

GRANT IMPERSONATE ON USER:: user2 TO user1;

GO

--Test the execution context is now Anna.

SELECT SUSER_NAME(), USER_NAME();

-- Anna will transfers the execution context to Jack.

EXECUTE AS USER = 'user2';

--Display the present execution context.

SELECT SUSER_NAME(), USER_NAME();

--The REVERT statement will change the execution context back to Anna .

REVERT;

--Displaying the present execution context.

SELECT SUSER_NAME(), USER_NAME();

(4):-

USE Clearwater

GO

CREATE USER assistant FOR LOGIN Anna;

GO

(5):- mysql> GRANT CREATE,GRANT ON Clearwater TO ‘assistant’ , ‘assistant2’ , ‘assistant3’ @'localhost’;

(6):- GRANT db_datareader TO assistant ;

(7):- mysql> GRANT UPDATE ON Clearwater.Products SET ` UnitPrice ` = '15', ` UnitsInStock ` = '1700' WHERE `Product_ID` = 2 TO ‘assistant’ @'localhost’;

(8):- REVOKE SELECT Address,Phone ON TABLE Suppliers FROM Assistant;

==========================================================================================

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.