ProtectDB_Laba1

--Создание таблицы с данными
drop table DataTable;
create table DataTable
(
    id    int primary key identity,
    name  nvarchar(20) not null,
    level nvarchar(20) not null
);
insert into DataTable
values ('Ivan Petrov', 'SECRET'),
       ('Peter Petrov', 'TOP SECRET'),
       ('Michael Sidorov', 'UNCLASSIFIED');
--select * from DataTable


--Создание таблицы уровней доступа
drop table Classifications;
create table Classifications
(
    name  nvarchar(20) primary key not null,
    level int                      not null
);
insert into Classifications
values ('TOP SECRET', 1),
       ('SECRET', 2),
       ('UNCLASSIFIED', 3);


--Создание таблицы пользователей
drop table Users;
create table Users
(
    name      nvarchar(20) primary key not null,
    clearance nvarchar(20) default ('UNCLASSIFIED')
);
insert into Users
values ('Anna', 'SECRET'),
       ('Alex', 'UNCLASSIFIED');

--Создаем роль laba_reader и выдаем права на select on DataTable
create role [laba_reader]
grant select on DataTable to [laba_reader]

--Триггер на регистрацию новых пользователей в таблицу Users
--drop trigger NewUserAdditionTrigger
CREATE OR ALTER TRIGGER NewUserAdditionTrigger
    ON DATABASE
    after CREATE_USER
    AS
BEGIN
    DECLARE @created_user NVARCHAR(2000) = EVENTDATA()
        .value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)')
    insert into Users
    values (@created_user, 'UNCLASSIFIED')

    --declare @sqlCommand nvarchar(255)
    --set @sqlCommand = 'alter role [laba_reader] add member ['+@created_user+']'
    --EXECUTE @sqlCommand

END;

--Добавляем пользователей, повышаем доступ Anna
--drop user [Alex]
--drop user [Anna]
--delete from Users where name is not null
create user [Alex] without login;
alter role [laba_reader] add member [Alex]
create user [Anna] without login;
alter role [laba_reader] add member [Anna]
update Users
set clearance = 'SECRET'
where name = 'Anna';

--select * from Users
--Создаем функцию для политики
create schema Security
go;

create or alter function [Security].[fn_DataTable_read](@clearance as nvarchar(20))
    returns table
        with schemabinding--привязка к схеме, чтобы система следила чтоб функцию не поломали из-за изменений названия колонки, удаление таблицы и т.п.
        as
        return
        with cte_row_level as (select top 1 C.level as row_level
                               from [dbo].Classifications C
                               where C.name = @clearance),
             cte_user_level as (select top 1 C.level as user_level
                                from [dbo].Classifications C
                                         join [dbo].Users U on C.name = U.clearance
                                where U.name = CURRENT_USER)
        select 1 as fn_result
        from cte_row_level, cte_user_level
        where cte_row_level.row_level >= cte_user_level.user_level
go;
--return select 1 as fn_result
--where 'dbo' = CURRENT_USER
--end
-- Создаем политику безопасности
create security policy [Security].P_RLS_DataTable_read
add filter predicate [Security].[fn_DataTable_read]([level])--1 фильтрпредикат на 1 таблицу
on [dbo].[DataTable]
with (state=on)


-- Проверка
execute as user = 'Anna'
select * from [dbo].[DataTable]
revert

execute as user = 'Alex'
select * from [dbo].[DataTable]
revert