LabaNewMasking

--Разработать свой метод маскирования символьных данных и применить его
--для указанных пользователей. Инструменты: View, Триггеры, Функции


--Необходимо создать свою систему маскирования (желательно условно автоматическую)
--Для этого я предлагаю следующее:
--Создать:
--1) Таблицу с правами на столбцы(Пользователь, таблица, столбец)
--2) Таблицу с указания функций маскирования столбцов(таблица, столбец, функция)
--3) Тригер на создание таблиц, чтобы сразу регистрировать в 2)
--4) Хранимая процедура для выборки определенной таблицы(таблица, колонки)
--5) Тригер на выдачу прав для 4) для новых пользователей
--6) Создать и зарегистрировать функции маскирования
create SCHEMA MaskingTables;
create SCHEMA MaskingFunctions;
create SCHEMA ResultSchema;
go;
--1) Таблицу с правами на столбцы(Пользователь, таблица, столбец)
create table MaskingTables.ColumnsRights(
    userName nvarchar(40) not null,
    tableName nvarchar(40) not null,
    columnName nvarchar(40) not null,
    primary key(userName, tableName, columnName)
)
go;


--2) Таблицу с указания функций маскирования столбцов(таблица, столбец, функция)
create table MaskingTables.ColumnsMaskFunctions(
    tableName nvarchar(40) not null,
    columnName nvarchar(40) not null,
    maskDefinition nvarchar(255) not null,
    primary key(tableName, columnName, maskDefinition)
)
go;


--3) Тригер на создание таблиц, чтобы сразу регистрировать в 2)
--Для начала создадим функцию-заглушку чтобы она возвращала переданное значение
create function MaskingFunctions.DefaultFunction(@value as nvarchar(max))
returns nvarchar(max)
as
    begin
        return @value
    end
go;
--Пишем сам тригер
create or alter trigger RegisterNewTableColumnsTrigger
on database
after CREATE_TABLE
as
    begin
         DECLARE @created_table NVARCHAR(200) = EVENTDATA()
        .value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)')

         insert into MaskingTables.ColumnsMaskFunctions
         SELECT @created_table, sys.columns.name, 'MaskingFunctions.DefaultFunction(@value)'
         FROM sys.columns
         WHERE object_id = OBJECT_ID('dbo.' + @created_table);
         --завязка только на dbo
    end
go;
--Создадим таблицу для дальнейших тестов
create table Users
(
    id          bigint identity (1,1) primary key,
    name        nvarchar(20) not null,
    phoneNumber char(12)     not null
)
go;
insert into Users
values (N'Сарожа', '+79998887766'),
       (N'Егор', '+71112223344'),
       (N'Лаврентий', '+70000000000')
go;


--4) Хранимая процедура для выборки определенной таблицы(таблица, колонки)
--Костыль
create view Result
as
select 'dasdas' as test
where 2>1

--Сама процедура
create or alter procedure MaskingTables.SelectColumns(
    @tableName as nvarchar(200),
    @columnsName as nvarchar(200)
) as
begin
    declare @currentColumn nvarchar(200);
    declare columnsCursor cursor for
        SELECT value
        FROM STRING_SPLIT(@columnsName, ',')
        WHERE TRIM(value) <> '';
    open columnsCursor;
    fetch next from columnsCursor into @currentColumn
    while @@fetch_status=0
    begin
        set @currentColumn = TRIM(@currentColumn)
        declare @currentColumnDefinition nvarchar(200);
        if not exists(select *
                      from MaskingTables.ColumnsRights cr
                      where cr.userName = current_user and
                            cr.tableName = @tableName and
                            cr.columnName = @currentColumn)
        begin
             select @currentColumnDefinition = maskDefinition
             from MaskingTables.ColumnsMaskFunctions
             where tableName = @tableName and columnName = @currentColumn

             set @columnsName = replace(@columnsName, @currentColumn, @currentColumnDefinition+N' as '+@currentColumn)
             set @columnsName = replace(@columnsName, '@value', @currentColumn)
        end

        fetch next from columnsCursor into @currentColumn;
    end
    close columnsCursor;
    deallocate columnsCursor;

    --if exists(select *
    --    from sys.views
    --    where object_id = object_id('ResultSchema.Result'))
    --begin
    --    drop view ResultSchema.Result
    --end
    DECLARE @SQLString NVARCHAR(500)
