This article is part of the upcoming Troubleshooting JavaScript chapter that should soon make it into the JavaScript Developer’s Guide. It covers a common problem developers face when writing database logic in Oracle, and it can be summed up as role grants often won’t suffice.
TL;DR
By default, named JavaScript blocks (and PL/SQL blocks) in Oracle Database execute with definer‘s rights, where roles are disabled to prevent privilege escalation. This can lead to said ORA-942 errors if a user only has role-based access to a table. To fix this:
- Grant direct privileges to the user while retaining definer’s rights, or
- Modify the function to execute with invoker‘s rights using
AUTHID CURRENT_USERto respect the invoking user’s privileges, including roles
Both options are discussed in detail. More information concerning invoker- vs definer rights can be found in the Database Security Guide.
This post was written based on Oracle Database Free 23.9 running as a Arm container instance in Docker.
The long story
Imagine the following: you are tasked with writing an extension to an Oracle database application. The application is self-contained in its own schema, let’s call it APP_OWNER because I’m not good at naming things. The table in question, APP_OWNER.T1 has been created based on DBA_OBJECTS. The application owners chose to grant access to the application via a role, aptly named APP_ROLE. The developer’s account, DEMOUSER, has also been granted APP_ROLE, but nothing else. Using an IDE and other tools it’s possible to read from the table.
Here’s the complete setup:
SQL> create table app_owner.t1 as select * from dba_objects;
Table APP_OWNER.T1 created.
SQL> create role app_role;
Role APP_ROLE created.
SQL> grant select on app_owner.t1 to app_role;
Grant succeeded.
SQL> grant app_role to demouser;
Grant succeeded.Now, suppose the task is to identify the owner of the most database objects and expose this logic via a REST-consumable function. A query to achieve this might look like:
SQL> sho user
USER is "DEMOUSER"
SQL> with max_object_per_user as (
2 select
3 count(*) as num_obj,
4 owner
5 from
6 app_owner.t1
7 group by
8 all
9 )
10 select
11 owner
12 from
13 max_object_per_user
14 where
15* num_obj = (select max(num_obj) from max_object_per_user);
OWNER
_____
SYSIf you have been using an Oracle database in the past it’s probably no surprise that SYS has come up as the winner. Now let’s wrap this query into a function, because it’s got to be REST-consumed.
create or replace function owns_most_objects
return varchar2
as mle language javascript
{{
const result = session.execute(`
with max_object_per_user as (
select
count(*) as num_obj,
owner
from
app_owner.t1
group by
all
)
select
owner
from
max_object_per_user
where
num_obj = (select max(num_obj) from max_object_per_user)`
);
return result.rows[0].OWNER;
}};
/The function compiles fine, but as soon as DEMOUSER executes it, an error is thrown at runtime:
SQL> select
2* owns_most_objects;
Error starting at line : 1 in command -
select
owns_most_objects
Error at Command Line : 2 Column : 19
Error report -
SQL Error: ORA-00942: table or view "APP_OWNER"."T1" does not existORA-942: APP_OWNER.T1 doesn’t exist? But it surely does, the query output from earlier in the article is proof!
Although it might come as a surprise, the ORA-942 error is expected behaviour. DEMOUSER merely has a role grant to select from APP_OWNER.T1 and the function defaults to definer rights.
What can be done to overcome the error?
Option 1: retain definer rights
Since all roles are disabled in a definer rights code unit, a direct grant is what’s required. Let’s apply it and try again:
SQL> grant select on app_owner.t1 to demouser;
Grant succeeded.Note: While this solution works for the sake of demonstration in this article, it may not be ideal in production environments. Direct grants can complicate privilege management and potentially violate least-privilege principles. A more comprehensive authorization strategy, tailored to the application’s needs, is recommended but beyond the scope of this article
With the direct grant completed, DEMOUSER can successfully run the function:
SQL> sho user
USER is "DEMOUSER"
SQL> select
2* owns_most_objects;
OWNS_MOST_OBJECTS
_________________
SYSThis concludes the first option.
Option 2: switch to invoker rights
The other option you have is to change the function to an invoker rights function by adding authid current_user, like so:
SQL> sho user
USER is "DEMOUSER"
SQL> create or replace function owns_most_objects
2 return varchar2
3 authid current_user
4 as mle language javascript
5 {{
6 const result = session.execute(`
7 with max_object_per_user as (
8 select
9 count(*) as num_obj,
10 owner
11 from
12 app_owner.t1
13 group by
14 all
15 )
16 select
17 owner
18 from
19 max_object_per_user
20 where
21 num_obj = (select max(num_obj) from max_object_per_user)`
22 );
23
24 return result.rows[0].OWNER;
25 }};
26* /The important bit can be found in line 3 – authid current_user defines the function as an invoker-rights function. With invoker’s rights, DEMOUSER can execute the function successfully without needing a direct grant, as the APP_ROLE privileges are now recognized:
SQL> select
2* owns_most_objects;
OWNS_MOST_OBJECTS
_________________
SYSKey takeaways and conclusion
- Definer’s Rights (Default): Executes with the creator’s privileges; roles are disabled for security. Use direct grants if this mode is required
- Invoker’s Rights: Executes with the invoker’s privileges; roles are enabled. Use
AUTHID CURRENT_USERto enable this mode for better flexibility - Choose the appropriate mode based on your application’s security and performance requirements
- For deeper insights into rights management, consult the Oracle Database Security Guide
The ORA-942 error in Oracle JavaScript functions often stems from privilege issues tied to definer’s rights. By understanding the difference between definer’s and invoker’s rights, developers can apply the most suitable solution—whether through direct grants or switching to invoker’s rights. This ensures both functionality and adherence to security best practices in Oracle Database development.
Martins Blog 