Лаба роли

---------------------РУКОВОДИТЕЛЬ---------------------
--1) Создание роли и выдача прав
create role DirectorRole
go
grant control to DirectorRole

--2) Создание пользователя и назначение роли
create user DirectorUserTest without login
go
alter role DirectorRole add member DirectorUserTest
go

--3) Тестирование доступа

--3.0) Мы точно директор
execute as user = 'DirectorUserTest'
select user
revert

--3.1) Селект случайной таблицы
execute as user = 'DirectorUserTest'
select *
from SeregaTheDed_SQLLogin_1.users;
revert

--3.2) Селект случайного вью
execute as user = 'DirectorUserTest'
select *
from SeregaTheDed_SQLLogin_1.DatesAndOrdersAtDate;
revert

--3.3) Вызов случайной процедуры
execute as user = 'DirectorUserTest'
execute SeregaTheDed_SQLLogin_1.GetMenuToday;
revert


--3.4) Селект случайной фукнции
execute as user = 'DirectorUserTest'
select *
from SeregaTheDed_SQLLogin_1.GetCustomerList();
revert



---------------------ПРОСТОЙ_СОТРУДНИК---------------------
--1) Создание роли и выдача прав
create role EmployeeRole
go

grant select on SeregaTheDed_SQLLogin_1.categories(name) TO EmployeeRole
grant update, select on SeregaTheDed_SQLLogin_1.config(next_order_day, last_time_to_do_order) TO EmployeeRole
grant all privileges on SeregaTheDed_SQLLogin_1.orders(customer_name, date, status_id) TO EmployeeRole
grant all privileges on SeregaTheDed_SQLLogin_1.positions(status_id, date, product_id_first, product_id_second, customer_name, user_id, with_sauce, price) TO EmployeeRole
grant update, select, insert on SeregaTheDed_SQLLogin_1.products TO EmployeeRole
grant all privileges on SeregaTheDed_SQLLogin_1.rel_orders_products TO EmployeeRole
grant select on SeregaTheDed_SQLLogin_1.statuses to EmployeeRole

grant execute on SeregaTheDed_SQLLogin_1.PrintProductsTopAtAllDates to EmployeeRole
grant execute on SeregaTheDed_SQLLogin_1.GetMenuToday to EmployeeRole
grant select on SeregaTheDed_SQLLogin_1.GetRollbackCustomer to EmployeeRole



go
--2) Создание пользователя и назначение роли
create user EmployeeUserTest without login
go
alter role EmployeeRole add member EmployeeUserTest
go

--3) Тестирование доступа

--3.0) Мы точно работник НЕТ ПРАВ
execute as user = 'EmployeeUserTest'
select user
revert

--3.1) Селект случайной таблицы НЕТ ПРАВ
execute as user = 'EmployeeUserTest'
select *
from SeregaTheDed_SQLLogin_1.users;
revert

--3.2) Селект случайного вью НЕТ ПРАВ
execute as user = 'EmployeeUserTest'
select *
from SeregaTheDed_SQLLogin_1.DatesAndOrdersAtDate;
revert

--3.3) Вызов случайной процедуры НЕТ ПРАВ
execute as user = 'EmployeeUserTest'
execute SeregaTheDed_SQLLogin_1.GetMenuToday;
revert


--3.4) Селект случайной фукнции НЕТ ПРАВ
execute as user = 'EmployeeUserTest'
select *
from SeregaTheDed_SQLLogin_1.GetCustomerList();
revert

--3.5) Все категории
execute as user = 'EmployeeUserTest'
select *
from SeregaTheDed_SQLLogin_1.categories
revert
--С учетом прав:
execute as user = 'EmployeeUserTest'
select name
from SeregaTheDed_SQLLogin_1.categories
revert

--3.6) Выполнение функции, на которую выданы правы
execute as user = 'EmployeeUserTest'
select *
from SeregaTheDed_SQLLogin_1.GetRollbackCustomer()
revert

--3.7) Выполнение хранимой процедуры, на которую выданы правы
execute as user = 'EmployeeUserTest'
execute SeregaTheDed_SQLLogin_1.GetMenuToday
revert