Уменьшение размера базы данных AdventureWorks.

 

Перед уменьшением размера базы данных полезно выяснить, сколько места в базе данных занято данными и сколько свободно: в контекстном меню базы данных AdventureWorks выбрать: Tasks → Shrink → Files. Затем в том же окне Вы можете выбрать файлы и метод для уменьшения (Вам предлагается уменьшить файл, добавлнный в предыдущем пункте лабораторной работы).

Лабораторная работа №7. Назначение прав на обекты SQL Server 2005.

Задание:

1. Создать два логина SQL Server 2005. Для первого логина использовать имя Login1 и пароль p@ssw0rd1, для второго - Login2 и p@ssw0rd2.

2. Предоставить логину Login1 права на схему HumanResources в базе данных AdventureWorks и убедиться, что он может выполнять запросы к любым таблицам этой схемы. Проверить также осутствие у логина Login2 прав на выполнение запросов к таблицам в схеме HumanResources.

3. Предоставить логину Login2 права на выполнение запроса от имени логина Login1. Написать код запроса с использованием конструкции EXECUTE AS, в ходе которого пользователь Login2 смог бы выполнять запрос к таблице HumanResources.Employee от имени пользователя Login1.

 

PVSCN

 

К пункту 1 задания - создание логинов:

 

Запустите SQL Server Management Studio и подключитесь к своему локальному серверу. Затем нажмите кнопку New Query, чтобы открыть редактор кода Transact-SQL.

Введите и выполните в окне редактора кода следующие команды:

USE master;

GO

CREATE LOGIN Login1 WITH PASSWORD = N'p@ssw0rd1', DEFAULT_DATABASE = master, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;

GO

CREATE LOGIN Login2 WITH PASSWORD = N'p@ssw0rd2', DEFAULT_DATABASE = master, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;

GO

 

К пункту 2 задания — предоставление разрешений на схему и проверка прав:

Чтобы создать объект пользователя в базе данных AdventureWorks для логина Login1, можно выполнить следующий код:

USE AdventureWorks;

GO

CREATE USER Login1 FOR LOGIN Login1;

GO

Чтобы предоставить пользователю Login1 права на чтение для объектов схемы HumanResources, можно выпонить код:

GRANT SELECT ON SCHEMA:: HumanResources TO Login1;

Для проверки полученных прав можно подключиться от имени Login1 (File → New → Database Engine Query)и выполнить любой запрос на выборку.Если попытаться выполнить запрос от имени Login2, то вернётся ошибка.

К пункту 3 задания — предоставление права на выполнение с помощью выражения EXECUTE AS:

 

Для того, чтобы предоставить право логину Login2 выполнять команды от имени логина Login1, можно выполнить следующий код (от имени администратора сервера):

 

USE master;

GO

GRANT IMPERSONATE ON LOGIN::Login1 TO Login2;

GO

 

Для проверки предоставленных прав и изменения контекста выполнения можно воспользоваться следующим запросом (его нужно выполнить, подключившись к серверу от имени логина Login2):

 

EXECUTE AS LOGIN = 'Login1';

GO

USE AdventureWorks;

GO

SELECT * FROM HumanResources.Employee;

GO

Лабораторная работа №8.
Шифрование информации в таблицах баз данных SQL Server 2005.

 

Задание:

В соответствии с новой политикой организации, информация о заработной плате сотрудникам за предыдущие периоды должна храниться только в зашифрованном виде с использованием алгоритма шифрования AES с длиной ключа не менее 256 бит.

Для этого:

1. Создать в базе данных AdventureWorks симметричный ключ PayKey для применения с алгоритмом AES_256. Ключ должен быть защищён паролем P@ssw0rd.

2. Создать в этой базе данных копию таблицы HumanResources. EmployeePayHistory. Новая копия должна называться HumanResources. EmployeePayHistoryEncrypted и все данные в ней должны быть зашифрованы при помощи созданного Вами симметричного ключа.

3. Выполнить запрос, который бы вернул все данные из зашифрованной таблицы HumanResources. EmployeePayHistoryEncrypted

 

Примечание:

Зашифрованную информацию нельзя поместить в стобцы типа int, money и т.п. Поэтому создание таблицы HumanResources. EmployeePayHistoryEncrypted придётся производить вручную. При этом можно использовать для всех столбцов этой таблицы один и тот же тип данных — nvarchar(100). Кроме того, несимвольные типы данных необходимо преобразовать в символьные (например, nvarchar(100)) перед передачей их шифрующей функции.

Решение:

 

К пункту 1 задания - создание симметричного ключа:

Команда на создание симметричного ключа в соответствии с поставленными условиями может выглядеть так:

USE AdventureWorks;

GO

CREATE SYMMETRIC KEY PayKey

WITH ALGORITHM = AES_256

ENCRYPTION BY PASSWORD = 'P@ssw0rd;

GO

 

Убедитесь, используя SQL Server Management, что ключ был успешно создан:

Databases → AdventureWorks → Security → Symmetric Keys.

 

К пункту 2 задания - создание зашифрованной копии таблицы:

Команда для создания зашифрованной копии таблицы HumanResources. EmployeePayHistory может выглядеть так:

 

USE AdventureWorks;

GO

-- Создаём таблицу для вставки шифрованных данных

CREATE TABLE HumanResources.EmployeePayHistoryEncrypted

(EmployeeID nvarchar(100),

RateChangeDate nvarchar(100),

Rate nvarchar(100),

PayFrequency nvarchar(100),

ModifiedDate nvarchar(100));

GO

-- Открываем симметричный ключ

OPEN SYMMETRIC KEY PayKey DECRYPTION BY PASSWORD = 'P@ssw0rd ';

GO

-- Вставляем данные в таблицу при помощи INSERT INTO

INSERT INTO AdventureWorks.HumanResources.EmployeePayHistoryEncrypted

(EmployeeID, RateChangeDate, Rate, PayFrequency, ModifiedDate)

SELECT

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), EmployeeID)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), RateChangeDate)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), Rate)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), PayFrequency)),

EncryptByKey(Key_GUID('PayKey'), CONVERT(nvarchar(100), ModifiedDate))

FROM AdventureWorks.HumanResources.EmployeePayHistory;

GO

 

Убедитесь, что в таблице EmployeePayHistoryEncrypted данные таки да зашифрованы:

 

SELECT * FROM EmployeePayHistoryEncrypted;

GO

 

К пункту 3 задания - запрос к зашифрованным данным:

Запрос, возвращающий данные из нашей зашифрованной таблицы может выглядеть так:

 

OPEN SYMMETRIC KEY PayKey DECRYPTION BY PASSWORD = 'P@ssw0rd ';

GO

SELECT

Convert(Nvarchar(100), DecryptByKey(EmployeeID)) AS EmployeeID,

Convert(Nvarchar(100), DecryptByKey(RateChangeDate)) AS RateChangeDate,

Convert(Nvarchar(100), DecryptByKey(Rate)) AS Rate,

Convert(Nvarchar(100), DecryptByKey(PayFrequency)) AS PayFrequency,

Convert(Nvarchar(100), DecryptByKey(ModifiedDate)) AS ModifiedDate

FROM AdventureWorks.HumanResources.EmployeePayHistoryEncrypted;

GO

 

Откройте новую сессию подключения к Вашему SQL Server и выполните этот же запрос на выборку (SELECT...),но без открытия симметричного ключа (OPEN SYMMETRIC KEY...)

Примечание:

В реальной жизни, возможно, потребуется произвести дополнительные преобразования, чтобы вернуть информацию в виде значений с типами данных int, money и т.п. В этом примере для простоты все данные возвращаются как nvarchar(100).