ProtectDB_Laba4(5)_DDL_Trigger

--Задание 1

-- Создать роль базы данных “DevUserRole”.Эта роль должна быть членом
-- ролей базы данных db_datareader и db_datawriter.Напишите скрипт для
-- предоставления прав DevUserRole на выполнение всех существующих в
-- заданной БД хранимых процедур.
-- Реализуйте триггер DDL для того чтобы автоматически выдавать права роли
-- DevUserRole на выполнение всех вновь создаваемых в схеме “dbo” хранимых
-- процедур.


--1) Создать роль базы данных “DevUserRole”.Эта роль должна быть членом
-- ролей базы данных db_datareader и db_datawriter.
DROP ROLE IF EXISTS DevUserRole
go
CREATE ROLE DevUserRole
go
alter role db_datareader add member DevUserRole
alter role db_datawriter add member DevUserRole
go


--2) Напишите скрипт для
-- предоставления прав DevUserRole на выполнение всех существующих в
-- заданной БД хранимых процедур.

--2.1) Для начала создадим фиктивные функции для теста
create procedure Test1 as
begin
    print 1
end

create procedure Test2 as
begin
    print 2
end


--2.2) Пишем скрипт для
-- предоставления прав DevUserRole на выполнение всех существующих в
-- заданной БД хранимых процедур.
create or alter procedure GiveExecuteToAllExistsProcedures as
begin
    declare @currentSchemaAndProcNames nvarchar(200);
    declare namesCursor cursor for
        select sys.schemas.name + '.' + sys.procedures.name as schemaAndProcNames
        from sys.procedures
        join sys.schemas on sys.procedures.schema_id = sys.schemas.schema_id
    open namesCursor;

    fetch next from namesCursor into @currentSchemaAndProcNames
    while @@fetch_status=0
    begin
        declare @sqlCommand nvarchar(200) = 'grant execute on '+@currentSchemaAndProcNames+' to DevUserRole'
        exec sp_executesql @sqlCommand
        print(@sqlCommand)

        fetch next from namesCursor into @currentSchemaAndProcNames
    end
    close namesCursor;
    deallocate namesCursor;
end

execute GiveExecuteToAllExistsProcedures

--2.3) Тестим
drop user if exists TestDevUserRoleUser
create user TestDevUserRoleUser without login
alter role DevUserRole add member TestDevUserRoleUser

execute as user = 'TestDevUserRoleUser'
execute Test1
go
execute Test2
go
print N'Есть права и на GiveExecuteToAllExistsProcedures, но нет прав на выдачу прав -> выкинет ошибку'
go
execute GiveExecuteToAllExistsProcedures
revert
go

--3) Реализуйте триггер DDL для того чтобы автоматически выдавать права роли
-- DevUserRole на выполнение всех вновь создаваемых в схеме “dbo” хранимых
-- процедур.

--3.1) Пишем тригер
CREATE OR ALTER TRIGGER NewUserAdditionTrigger
    ON DATABASE
    after CREATE_PROCEDURE
    AS
BEGIN
    DECLARE @schemaName NVARCHAR(200) = EVENTDATA()
        .value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(256)')
    if @schemaName != 'dbo'
        return
    DECLARE @procName NVARCHAR(200) = EVENTDATA()
        .value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)')
    declare @currentSchemaAndProcNames NVARCHAR(400) = @schemaName + '.' + @procName
    declare @sqlCommand nvarchar(200) = 'grant execute on '+@currentSchemaAndProcNames+' to DevUserRole'
    exec sp_executesql @sqlCommand
    print(@sqlCommand)
END;

--3.2) Создаем тестовую таблицу, ручками права не выдаем!
drop procedure if exists Test3
go
create procedure Test3 as
begin
    print 3
end
go

--3.3) Тестим
execute as user = 'TestDevUserRoleUser'
execute Test3
revert



------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
--Задание 2

--Реализовать аудит действий пользователя TestAdmin.
--Пользователю должно быть разрешено подключение к DBServerv только во
--время учебных пар по нашему курсу и только с компьютеров в 403 ауд. Все
--неудачные попытки входа фиксировать в таблице LogAuditTestAdmin.

--1) Создадим пользователя и выдадим права
--DROP LOGIN TestAdmin
CREATE LOGIN TestAdmin WITH PASSWORD = 'TestAdmin';
go
--DROP USER TestAdmin
CREATE USER TestAdmin FOR LOGIN TestAdmin;
go
alter role db_datareader add member TestAdmin
alter role db_datawriter add member TestAdmin
go

--2) Создадим таблицы
--2.1) Таблицу расписания пар (разрешенных периодов)
create table AllowedPeriods(
    start datetime,
    [end] datetime,
    note nvarchar(255)
)
insert into AllowedPeriods
values ('2023-12-06 10:45', '2023-12-06 12:20', N'Среда, числ, 2 пара'),
       ('2023-12-06 13:20', '2023-12-06 14:55', N'Среда, числ, 3 пара'),--:00.000
       ('2023-12-13 13:20', '2023-12-13 14:55', N'Среда, знам, 3 пара')
--Сохраняем дату начала и конца первой пары чтобы не придумывать сложных
--систем вычисления четности недели(числитель, знаменатель)

