Oracle 通过角色权限创建视图时，“ORA-00942: 表或视图不存在”

我们的应用程序开发团队遇到了通过角色特权创建视图的问题。

我知道这是通过角色特权创建视图时的正常行为。它会抛出一个错误。直接赠款将解决该问题，但是我们的编码标准要求我们使用角色而不是直接赠款。授予选择任何特权也不是一个选项，因为只允许dba拥有该特权。

有工作吗？请指教。谢谢。

下面是一个例子:

SQL> create role TESTROLE;

Role created.

SQL> create user PR identified by "********" account unlock;

User created.

SQL> create user scott identified by "********" account unlock;

User created.

SQL> grant create view to PR;

Grant succeeded.

SQL> grant create table to PR;

Grant succeeded.

SQL> grant create procedure to scott;

Grant succeeded.

SQL> create view PR.today_date_v as select to_char(sysdate,'dd-Mon-yyyy day hh:mi:ss am') today_dt from dual;

View created.

SQL> grant select on PR.today_date_v to TESTROLE;

Grant succeeded.

SQL> grant TESTROLE to scott;

Grant succeeded.

SQL> -- connect as user scott
SQL> show user;
USER is "SCOTT"
SQL> select * from  PR.today_date_v;

TODAY_DT
---------------------------------
23-Apr-2020 thursday  01:34:20 pm

SQL> -- scott has select privelege on PR.today_date_v view via role TESTROLE
SQL> -- however when used in a function it is throwing an error ORA-00942
SQL> create or replace function get_todays_date return varchar2 is
  2    vtodaydt varchar(40);
  3  begin
  4    select today_dt into vtodaydt from PR.today_date_v;
  5    return (vtodaydt);
  6  end;
  7  /

Warning: Function created with compilation errors.

SQL> show errors;
Errors for FUNCTION GET_TODAYS_DATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
4/41     PL/SQL: ORA-00942: table or view does not exist
SQL>

如果要引用另一个架构中的表/视图，则必须直接授予才能使用它:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1065832643319

您需要更改编码标准或将采购移至用户PR。然后格兰特·斯科特对它执行。

您可能还想研究使用基于代码的访问控制。这允许您将PL/SQL函数/过程授予角色:

https://oracle-base.com/articles/12c/code-based-access-control-12cr1