-- DATABASE SYSTEMS 2019/20
-- LECTURE 14 QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- DATABASE SECURITY
-- ~~~~~~~~~~~~~~~~~~~

-- Logons and Privileges
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- CREATE Login Statement
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Create a new SQL Server Authentication login with the specified login name and password. 
-- I have created the following three logins for you to use to test other security features that we will discuss
-- The SQL is shown for reference purposes and so so you can create logins on your personal computers at home.
-- You have to make sure that the password now has capital letter and number so the simple assword may not work try it out.
-- CREATE LOGIN P000001 WITH PASSWORD = 'Dbmsuser1';
-- CREATE LOGIN P000002 WITH PASSWORD = 'dbmsuser';
-- CREATE LOGIN P000003 WITH PASSWORD = 'dbmsuser';


-- If you try and create a user without a login, you will get an error because this is not a contained database
-- A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
-- In contained databases, User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
-- See more: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

-- This command WILL FAIL
CREATE USER TestUser WITH PASSWORD = 'dbmsuser'


-- Create a user mapped to the login for database access to DreamHome
CREATE USER P000001 FOR LOGIN P000001;
CREATE USER P000002 FOR LOGIN P000002;


-- Connect another query window, logged in as user, P000001.

-- Now try running the following query in the window for P000001. Does it work?
SELECT * FROM Branch;

-- Grant the user, P000001, all privileges on the branch table
-- Note that The ALL permission is deprecated and maintained only for backwards compatibility in MS SQL Server. 
-- It will not necessarily grant ALL permissions defined on the entity.
-- For the purpose of what we want to do, it still works okay
GRANT ALL PRIVILEGES
ON Branch
TO P000001 
WITH GRANT OPTION;

-- Now try the following again. Did it work this time.
SELECT * FROM Branch;

-- The user P000001 should now also be able to GRANT privileges for the Branch table to other users.
-- Try the following in P000001's query window
GRANT SELECT
ON Branch
TO P000002;

-- Connect another query window, logged in as user, P000002.
-- You should be able to run this query:
SELECT * FROM Branch;

-- but not this one
UPDATE Branch
SET PhoneNo = '01792400000'
WHERE BranchNo = 'B0001';


-- Grant specific privileges
GRANT SELECT, UPDATE(Salary) ON Staff TO P000001;

-- If you go back to the query window for P000001, you should be able to run
SELECT * FROM Staff;
UPDATE Staff SET Salary = Salary * 1.05 WHERE StaffNo = 'S29009';

-- but not
UPDATE Staff SET Position = 'Manager' WHERE StaffNo = 'S29009';

-- If you want to set privileges for ALL Users (now and in the future), use the PUBLIC account. Give all users SELECT on Branch table.
GRANT SELECT ON Branch TO PUBLIC;

-- Revoke the SELECT privilege on the Branch table from ALL Users.
REVOKE SELECT ON Branch FROM PUBLIC;

-- Revoke ALL privileges you have given to P000001 on the Staff table.
REVOKE ALL PRIVILEGES ON Staff FROM P000001;

-- If you go back to the query window for P000001, you should no longer be able to run
SELECT * FROM Staff;


-- ADDITIONAL EXERCISES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Give the user P000001 full access to the Property table with the privilege to pass the access on to others.


-- Give the user P000002 full access to the Staff table.



-- Now, revoke UPDATE rights on the salary column of the staff table for user P000002



-- Give the user P000002 SELECT access to the BranchProperty view (you should have created this in a previous exercise, if not use any other view you have).


-- Test if P000002 can query the Property table.
-- Now test if P000002 can query the view you granted access to.



-- Now, revoke the rights given to P000002 in the last exercise.