Oracle Database 23ai introduces In-Database JavaScript for Linux x86-64 and aarch64. That’s Linux on Intel and Arm, respectively. Developers with a preference for Typescript can use it alternatively after transpilation to JavaScript. JavaScript support in Oracle Database is known as Multilingual Engine, or MLE for short.
Oracle Database 23ai Release Update 9 does away with the requirement to grant execute on javascript to <user>. From now on, if you like to write MLE/JavaScript code, all you need is the create MLE system privilege.
As always, you can find the details in Oracle’s JavaScript Developers Guide and the MLE Module API documentation on GitHub.
What does this feature address?
Previous versions of Oracle Database 23ai required you to grant multiple system and object privileges to your account before you could start writing JavaScript code in the database. These are detailed in the JavaScript Developer’s Guide security chapter. The most relevant ones are listed here:
| Privilege | Required for |
| create MLE | Creating MLE schema objects such as JavaScript modules and environments |
| create procedure | Writing PL/SQL call specifications needed for SQL and PL/SQL execution |
| execute on javascript | Executing JavaScript code in the database |
| execute dynamic MLE | Using DBMS_MLE, APEX, any REPL server you write that relies on DBMS_MLE |
This post concerns execute on javascript. Unfortunately you couldn’t add grant execute on javascript to a role like db_developer_role – it had to be a direct grant.
What’s the difference now?
Beginning with Release Update 23.9 execute on javascript is no longer required if you want to execute JavaScript code. You still need the create MLE privilege to create JavaScript code though, and potentially create procedure as well if you want to expose the JavaScript code in SQL and PL/SQL. If you have a requirement to prevent the execution of JavaScript code you can use one of the methods described in the JavaScript Developer’s Guide’s security chapter.
What are the implications? They are best shown in an example.
Let’s assume the JavaScript code featured in this blog post is owned by jscodeowner. The examples have been validated against an Oracle Database 23ai Free instance using Gerald Venzl’s 23.9 image (docker.io/gvenzl/oracle-free:23.9) on Linux x86-64.
select
*
from
product_component_version;
PRODUCT VERSION VERSION_FULL STATUS
____________________________ _____________ _______________ ___________________________________
Oracle Database 23ai Free 23.0.0.0.0 23.9.0.25.07 Develop, Learn, and Run for Free
The user must be created first:
create user jscodeowner identified by '*****************'
quota 100m on users;
grant create session to jscodeowner;
grant create table to jscodeowner;
grant create procedure to jscodeowner;
grant create mle to jscodeowner;
These are the bare minimum requirements for the owner of the JavaScript code. Typically additional grants are required, not for this article though. NOTE the total absence of grant execute on javascript to jscodeowner ! This grant was required up to RU 23.8.
With the user created, it’s time to load the validator module.
As with every open source/third party module caution is required: you cannot simply load a 3rd party module into your project and/or database, there are many things to consider first. The 3rd party license must be compatible with your project, and your security and compliance compartments have to approve its use as well. Always make sure to follow the rules before using external code.
validator.js’s ECMAScript (ESM) version is available from your favourite Content Delivery Network (CDN), for example JSDelivr. I pulled it to a local directory and loaded it into the database using SQLcl:
connect jscodeowner@localhost/freepdb1
SQL> sho user
USER is "JSCODEOWNER"
mle create-module -filename ./validator.js -version 13.15.15 -module-name validator_module
You should see MLE Module validator_module created, acknowledging the successful creation of the JavaScript module in the database schema. In releases prior to Oracle Database 23.9 you wouldn’t have been able to create the module: the missing grant would have prevented a successful execution. Here is the output of the same command, executed against a 23.8 system:
SQL> mle create-module -filename ./validator.js -version 13.15.15 -module-name validator_module -verbose
MLE Module validator_module not created: java.sql.SQLException:
ORA-04129: insufficient privileges to use MLE language JAVASCRIPT
In this case, a DBA would have granted execute on javascript to jscodeowner to fix the error and allow for the module creation.
To make sure everything works as expected, let’s expose isEmail() to PL/SQL:
create function is_email(p_str varchar2)
return boolean
as
mle module validator_module
signature 'default.isEmail';
/
This should have worked without problems – you can now validate a few email addresses:
select
is_email('a@aasdasda');
IS_EMAIL('A@AASDASDA')
_________________________
false
select
is_email('a@b.com');
IS_EMAIL('A@B.COM')
______________________
true
This concludes the example.
Summary
Oracle Database 23ai Release Update 9 (aka 23.9) removed the requirement to grant execute on javascript to <user> thereby enhancing the developer experience. Not having to grant the execute privilege to each user who required it simplifies database maintenance and user provisioning.
Martins Blog 