Ограничение доступа
Если мы уверены, что подключаться к нашей базе данных могут лишь уполномоченные пользователи и что они могут запускать только те модули, на выполнение которых им явно предоставлено право, то нужно подумать о следующем уровне безопасности — ограничении доступа этих пользователей к данным.
Огромным шагом вперед в обеспечении безопасности данных стало введение ролей в Oracle7. До Oracle7 каждому пользователю приходилось явно предоставлять права доступа к каждому объекту базы данных, который ему разрешено было использовать. Этот процесс упрощается за счет того, что доступ к совокупности объектов предоставляется роли, а затем право на использование этой роли предоставляется соответствующим лицам. С помощью команды GRANT мы можем предоставить пользователям право выполнять над объектами БД (например, над таблицами) операции SELECT, INSERT, UPDATE и DELETE. Однако само по себе это не обеспечивает значительной гибкости. Мы можем ограничить доступ пользователей частями таблицы, разделив ее по горизонтали (ограничив пользователя определенными строками), по вертикали (ограничив его определенными столбцами) или и по горизонтали, и по вертикали. Как это сделать?
Вернемся к нашему примеру с таблицей PAYROLL. Мы не хотим, чтобы все пользователи видели столбец SALARY, и желаем ограничить доступ пользователей так, чтобы они могли видеть только записи о сотрудниках их отдела.
Таблица PAYROLL
| ID | NAME | DEFT | PAYMENT_PERIOD | SALARY |
| JONES | WEEKLY | |||
| K1RKUP | MONTHLY | |||
| DAVIES | WEEKLY | |||
| ARMSTRONG | MONTHLY | |||
| KEMP | MONTHLY | |||
| FISHER | WEEKLY |
Мы можем определить представление и предоставить пользователям доступ к этому представлению, а не к базовой таблице (PAYROLL). Они смогут запрашивать данные этой таблицы лишь через представление, которое ограничивает их доступ. Определение такого представления приведено ниже.
CREATE VIEW vjpayroll AS SELECT id
, name, dept
, payment_period FROM payroll WHERE dept = (SELECT dept
FROM mysys_users WHERE username = USER) WITH CHECK OPTION;
Столбец SALARY в этом примере не включен в представление, поэтому зарплату в нем увидеть нельзя, а фраза WHERE гарантирует, что пользователи смогут запрашивать данные из таблицы PAYROLL только по своему отделу.
По поводу этого решения надо сказать следующее. Во-первых, мы должны сделать так, чтобы пользователи не могли изменить свой отдел, обновив значение MYSYSJUSERS, и затем запросить записи из другого отдела. Во-вторых, с помощью этого представления пользователи могли бы обновлять, вставлять и удалять даже не относящиеся к их отделу строки таблицы PAYROLL, если бы мы не отключили эту функцию с помощью фразы WITH CHECK OPTION.
Примечание
Вряд ли представление V_PAYROLL будет обновляемым, потому что к столбцу SALARY почти наверняка применено ограничение NOT NULL. Тем не менее, мы рекомендуем использовать опцию WITH CHECK OPTION во всех ограничивающих представлениях, так как в версии 7.3 значительно увеличилось число обновляемых представлений.
Ограничение на просмотр данных с помощью представлений работает очень хорошо. Но для большой таблицы со сложными требованиями к безопасности, возможно, придется создать несколько представлений и заставить приложения решать, какое из них необходимо для конкретного пользователя. Реализовывать это внутри приложения нежелательно, поэтому нужно исследовать другие решения. Мы можем инкапсулировать все операции над таблицей PAYROLL в хранимый пакет или же разработать несколько триггеров. Давайте рассмотрим первое решение.
Использование пакетов
В пакете есть методы (процедуры/функции), позволяющие оперировать таблицей или запрашивать из нее строки. Пользователю, у которого есть разрешение на выполнение пакета, но нет полномочий на доступ к таблице, содержимое и структура таблицы непосредственно недоступны. Владелец пакета имеет полный доступ к PAYROLL, а вызывающий пользователь — нет. Когда пользователь выполняет хранимую процедуру, т.е., по сути, использует представление, он действует с разрешением на доступ, предоставленным ее владельцу.
Наша первая попытка создать такой пакет представлена в примере 10.2. Пакет k_payroll гарантирует, что записи могут удаляться только начальником отдела и что устанавливать значение столбца SALARY может только начальник отдела.
Пример построения пакета для обеспечения безопасности доступа к данным
CREATE OR REPLACE PACKAGE k_payroll AS my_dept payroll.dept%TYPE; mgr BOOLEAN;
PROCEDURE del (p_emp_id INTEGER);
PROCEDURE ins (p_emp_id INTEGER, p_name VARCHAR2
,p_dept INTEGER, p_payment_period VARCHAR2
,p_salary INTEGER);
PROCEDURE upd (p_emp__id INTEGER, p_name VARCHAR2
,p_payment_penod VARCHAR2,p_salary INTEGER);
END k_payroll;
/
CREATE OR REPLACE PACKAGE BODY k_payroll AS
mgr_flag payroll.mgr_flag%TYPE;
CURSOR c_me IS
SELECT dept,
mgr_flag
FROM mysys_users
WHERE username = USER;
FUNCTION checkdept (p_emp_id INTEGER) RETURN BOOLEAN IS
dept payroll.dept%TYPE;
CURSOR cjpayroll IS
SELECT pay.dept
FROM payroll pay
WHERE id = p_emp_id;
BEGIN
OPEN c_payroll;
FETCH cjpayroll INTO dept;
CLOSE c_payroll;
IF dept <> my_dept THEN
RETURN FALSE;
END IF;
RETURN TRUE;
END checkdept;
PROCEDURE del (p_emp_id INTEGER) IS
Удалять сотрудников могут только начальники их отделов
Записи таблицы Payroll
BEGIN
IF checkdept(p_emp_id) AND mgr THEN
DELETE payroll
WHERE id = p_emp_id;
ELSE
raise_application_error (-20001, 'Insufficient Privilege'); END IF;
END del;
PROCEDURE ins (p_emp_id INTEGER, p_name VARCHAR2
,p—dept INTEGER
,payment_period VARCHAR2
,p_salary INTEGER) IS
— Можете вставлять записи Payroll только в свой отдел
~ Устанавливать зарплату может только начальник отдела
(в противном случае устанавливается в пустое значение)
l_salary payroll.salary%TYPE;
BEGIN
IF NOT checkdept(p_emp_id) THEN
raise_application_error (-20001, 'Insufficient Privilege');
END IF;
IF NOT mgr THEN
l_salary:= NULL;
ELSE
l_salary:= p_salary;
END IF;
INSERT INTO payroll (id,name,dept,payment_period, salary)
VALUES (p_erap_id,p_name,p_dept,p_payraent_period,l_salary);
END ins;
PROCEDURE upd (p_emp_id INTEGER, p_name VARCHAR2
,p_payment_period VARCHAR2,p_salary INTEGER) IS
- Можете обновлять записи Payroll только в своем отделе
- Обновлять зарплату может только начальник отдела
(в противном случае остается без изменений)
- Отдел изменять нельзя
l_salary payroll.salary%TYPE;
CURSOR c_old_salary IS
SELECT pay.salary
FROM payroll pay
WHERE id = p_erap_id;
BEGIN
IF NOT checkdept (p_emp_id) THEN
raise applicatiori_error (-20001, 'Insufficient Privilege');
END IF;
IF NOT mgr THEN
OPEN c_old_salary;
FETCH c__old_s alary INTO l__salary;
CLOSE c_old_salary,
ELSE
l_salary:= p_salary;
END IF;
UPDATE payroll
SET name = p_name
,payment_period = p_payment_period
,salary = l_salary
WHERE id = p_emp_id;
END upd;
-Код инициализации пакета
BEGIN
OPEN c_me;
FETCH c_me
INTO ray_dept
,mgr_flag;
CLOSE c_me;
IF mgr_flag = 'Y' THEN
mgr:= TRUE;
ELSE
mgr:= FALSE;
END IF;
END k_payroll;
/