尽管有授予，但无法创建访问其他架构对象的Oracle View [英] Unable to create Oracle View accessing another schemas' objects, despite grants

问题描述

我有2种模式:

ARIEL ARIEL_APEX

ARIEL ARIEL_APEX

ARIEL_APEX可以访问ARIEL中的所有表，并且查询可以从ARIEL_APEX模式中运行.

All the tables in ARIEL are accessible to ARIEL_APEX and the queries run OK from the ARIEL_APEX schema.

例如

SELECT * FROM ARIEL.DIM_REGISTRATION_SET

在ARIEL_APEX模式下工作正常.

works fine from the ARIEL_APEX schema.

当我尝试在ARIEL_APEX中创建视图时

When I try to create a view in ARIEL_APEX:

CREATE VIEW TEST_VIEW AS
SELECT * FROM ARIEL.DIM_REGISTRATION_SET

我明白了:

Error at Command Line : 465 Column : 23
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

这在测试和生产环境中绝对可以正常工作，这就是开发. DBA说一切都好了.

This works absolutely fine in the test and production environment, this is development. DBA saying all is well at their end.

ARIEL_APEX having below privileges.

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
ARIEL_APEX                     CREATE JOB
ARIEL_APEX                     CREATE MATERIALIZED VIEW
ARIEL_APEX                     CREATE TABLE
ARIEL_APEX                     CREATE OPERATOR
ARIEL_APEX                     CREATE VIEW
ARIEL_APEX                     CREATE TYPE
ARIEL_APEX                     CREATE SYNONYM
ARIEL_APEX                     CREATE CLUSTER
ARIEL_APEX                     CREATE DIMENSION
ARIEL_APEX                     CREATE TRIGGER
ARIEL_APEX                     CREATE SESSION
ARIEL_APEX                     CREATE INDEXTYPE
ARIEL_APEX                     CREATE PROCEDURE
ARIEL_APEX                     CREATE SEQUENCE

并且我们知道可以对ARIEL_APEX的ARIEL对象授予权限，因为我们可以手动执行查询.

And we know the grants are OK on the ARIEL objects to ARIEL_APEX as we can execute the query manually.

这是Oracle12.在升级之前从未出现此问题，但是怀疑这与从视图中的另一个架构访问对象有关.

This is Oracle 12. Never had the issue before we upgraded, but suspect this is related to accessing objects from another schema within a view.

ARIEL_APEX是ANALYTICS_ROLE的成员，ANALYTICS_ROLE授予ARIEL模式中所有表上的select权限，可以在下面看到.

ARIEL_APEX is a member of the ANALYTICS_ROLE, the ANALYTICS_ROLE grants select on all tables in the ARIEL schema, which can be seen to working below.

推荐答案

在较高环境中工作，唯一的区别是角色提供的赠款...在其他环境中，直接在对象上提供的SELECT赠款.

Works in upper environments, only difference is grants provided by a role...in other environments SELECT grants provided directly on objects.

如文档中所述(添加了重点):

As noted in the documentation (emphasis added):

包含视图的模式的所有者必须具有选择(READ或SELECT特权)，从视图所基于的所有表或视图中插入，更新或删除行所必需的特权. 必须直接向所有者授予这些特权，而不是通过角色.

The owner of the schema containing the view must have the privileges necessary to either select (READ or SELECT privilege), insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

如果仅通过角色授予对基础表的选择特权，则无法针对该表创建视图.即使通常转为角色，您也必须在要创建的任何视图上始终保留显式授予.

If you only have the select privileged on the underlying table granted through a a role then you cannot create a view against it. Even if you move to role generally, you'll have to keep explicit grant on top for any views you want to create.

我想这与角色的工作方式有关.通过直接授予，Oracle知道您是否可以在其他模式中看到该表.如果您将视图上的select授予其他人，则当他们查询该视图时，Oracle会知道其中存在特权链.如果您在表上的直接授予被吊销，则存在使依赖对象无效的机制.但是撤消了角色在表上的选择特权.或您对角色的访问权限已被撤销；或仅在您自己的会话中，如果禁用该角色，应该怎么办-您仍然可以访问视图吗?乍看起来似乎有点复杂.

I imagine this is to do with how roles work. With a direct grant Oracle knows whether you can see the table in the other schema. If you grant select on your view to someone else then when they query the view Oracle knows that the chain of privileges is there. If your direct grant on the table is revoked then there are mechanisms to invalidate dependent objects. But what should happen the role's select privilege on the table is revoked; or your access to the role is revoked; or just within your own session, what should happen if you disable that role - can you still access the view? It's a bit more complicated that it seems at first glance.

幸运的是，创建视图应该相对较少且可控制.通过角色访问表的大多数人都不需要在该表上创建视图(我想是！).

Fortunately creating views should be relatively rare and controllable. Most people accessing the table via the role won't need to create a view on it (I assume!).

这里的另一个选项是在ARIEL模式中创建视图，然后向APEX_ARIEL和/或角色授予特权.是否合适取决于您的实际视图查询和创建视图的动机.

Another option here is create the view in the ARIEL schema, and then grant privileges to APEX_ARIEL and/or a role. Whether that is appropriate depends on your real view query and your motivation for creating the view.

这篇关于尽管有授予，但无法创建访问其他架构对象的Oracle View的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！