Oracle права на создание view
когда я пытаюсь создать представление, включающее разные таблицы, я получаю следующую ошибку: Ошибка в строке 1: ORA-01031 недостаточные привилегии.
ORA-01031: недостаточно привилегий при выборе view
пожалуйста, дайте мне знать, как я здесь.
Мой Запрос это так:
исходный вопрос: создайте представление для выбора идентификатора сотрудника, имени сотрудника, даты найма и номера отдела.
тогда, вероятно, у вас может не быть привилегий для выполнения в схеме базы данных. Войдите в учетную запись SYSDBA и введите команду
здесь следует заменить именем пользователя, которому вы хотите предоставить доступ к
вы можете проверить, если пользователь имеет 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 :
- атомарность — выполнение или не выполнение всех DML команд входящих в тразакцию;
- целостность БД — завершение транзакции не должно нарушать целостность БД;
- изоляция — можно отображать либо исходные данные, которые были до начала транзакции, либо новые данные после выполнения транзакции;
- сохранность данных — если пользователю пришло подтверждение выполнения транзакции, то его изменения не будут отменены по каким-либо причинам.
В стандарте предусмотрены следующие команды управления транзакциями:
- START TRANSACTION — явное начало транзакции. Команда не поддерживается в Oracle. В MySQL и PostgreSQL можно использовать синоним begin (не путать с блоковым оператором begin, после которого нет разделителя). Если начало транзакции явно не указано, то PostgreSQL считает каждую DML команду отдельной транзакцией. В Oracle транзакции следуют одна за другой. То есть первая DML команда открывает транзакцию, следующие команды становятся частью этой транзакции, пока не будет вызвана команда commit или rollback. Если режим автоподтверждения (autocommit) включен, то MySQL работает также как PostgreSQL, иначе как Oracle;
- COMMIT — завершить транзакцию, применяя все сделанные изменения;
- ROLLBACK — завершить транзакцию, отменяя все сделанные изменения. Если точка отката не указана, то отменяется вся текущая транзакция;
- SAVEPOINT — сохранить точку отката;
- RELEASE SAVEPOINT — уничтожить точку отката, что позволяет освободить часть ресурсов до завершения транзакции;
- SET TRANSACTION — устанавливает характеристики текущей транзакции. Если транзакция не начата явно, то эта команда игнорируется в PostgreSQL.
Ниже приведен пример использования некоторых команд внутри выполняемого блока в Oracle.
Команда select … for update, блокирует записи таблицы от изменений другими пользователями.
При этом нельзя использовать другие конструкции как distinct, group by.
Для блокировки нескольких таблиц в различных режимах используется команда lock table. Блокировка снимается при завершении текущей транзакции.
В MySQL поддержка транзакций зависит от используемого способа хранения таблиц. Так для таблиц на движке InnoDB возможны транзакции, а для MyISAM нет. В последнем случае отсутствие одновременного обновления одних и тех же данных разными пользователями гарантируется самой СУБД. Невозможность отката, означает, необходимость самостоятельной предварительной проверки на ошибки перед внесением изменений. Такой подход увеличивает скорость работы от трех до пяти раз за счет уменьшения использования памяти, дискового пространства и процессора.
8 Answers 8
Finally I got it to work. Steve's answer is right but not for all cases. It fails when that view is being executed from a third schema. For that to work you have to add the grant option:
That way, [READ_USERNAME] can also grant select privilege over the view to another schema
What if dba grants the priviledge. I am not getting this work although user is granted privilege by dba to views created and underlying tables.
As the table owner you need to grant SELECT access on the underlying tables to the user you are running the SELECT statement as.
Q. When is the "with grant option" required ?
A. when you have a view executed from a third schema.
Example: schema DSDSW has a view called view_name
Typical grants: grant select on dsdw.view_name to dsdw_select_role; grant dsdw_select_role to fdr;
But: fdr gets select count(*) from dsdw.view_name; ERROR at line 1: ORA-01031: insufficient privileges
issue the grant:
now fdr: select count(*) from dsdw.view_name; 5 rows
Let me make a recap.
When you build a view containing object of different owners, those other owners have to grant "with grant option" to the owner of the view. So, the view owner can grant to other users or schemas.
Example: User_a is the owner of a table called mine_a User_b is the owner of a table called yours_b
Let's say user_b wants to create a view with a join of mine_a and yours_b
For the view to work fine, user_a has to give "grant select on mine_a to user_b with grant option"
Then user_b can grant select on that view to everybody.
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
simply type this
grant all on to public;
To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view's definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer's rights have been revoked from the view's underlying objects.
Use the GRANT statement to grant:
Roles to users, roles, and program units. The granted roles can be either user-defined (local or external) or predefined. For a list of predefined roles, refer to Oracle Database Security Guide .
Global roles (created with IDENTIFIED GLOBALLY ) are granted through enterprise roles and cannot be granted using the GRANT statement.
Notes on Authorizing Database Users
You can authorize database users through means other than the database and the GRANT statement.
Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, refer to the documentation for the appropriate package.
Some operating systems have facilities that let you grant roles to Oracle Database users with the initialization parameter OS_ROLES . If you choose to grant roles to users through operating system facilities, then you cannot also grant roles to users with the GRANT statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles.
Note on Oracle Automatic Storage Management
A user authenticated AS SYSASM can use this statement to grant the system privileges SYSASM , SYSOPER , and SYSDBA to a user in the Oracle ASM password file of the current node.
Note on Editionable Objects
A GRANT operation to grant object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.
CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle Database Security Guide for information about other authorization methods and for information about privileges
REVOKE for information on revoking grants
To grant a system privilege , one of the following conditions must be met:
You must have been granted the GRANT ANY PRIVILEGE system privilege. In this case, if you grant the system privilege to a role, then a user to whom the role has been granted does not have the privilege unless the role is enabled in user's session.
You must have been granted the system privilege with the ADMIN OPTION . In this case, if you grant the system privilege to a role, then a user to whom the role has been granted has the privilege regardless whether the role is enabled in the user's session.
To grant a role to a user or another role , you must have been directly granted the role with the ADMIN OPTION , or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
To grant a role to a program unit in your own schema , you must have been directly granted the role with either the ADMIN OPTION or the DELEGATE OPTION , or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
To grant a role to a program unit in another user's schema , you must be the user SYS and the role must have been created by the schema owner or directly granted to the schema owner.
To grant an object privilege on a user , by specifying the ON USER clause of the on_object_clause , you must be the user on whom the privilege is granted, or you must have been granted the object privilege on that user with the WITH GRANT OPTION , or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you can grant an object privilege on a user only because you have the GRANT ANY OBJECT PRIVILEGE , then the GRANTOR column of the *_TAB_PRIVS views displays the user on whom the privilege is granted rather than the user who issued the GRANT statement.
To grant an object privilege on all other types of objects , you must own the object, or the owner of the object must have granted you the object privileges with the WITH GRANT OPTION , or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE , then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR column of the *_TAB_PRIVS views displays the object owner rather than the user who issued the GRANT statement.
To specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify CONTAINER = ALL , the current container must be the root.
Use the GRANT statement to grant:
Roles to users, roles, and program units. The granted roles can be either user-defined (local or external) or predefined. For a list of predefined roles, refer to Oracle Database Security Guide .
Global roles (created with IDENTIFIED GLOBALLY ) are granted through enterprise roles and cannot be granted using the GRANT statement.
Notes on Authorizing Database Users
You can authorize database users through means other than the database and the GRANT statement.
Many Oracle Database privileges are granted through supplied PL/SQL and Java packages. For information on those privileges, refer to the documentation for the appropriate package.
Some operating systems have facilities that let you grant roles to Oracle Database users with the initialization parameter OS_ROLES . If you choose to grant roles to users through operating system facilities, then you cannot also grant roles to users with the GRANT statement, although you can use the GRANT statement to grant system privileges to users and system privileges and roles to other roles.
Note on Oracle Automatic Storage Management
A user authenticated AS SYSASM can use this statement to grant the system privileges SYSASM , SYSOPER , and SYSDBA to a user in the Oracle ASM password file of the current node.
Note on Editionable Objects
A GRANT operation to grant object privileges on an editionable object actualizes the object in the current edition. See Oracle Database Development Guide for more information about editions and editionable objects.
CREATE USER and CREATE ROLE for definitions of local, global, and external privileges
Oracle Database Security Guide for information about other authorization methods and for information about privileges
REVOKE for information on revoking grants
To grant a system privilege , one of the following conditions must be met:
You must have been granted the GRANT ANY PRIVILEGE system privilege. In this case, if you grant the system privilege to a role, then a user to whom the role has been granted does not have the privilege unless the role is enabled in user's session.
You must have been granted the system privilege with the ADMIN OPTION . In this case, if you grant the system privilege to a role, then a user to whom the role has been granted has the privilege regardless whether the role is enabled in the user's session.
To grant a role to a user or another role , you must have been directly granted the role with the ADMIN OPTION , or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
To grant a role to a program unit in your own schema , you must have been directly granted the role with either the ADMIN OPTION or the DELEGATE OPTION , or you must have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
To grant a role to a program unit in another user's schema , you must be the user SYS and the role must have been created by the schema owner or directly granted to the schema owner.
To grant an object privilege on a user , by specifying the ON USER clause of the on_object_clause , you must be the user on whom the privilege is granted, or you must have been granted the object privilege on that user with the WITH GRANT OPTION , or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you can grant an object privilege on a user only because you have the GRANT ANY OBJECT PRIVILEGE , then the GRANTOR column of the *_TAB_PRIVS views displays the user on whom the privilege is granted rather than the user who issued the GRANT statement.
To grant an object privilege on all other types of objects , you must own the object, or the owner of the object must have granted you the object privileges with the WITH GRANT OPTION , or you must have been granted the GRANT ANY OBJECT PRIVILEGE system privilege. If you have the GRANT ANY OBJECT PRIVILEGE , then you can grant the object privilege only if the object owner could have granted the same object privilege. In this case, the GRANTOR column of the *_TAB_PRIVS views displays the object owner rather than the user who issued the GRANT statement.
To specify the CONTAINER clause, you must be connected to a multitenant container database (CDB). To specify CONTAINER = ALL , the current container must be the root.
PostgreSQL
Команда copy в PostgreSQL позволяет, как сохранить выборку в файле, так загрузить данные из файла. Файловый источник должен существовать заранее, при записи автоматически он не создается. При повторном копировании содержимое файла перезаписывается. При чтении из файла в таблицу, кодировки строковых данных должны совпадать.
роли PostgreSQL
В PostgreSQL роль и пользователь являются одним типом объекта. Различие в командах CREATE USER и CREATE ROLE, заключается в том, что в первом случае по умолчанию определяется имя с возможностью соединения (параметр LOGIN). Во втором случае соединение запрещено (параметр NOLOGIN). Группы пользователей, существовавшие в предыдущих версиях, теперь также слились с ролями. Ниже приведен список параметров определяющих роль:
- SUPERUSER, NOSUPERUSER — определяют является ли новая роль суперпользователем —
пользователем не имеющего ограничений внутри базы; - CREATEDB, NOCREATEDB — может ли новая роль создавать базы данных;
- CREATEROLE, NOCREATEROLE — может ли новая роль создавать новые роли;
- LOGIN, NOLOGIN — может ли новая роль использоваться для соединения к БД;
- CONNECTION LIMIT n — если роли доступно соединение к БД, то n указывает предел этих
соединений. По умолчанию значение -1, означающее отсутствие предела; - PASSWORD psw — определяет пароль для соединения;
- ENCRYPTED, UNENCRYPTED — шифровать или нет пароль в системном каталоге;
- VALID UNTIL ‘временная отметка’ — определяет действие пароля до указанной
временной отметки; - IN ROLE lst — определяет список ролей lst, добавляемых к новой;
- ROLE lst — определяет список ролей, которые станут членами новой группы;
- ADMIN lst — аналогично предыдущему плюс указанные роли сами могут добавлять другие роли в эту группу.
роли MySQL
MySQL пока не поддерживает ролей.
права доступа Oracle
В Oracle команды назначения прав не так актуальны за счет готовых ролей и как указывалось ранее, каждый пользователь имеет полный доступ к одноименной схеме. В Oracle имя привилегии определяет и действие, и тип объектов.
Ключевое слово ANY указывает, что привилегия распространяется на все схемы, иначе только на текущую (из которой вызывается команда GRANT).
права доступа
Права доступа обеспечивают уровень безопасности как для сокрытия данных от посторонних лиц, так и безопасность всей структуры БД от не преднамеренного уничтожения объектов. Команда добавления привелегий GRANT имеет три составляющие: имя привелегии, объект, на который даются права, и кому даются права — пользователь или роль.
Чтобы задать все доступные права на указанный объект, в стандарте предусмотрена конструкция
ALL PRIVILEGES. Конструкция WITH GRANT OPTION дает разрешение пользователю
самому назначать права. Чтобы сделать некоторые права на указанные объекты общедоступными
в Oracle и PostgreSQL в качестве имени пользователя нужно указать public.
права доступа PostgreSQL
В PostgreSQL права задаются только на существующие объекты следующих типов: table (таблица), view (представление), sequence (последовательность), database (база данных), function (функция), procedural language (процедурный язык), schema (схема) и tablespace (табличное пространство). За исключение таблиц перед именами объектов обязательно указывается их тип. Ниже приведен список привилегий:
- ALL PRIVILEGES — все привилегии на указанный объект;
- CREATE — право на создание. Для базы данных это создание
схем внутри ее. Для схем это создание новых объектов внутри ее. Для
табличного пространства это создание таблиц, индексов и временных файлов
внутри него; - REFERENCES — право на создание внешний ключей;
- TRIGGER — право создания триггера на указанную таблицу;
- CONNECT — право на соединение с указанной базой данных;
- TEMPORARY, TEMP — право на создание временных таблиц;
- EXECUTE — право на выполнение функций;
- USAGE — право на использование указанного языка для
написания функций. Для схемы это доступ к объектам внутри ее. Для
последовательности это право использовать функции currval и nextval; - SELECT — право на выборку;
- DELETE — право на удаление;
- INSERT — право на вставку;
- UPDATE — право на обновление.
MySQL
В MySQL данная задача реализована аналогично сохранению выборки в списке переменных в
команде select.
Если какой-либо разделитель не нужен, то его определение можно опустить. В отличие от Oracle здесь нельзя вывести более одной выборки в один файл.
Команда LOAD DATA INFILE является обратной и позволяет загрузить данные из файла в таблицу.
Например пусть есть файл myfile.lst, со следующим содержимым в обычной для Windows русской кодировке.
Тогда загрузить данные можно следующей командой.
Если таблица colors использует другую кодировку, например utf-8, данные преобразуются корректно.
Управление аккаунтами SQL
PostgreSQL
В PostgreSQL есть пару отличий от стандарта. Хотя по стандарту все объекты схемы принадлежат владельцу схемы, в PostgreSQL объекты могут принадлежать различным пользователям. Это случается, если им были даны соответствующие права.
Во-вторых, в текущей версии СУБД порядок вложенных команд создания объектов схемы может иметь значение. В качестве вложенных команд могут быть команды создания таблиц, представлений, индексов, последовательностей, триггеров и команды раздачи привилегий. Если перед именем объекта имя схемы не указывается, то используется схема public.
пользователи
Аккаунт или учетная запись характеризуется пользователем и правами доступа (привилегиями) назначенных пользователю. Обычно аккаунты защищены паролем.
удаление прав доступа
Удаление прав делается командой REVOKE.
Для удобства управления привилегиями используются роли. Им можно назначать права так же, как и пользователям или использовать вместо имени привилегии в командах
GRANT и REVOKE.
Информация о базе данных
В стандарте SQL определена особая схема information_schema, содержащая информацию о базе данных. Например, таблица tables хранит значения различных параметров всех таблиц, как их имена. Само число параметров зависит от СУБД.
В старых версиях Oracle пока не реализована эта возможность. А использование системных таблиц или пакета dbms_metadata по ряду причин не так удобны с точки зрения разработки инструментов управления БД.
В MySQL имеется специальная команда для получения такого рода информации — show.
В PostgreSQL тоже есть одноименная команда, но она служит для других целей.
When I try to execute a view that includes tables from different schemas an ORA-001031 Insufficient privileges is thrown. These tables have execute permission for the schema where the view was created. If I execute the view's SQL Statement it works. What am I missing?
MySQL
Oracle
В Oracle для каждого пользователя автоматически создается одноименная схема с полным доступом. А команда CREATE SCHEME служит лишь для наполнения схемы множеством таблиц, представлений и правами доступами к ним другим пользователям. Ниже приведен пример, как пользователь root за одну команду создает одну таблицу, одно представление и открывает к ним общий доступ.
Oracle
В Oracle вывод в файл реализуется с помощью команды SQL plus spool. Она служит как для начала вывода в файл, так и для остановки. По умолчанию расширение файла lst.
Так как это не SQL команда, то ее нельзя использовать внутри PL/SQL блока, но можно поместить блок между этими командами. По этой же причине, чтобы выполнить этот пример в PL/SQL developer, нужно открыть Command window.
На некоторых операционных системах поддерживается дополнительный параметр out, позволяющий сразу распечатать выборку на принтере установленном по умолчанию.
Схема SQL
Объект схема позволяет логически сгруппировать множество других объектов. Доступ к объектам схемы осуществляется через точку. По стандарту в команду создания схемы позволяется вкладывать дополнительные команды создающие различные объекты схемы и раздающие права на эти объекты. А также владельцем всех объектов схемы должен являться владелец схемы.
Файловый вывод/ввод
Большинство программ, работающих с БД, позволяют сохранять выборки в различных форматах, но не все позволяют сохранить в виде простого текстового файла. Например, была ситуация, когда в БД была необходимая информация о сотрудниках (более тысячи человек) для одной стандартной формы. А формат файла, описывающий эту форму, оказался текстовым. Поэтому проблема решилась выборкой в файл и вставкой его содержимого в файл формы. А соответствующий отдел был избавлен от лишней работы. Для конкретности примера, воспользуемся следующей таблицей с данными.
роли Oracle
Синтаксис создания ролей в Oracle также схож с синтаксисом создания пользователей.
права доступа MySQL
В MySQL схемы и пользователи независимы, поэтому при назначении прав необходимо указывать конкретную схему (имя_схемы.*) или распространять право на все схемы сразу (*.*). Последний вариант в MySQL называется глобальным уровнем назначением прав.
По сравнению с Oracle список прав не так велик из-за отсутствия некоторых типов объектов и объединения использования нескольких команд в одну привилегию. Ниже приведен список основных прав:
- ALL PRIVILEGES — все права на указанный объект за исключением назначения прав на этот объект;
- CREATE — право на создание таблицы;
- ALTER — право на изменение таблицы;
- DROP — право на уничтожение таблицы;
- LOCK TABLES — право на блокировку таблицы;
- CREATE TEMPORARY TABLES — право на создание временных таблиц;
- CREATE ROUTINE — право на создание хранимых процедур и функций;
- ALTER ROUTINE — право на изменение или уничтожение хранимых процедур и функций;
- CREATE VIEW — право на создание представления;
- TRIGGER — право на создание и уничтожение триггеров;
- INDEX — права на создание и уничтожение индексов;
- EXECUTE — право на выполнения хранимых процедур и функций;
- EVENT — право на создание событий;
- CREATE USER — права на создание, уничтожение, переименование пользователя и снятия все прав. Назначается только на глобальном уровне;
- SELECT — право на выборку;
- DELETE — право на удаление;
- INSERT — право на вставку;
- UPDATE — право на обновление;
- FILE — право на использование команд SELECT … INTO OUTFILE и LOAD DATA INFILE;
- PROCESS — право на просмотр всех процессов командой SHOW PROCESSLIST;
- SHOW DATABASES — право на просмотр списка схем;
- SHOW VIEW — право на просмотр списка представлений;
- SHUTDOWN — право на закрытие.
Читайте также: