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.
The availability of Oracle Database 23ai Release Update 8 marks the introduction of an interesting new feature: restricted execution context. 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?
Any code stored inside a database can and will be executed with the privileges of the code’s owner. Take PL/SQL, Java, or JavaScript in Oracle database for example. If the code owner, let’s call the user EMILY, owns tables, views, and other schema objects, stored code in any language has access to these schema objects. In most cases, this is exactly what you want.
Except of course if you have very high security constraints. In these circumstances developers often separated data from programming logic by creating separate schemas for each. Oracle offers lots of fine-grained controls to manage object privileges, making such a design possible.
With the introduction of JavaScript in Oracle Database 23ai another aspect has been added to this mix. If you want, you can use existing JavaScript modules, hosted on Content Delivery Networks (CDNs), GitHub, or elsewhere, and provided they are compatible with MLE and compliant with your license/security department/etc, use them in your project.
Wouldn’t it be nice if you could turn off access to the database’s data for modules sourced externally? That is exactly what the restricted execution context allows you to do.
What exactly does the restricted execution context restrict access to?
If you declare a piece of JavaScript to be pure, access to the SODA (Simple Oracle Document Access) API and MLE JavaScript SQL Driver is disabled (🔗 to docs)
Pure execution serves as a method to isolate certain code, such as third-party JavaScript libraries, from the database itself. This isolation can – to some extent – reduce the impact of supply chain attacks, in which access to the database state is a security concern. Using PURE execution also allows less-privileged developers to create these restricted user-defined functions without requiring additional access or privileges to the database state or network.
As with any feature, turning on restricted execution contexts is no silver bullet. You still need to remain vigilant and apply all related industry security best practices, always.
Declaring code as “pure”
You can store JavaScript code in Oracle Database as
- Inline function
- Inline procedure
- JavaScript module
Additionally you can use DBMS_MLE to invoke JavaScript code dynamically.
Let’s look at these in turn.
Inline functions and procedures
Oracle Database 23.8 for Linux introduced a new keyword, PURE, to indicate a pure function. Any access to the SQL driver and other database-related APIs is disabled. Consider this example:
SQL> sho user
USER is "EMILY"
SQL> create or replace function epoch_to_date(
2 "p_epoch" number
3 ) return date
4 as mle language javascript PURE
5 {{
6 let d = new Date(0);
7 d.setUTCSeconds(p_epoch);
8
9 const result = session.execute(
10 `select 'random thing'`
11 )
12
13 return d;
14 }};
15* /
Function EPOCH_TO_DATE compiled
Trying to invoke this function will throw a runtime error, as expected:
SQL> select epoch_to_date(123456789);
Error starting at line : 1 in command -
select epoch_to_date(123456789)
Error at Command Line : 1 Column : 31
Error report -
SQL Error: ORA-04161: ReferenceError: session is not defined
ORA-04171: at :=> (<inline-src-js>:5:20)
Access to the SQL driver is disabled, leading to the ORA-4161. You must remove any references to the disabled objects from your code and recompile. Only then will the error go away.
The same applies to inline procedures.
JavaScript Modules
Tthe PURE keyword is not a part of the create mle module DDL statement. If you would like to declare a JavaScript module as PURE you must create an environment and make it part of the call specification. Here is an example of a piece of code that is grossly violating the requirements of a PURE function. Let’s assume, for the sake of the discussion, this has gone unnoticed.
create or replace mle module common_utils language javascript as
/**
* Convert a string delimited by ';' to a JavaScript object and return it
*
* only functions exported from a module can be accessed externally
*
* @param {string} delimited string (key1=value1;...;keyN=valueN) to convert
* @returns {object} string converted to a JavaScript object
*/
export function string2JSON(inputString) {
let myObject = {};
if ( inputString.length === 0 ) {
return myObject;
}
session.execute(
'insert into log_table (str) values (:str)',
[ inputString ]
);
const kvPairs = inputString.split(";");
kvPairs.forEach( pair => {
const tuple = pair.split("=");
if ( tuple.length === 1 ) {
tuple[1] = false;
} else if ( tuple.length != 2 ) {
throw "parse error: you need to use exactly one " +
" '=' between key and value and not use '=' in either " +
"key or value";
}
myObject[tuple[0]] = tuple[1];
});
return myObject;
}
On the path to a creating the PURE function you create an MLE environment in the next step. Note the new PURE keyword highlighted.
create or replace mle env common_utils_env imports (
'common' module common_utils
) PURE;
Now you can bring it all together in a call specification:
create or replace function string_to_json(p_str varchar2)
return json
as mle module common_utils
env common_utils_env
signature 'string2JSON';
/
Thanks to the environment associated with the function the call is going to fail
SQL> select
2* string_to_json('a=1;b=2');
Error starting at line : 1 in command -
select
string_to_json('a=1;b=2')
Error report -
ORA-04161: ReferenceError: session is not defined
ORA-04171: at string2JSON (EMILY.COMMON_UTILS:15:5)
Only after removing the reference to the SQL driver will this select work without an error.
Dynamic Execution
The final example concerns the use of PURE functions in DBMS_MLE. Again, it all depends on the (previously created) MLE environment. Repeating the above example:
declare
l_ctx dbms_mle.context_handle_t;
l_snippet clob;
begin
-- to specify pure execution with DBMS_MLE, make sure you use
-- an MLE environment that has been created with the pure keyword
l_ctx := dbms_mle.create_context(
environment => 'COMMON_UTILS_ENV'
);
l_snippet := q'~
(async () => {
const { string2JSON } = await import ('common');
console.log(JSON.stringify(string2JSON('a=1;b=2')));
}) ()
~';
dbms_mle.eval(l_ctx, 'JAVASCRIPT', l_snippet);
dbms_mle.drop_context(l_ctx);
exception
when others then
dbms_mle.drop_context(l_ctx);
raise;
end;
/
Just as before, an error will be thrown because all access to the SQL driver is turned off:
declare
*
ERROR at line 1:
ORA-04161: ReferenceError: session is not defined
ORA-06512: at line 26
ORA-04171: at string2JSON (EMILY.COMMON_UTILS:15:5)
ORA-06512: at "SYS.DBMS_MLE", line 447
ORA-06512: at line 21
Summary
PURE functions allow for the definition of restricted execution contexts. Access to the SQL driver and other APIs is turned off, adding another layer of security to MLE/JavaScript. There is no perfect protection though, it remains the developer’s responsibility (and that of any team that might be involved) to ensure industry best practices concerning security and secure coding are adhered to.
Happy coding!
Martins Blog 