Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

SQL> CREATE TABLE user2.Tab(Pole1 NUMBER);

CREATE TABLE user2.Tab(Pole1 NUMBER)

ERROR at line 1: ORA-01031: insufficient privileges

SQL> CONNECT system/manager@Komp1416;

Connected.

SQL> GRANT CREATE ANY TABLE TO user1;

Grant succeeded.

SQL> CONNECT user1/stud @Komp1416;

Connected.

SQL> CREATE TABLE user2.Tab(Pole1 NUMBER);

Table created.

Відзначимо, що користувач user1, який створив таблицю Tab у

схемі user2, не має ніяких прав на операції з даною таблицею.

Користувач user2 може виконувати вибірку з таблиці Tab, а

користувач user1 – ні. Дозвіл на виконання конкретної операції

повинен бути наданий явно. Після того, як адміністратор надав

глобальний дозвіл на вибірку користувачу user1, операція

проходить успішно.

Привілей створення користувача ніяк не пов’язаний з

можливістю надання новому користувачу якихось прав.

Приведений нижче приклад показує, що користувач user1,

володіючи відповідним привілеєм, успішно створив користувача

user2, але не передав йому права на створення сесії.

SQL> CONNECT system/manager@Komp1416;

Connected.

SQL> GRANT CREATE USER TO user1;

Grant succeeded.

SQL> CONNECT user1/stud@Komp1416;

Connected.

SQL> CREATE USER user2 IDENTIFIED BY stud2;

User created.

SQL> GRANT CREATE SESSION TO user2;

GRANT CREATE SESSION TO user2

ERROR at line 1:

ORA-01031: insufficient privileges

Розглянемо інший приклад. Нехай користувач user1 створив

процедуру InsRec, яка вставляє запис в таблицю Таb. Спроба

виконати цю процедуру користувачем user2 відкидається

системою. Після надання адміністратором користувачу user2

привілею EXECUTE ANY PROCEDURE запуск процедури проходить

успішно. Зауважимо, що явна вставка (не через процедуру) рядків

у таблицю Таb користувачу user2 не дозволена.

SQL> CONNECT user2/stud2 @Komp1416;

Connected.

SQL> BEGIN

2 user1.InsRec(56);

3 END;

4 /

BEGIN user1.InsRec(56); END;

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'user1.INSREC' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> CONNECT system/manager@Komp1416;

Connected.

SQL> GRANT EXECUTE ANY PROCEDURE TO user2;

Grant succeeded.

SQL> CONNECT user2/stud2 @Komp1416;

Connected.

SQL> BEGIN

1 user1.InsRec(56);

2 END;

3 /

PL/SQL procedure successfully completed.

SQL> INSERT INTO user1.Tab VALUES(IOO);

INSERT INTO user1.Tab VALUES(100)

ERROR at line 1:

ORA-00942: table or view does not exist

Специфікою табличної області є можливість її переводу в

автономний або оперативний стан, яким можна керувати за

допомогою привілею MANAGE TABLESPACE. Користувачу може

бути наданий привілей по керуванню табличною областю, але не

більш сильні привілеї, пов’язані зі зміною табличної області,

наприклад, привілей додавання файлу для збереження даних.

Приклад, який ілюструє надання системних привілеїв по

керуванню табличною областю:

SQL> CONNECT system/manager@Komp1416;

Connected.

SQL> GRANT MANAGE TABLESPACE TO userl;

Grant succeeded.

SQL> CONNECT user1/stud @Komp1416 ;

Connected.

SQL> ALTER TABLESPACE nafta OFFLINE NORMAL;

Tablespace altered.

SQL> ALTER TABLESPACE nafta

2 ADD DATAFILE 'nafta.dbf';

ALTER TABLESPACE nafta ADD DATAFILE 'nafta.dbf'

ERROR at line 1:

ORA-01031: insufficient privileges

4.4.3 Ролі

Велике число користувачів, статус яких вимагає різноманітних

привілеїв для доступу до ресурсів БД, створює значний об’єм

рутинних робіт адміністратору. Oracle має засоби для їх

автоматизації за допомогою ролей. Роль являє собою

поіменований набір привілеїв, який можна надати користувачу або

іншій ролі. Є два типи ролей – системні і створені користувачем.

