313 lines
11 KiB
Transact-SQL
313 lines
11 KiB
Transact-SQL
:setvar ShadowSourceDatabase "FT546-wcs"
|
|
:setvar ShadowEvidenceLogin "ft546_shadow_evidence"
|
|
:setvar ShadowEvidenceUser "ft546_shadow_evidence"
|
|
:setvar ShadowEvidencePassword "REPLACE_AT_DEPLOY_TIME"
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
IF DB_ID() = DB_ID(N'$(ShadowSourceDatabase)')
|
|
THROW 51001, 'Evidence objects must be installed in a separate database.', 1;
|
|
|
|
IF N'$(ShadowEvidencePassword)' = N'REPLACE_AT_DEPLOY_TIME'
|
|
THROW 51002, 'Supply ShadowEvidencePassword through the approved secret channel.', 1;
|
|
|
|
DECLARE @evidenceLoginCreated bit = 0;
|
|
IF NOT EXISTS (SELECT 1 FROM sys.server_principals
|
|
WHERE name = N'$(ShadowEvidenceLogin)')
|
|
BEGIN
|
|
DECLARE @createEvidenceLogin nvarchar(max) =
|
|
N'CREATE LOGIN ' + QUOTENAME(N'$(ShadowEvidenceLogin)')
|
|
+ N' WITH PASSWORD = N'''
|
|
+ REPLACE(N'$(ShadowEvidencePassword)', N'''', N'''''')
|
|
+ N''', CHECK_POLICY = ON, CHECK_EXPIRATION = ON';
|
|
EXEC sys.sp_executesql @createEvidenceLogin;
|
|
SET @evidenceLoginCreated = 1;
|
|
END;
|
|
|
|
BEGIN TRY
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM sys.server_role_members rm
|
|
JOIN sys.server_principals login_principal
|
|
ON login_principal.principal_id = rm.member_principal_id
|
|
WHERE login_principal.name = N'$(ShadowEvidenceLogin)'
|
|
)
|
|
THROW 51003, 'The shadow evidence login must not belong to a server role.', 1;
|
|
|
|
DECLARE @serverContextSet bit = 0;
|
|
BEGIN TRY
|
|
EXECUTE AS LOGIN = N'$(ShadowEvidenceLogin)';
|
|
SET @serverContextSet = 1;
|
|
IF EXISTS (
|
|
SELECT 1 FROM sys.fn_my_permissions(NULL, N'SERVER')
|
|
WHERE permission_name NOT IN (N'CONNECT SQL', N'VIEW ANY DATABASE')
|
|
)
|
|
THROW 51009, 'The shadow evidence login retains forbidden server permissions.', 1;
|
|
REVERT;
|
|
SET @serverContextSet = 0;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @serverContextSet = 1
|
|
REVERT;
|
|
THROW;
|
|
END CATCH;
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.database_principals
|
|
WHERE name = N'$(ShadowEvidenceUser)')
|
|
BEGIN
|
|
DECLARE @createEvidenceUser nvarchar(max) =
|
|
N'CREATE USER ' + QUOTENAME(N'$(ShadowEvidenceUser)')
|
|
+ N' FOR LOGIN ' + QUOTENAME(N'$(ShadowEvidenceLogin)');
|
|
EXEC sys.sp_executesql @createEvidenceUser;
|
|
END
|
|
ELSE IF SUSER_SID(N'$(ShadowEvidenceLogin)')
|
|
<> USER_SID(N'$(ShadowEvidenceUser)')
|
|
THROW 51004, 'The existing shadow evidence user maps to a different login.', 1;
|
|
|
|
DECLARE @evidencePrincipal nvarchar(258) =
|
|
QUOTENAME(N'$(ShadowEvidenceUser)');
|
|
DECLARE @evidenceRole sysname;
|
|
DECLARE evidence_roles CURSOR LOCAL FAST_FORWARD FOR
|
|
SELECT role_principal.name
|
|
FROM sys.database_principals role_principal
|
|
JOIN sys.database_role_members rm
|
|
ON rm.role_principal_id = role_principal.principal_id
|
|
JOIN sys.database_principals member_principal
|
|
ON member_principal.principal_id = rm.member_principal_id
|
|
WHERE role_principal.type = 'R'
|
|
AND member_principal.name = N'$(ShadowEvidenceUser)';
|
|
|
|
OPEN evidence_roles;
|
|
FETCH NEXT FROM evidence_roles INTO @evidenceRole;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
DECLARE @dropEvidenceRole nvarchar(max) = N'ALTER ROLE '
|
|
+ QUOTENAME(@evidenceRole) + N' DROP MEMBER ' + @evidencePrincipal;
|
|
EXEC sys.sp_executesql @dropEvidenceRole;
|
|
FETCH NEXT FROM evidence_roles INTO @evidenceRole;
|
|
END;
|
|
CLOSE evidence_roles;
|
|
DEALLOCATE evidence_roles;
|
|
|
|
DECLARE @revokeEvidenceDatabase nvarchar(max) =
|
|
N'REVOKE CONTROL, ALTER, TAKE OWNERSHIP, SELECT, INSERT, UPDATE, DELETE, '
|
|
+ N'EXECUTE FROM ' + @evidencePrincipal + N';';
|
|
EXEC sys.sp_executesql @revokeEvidenceDatabase;
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_run', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_run (
|
|
run_id uniqueidentifier NOT NULL PRIMARY KEY,
|
|
started_at datetime2(3) NOT NULL,
|
|
artifact_sha256 char(64) NOT NULL,
|
|
configuration_sha256 char(64) NOT NULL,
|
|
source_server nvarchar(256) NOT NULL,
|
|
source_database sysname NOT NULL,
|
|
source_login sysname NOT NULL,
|
|
status varchar(32) NOT NULL
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_cycle', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_cycle (
|
|
cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
|
|
run_id uniqueidentifier NOT NULL,
|
|
input_batch_hash char(64) NOT NULL,
|
|
parent_task_id nvarchar(128) NOT NULL,
|
|
phase varchar(64) NOT NULL,
|
|
route_version bigint NOT NULL,
|
|
status varchar(64) NOT NULL,
|
|
reason_code nvarchar(512) NULL,
|
|
database_captured_at datetime2(3) NULL,
|
|
completed_at datetime2(3) NOT NULL,
|
|
evidence_hash char(64) NOT NULL,
|
|
CONSTRAINT fk_shadow_cycle_run FOREIGN KEY (run_id)
|
|
REFERENCES dbo.shadow_run(run_id),
|
|
CONSTRAINT uq_shadow_cycle_identity UNIQUE
|
|
(run_id, input_batch_hash, parent_task_id, phase, route_version)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_input_batch', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_input_batch (
|
|
cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
|
|
content_hash char(64) NOT NULL,
|
|
captured_json nvarchar(max) NOT NULL,
|
|
CONSTRAINT fk_shadow_input_cycle FOREIGN KEY (cycle_id)
|
|
REFERENCES dbo.shadow_cycle(cycle_id)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_legacy_queue', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_legacy_queue (
|
|
cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
|
|
vehicle_id nvarchar(64) NULL,
|
|
path_info nvarchar(max) NULL,
|
|
result_status varchar(64) NOT NULL,
|
|
result_json nvarchar(max) NOT NULL,
|
|
content_hash char(64) NOT NULL,
|
|
CONSTRAINT fk_shadow_legacy_cycle FOREIGN KEY (cycle_id)
|
|
REFERENCES dbo.shadow_cycle(cycle_id)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_decision', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_decision (
|
|
cycle_id uniqueidentifier NOT NULL PRIMARY KEY,
|
|
decision varchar(64) NOT NULL,
|
|
vehicle_id nvarchar(64) NULL,
|
|
reason_code nvarchar(512) NULL,
|
|
decision_json nvarchar(max) NOT NULL,
|
|
content_hash char(64) NOT NULL,
|
|
CONSTRAINT fk_shadow_decision_cycle FOREIGN KEY (cycle_id)
|
|
REFERENCES dbo.shadow_cycle(cycle_id)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_encoded_write', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_encoded_write (
|
|
cycle_id uniqueidentifier NOT NULL,
|
|
write_sequence int NOT NULL,
|
|
register_address varchar(16) NOT NULL,
|
|
data_type varchar(16) NOT NULL,
|
|
values_json nvarchar(max) NOT NULL,
|
|
trigger_value bigint NULL,
|
|
CONSTRAINT pk_shadow_encoded_write PRIMARY KEY
|
|
(cycle_id, write_sequence),
|
|
CONSTRAINT fk_shadow_write_cycle FOREIGN KEY (cycle_id)
|
|
REFERENCES dbo.shadow_cycle(cycle_id)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_difference', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_difference (
|
|
difference_id bigint IDENTITY(1, 1) NOT NULL PRIMARY KEY,
|
|
cycle_id uniqueidentifier NOT NULL,
|
|
category varchar(64) NOT NULL,
|
|
reason_code nvarchar(512) NOT NULL,
|
|
expected_json nvarchar(max) NULL,
|
|
actual_json nvarchar(max) NULL,
|
|
CONSTRAINT fk_shadow_difference_cycle FOREIGN KEY (cycle_id)
|
|
REFERENCES dbo.shadow_cycle(cycle_id)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_traffic_state', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_traffic_state (
|
|
state_key nvarchar(256) NOT NULL PRIMARY KEY,
|
|
snapshot_version bigint NOT NULL,
|
|
state_json nvarchar(max) NOT NULL,
|
|
updated_at datetime2(3) NOT NULL
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_hold_history', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_hold_history (
|
|
parent_task_id nvarchar(128) NOT NULL,
|
|
vehicle_id nvarchar(64) NOT NULL,
|
|
resource_key nvarchar(256) NOT NULL,
|
|
reason_code nvarchar(512) NOT NULL,
|
|
consecutive_count int NOT NULL,
|
|
updated_at datetime2(3) NOT NULL,
|
|
CONSTRAINT pk_shadow_hold_history PRIMARY KEY
|
|
(parent_task_id, vehicle_id, resource_key, reason_code)
|
|
);
|
|
|
|
IF OBJECT_ID(N'dbo.shadow_startup_check', N'U') IS NULL
|
|
CREATE TABLE dbo.shadow_startup_check (
|
|
check_id bigint IDENTITY(1, 1) NOT NULL PRIMARY KEY,
|
|
check_name varchar(128) NOT NULL,
|
|
status varchar(32) NOT NULL,
|
|
checked_at datetime2(3) NOT NULL
|
|
);
|
|
|
|
DECLARE @grantEvidence nvarchar(max) = N'GRANT CONNECT TO '
|
|
+ @evidencePrincipal + N';';
|
|
DECLARE @evidenceTable sysname;
|
|
DECLARE evidence_tables CURSOR LOCAL FAST_FORWARD FOR
|
|
SELECT name
|
|
FROM sys.tables
|
|
WHERE schema_id = SCHEMA_ID(N'dbo')
|
|
AND name IN (N'shadow_run', N'shadow_cycle', N'shadow_input_batch',
|
|
N'shadow_legacy_queue', N'shadow_decision',
|
|
N'shadow_encoded_write', N'shadow_difference',
|
|
N'shadow_traffic_state', N'shadow_hold_history',
|
|
N'shadow_startup_check');
|
|
OPEN evidence_tables;
|
|
FETCH NEXT FROM evidence_tables INTO @evidenceTable;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SET @grantEvidence += N'GRANT SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.'
|
|
+ QUOTENAME(@evidenceTable) + N' TO ' + @evidencePrincipal + N';';
|
|
FETCH NEXT FROM evidence_tables INTO @evidenceTable;
|
|
END;
|
|
CLOSE evidence_tables;
|
|
DEALLOCATE evidence_tables;
|
|
EXEC sys.sp_executesql @grantEvidence;
|
|
|
|
DECLARE @databaseContextSet bit = 0;
|
|
BEGIN TRY
|
|
EXECUTE AS USER = N'$(ShadowEvidenceUser)';
|
|
SET @databaseContextSet = 1;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM sys.database_role_members rm
|
|
JOIN sys.database_principals member_principal
|
|
ON member_principal.principal_id = rm.member_principal_id
|
|
WHERE member_principal.name = USER_NAME()
|
|
)
|
|
THROW 51005, 'The shadow evidence login retains a database role.', 1;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM sys.fn_my_permissions(NULL, N'DATABASE')
|
|
WHERE permission_name <> N'CONNECT'
|
|
)
|
|
THROW 51006, 'The shadow evidence login retains forbidden database permissions.', 1;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM sys.schemas s
|
|
CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name), N'SCHEMA') p
|
|
WHERE s.name NOT IN (N'sys', N'INFORMATION_SCHEMA')
|
|
)
|
|
THROW 51007, 'The shadow evidence login retains forbidden schema permissions.', 1;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM sys.objects o
|
|
JOIN sys.schemas s ON s.schema_id = o.schema_id
|
|
CROSS APPLY sys.fn_my_permissions(
|
|
QUOTENAME(s.name) + N'.' + QUOTENAME(o.name), N'OBJECT') p
|
|
WHERE o.is_ms_shipped = 0
|
|
AND NOT (
|
|
s.name = N'dbo'
|
|
AND o.name IN (
|
|
N'shadow_run', N'shadow_cycle', N'shadow_input_batch',
|
|
N'shadow_legacy_queue', N'shadow_decision',
|
|
N'shadow_encoded_write', N'shadow_difference',
|
|
N'shadow_traffic_state', N'shadow_hold_history',
|
|
N'shadow_startup_check')
|
|
AND p.permission_name IN (
|
|
N'SELECT', N'INSERT', N'UPDATE', N'DELETE')
|
|
)
|
|
)
|
|
THROW 51008, 'The shadow evidence login retains forbidden object permissions.', 1;
|
|
|
|
REVERT;
|
|
SET @databaseContextSet = 0;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @databaseContextSet = 1
|
|
REVERT;
|
|
THROW;
|
|
END CATCH;
|
|
|
|
COMMIT TRANSACTION;
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF XACT_STATE() <> 0
|
|
ROLLBACK TRANSACTION;
|
|
IF @evidenceLoginCreated = 1
|
|
BEGIN
|
|
DECLARE @dropEvidenceLogin nvarchar(max) =
|
|
N'DROP LOGIN ' + QUOTENAME(N'$(ShadowEvidenceLogin)');
|
|
EXEC sys.sp_executesql @dropEvidenceLogin;
|
|
END;
|
|
THROW;
|
|
END CATCH;
|