Oracle права на создание view

Обновлено: 04.07.2024

Основы Oracle 18c - 19c часть 8 - права доступа, роли, учетные записи

Приведем наиболее часто используемые:
CREATE SESSION – право подключения к БД
ALTER DATABASE – право изменения БД
CREATE TABLESPACE – право создавать табличное пространтсво
ALTER TABLESPACE – право изменять табличное пространтсво
DROP TABLESPACE – право удалять табличное пространтсво
CREATE TABLE – право создавать, изменять, удалять таблицы в своей схеме
INSERT ANYTABLE – право добавлять данные в таблиц, которые не принадлежат учетной записи
UPDATE ANYTABLE – право изменять данные в таблиц, которые не принадлежат учетной записи
DELETE ANYTABLE – право удалять данные в таблиц, которые не принадлежат учетной записи
SELECT ANYTABLE – право выборки данных из таблиц, которые не принадлежат учетной записи

Синтаксис назначения прав:
GRANT privilege [,privilege…] TO User_Name;

Пример создания учетной записи (схемы) User_Name
С паролем User_Pass
Разрешаем занимаемое пространство в 10мб. от пространства по умолчанию USERS

CREATE USER User_Name IDENTIFIED BY User_Pass
DEFAULT TABLESPACE USERS QUOTA 10M ON USERS;

Пример, назначение всех основных привилегий для учетной записи:
GRANT CREATE SESSION, ALTER SESSION,
CREATE TABLE, CREATE VIEW, CREATE TRIGGER, CREATE PROCEDURE,
CREATE CLUSTER, CREATE DATABASE LINK,
CREATE SYNONYM, CREATE SEQUENCE, CREATE TYPE, CREATE OPERATOR
TO User_Name ;

В примере выше разрешено подключаться, настраивать сессию, создавать объекты в БД
Создавать объекты разрешено только в схеме аккаунта
Отсутствуют права к схемам других аккаунтов

Пример предоставления табличного пространства USERS по умолчанию для учетной записи User_Name:
ALTER USER User_Name DEFAULT TABLESPACE USERS

Создание ролей, учетных записей, пароля, связь ролей и учетных записей

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

Синтаксис:
GRANT privilege ON [schema.]object TO username [WITH GRANT OPTION];

Информация о имеющихся привилегиях

Отмена, удаление привилегий, ролей, четных записей

Вы можете предоставлять и отменять привилегии для различных объектов базы данных в Oracle. Сначала мы рассмотрим, как предоставлять и отменять привилегии для таблиц, а затем, как предоставлять и отменять привилегии функциям и процедурам в Oracle.

Grant Privileges для таблиц

Вы можете предоставить пользователям различные привилегии к таблицам. Эти привилегии могут быть любой комбинацией SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, или другие.

Синтаксис

Синтаксис для предоставления привилегий для таблицы в Oracle/PLSQL:

privileges
Привилегии для назначения. Это может быть любое из следующих значений:

Привилегии	Описание
SELECT	Возможность выполнения SELECT на таблице
INSERT	Возможность выполнения INSERT на таблице
UPDATE	Возможность выполнения UPDATE на таблице
DELETE	Возможность выполнения DELETE на таблице
REFERENCES	Возможность создавать CONSTRAINT, который ссылается на таблицу.
ALTER	Возможность выполнять оператор ALTER TABLE, чтобы изменить описание таблицы.
INDEX	Возможность создавать INDEX таблице с помощью оператора CREATE INDEX.
ALL	Все привилегии для таблицы

object
наименование объекта базы данных, которому вы предоставляете привилегии. В случае предоставления привилегий таблице, это должно быть название таблицы.
user
Имя пользователя, которому будет предоставлена эта привилегия.

Пример

Рассмотрим некоторые примеры, предоставления привилегий для таблиц в Oracle.
Например, если вы хотите предоставить SELECT, INSERT, UPDATE и DELETE привилегии на таблицу с наименованием suppliers для user с именем trizor , то нужно выполнить следующие GRANT предложение:

