Приложение 12 – безопасность Роли

--роль начальник караула

CREATE ROLE NachKar

--рольначальникачасти

CREATEROLENach

Праванароли

--праваролиНачКар

GRANT EXECUTE ON PHydrantInsert TO NachKar

GRANT EXECUTE ON PPondInsert TO NachKar

GRANT EXECUTE ON PStreetInsert TO NachKar

GRANT EXECUTE ON PToOrganozationInsert TO NachKar

GRANT EXECUTE ON POrganizationInsert TO NachKar

GRANT EXECUTE ON PWaterSourceInsert TO NachKar

GRANT EXECUTE ON PInspectionInsert TO NachKar

GRANT EXECUTE ON PDefectInsert TO NachKar

GRANT EXECUTE ON PTestDefectInsert TO NachKar

GRANT EXECUTE ON PHydrantUpdate TO NachKar

GRANT EXECUTE ON PPondUpdate TO NachKar

GRANT EXECUTE ON PStreetUpdate TO NachKar

GRANT EXECUTE ON PToOrganozationUpdate TO NachKar

GRANT EXECUTE ON POrganizationUpdate TO NachKar

GRANT EXECUTE ON PWaterSourceUpdate TO NachKar

GRANT EXECUTE ON PInspectionUpdate TO NachKar

GRANT EXECUTE ON PDefectUpdate TO NachKar

GRANT EXECUTE ON PTestDefectUpdate TO NachKar

GRANT EXECUTE ON PInspectionDelete TO NachKar

GRANT EXECUTE ON PDefectDelete TO NachKar

GRANT EXECUTE ON PPlanProverki TO NachKar

GRANT EXECUTE ON PWSFind TO NachKar

--праваролиНачальникачасти

GRANT EXECUTE ON PHydrantInsert TO Nach

GRANT EXECUTE ON PPondInsert TO Nach

GRANT EXECUTE ON PStreetInsert TO Nach

GRANT EXECUTE ON PToOrganozationInsert TO Nach

GRANT EXECUTE ON POrganizationInsert TO Nach

GRANT EXECUTE ON PWaterSourceInsert TO Nach

GRANT EXECUTE ON PInspectionInsert TO Nach

GRANT EXECUTE ON PDefectInsert TO Nach

GRANT EXECUTE ON PTestDefectInsert TO Nach

GRANT EXECUTE ON PHydrantUpdate TO Nach

GRANT EXECUTE ON PPondUpdate TO Nach

GRANT EXECUTE ON PStreetUpdate TO Nach

GRANT EXECUTE ON PToOrganozationUpdate TO Nach

GRANT EXECUTE ON POrganizationUpdate TO Nach

GRANT EXECUTE ON PWaterSourceUpdate TO Nach

GRANT EXECUTE ON PInspectionUpdate TO Nach

GRANT EXECUTE ON PDefectUpdate TO Nach

GRANT EXECUTE ON PTestDefectUpdate TO Nach

GRANT EXECUTE ON PHydrantDelete TO Nach

GRANT EXECUTE ON PPondDelete TO Nach

GRANT EXECUTE ON PStreetDelete TO Nach

GRANT EXECUTE ON PToOrganozationDelete TO Nach

GRANT EXECUTE ON POrganizationDelete TO Nach

GRANT EXECUTE ON PWaterSourceDelete TO Nach

GRANT EXECUTE ON PInspectionDelete TO Nach

GRANT EXECUTE ON PDefectDelete TO Nach

GRANT EXECUTE ON PTestDefectDelete TO Nach

GRANT EXECUTE ON PPlanProverki TO Nach

GRANT EXECUTE ON PLetterToOrganization TO Nach

GRANT EXECUTE ON PLetterToGosPN TO Nach

GRANT EXECUTE ON PWSFind TO Nach

Пользователи

USE WaterSources

CREATE USER NachKar1

WITHOUT LOGIN

USE WaterSources

CREATE USER NachKar2

WITHOUT LOGIN

USE WaterSources

CREATE USER NachKar3

WITHOUT LOGIN

USE WaterSources

CREATE USER NachKar4

WITHOUT LOGIN

USE WaterSources

CREATE USER Nach1

WITHOUT LOGIN

USE WaterSources

CREATE USER ZamNach1

WITHOUTLOGIN

Добавлениепользователейкролям

sp_addrolememberNachKar,

NachKar1

sp_addrolemember NachKar,

NachKar2

sp_addrolemember NachKar,

NachKar3

sp_addrolemember NachKar,

NachKar4

sp_addrolemember Nach,

Nach1

sp_addrolemember Nach,

ZamNach1

Приложение 13 – оптимизация

CREATE NONCLUSTERED INDEX HydrantIDWaterSource

ON Hydrant (IDWaterSource ASC)

CREATE NONCLUSTERED INDEX PondIDWaterSource

ON Pond (IDWaterSource ASC)

CREATE UNIQUE INDEX UniqueStreet

ON Street (NameStreet ASC)

CREATE NONCLUSTERED INDEX OrganizationIDTypeOfOrganization

ON Organization (IDTypeOfOrganization ASC)

CREATE UNIQUE INDEX UniqueNameOrganization

ON Organization (NameOrganization ASC)

CREATE NONCLUSTERED INDEX WaterSourceIDStreet

ON WaterSource (IDStreet ASC)

CREATE NONCLUSTERED INDEX WaterSourceIDOrganization

ON WaterSource (IDOrganization ASC)

CREATE NONCLUSTERED INDEX InspectionIDWaterSource

ON Inspection (IDWaterSource ASC)

CREATE NONCLUSTERED INDEX DefectIDTypeOfOrganization

ON Defect (IDTypeOfOrganization ASC)

CREATE NONCLUSTERED INDEX LetterIDOrganization

ON Letter (IDOrganization ASC)

CREATE NONCLUSTERED INDEX LetterIDInspection

ON Letter (IDInspection ASC)

CREATE NONCLUSTERED INDEX LetterIDLetter

ON Letter (IDLetter ASC)

CREATE NONCLUSTERED INDEX TestDefectIDDefect

ON TestDefect (IDDefect ASC)

CREATE NONCLUSTERED INDEX TestDefectIDInspection

ON TestDefect (IDInspection ASC)

CREATE NONCLUSTERED INDEX TestDefectIDLetter

ON TestDefect (IDLetter ASC)