Home » SQL & PL/SQL » SQL & PL/SQL » "ORA-00942: table or view does not exist" when creating view via role's privilege (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
"ORA-00942: table or view does not exist" when creating view via role's privilege [message #680136] Thu, 23 April 2020 12:53 Go to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
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>

[Updated on: Thu, 23 April 2020 13:34]

Report message to a moderator

Re: "ORA-00942: table or view does not exist" when creating view via role's privilege [message #680137 is a reply to message #680136] Thu, 23 April 2020 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My first idea was to declare the function with "AUTHID CURRENT_USER" but this does not work:
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
I'm not sure I understand why.
Documentation states:

Quote:
An invoker’s rights procedure executes with all of the invoker's privileges.
...
The invoker must have privileges at run time to access program references embedded in DML statements or dynamic SQL statements, because they are effectively recompiled at run time.
...
For all other external references, such as direct PL/SQL function calls, Oracle Database checks the privileges of the owner at compile time, but does not perform a run-time check.
It seems that "DML" here does not include SELECT and so this falls in the second case: static check at compilation time.
A workaround is then to change the SELECT into a dynamic statement:
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.
But this means that any caller of the SCOTT function must have the privilege to select the view... which may not be what you want.

Re: "ORA-00942: table or view does not exist" when creating view via role's privilege [message #680140 is a reply to message #680137] Fri, 24 April 2020 02:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Generally you would create all objects in one or more application schemas and grant the permissions on those objects to roles.
If you're going to let users create their own objects that reference other schema objects then they're going to need direct grants - no way around it.
Re: "ORA-00942: table or view does not exist" when creating view via role's privilege [message #680145 is a reply to message #680136] Fri, 24 April 2020 03:12 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you may need to use what Oracle calls "code based access control", where you grant a role to the function.

The problem it attempts to solve (if I understand it correctly) is unauthorised privilege escalation:
(a) Definer's rights code lets a user take on ALL the privileges of the definer for the duration of the call, whereas
(b) Invoker's rights code lets the definer take on ALL the privileges of the invoker for the duration of the call.

Using CBAC, the invoker has access to nothing more than the role granted to the program unit, and he has no direct privileges that the definer can steal.

This is a demo I sometimes use:
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;
This should solve the problem: a malicious developer cannot steal the invoker's privileges (because he has none) and a malicious user can't steal the developer's privileges other than those granted to the role.

[Updated on: Fri, 24 April 2020 03:14]

Report message to a moderator

Re: "ORA-00942: table or view does not exist" when creating view via role's privilege [message #680146 is a reply to message #680145] Fri, 24 April 2020 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The main problem here is that the owner of the function is not the owner of the view (or table) but acquires the SELECT privilege on this object via a role. In this case the function is invalid unless you define the SELECT in a dynamic statement as I showed.

John's idea solves the problem I mentioned to grant some role you don't want to grant to a user.
Now combining the 2, you have some closer solution (from the state I left):
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.
I said a "closer" solution as this one requires that SCOTT has been granted the TESTROLE role WITH ADMIN OPTION, otherwise it could not grant it to its function and a DBA could not either as only the owner of the function can grant it a privilege/role.

[Updated on: Fri, 24 April 2020 12:36]

Report message to a moderator

Re: "ORA-00942: table or view does not exist" when creating view via role's privilege [message #680204 is a reply to message #680146] Tue, 28 April 2020 14:28 Go to previous message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
thank you all i appreciate all these help will try these solutions.
Previous Topic: Query
Next Topic: ORA-04042 issue when trying to grant the access
Goto Forum:
  


Current Time: Thu Mar 28 11:35:49 CDT 2024