Найсильнішою системною роллю є роль DBA, вона включає всі

системні привілеї з параметром WITH ADMIN OPTION.

Створити роль може користувач, який має системний привілей

CREATE ROLE. Для того, щоб надати користувачу якусь роль,

необхідно її створити і дозволити в поточному сеансі. Звернемо

увагу на аналогію з користувачем: його потрібно створити і надати

йому мінімальні привілеї. Команда створення ролі в Oracle

використовує такий синтаксис:

CREATE ROLE ім'я_ролі

[NOT IDENTIFIED або

IDENTIFIED BY пароль_ролі або EXTERNALLY];

Параметр NOT IDENTIFIED означає, що аутентифікація за

допомогою пароля не виконується. Параметр IDENTIFIED BY

паролъ_ролі означає, що при використанні ролі виконується

аутентифікація за допомогою пароля, а параметр – IDENTIFIED

EXTERNALLY, що аутентифікація виконується засобами операційної

системи. За замовчуванням перевірка дозволу не забезпечується.

Розглянемо приклад створення ролі sat, якій наданий системний

привілей на виконання вибірки з будь-якої таблиці, і ролі studrole на

виконання вибірки, модифікації і вилучення рядків з будь-якої

таблиці. Для ролі studrole передбачений захист паролем stud.

SQL> CONNECT system/manager@Komp1416;

Connected.

SQL> CREATE ROLE sat;

Role created.

SQL> CREATE ROLE studrole IDENTIFIED BY stud;

Role created.

SQL> GRANT SELECT ANY TABLE TO sat;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE, UPDATE ANY TABLE,

2 DELETE ANY TABLE TO studrole;

Grant succeeded.

SQL> GRANT sat TO user1;

Grant succeeded.

SQL> GRANT studrole TO user1;

Grant succeeded.

Команда, яка дозволяє або забороняє використання ролей, має

такий синтаксис:

SET ROLE роль [IDENTIFIED BY пароль_ролі]