когда я пытаюсь создать представление, включающее разные таблицы, я получаю следующую ошибку: Ошибка в строке 1: ORA-01031 недостаточные привилегии.

ORA-01031: недостаточно привилегий при выборе view

пожалуйста, дайте мне знать, как я здесь.

Мой Запрос это так:

исходный вопрос: создайте представление для выбора идентификатора сотрудника, имени сотрудника, даты найма и номера отдела.

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

здесь <dbusername> следует заменить именем пользователя, которому вы хотите предоставить доступ к

вы можете проверить, если пользователь имеет VIEW создание привилегий с помощью select * from session_privs .

обратите внимание, что для создания представления пользователь, который его создает, должен быть предоставлен SELECT права на все используемые объекты, а также упомянул CREATE VIEW привилегии. Вы также можете проверить это, запросив USER_TAB_PRIVS С пользователем получаю ошибку.

вы должны предоставить пользователю право выбора любой таблицы. Затем представление будет успешно скомпилировано. Нет необходимости явно предоставлять пользователю select для всех объектов.

когда я хотел выполнить вышеуказанный запрос в SQL developer, я столкнулся с проблемами, поскольку у меня не было достаточно прав для создания представления или другой схемы объекта oracle, такой как триггер, пакеты, процедуры и т. д. Я нашел ошибку, т. е. "ошибка в строке 1: Ora-01031 недостаточные привилегии". Итак, мне нужны были все привилегии, чтобы практиковать все эти запросы и программы. Я предпринял следующие шаги, чтобы решить свою проблему:

как я вошел в систему как имя пользователя "Скотт", так что мое имя это "Скотт", а не "Дхрув". Моя амбиция состояла в том, чтобы предоставить все привилегии мне, т. е. пользователю "scott".
для этого мне нужно ввести в базу данных как DBA. Теперь вопрос в том! Как войти в систему как DBA. Для этого я открыл командную строку и вошел в базу данных как sysdba, выполнив следующие шаги:

a) в окне запуска я набрал cmd, чтобы открыть командную строку. Я набрал: sqlplus / nolog, что означает, что я вошел в систему без предоставления необходимых учетных данных.
си) Я аутентифицировал себя для моих базовых O / S и вошел в базу данных как DBA. Для этого я набрал в командной строке: connect / as sysdba; c) я оценил, кто является пользователем DBA в моей базе данных, если он существует. Для этого я набрал: выберите имя из V$database; д) Здесь мы идем после этой команды. Наконец, я предоставил себе (scott) для создания представления в SQL developer, введя команду: grant create view to scott; e) наконец, я предоставил себе все привилегии, набрав: предоставьте все привилегии Скотту;

Под понимается некоторая группа DML команд. Все изменения сделанные ими, сохраняются в отдельной области памяти до окончательного подтверждения изменений (успешное завершение транзакции), либо до их отмены. Если во время транзакции делается запрос на выборку данных, то создается отдельное представление. Для чего нужны транзакции? В англоязычной литературе концепция транзакций описывается абривиатурой
ACID :

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

Ниже приведен пример использования некоторых команд внутри выполняемого блока в Oracle.

Для блокировки нескольких таблиц в различных режимах используется команда lock table. Блокировка снимается при завершении текущей транзакции.

В MySQL поддержка транзакций зависит от используемого способа хранения таблиц. Так для таблиц на движке InnoDB возможны транзакции, а для MyISAM нет. В последнем случае отсутствие одновременного обновления одних и тех же данных разными пользователями гарантируется самой СУБД. Невозможность отката, означает, необходимость самостоятельной предварительной проверки на ошибки перед внесением изменений. Такой подход увеличивает скорость работы от трех до пяти раз за счет уменьшения использования памяти, дискового пространства и процессора.

Схема SQL

Объект схема позволяет логически сгруппировать множество других объектов. Доступ к объектам схемы осуществляется через точку. По стандарту в команду создания схемы позволяется вкладывать дополнительные команды создающие различные объекты схемы и раздающие права на эти объекты. А также владельцем всех объектов схемы должен являться владелец схемы.

