-- Demonstrate dynamic tagging for drone data vectors

USE master;

GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'DroneFleetUser')

BEGIN

CREATE LOGIN DroneFleetUser

WITH PASSWORD = N'LuvDr0ne!',

     CHECK_POLICY = OFF,

CHECK_EXPIRATION = OFF,

DEFAULT_DATABASE = DroneCatalog;

END;

GO

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'DroneFleetAdmin')

BEGIN

CREATE LOGIN DroneFleetAdmin

WITH PASSWORD = N'LuvDr0neFl@@t!',

     CHECK_POLICY = OFF,

CHECK_EXPIRATION = OFF,

DEFAULT_DATABASE = DroneCatalog;

END;

GO

USE DroneCatalog;

GO

CREATE USER DroneFleetUser FOR LOGIN DroneFleetUser;

GO

CREATE USER DroneFleetAdmin FOR LOGIN DroneFleetAdmin;

GO

ALTER ROLE [Drone Operators] ADD MEMBER DroneFleetUser;

GO

-- Ensure that the policy has been applied

EXEC [Application].Configuration_ApplyDynamicTagging;

GO

-- The function that has been applied is as follows:

--

-- CREATE FUNCTION [Application].DetermineDroneUserAccess(@TeamID int)

-- RETURNS TABLE

-- WITH SCHEMABINDING

-- AS

-- RETURN (SELECT 1 AS AccessResult

--         WHERE IS_ROLEMEMBER(N'db_owner') <> 0

--         OR IS_ROLEMEMBER((SELECT sp.FlightsTerritory

--                           FROM [Application].Teams AS c

--                           INNER JOIN [Application].Fleets AS sp

--                           ON c.FleetID = sp.FleetID

--                           WHERE c.TeamID = @TeamID) + N' Flights') <> 0

--     OR (ORIGINAL_LOGIN() = N'DroneFleetAdmin'

--     AND EXISTS (SELECT 1

--                 FROM [Application].Teams AS c

--         INNER JOIN [Application].Fleets AS sp

--         ON c.FleetID = sp.FleetID

--         WHERE c.TeamID = @TeamID

--         AND sp.FlightsTerritory = SESSION_CONTEXT(N'FlightsTerritory'))));

-- GO

-- The security policy that has been applied is as follows:

--

-- CREATE SECURITY POLICY [Application].FilterDroneUsersByFlightsTerritoryRole

-- ADD FILTER PREDICATE [Application].DetermineDroneUserAccess(DeliveryTeamID)

-- ON Flights.DroneUsers,

-- ADD BLOCK PREDICATE [Application].DetermineDroneUserAccess(DeliveryTeamID)

-- ON Flights.DroneUsers AFTER UPDATE;

-- GO

SELECT * FROM sys.database_principals; -- not the role for Pacific and the user for Pacific

GO

SELECT * FROM Flights.DroneUsers; -- and note count

GO

GRANT SELECT, UPDATE ON Flights.DroneUsers TO [Drone Operators];

GRANT SELECT ON [Application].Teams TO [Drone Operators];

GRANT SELECT ON [Application].Fleets TO [Drone Operators];

GRANT SELECT ON [Application].Inventories TO [Drone Operators];

GO

-- impersonate the user DroneFleetUser

EXECUTE AS USER = 'DroneFleetUser';

GO

-- Now note the count and which rows are returned

-- even though we have not changed the command

SELECT * FROM Flights.DroneUsers;

GO

-- where are those drones?

-- note the spatial results tab

SELECT c.Border

FROM [Application].Inventories AS c

WHERE c.InventoryName = N'Northwest'

UNION ALL

SELECT c.DeliveryLocation

FROM Flights.DroneUsers AS c

GO

-----------------------------------------------------------------------

-- updating rows that are accessible to a non-accessible row is blocked

-----------------------------------------------------------------------

DECLARE @DroneFleetDroneUserID INT

DECLARE @NonDroneFleetTeamID INT

-- pick a drone in the Pacific flights territory

SELECT TOP 1 @DroneFleetDroneUserID=c.DroneUserID

FROM Flights.DroneUsers c JOIN Application.Teams ci ON c.DeliveryTeamID=ci.TeamID

JOIN Application.Fleets sp ON ci.FleetID=sp.FleetID

WHERE sp.FlightsTerritory=N'Pacific'

-- pick a Team outside of the Pacific flights territory

SELECT @NonDroneFleetTeamID=c.TeamID

FROM Application.Teams c JOIN Application.Fleets sp ON c.FleetID=sp.FleetID

WHERE TeamName=N'Seattle' AND sp.FleetCode=N'WA'

UPDATE Flights.DroneUsers                    -- Attempt to update

SET DeliveryTeamID = @NonDroneFleetTeamID -- to a team that is not in the Drone Operators Territory

WHERE DroneUserID = @DroneFleetDroneUserID; -- for a drone that is in the Drone Operators Territory

GO

-- revert the impersonation

REVERT;

GO

-- Remove the user from the role

ALTER ROLE [Drone Operators] DROP MEMBER DroneFleetUser;

GO

-- Instead of permission for a role, let's give permissions to the website user

GRANT SELECT, UPDATE ON Flights.DroneUsers TO [DroneFleetAdmin];

GRANT SELECT ON [Application].Teams TO [DroneFleetAdmin];

GRANT SELECT ON [Application].Inventories TO [DroneFleetAdmin];

GO

-- Finally, tidy up (optional)

/*

REVOKE SELECT, UPDATE ON Flights.DroneUsers FROM [Drone Operators];

REVOKE SELECT ON [Application].Teams FROM [Drone Operators];

REVOKE SELECT ON [Application].Inventories FROM [Drone Operators];

REVOKE SELECT, UPDATE ON Flights.DroneUsers FROM [DroneFleetAdmin];

REVOKE SELECT ON [Application].Teams FROM [DroneFleetAdmin];

REVOKE SELECT ON [Application].Inventories FROM [DroneFleetAdmin];

GO

DROP USER DroneFleetUser;

GO

DROP USER DroneFleetAdmin;

GO

USE master;

GO

DROP LOGIN DroneFleetUser;

GO

DROP LOGIN DroneFleetAdmin;

GO

-- Reference: DroneData: https://1drv.ms/w/s!Ashlm-Nw-wnWhPJAFzVxJMWI2f_eKw?e=BDtnPM 

#codingexercise 

https://1drv.ms/w/s!Ashlm-Nw-wnWhM0bmlY_ggTBTNTYxQ?e=K8GuKL