主页 / dba / 问题 / 218216
Accepted
jpmc26
Asked: 2018-09-21 14:12:26 +0800 CST

在使用 ALL_OBJECTS 的视图上授予 SELECT 时出错

  • 772

我正在为我们的应用程序测试 Oracle 12c。我在一个特定的模式中有类似于这个视图的东西，我们将要求MY_OWNER_SCHEMA这个问题：

CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER 
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
    OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
;

该视图是一种“元数据”视图，用于（以编程方式）验证所有表和视图是否都有注释。由于MY_OWNER_SCHEMA在部署期间被锁定，因此我们授予对其MY_LOGIN_USER的权限SELECT：

GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;

（这GRANT是在以 . 身份登录时在部署期间执行的MY_OWNER_SCHEMA。）

这在 Oracle 11g 中工作得很好，在 12c 中也可以创建它。但是，仅在 12c 中，GRANT失败并显示以下消息：

SQL Error: ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

这是相当奇怪的，因为ALL_OBJECTS所有用户都可以访问公共接口，并且它只显示用户已经有一些访问权限的对象。Oracle 12c 中的权限模型或默认权限设置是否发生了变化？数据库是否可能以不同的方式安装？有没有办法绕过这个错误而不给这个全局系统视图提供MY_OWNER_SCHEMA明确的权限？GRANT

oracle oracle-11g-r2

1 个回答

  1. Best Answer

    是的，使用READ特权（在 12c 中引入）而不是SELECT.

    SQL> create user MY_OWNER_SCHEMA identified by MY_OWNER_SCHEMA;

User created.

SQL> create user MY_LOGIN_USER identified by MY_LOGIN_USER;

User created.

SQL> grant create session, create view to MY_OWNER_SCHEMA;

Grant succeeded.

SQL> conn MY_OWNER_SCHEMA/MY_OWNER_SCHEMA
Connected.
SQL> CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
  2    3    4    5    6    7    8    9      OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
; 10   11   12

View created.

SQL> GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;
GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'

    以上失败，但是：

    SQL> GRANT READ ON OBJECT_COMMENTS TO MY_LOGIN_USER;

Grant succeeded.
    • 2

相关问题