PostgreSQL

В PostgreSQL есть пару отличий от стандарта. Хотя по стандарту все объекты схемы принадлежат владельцу схемы, в PostgreSQL объекты могут принадлежать различным пользователям. Это случается, если им были даны соответствующие права.

Во-вторых, в текущей версии СУБД порядок вложенных команд создания объектов схемы может иметь значение. В качестве вложенных команд могут быть команды создания таблиц, представлений, индексов, последовательностей, триггеров и команды раздачи привилегий. Если перед именем объекта имя схемы не указывается, то используется схема public.

Oracle

В Oracle для каждого пользователя автоматически создается одноименная схема с полным доступом. А команда CREATE SCHEME служит лишь для наполнения схемы множеством таблиц, представлений и правами доступами к ним другим пользователям. Ниже приведен пример, как пользователь root за одну команду создает одну таблицу, одно представление и открывает к ним общий доступ.

MySQL

Управление аккаунтами SQL

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

Аккаунт или учетная запись характеризуется пользователем и правами доступа (привилегиями) назначенных пользователю. Обычно аккаунты защищены паролем.

права доступа

Чтобы задать все доступные права на указанный объект, в стандарте предусмотрена конструкция
ALL PRIVILEGES. Конструкция WITH GRANT OPTION дает разрешение пользователю
самому назначать права. Чтобы сделать некоторые права на указанные объекты общедоступными
в Oracle и PostgreSQL в качестве имени пользователя нужно указать public.

права доступа MySQL

В MySQL схемы и пользователи независимы, поэтому при назначении прав необходимо указывать конкретную схему (имя_схемы.*) или распространять право на все схемы сразу (*.*). Последний вариант в MySQL называется глобальным уровнем назначением прав.

По сравнению с Oracle список прав не так велик из-за отсутствия некоторых типов объектов и объединения использования нескольких команд в одну привилегию. Ниже приведен список основных прав:

права доступа PostgreSQL

В PostgreSQL права задаются только на существующие объекты следующих типов: table (таблица), view (представление), sequence (последовательность), database (база данных), function (функция), procedural language (процедурный язык), schema (схема) и tablespace (табличное пространство). За исключение таблиц перед именами объектов обязательно указывается их тип. Ниже приведен список привилегий:

права доступа Oracle

В Oracle команды назначения прав не так актуальны за счет готовых ролей и как указывалось ранее, каждый пользователь имеет полный доступ к одноименной схеме. В Oracle имя привилегии определяет и действие, и тип объектов.

Ключевое слово ANY указывает, что привилегия распространяется на все схемы, иначе только на текущую (из которой вызывается команда GRANT).

удаление прав доступа

Удаление прав делается командой REVOKE.

Для удобства управления привилегиями используются роли. Им можно назначать права так же, как и пользователям или использовать вместо имени привилегии в командах
GRANT и REVOKE.

роли PostgreSQL

В PostgreSQL роль и пользователь являются одним типом объекта. Различие в командах CREATE USER и CREATE ROLE, заключается в том, что в первом случае по умолчанию определяется имя с возможностью соединения (параметр LOGIN). Во втором случае соединение запрещено (параметр NOLOGIN). Группы пользователей, существовавшие в предыдущих версиях, теперь также слились с ролями. Ниже приведен список параметров определяющих роль:

роли Oracle

Синтаксис создания ролей в Oracle также схож с синтаксисом создания пользователей.

роли MySQL

MySQL пока не поддерживает ролей.

Файловый вывод/ввод

Большинство программ, работающих с БД, позволяют сохранять выборки в различных форматах, но не все позволяют сохранить в виде простого текстового файла. Например, была ситуация, когда в БД была необходимая информация о сотрудниках (более тысячи человек) для одной стандартной формы. А формат файла, описывающий эту форму, оказался текстовым. Поэтому проблема решилась выборкой в файл и вставкой его содержимого в файл формы. А соответствующий отдел был избавлен от лишней работы. Для конкретности примера, воспользуемся следующей таблицей с данными.