[,роль [IDENTIFIED BY пароль_ролі]

ALL [EXCEPT список_ролей] або NONE;

Параметр ALL необов’язковий. EXCEPT використовується для

дозволу всіх ролей, крім ролей, перерахованих після слова EXCEPT.

У списку ролей, перерахованих після параметра EXCEPT, можуть

бути задані тільки ролі, явно призначені користувачу, тобто

неприпустиме використання параметра для виключення зі списку

ролей, призначених іншими ролями. Даний параметр застосується

тільки для ролей, не захищених паролем. Захищені паролем ролі

повинні бути дозволені явною вказівкою. Якщо роль була

захищена паролем, то для її дозволу потрібно в реченні IDENTIFIED

BY вказати пароль.

Параметр NONE використовується для заборони всіх ролей.

Розглянемо приклад. Нехай у системі визначені ролі sat і

studrole. Спочатку всі ролі заборонені, і користувач user1 не має

права виконати вибірку з таблиці Таb схеми user2. Після дозволу

йому ролі sat вибірка проходить успішно, але операція з

модифікації даних відкидається. Після надання йому ролі studrole,

захищеної паролем studnew, операція модифікації таблиці Таb зі

схеми user2 також виконується.

SQL> CONNECT user1/stud @Komp1416;

Connected.

SQL> SET ROLE NONE;

Role set.

SQL> SELECT * FROM user2.Tab;

SELECT * FROM user2.tab

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> SET ROLE sat;

Role set.

SQL> SELECT * FROM user2.Tab;

Pole

123

SQL> UPDATE user2.Tab SET Pole = 678;

UPDATE user2.Tab SET Pole = 678

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> SET ROLE studrole IDENTIFIED BY studnew;

Role set.

SQL> UPDATE user2.Tab SET Pole = 678;

1 row updated.

Для скасування привілеїв доступу до об’єкта в Oracle

використовується така команда:

REVOKE перелік_привілеїв_доступу_до_об'єкта або ALL [PRIVILEGES]

ON [схема.]ім’я_об'єкта

FROM перелік_користувачів чи ролей або PUBLIC

[CASCADE CONSTRAINTS];

Параметр перелік_привілеїв_доступу_до_об’екта може приймати

значення SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER,

EXECUTE, INDEX.

Якщо необов’язковий параметр схема не зазначений, то

передбачається, що об’єкт належить схемі користувача, який

виконує команду.

Ключове слово PUBLIC скасовує привілей доступу до об’єкта

для всіх користувачів. При скасуванні системного привілею або

ролі з параметром PUBLIC привілеї, надані користувачу явно чи

через роль, не скасовуються.

Конструкція CASCADE CONSTRAINTS скасовує обмеження

цілісності, надані за допомогою ключового слова REFERENCES.

4.4.4 Профілі

Профіль користувача являє собою поіменований набір

обмежень на ресурси системи. Його застосування є додатковим

заходом щодо захисту системи. Наприклад, обмеживши

процесорний час або кількість операцій за сеанс, адміністратор

може позбавити шкідника можливості завантажувати систему

зайвою роботою. Обмеживши час перебування клієнта в

неактивному стані, можна захистити систему від порушника, який

скористався тимчасовою відсутністю законного користувача і

дістав доступ до системи.

Початково в системі існує один профіль DEFAULT, у якого

відсутні будь-які обмеження на ресурси системи. Він

призначається всім користувачам за замовчуванням. Для

включення системи обмежень необхідно виконати команду SQL:

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Новий профіль можна створити за допомогою такої команди:

CREATE PROFILE ім’я_профілю

LIMIT перелік імен та значень параметрів обмеження;

Наведемо декілька прикладів параметрів обмеження, це:

SESSION_PER_USER – максимальна кількість одночасних сесій

користувача;

CRU_PER_SESSION – максимальний сумарний процесорний час на

одну сесію;

CRU_PER_CALL – максимальний час на одну операцію;

IDLE_TIME – максимальний час неактивного стану користувача.

Значеннями параметра обмеження можуть бути ціле число (час

вимірюється в сотих долях секунди) або UNLIMITED.

Інформацію про поточні профілі можна одержати з

представлення dba_profiles за допомогою такої команди:

SQL> select * from dba_profiles;

Запитання для самоперевірки

1. Що таке аутентифікація користувача?

2. Якими привами наділений щойно створений користувач?

3. Розрізніть поняття: профіль, роль, привілей.

4. Запишіть команду надання користувачу всіх привілеїв.

5. Який привілей надає користувачу найменші права?

6. Запишіть команди для надання та скасування привілеїв

користувачу.

7. Що таке принцип мінімальних привілеїв?

8. Як можна використати привілеї для обмеження дій

користувача так, щоб він не міг зіпсувати дані таблиці?

9. Чи можна надати користувачу декілька привілеїв за

допомогою лише одної команди?

10. Як можна одержати інформацію про привілеї заданого

користувача?

11. Чим відрізняються системні ролі від створених

користувачем?

12. Як може користувач змінити дані таблиці, не маючи прямого

доступу до неї?

13. Як можна надати користувачу декілька привілеїв одною

командою?

4.5 Засоби збереження даних

4.5.1 Типи даних Oracle

Oracle підтримує всі типи даних стандарту ANSI SQL92, та

свої, додаткові. Розглянемо найбільш поширені з них.

CHARACTER(довжина) або CHAR(довжина) або CHAR – літерні дані

довжиною до 255 символів. Якщо довжина менша, то речення

доповнюється пробілами до 255. Якщо довжина не вказана, то за

замовчуванням вона дорівнює 1. Приклад: Slovo CHAR(12).

VARCHAR(довжина) – літерні дані довжиною до 4000 символів.

Якщо довжина менша, то речення займає реально потрібну кількість

пам’яті. За замовчуванням довжина = 1. Приклад оголошення: Slovo

VARCHAR(12).

VARCHAR2(довжина) – те ж, що VARCHAR, лише для Oracle,

незалежно від можливих майбутніх змін у стандарті SQL.

INTEGER або INT – ціле число від -2

до 2

. Приклад: Cile INT.

NUMBER(мантиса, порядок) – число в формі з фіксованою

крапкою. мантиса може складати від 1 до 38 знаків (за

замовчуванням 38), порядок – до 127 (за замовчуванням 0).

Приклад: Kilk NUMBER(6).

RAW(довжина) – двійкові рядки змінної довжини до 2000 байт.

Від CHAR i VARCHAR відрізняються тим, що при передачі між

клієнтом і сервером не відбувається автоматичного перетворення

даних. Приклад: Tekct RAW.

DATE – дата (час), може приймати значення від 01.01.4712 року

до н.е. до 31.12.4712 року. Враховуючи наявність різних форматів

дати, для її формування можна використати функцію перетворення

рядка в дату TO_DATE(рядок, формат). Приклад: Data DATE;

Data=TO_DATE(’12.06.2009’, ’DD.MM.YYYY’);

ROWID – спеціальний тип даних, номер запису таблиці. Нижче

поданий приклад його застосування для знищення тих записів

таблиці Tab, де повторюються значення полів.

SQL> CREATE TABLE Tab(a NUMBER, b NUMBER);

Table created

SQL> INSERT INTO Tab VALUES(1,2);

1 row created

SQL> INSERT INTO Tab VALUES(1,2);

1 row created

SQL> DELETE FROM Tab

2 WHERE ROWID NOT IN (SELECT MIN(ROWID)

3 FROM Tab

4 GROUP BY a,b);

1 row deleted

4.5.2 Послідовності

Послідовність автоматично генерує послідовність цілих чисел.

Вона, як правило, використовується в якості значень первинних

ключів таблиці. Послідовність є аналогом типу Лічильник MS

Access.

Оператор утворення послідовності має такий вигляд:

CREATE SEQUENCE ім’я_послідовності

INCREMENT BY нарощення

START WITH початкове значення

ORDER або NOORDER;

Нарощення послідовності відбувається під час звертання до неї

в запитах. Параметр ORDER означає генерацію чергового значення

точно в порядку звертання, тобто якщо до однієї і тієї ж

послідовності почергово звертаються декілька різних користувачів,

то кожен з них одержить числа з проміжками.

Команда SELECT використовує звертання до послідовності за

допомогою псевдостовпця:

ім’я_послідовності.псевдостовпець,

який може мати назву NEXTVAL – чергове значення або CURVAL –

поточне значення.

Нижче подано два приклади роботи послідовності Poslidow, яка

починається з 2 і нарощується на 1, та один – для занесення даних

у таблицю Pidpr (код підприємства і назва підприємства Київ).

SQL> CREATE SEQUENCE Poslidow

2 START WITH 2;

Sequence created

SQL> SELECT Poslidow.NEXTVAL FROM Pidpr;

NEXTVAL

SQL> SELECT Poslidow.NEXTVAL FROM Pidpr;

NEXTVAL

SQL> INSERT Kodpp, Nazpp INTO Pidpr VALUES(Poslidow.NEXTVAL, 'Київ');

1 row created

Оператор вилучення послідовності має такий синтаксис:

DROP SEQUENCE [ім'я_схеми.]ім'я_послідовності;

Однією із ситуацій, коли необхідно знищувати послідовність, є

повторний її старт. При цьому необхідно враховувати, що об’єкти,

які залежать від цієї послідовності, стануть непридатними для

використання. Тому для приведення послідовності до необхідного

стану (зсув на необхідне число елементів) рекомендується

використовувати більш витончені способи. Наприклад, за

допомогою анонімного PL/SQL-блоку обчислити наступне

значення для циклічної послідовності необхідне число разів.

4.5.3 SQL-функції Oracle

База даних Oracle має спеціальну загальнодоступну таблицю з

ім’ям dual. Ця таблиця знаходиться в схемі користувача SYS, вона

містить одне поле dummy і один запис. Одним із найпоширеніших

варіантів використання таблиці dual є обчислення результатів SQL-

функцій за допомогою запитів до неї.

Функції для роботи з символами

CHR(номер_символа) – символ, який має ASCII-код номер_символа.

Приклад:

SQL> SELECT CHR(65)||CHR(66)||CHR(67) FROM dual;

CHR

ABC

ASCII(символ) – ASCII-код символа, заданого параметром символ.

Приклад:

SQL> SELECT ASCII('D') FROM dual;

ASCII ( ' D ')

INITCAP(рядок) – перетворить кожну першу букву слів параметра

рядок у прописну, а всі наступні в рядкові (малі). Нагадаємо, що

літерні константи SQL беруться в одинарні лапки.

100