Приложение 9 Безопасность

-------------------------------------------------------------------------------------------------------------------

--безопастность

--роль диспетчеры

CREATEROLEDispatchers

--роль Администраторы

CREATEROLEAdministrators

--права роли Диспетчеры

GRANT EXECUTE ON PDefectINSERT TO Dispatchers

GRANT EXECUTE ON PDefectsINSERT TO Dispatchers

GRANT EXECUTE ON PRequestINSERT TO Dispatchers

GRANT EXECUTE ON POrdersINSERT TO Dispatchers

GRANT EXECUTE ON UpdateDefect TO Dispatchers

GRANT EXECUTE ON UpdateDefects TO Dispatchers

GRANT EXECUTE ON UpdateRequest TO Dispatchers

GRANT EXECUTE ON UpdateOrders TO Dispatchers

GRANT EXECUTE ON DeleteDefect TO Dispatchers

GRANT EXECUTE ON DeleteDefects TO Dispatchers

GRANT EXECUTE ON DeleteRequest TO Dispatchers

GRANT EXECUTE ON DeleteOrders TO Dispatchers

GRANT SELECT ON Defect TO Dispatchers

GRANT SELECT ON Defects TO Dispatchers

GRANT SELECT ON Request TO Dispatchers

GRANT SELECT ON Orders TO Dispatchers

GRANT SELECT ON PrintElevator TO Dispatchers

GRANT SELECT ON PrintPodr TO Dispatchers

GRANT SELECT ON print1El TO Dispatchers

GRANT SELECT ON printall TO Dispatchers

--праваролиАдминистраторы

GRANT EXECUTE ON DeleteStreet TO Administrators

GRANT EXECUTE ON DeleteElectrician TO Administrators

GRANT EXECUTE ON DeleteTypeOfApplication TO Administrators

GRANT EXECUTE ON DeleteElevators TO Administrator

GRANT EXECUTE ON UpdateStreet TO Administrators

GRANT EXECUTE ON UpdateElectrician TO Administrators

GRANT EXECUTE ON UpdateTypeOfApplication TO Administrators

GRANT EXECUTE ON UpdateElevators TO Administrators

GRANT EXECUTE ON PStreetINSERT TO Administrators

GRANT EXECUTE ON PElectricianINSERT TO Administrators

GRANT EXECUTE ON PTypeOfApplicationINSERT TO Administrators

GRANT EXECUTE ON PElevatorsINSERT TO Administrators

GRANT SELECT ON Street TO Administrators

GRANT SELECT ON Electrician TO Administrators

GRANT SELECT ON TypeOfApplication TO Administrators

GRANT SELECT ON Elevators TO Administrators

-- Пользователь – Диспетчер1

USE Elevator

CREATE USER Dispatcher1

WITHOUT LOGIN

-- Пользователь – Диспетчер2

USE Elevator

CREATE USER Dispatcher2

WITHOUT LOGIN

-- Пользователь – Диспетчер3

USE Elevator

CREATE USER Dispatcher3

WITHOUT LOGIN

-- Пользователь – Администратор1

USE Elevator

CREATE USER Administrator1

WITHOUTLOGIN

-- Пользователь – Администратор2

USEElevator

CREATE USER Administrator2

WITHOUTLOGIN

--добавлениечленовврольDispatchersбазыданных

sp_addrolemember Dispatchers,

Dispatcher1

sp_addrolemember Dispatchers,

Dispatcher2

sp_addrolemember Dispatchers,

Dispatcher3

--добавлениtчленов в роль Administrators базыданных

sp_addrolemember Administrators,

Administrator1

sp_addrolemember Administrators,

Administrator2

Приложение 10 Индексы

-------------------------------------------------------------------------------------------------------------------

--индексы

--Street

CREATE UNIQUE INDEX UniqueStreetIndex

ON Street (NameStreet ASC)

--Electrician

CREATE UNIQUE INDEX UniqueElectricianIndex

ON Electrician (FIO ASC)

--TypeOfApplication

CREATE UNIQUE INDEX UniqueTypeOfApplicationIndex

ON TypeOfApplication (TypeOfApp ASC)

--Defect

CREATE UNIQUE INDEX UniqueDefectIndex

ON Defect (TypeOfDefect ASC)

--Elevators

CREATE NONCLUSTERED INDEX ElevatortsIDStreet

ON Elevators (IDStreet ASC)

CREATE UNIQUE INDEX UniqueElevatorsIndex

ON Elevators (Building, Door, PersonalNumber ASC)

--Request

CREATE NONCLUSTERED INDEX RequestIDElevator

ON Request (IDElevator ASC)

CREATE NONCLUSTERED INDEX RequestIDtypeOfApplication

ON Request (IDTypeOfApplication ASC)

--Orders

CREATE NONCLUSTERED INDEX OrdersIDElectrician

ON Orders (IDElectrician ASC)

CREATE NONCLUSTERED INDEX OrdersIDRequest

ON Orders (IDRequest ASC)

--Defects

CREATE NONCLUSTERED INDEX DefectsIDOrders

ON Defects (IDOrders ASC)

CREATE NONCLUSTERED INDEX DefectsIDDefect

ON Defects (IDDefect ASC)