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>

SCOTT> create or replace function scott.get_todays_date return varchar2
  2    AUTHID CURRENT_USER
  3  is
  4    vtodaydt varchar(60);
  5  begin
  6    select today_dt into vtodaydt from PR.today_date_v;
  7    return (vtodaydt);
  8  end;
  9  /

Warning: Function created with compilation errors.

SCOTT> sho err
Errors for FUNCTION SCOTT.GET_TODAYS_DATE:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------
6/3      PL/SQL: SQL Statement ignored
6/41     PL/SQL: ORA-00942: table or view does not exist

SCOTT> create or replace function scott.get_todays_date return varchar2
  2    AUTHID CURRENT_USER
  3  is
  4    vtodaydt varchar(60);
  5  begin
  6  --  select today_dt into vtodaydt from PR.today_date_v;
  7    execute immediate 'select today_dt from PR.today_date_v' into vtodaydt;
  8    return (vtodaydt);
  9  end;
 10  /

Function created.

SCOTT> select get_todays_date from dual;
GET_TODAYS_DATE
----------------------------------------------------------------------------------
23-Avr. -2020 jeudi    09:18:38 PM

1 row selected.

grant dba to scott;

conn scott/tiger
drop user jw cascade;
grant create session to user jw identified by jw;


create or replace function count_emp return number 
authid current_user 
as 
n number;
begin
select count(*) into n from scott.emp;
return n;
end;
/

grant execute on count_emp to jw;

conn jw/jw
select count(*) from scott.emp;
select scott.count_emp from dual;

conn scott/tiger
create role sel_emp;
grant select on emp to sel_emp;
grant sel_emp to function count_emp;

conn jw/jw
select count(*) from scott.emp;
select scott.count_emp from dual;

SCOTT> grant testrole to function get_todays_date;

Grant succeeded.

-- connect as DBA
SQL> grant create session to test identified by test123;

Grant succeeded.

SQL> grant execute on scott.get_todays_date to test;

Grant succeeded.

-- connect as TEST
TEST> select scott.get_todays_date from dual;
GET_TODAYS_DATE
-------------------------------------------------------------------------
24-Avr. -2020 vendredi 11:21:02 AM

1 row selected.