our application development team is having an issue about creating a view via role's privilege.

i am aware that is the normal behavior when creating a view via role's privilege. it will throw an error. a direct grants will solved the issue however our coding standards required us to use a roles not a direct grants. grant SELECT ANY privileges is also not an option because only DBAs are allowed to have that privileges.

is there a work around? please advise. thank you.

here is an example:

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>