--2.2) Таблицу разрешенных компьютеров
--Тут на самом деле есть несколько путей
--Можно, к примеру, по айпи адресу попробовать анализировать маску подсети
--или еще что-нибудь придумать, но чем проще - тем легче с этим будет жить...
--Поэтому просто сохраняет айпишники компьютеров из 403 аудитории
--(в рамках лабы заполнил localhost'ом и фиктивными данными)
create table AllowedIps(
    ip varchar(16) primary key,
    note nvarchar(255)
)
insert into AllowedIps(ip, note)
values ('127.0.0.1', 'localhost'),
       ('192.168.0.1', N'Компьютер из 403 №1'),
       ('192.168.0.2', N'Компьютер из 403 №2'),
       ('192.168.0.3', N'Компьютер из 403 №3')

--2.3) Таблицу аудита
create table LogAuditTestAdmin(
    eventData xml,
    reason nvarchar(255)
)


--3) Пишем триггер
--DROP TRIGGER if exists TestAdminLogonManager on all server
CREATE OR ALTER TRIGGER TestAdminLogonManager
    ON ALL SERVER
    FOR LOGON
    AS
BEGIN
    --current_user при заходе = guest, проверено)
    if original_login() != 'TestAdmin'
        return; --можно было WITH EXECUTE, но так красивее
    declare @notAllowedReason nvarchar(255) = N'';

    declare @logonDatetime datetime = EVENTDATA()
        .value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime');

    if not exists(
        select *
        from [Laba4(5)].dbo.AllowedPeriods
        where DATEDIFF(day, @logonDatetime, AllowedPeriods.start) % 14 = 0 --совпадает четность недели
        and cast(@logonDatetime as time) between cast(start as time) and cast([end] as time)
        )
    begin
        set @notAllowedReason = @notAllowedReason + N'+Попытка подключения вне учебного времени'
    end;

    declare @userIp varchar(16) = EVENTDATA()
        .value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(16)')
    if not exists(select *
                  from [Laba4(5)].dbo.AllowedIps
                  where ip = @userIp)
    begin
        set @notAllowedReason = @notAllowedReason + N'+Попытка подключения вне кабинета 403'
    end;

    if @notAllowedReason != ''
    begin
        declare @eventData xml = cast(EVENTDATA() as xml);--Почему-то без приведения типа ругалось
        rollback tran;
        insert into [Laba4(5)].dbo.LogAuditTestAdmin
        values (@eventData, @notAllowedReason);
    end

END;


--4) Тестируем

--4.0) Переносим хост сервера из 403 кабинета
delete
from AllowedIps
where ip = '127.0.0.1'
--НЕ ПРОВЕРЯЛ! А то потом вообще зайти не смогу...

--4.1) Пытаемся зайти и смотрим почему не заходит
select *
from LogAuditTestAdmin

--4.2) Резко проводим пару в час ночи
insert into AllowedPeriods(start, [end], note)
values (SYSDATETIME(), DATEADD(minute, 95, SYSDATETIME()), N'Тест')

select *
from AllowedPeriods


------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
--Задание 3

--ИБ
--DDL-триггер с каждой создаваемой в текущей базе таблицей связывает DML
--триггер на вставку (подобен TrivialTrigger). Имя таблицы, на которой произошло
--срабатывание, извлекается из свойства SqlTriggerContext.EventData. DML триггер
--на вставку выдает сообщение на экран
-- Хорошая статья:
-- https://www.c-sharpcorner.com/article/how-to-create-clr-triggers-in-c-sharp-and-install-and-uninstall-clr-assembly-in-mssql/

--1) Напишем DML триггер на C# и зарегистрируем его на сервере

--1.1) Сам триггер вынес в отдельный проект на гитхабе
--https://github.com/SeregaTheDed/DDL_TRIGGER_LAB


--1.2) Регаем на сервере
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
sp_configure 'clr strict security', 0;
GO
RECONFIGURE;
GO

--drop trigger if exists trig_InsertPrint
--drop assembly if exists DDL_TRIGGER_LAB
CREATE ASSEMBLY DDL_TRIGGER_LAB
from 'C:\Users\user\source\repos\DDL_TRIGGER_LAB\DDL_TRIGGER_LAB\bin\Debug\DDL_TRIGGER_LAB.dll'
WITH PERMISSION_SET = SAFE


--2) Создаем триггер DDL на создание таблиц
create or alter trigger OnNewTableCreate
on database
for CREATE_TABLE
as
begin
    declare @schemaName nvarchar(255) = EVENTDATA()
        .value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(255)')
    declare @tableName nvarchar(255) = EVENTDATA()
        .value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)')
    declare @salt nvarchar(50) = cast(sysdatetime() as nvarchar(50))--Иначе может быть одновременно только 1 триггер
    set @salt = REPLACE(@salt, N'-', N'_');
    set @salt = REPLACE(@salt, N' ', N'_');
    set @salt = REPLACE(@salt, N':', N'_');
    set @salt = REPLACE(@salt, N'.', N'_');
    declare @triggerName nvarchar(560) = 'trig_InsertPrint_'+@salt+' '
    print(@triggerName)--Чтобы было удобно удалять для тестов
    declare @sqlCommand nvarchar(1000) =
    'CREATE TRIGGER '+@triggerName+
    'ON '+@schemaName+'.'+@tableName+' '+
    'FOR INSERT
     AS EXTERNAL NAME DDL_TRIGGER_LAB.[DDL_TRIGGER_LAB.Trigger_PrintTableNameOnInsert].PrintTableNameOnInsert'

    exec sp_executesql @sqlCommand
end


--3) Создаем тестовую таблицу
--drop table if exists TestTable3
create table TestTable3(
    test nvarchar(123)
)
--drop trigger trig_InsertPrint_2023_12_03_03_45_30_6706610

--4) Тестим
insert into TestTable3
values ('123')