--     К сожалению код ниже не работает
--set @SQLString = N'create VIEW [dbo].[Result]
--                                     AS
--                                     select @columnsNamee
--                                     from @tableNamee'
--DECLARE @ParamDefinition NVARCHAR(500) = N'@tableNamee nvarchar(200), @columnsNamee nvarchar(200)';
--exec sp_executesql @SQLString, @ParamDefinition, @tableNamee = @tableName, @columnsNamee = @columnsName;

    set @SQLString = N'create or alter VIEW ResultSchema.Result
                                     AS
                                     select '+@columnsName+'
                                     from '+@tableName
    print(@SQLString)
    execute(@SQLString)

    --set @SQLString = N'grant select on ResultSchema.Result to ' + current_user
    --execute(@SQLString)
end


--exec MaskingTables.SelectColumns N'Users', N'id, name, phoneNumber'
--5) Тригер на выдачу прав для 4) для новых пользователей
CREATE OR ALTER TRIGGER NewUserAdditionTrigger
    ON DATABASE
    after CREATE_USER
    AS
BEGIN
    DECLARE @createdUser NVARCHAR(200) = EVENTDATA()
        .value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)')
    declare @sqlCommand nvarchar(255)
    set @sqlCommand = 'grant execute on MaskingTables.SelectColumns to ['+@createdUser+']'
    --DECLARE @ParamDefinition NVARCHAR(500) = N'@createdUserr NVARCHAR(256)'
    exec sp_executesql @sqlCommand--, @ParamDefinition, @createdUserr = @createdUser
    set @sqlCommand = 'grant ALTER, CONTROL, SELECT on SCHEMA::ResultSchema to ['+@createdUser+']'
    exec sp_executesql @sqlCommand
    set @sqlCommand = 'GRANT CREATE VIEW TO ['+@createdUser+']'
    exec sp_executesql @sqlCommand
END;
--6) Создать и зарегистрировать функции маскирования
--Для имени
create function MaskingFunctions.MaskName(@value as nvarchar(max))
returns nvarchar(max)
as
    begin
        return N'***MaskedName***'
    end
go;
--Для номера телефона
create function MaskingFunctions.MaskPhone(@value as char(12))
returns char(12)
as
    begin
        return '***' + substring(@value, 8, 4)
    end
go;
--Регистрируем
update MaskingTables.ColumnsMaskFunctions
set MaskingTables.ColumnsMaskFunctions.maskDefinition = N'MaskingFunctions.MaskName(@value)'
where tableName = 'Users' and columnName ='name'

update MaskingTables.ColumnsMaskFunctions
set MaskingTables.ColumnsMaskFunctions.maskDefinition = N'MaskingFunctions.MaskPhone(@value)'
where tableName = 'Users' and columnName ='phoneNumber'

grant create view to [ПровидецВсего]
------------------ТЕСТИРОВАНИЕ-------------------------
--1) Создадим пользователей и выдадим им разные права
-- drop user [ПровидецИмени]
-- drop user [ПровидецНомераТелефона]
-- drop user [ПровидецВсего]
-- create user [ПровидецИмени] without login;
-- go;
-- create user [ПровидецНомераТелефона] without login;
-- go;
-- create user [ПровидецВсего] without login;
-- go;
-- select current_user
create user [ПровидецИмени] without login;
insert into MaskingTables.ColumnsRights values
(N'ПровидецИмени', N'Users', N'name')
go;
create user [ПровидецНомераТелефона] without login;
insert into MaskingTables.ColumnsRights values
(N'ПровидецНомераТелефона', N'Users', N'phoneNumber')
go;
create user [ПровидецВсего] without login;
insert into MaskingTables.ColumnsRights values
(N'ПровидецВсего', N'Users', N'name'),
(N'ПровидецВсего', N'Users', N'phoneNumber')
go;

--Проверяем
execute as user = N'ПровидецИмени'
exec MaskingTables.SelectColumns N'Users', N'id, name, phoneNumber'
select *
from ResultSchema.Result
revert

execute as user = N'ПровидецНомераТелефона'
exec MaskingTables.SelectColumns N'Users', N'id, name, phoneNumber'
select *
from ResultSchema.Result
revert

execute as user = N'ПровидецВсего'
exec MaskingTables.SelectColumns N'Users', N'id, name, phoneNumber'
select *
from ResultSchema.Result
revert