Шаг 101 - Oracle - Создание схемы БД - Назначение прав

Думаю, настало время разобраться с тем как же создаются "схемы" в БД Oracle. Под понятием схема имеется ввиду сам созданный аккаунт или говоря прямо - пользователь БД! Целью создания пользователя как раз и является получение схемы БД, с определенными правами и привилегиями. Создание пользователя в БД Oracle достаточно не сложный, но в тоже время довольно концептуальный момент. Для создания пользователя (схемы), применяется команда DDL - CREATE USER. Она имеет следующий синтаксис:

------ CREATE USER [пользователь] IDENTIFIED BY [пароль] ----------------------------
------ DEFAULT TABLESPACE [tablespace] QUOTA целое число [K][M] ON [tablespace] -----
------ TEMPORARY TABLESPACE [tablespace] QUOTA  целое число [K][M] ON [tablespace] --

Этот синтаксис не совсем полный, но для начала нам достаточно, далее мы рассмотрим то, что было не указано. Как правило, создание пользователей (схем) БД производится при подключении к БД, пользователем SYS или SYSTEM. Так как эти две схемы имеют права администраторов БД. Рассмотрим основные параметры команды - CREATE USER:

К слову сказать, в чем мы далее и убедимся. Для того, чтобы запросы пользователей могли создавать временные сегменты в табличном пространстве TEMP, им не нужны квоты на дисковое пространство. Попробуем создать пользователя! Запускайте SQL*Plus с пользователем SYS или SYSTEM пароли администраторов смотрите в шаге 5! Из всего выше сказанного, запишем вот такую конструкцию:

CREATE USER DUMMY IDENTIFIED BY DUMB 
DEFAULT TABLESPACE USERS QUOTA 100M ON USERS
TEMPORARY TABLESPACE TEMP QUOTA 10M ON TEMP
/

Здесь мы создаем пользователя (схему) DUMMY с паролем DUMB и позволяем ему резвится на 100 Мб пространства USERS и еще немного выделяем из пространства TEMP. Получаем в результате:

SQL> CREATE USER DUMMY IDENTIFIED BY DUMB
  2  DEFAULT TABLESPACE USERS QUOTA 100M ON USERS
  3  TEMPORARY TABLESPACE TEMP QUOTA 10M ON TEMP
  4  /

Пользователь создан.

Ок! Пользователь (схема) создан. Наверное, можно уже подключится и начать создавать объекты! Пробуем!

CONNECT DUMMY/DUMB@PROBA

Именное такой синтаксис подключения можно использовать, он еще называется строка коннекта и расписывается вот так:

---- [Username]/[Password]@[Sevice] ------------

Получаем:

SQL> CONNECT DUMMY/DUMB
ERROR:
ORA-01045: user DUMMY lacks CREATE SESSION privilege; logon denied 


Предупреждение: Вы больше не соединены с ORACLE.

Опа! Не повезло! Создание пользователя - это еще не все! Теперь ему нужно разрешить самое основное - создавать сессию с сервером. Сделать это можно командой GRANT. Она достаточно объемная и мы ей займемся чуть позже, а пока восстановим подключение:

SQL> CONNECT SYS/MANAGER@PROBA 
Соединено.

Даем пользователю право создавать сессию с сервером:

SQL> GRANT CREATE SESSION TO DUMMY
  2  /

Привилегии предоставлены.

Пробуем подключиться:

SQL> CONNECT DUMMY/DUMB
Соединено.

Вот теперь можно немного перевести дух. Итак, мы создали пользователя, определили ему табличные пространства, назначили квоты на них. И даже позволили создавать сессию с сервером. Давайте убедимся, что пользователь создан и чувствует себя нормально. Производим переконнект на админа БД:

SQL> CONNECT SYS/MANAGER@PROBA 
Соединено.

Дадим такой запрос к представлению DBA_USERS:

SELECT USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, DEFAULT_TABLESPACE, 
TEMPORARY_TABLESPACE, PROFILE   
FROM DBA_USERS
WHERE USERNAME = 'DUMMY'
/

Получаем:

SQL> SELECT USERNAME, USER_ID, PASSWORD, ACCOUNT_STATUS, DEFAULT_TABLESPACE,
  2  TEMPORARY_TABLESPACE, PROFILE
  3  FROM DBA_USERS
  4  WHERE USERNAME = 'DUMMY'
  5  /

USERNAME  USER_ID  PASSWORD         ACCOUNT_STATUS  DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
--------  -------- ---------------- --------------- ------------------ -------------------- -------
DUMMY     64       E888ADB4D5FFE1B2 OPEN            USERS              TEMP                 DEFAULT

Кто знаком с криптографией, может на досуге раскусить - E888ADB4D5FFE1B2 или хотя бы провести аналогию с DUMB! Итак, все с нашей схемой в порядке! Осталось только разрешить пользователю создавать объекты БД.

Разрешаем:

GRANT CREATE TABLE TO DUMMY
/
GRANT CREATE PROCEDURE TO DUMMY
/
GRANT CREATE TRIGGER TO DUMMY
/
GRANT CREATE VIEW TO DUMMY
/
GRANT CREATE SEQUENCE TO DUMMY
/

Получаем:

SQL> GRANT CREATE TABLE TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT CREATE PROCEDURE TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT CREATE TRIGGER TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT CREATE VIEW TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT CREATE SEQUENCE TO DUMMY
  2  /

Привилегии предоставлены.

Да, так как оператор GRANT это DDL, то COMMIT вызывается не явно! В данном случае мы разрешили пользователю, создавать такие основные объекты БД как - TABLE, PROCEDURE, TRIGGER, VIEW, SEQUENCE. Для начала этого достаточно. А что делать, если пользователю будет необходимо изменять эти объекты? Тогда нужно добавить еще немного прав, на изменение (ALTER) вот так:

GRANT ALTER ANY TABLE TO DUMMY
/
GRANT ALTER ANY PROCEDURE TO DUMMY
/
GRANT ALTER ANY TRIGGER TO DUMMY
/
GRANT ALTER PROFILE TO DUMMY
/

Получаем:

SQL> GRANT ALTER ANY TABLE TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT ALTER ANY PROCEDURE TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT ALTER ANY TRIGGER TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT ALTER PROFILE TO DUMMY
  2  /

Привилегии предоставлены.

Вот теперь он может не только создавать эти объекты, но и изменять их! А, что если пользователю необходимо будет удалить какой-либо объект или удалить записи из таблиц? Тогда нужно добавить права на удаление объектов БД вот так:

GRANT DELETE ANY TABLE TO DUMMY  
/
GRANT DROP ANY TABLE TO DUMMY
/
GRANT DROP ANY PROCEDURE TO DUMMY
/
GRANT DROP ANY TRIGGER TO DUMMY
/
GRANT DROP ANY VIEW TO DUMMY
/
GRANT DROP PROFILE TO DUMMY
/

Получаем:

SQL> GRANT DELETE ANY TABLE TO DUMMY  
  2  /

Привилегии предоставлены.

SQL> GRANT DROP ANY TABLE TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT DROP ANY PROCEDURE TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT DROP ANY TRIGGER TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT DROP ANY VIEW TO DUMMY
  2  /

Привилегии предоставлены.

SQL> GRANT DROP PROFILE TO DUMMY
  2  /

Привилегии предоставлены.

Уфф! Ну вот теперь кажется все! Пользователь действительно полноценный и может работать! Помните в шаге 6 мы с вами это уже проделывали, но тогда я не вдавался в подробности, так как было не до того! А, вот теперь давайте разберемся более детально и продолжим далее.


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.