An interesting question came in today and it’s a great opportunity to elaborate a little on the nature of the Oracle JavaScript SQL Driver. As you may have heard, Oracle AI Database 26ai introduced JavaScript as a first class citizen right next to PL/SQL and Java. Now you’ve got another language to write database lambdas (a term that sounds so much better than stored procedures or user defined functions), placing business logic right next to where your data live. JavaScript is powered by Multilingual Engine, or MLE for short.
The language would of course not be complete if it weren’t for the SQL driver. In contrast to (client-side) node-oracledb a database session exists as soon as you invoke JavaScript code. You have access to the session by either invoking oracledb.getDefaultConnection or using the session object available in the global namespace.
The MLE JavaScript SQL driver adheres to all the rules the database supports and enforces, including Virtual Private Database (VPD). You can either take the word for it, or try it out … the latter is what you’ll see in this post.
What is VPD?
As per the documentation,
Oracle Virtual Private Database (VPD) creates security policies to control database access at the row and column level.
Essentially, Oracle Virtual Private Database adds a dynamic
WHEREclause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.
Let’s implement a VPD policy and check if the MLE JavaScript SQL driver respects it. An Oracle AI Database Free 23.26.0 instance, running in a container, was used for testing.
Implementing VPD
Let’s implement a VPD policy on the widely popular EMP table. The table resides in the EMILY schema, and so do the other schema objects, too, unless stated otherwise. As a reminder this is what the table looks like:
SQL> desc emp
Name Null? Type
___________ ___________ _______________
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) As you can see, the table stores information about employees from different departments. The VPD policy to be implemented enforces a business rule where you can only view information about your colleagues from the same department. To do so, a VPD policy will add a predicate to the where clause.
Creation of an Application Context skipped
Real-life applications typically create an application context for use with the VPD policy, and populate it when a session connects. Continuing the above example you would write some logic working your department out, and stick that department number into the application context.
This example skips that step for the sake of brevity. And besides, this article is about MLE, not VPD policies 😁
Write a function to dynamically adjust the where clause
The most important part of a VPD policy is to adjust the where clause dynamically. As you read earlier this is typically done with the help of an application context, the example is taking a shortcut here by returning department number 20 for everyone.
Here’s a greatly simplified example suitable for this demo but not much else really:
create or replace package vpd_demo_sec_pkg as
-- owner and object name must be passed to
-- the function
function generate_where_clause(
owner varchar2,
objname varchar2
) return varchar2;
end;
/
create or replace package body vpd_demo_sec_pkg as
function generate_where_clause(
owner varchar2,
objname varchar2
) return varchar2
as
begin
-- this is an unrealistic example, in real
-- life you use application contexts ...
return ' deptno = 20 ';
end;
end;
/Adding the policy to EMILY.EMP
The final step is to create the policy. This typically requires DBA intervention unless your account has has been allowed to execute dbms_rls (🔗 to documentation). Here’s the call to dbms_rls.add_policy as executed by the SYSTEM account:
begin
dbms_rls.add_policy(
object_schema => 'EMILY',
object_name => 'EMP',
policy_name => 'SECURE_SELECT',
function_schema => 'EMILY',
policy_function => 'VPD_DEMO_SEC_PKG.GENERATE_WHERE_CLAUSE'
);
end;
/There is a lot to say about that package and VPD, please refer to the documentation for all the details.
Testing the effect of the VPD policy
Before jumping to MLE/JavaScript, let’s make sure the policy works as expected.
A first test on the command line
Querying the EMP table using SQLcl (and any other client by the way) will only ever return those rows for DEPTNO = 20:
SQL> select -- findme
2 empno,
3 ename,
4 deptno
5 from
6* emp;
EMPNO ENAME DEPTNO
________ ________ _________
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20 There is nothing secretive going on, Oracle tells you that the VPD policy was enabled. Just look at the execution plan:
SQL> select * from table(dbms_xplan.display_cursor('4qad2mwk4fdxh'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________
SQL_ID 4qad2mwk4fdxh, child number 0
-------------------------------------
select -- findme empno, ename, deptno from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 165 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20)This confirms the correct working of the policy.
Testing the MLE JavaScript SQL Driver
The same rules apply for the MLE/JavaScript driver. Let’s run an example:
create or replace procedure mle_vpd_test
as mle language javascript
{{
const result = session.execute(`
select -- mle
empno,
ename,
deptno
from
emp`
);
console.log(`EMPNO\tENAME\tDEPTNO`);
for (const row of result.rows) {
console.log(`${row.EMPNO}\t${row.ENAME}\t${row.DEPTNO}`);
}
}};
SQL> exec MLE_VPD_TEST
EMPNO ENAME DEPTNO
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
PL/SQL procedure successfully completed.The fact that only rows from department 20 are returned should prove that the VPD policy is respected by the MLE JavaScript SQL driver. As with the earlier example you can see this in the execution plan, too:
SQL> select * from table(dbms_xplan.display_cursor('9g3mbj07z8f86'));
PLAN_TABLE_OUTPUT
_____________________________________________________________________________
SQL_ID 9g3mbj07z8f86, child number 0
-------------------------------------
select -- mle empno, ename,
deptno from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 165 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20) Summary
This post showed that the MLE JavaScript SQL Driver adheres to the same rules any other language. VPD policies, PGA memory limits, Resource Manager consumer group directives should all be respected, too.
Happy coding!
Martins Blog 