Oracle

В Oracle вывод в файл реализуется с помощью команды SQL plus spool. Она служит как для начала вывода в файл, так и для остановки. По умолчанию расширение файла lst.

Так как это не SQL команда, то ее нельзя использовать внутри PL/SQL блока, но можно поместить блок между этими командами. По этой же причине, чтобы выполнить этот пример в PL/SQL developer, нужно открыть Command window.

На некоторых операционных системах поддерживается дополнительный параметр out, позволяющий сразу распечатать выборку на принтере установленном по умолчанию.

MySQL

В MySQL данная задача реализована аналогично сохранению выборки в списке переменных в
команде select.

Если какой-либо разделитель не нужен, то его определение можно опустить. В отличие от Oracle здесь нельзя вывести более одной выборки в один файл.

Команда LOAD DATA INFILE является обратной и позволяет загрузить данные из файла в таблицу.

Например пусть есть файл myfile.lst, со следующим содержимым в обычной для Windows русской кодировке.

Тогда загрузить данные можно следующей командой.

Если таблица colors использует другую кодировку, например utf-8, данные преобразуются корректно.

PostgreSQL

Команда copy в PostgreSQL позволяет, как сохранить выборку в файле, так загрузить данные из файла. Файловый источник должен существовать заранее, при записи автоматически он не создается. При повторном копировании содержимое файла перезаписывается. При чтении из файла в таблицу, кодировки строковых данных должны совпадать.

Информация о базе данных

В стандарте SQL определена особая схема information_schema, содержащая информацию о базе данных. Например, таблица tables хранит значения различных параметров всех таблиц, как их имена. Само число параметров зависит от СУБД.

В старых версиях Oracle пока не реализована эта возможность. А использование системных таблиц или пакета dbms_metadata по ряду причин не так удобны с точки зрения разработки инструментов управления БД.

По умолчанию аккаунт не имеет никаких прав в БД Oracle. Невозможно даже создать подключения без назначенных прав. И даже после получения прав на подключения, аккаунт не может сделать ничего полезного (или опасного) без получения соответсвующих прав. Права назначаются с помощью команды GRANT и убираются с помощью команды REVOKE. Дополнительные директивы команды используются для разрешения аккаунта делится правами которые у него есть с другими пользователями. По умолчанию только аккаунта администратора (SYS и SYSTEM) владеют правами назначения прав. Пользователь который назначает права другому пользователю называется grantor когда получатель прав – grantee. Права разбиты на две группы: системные права, которые грубо говоря позволяют пользователю совершать действия влияющие на словарь данных, и права над объектами, которые позволяют пользователю совершать действия влияющие на данные.

Системные права

Всего доступно около двух сотен системных прав. Большинство из них влияет на действия затрагивающие словать данных (такие как создание таблиц или пользователей). Остальные влияют на экземпляр или БД (создание табличны пространств, изменение параметров БД и создание сессий). Наиболее часто используемые права это

CREATESESSION – права на подключения. Без этих прав вы даже не сможете подключиться к БД
RESTRICTEDSESSION – Если БД запущена с директивой STARTUPRESTRICT или применялась команда ALTERSYSTEMENABLERESTRICTEDSESSION, то только пользователи с этими правами смогут подключаться к БД
ALTERDATABASE – разрешает выполнять команды влияющие на физические структуры
ALTERSYSTEM – разрешает изменять параметры экземпляра и структуры памяти
CREATETABLESPACE – вместе с ALTERTABLESPACE и DROPTABLESPACE позволяют пользователю управлять табличными пространтсвами
CREATETABLE – позволяет gratee создавать таблицы в своей схеме; включает возможность создавать, изменять и удалять таблицы, выполнять команды DML и select и управлять индексами
GRANTANYOBJECTPRIVILEGE – позволяет grantee управлять правами объектов которые ему не принаджлежат, но не даёт прав ему самому
CREATEANYTABLE – grantee может создавать таблицы которые принадлежат другим аккаунтам
DROPANYTABLE – grantee позволяется удалять таблицы которые принадлежат другим аккаунтам
INSERTANYTABLE, UPDATEANYTABLE, DELETEANYTABLE – даёт grantee право выполнять DML команды над объектами которые ему не принадлежат
SELECTANYTABLE – Даёт право grantee выполнять SELECT к любмы таблицам.

