-- 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