Синтаксис для назначения прав

GRANT privilege [,privilege…] TO username;

После создания аккаунта, обычно назначаются права часто используемые пользователями кто вовлечён в разработку приложения

grant create session, alter session,

create table, create view, create synonym, create cluster,

create database link, create sequence,

create trigger, create type, create procedure, create operator

Эти права позволяют подключаться и настраивать сессию, создавать объекты и хранить PL/SQL объекты. Объекты могут быть созданы только в схеме аккаунта; нет прав к схемам других аккаунтов. Также создание объектов ограничивается лмимитами табличных пространств.

Другим вариантом назначения прав будет назначение grantee доступа для переназначения прав другим аккаунтам. Например

grant create table to scott with admin option;

grant create table to jon;

Выполнение этих команд позволит SCOTT создавать таблицы в совей схеме, и выполнять команду GRANT. SCOTT даёт права пользователю JON создавать таблицы – но JON сможет создавать таблицы только в схеме JON. На рисунке 6-5 показаны права пользователя в Database Control; ту же информацию можно получить выполнив запрос к представлению DBA_SYS_PRIVS.

Если системные разрешения были отозваны, все действия которые вы выполнили пока у вас были права остаются в силе. Если у вас были права с ADMIN OPTION то у всех пользователей которым вы назначили права – права остаются, несмотря на то что у вас права отозвали. Не остаётся записей кто именно назначил системные привилегии, таким образом невозможно забрать права CASCADE как показано на рисунке 6-6

Revocation of a system privilege will not cascade (unlike

revocation of an object privilege).

Права ANY дают доступ ко всем объектам в БД. Таким образом

grant select any table to scott

позволить аккаунту SCOTT выполнять запрос SELECT ко всем таблицам во всех схемах БД. Такое назначение прав считается дурным тоном и ANY права назначаются только DBA.

In fact, ANY is not as dangerous now as with earlier releases. It no longer

includes tables in the SYS schema, so the data dictionary is still protected. But

ANY should still be used with extreme caution, as it removes all protection

from user tables.

Объектные права

Объектные права дают доступ к выполнению команд DML и SELECT к соответствующим объектам и выполнению PL/SQL объектов. Эти права не существуют для объектов в схеме аккаунта; если у пользователя есть системные права CREATE TABLE – это значит что он может выполнять SELECT и DML запросы к таблицам которые он создал без дополнительных прав.

The ANY privileges, that grant permissions against objects in

every user account in the database, are not object privileges—they are

Объектные права применяются к разным группам объектов

GRANT privilege ON [schema.]object TO username [WITH GRANT OPTION];

grant select on store.customers to scott;

Можно использовать ALL чтобы применить права для всех операций, или использовать конкретное указание столбца таблицы или представления.

grant select on store.orders to scott;

grant update (order_status) on store.orders to scott;

grant all on store.regions to scott;

Эти команды позволят аккаунту SCOTT выполнять запрос SELECT ко всем столбцам таблицы ORDERS в схеме STORE но обновлять данные только в одном столбце. Также у аккаунта SCOTT есть доступ ко всем операциям к таблице REGIONS. На рисунке 6-7 отображается результат назначения прав при просмотре в Database Control

Granting privileges at the column level is often said to be bad practice

because of the massive workload involved. If it is necessary to restrict peoples’

access to certain columns, creating a view that shows only those columns will

Использование директивы WITH GRANT OPTION позволит пользователю передавать свои права другим аккаунта. Оракл хранит информацию о том кто и кому дал доступ на объектном уровне; это позволяет отзывать права учитывая эту информацию. Рассмотрим пример

Читайте также: