Each piece of PL/SQL that is part of an application is visible to anyone who has access to the database in USER_SOURCE, ALL_SOURCE and DBA_SOURCE depending on privileges. Even if the code is wrapped then it can be unwrapped although even if 10g and above is easy to unwrap and no unwrappers are available for 9ir2 and below it is still possible; I can do it!
What I wanted was a combination of two things:
- Use code in our PL/SQL that is not visible in the SOURCE$ tables or views so that someone with direct database access cannot as easily work out how our code works
- Extend an application at run time without the need to re-compile the PL/SQL or allow the user of the application to do so. If we let end users change the PL/SQL the there is a
danger of security issues and the need to allow elevated access to the PL/SQL interface
If we allow end users or more likely power users to extend a PL/SQL application at run time then we must control the level of extension and how its extended and what features or aspects of the original application are exposed to the scripting interface.
In other words the end user/customiser can write as much or as little scripting language as they want BUT we as designers control how the script language is exposed to the PL/SQL application.
A good example/model is the embedding of Lua into C programs. We do this by embedding Lua into all of our products so that rules in PFCLScan can be written in Lua or generic obfuscation can be done in PFCLObfuscate using Lua and rules in our PL/SQL code analyser PFCLCode can be written in Lua; in fact all of our products because they are built on PFCLScan can use Lua as part of then checks and functionality.
Lua is a good example of extending a product. In the case of PFCLScan where the core engine is written in C we have embedded Lua.
The extension to add Lua is done at compile time of our C based applications; in the case of PFCLScan we compile in the Lua engine and we provide an interface to the Lua engine so that scripts can be loaded and then executed. To be able to do something useful we needed to extend Lua to give it access to our C based engine. We added C functions written using the Lua API to extend Lua from C so that when they are also compiled in with the Lua engine scripts can be written in Lua that also execute code against our engine BUT from Lua.
Some examples of the extension functions we added to Lua for PFCLScan are first that we add functions to Lua written in C:
- PFCLOpen() â€" this is used to open previous checks so that results can be parsed and worked on in Lua
- PFCLLine() â€" we can read one line of results to split into columns of data
- PFCLSplit() â€" can be used to split a line of data based on a passed in separator
- PFCLPrint() â€" print a line of data to our scanner output
Second we also pass in tables of data from the C engine as Lua is executed so that data items processed in C can be accessed via the Lua scripts. In our code analyser in PFCLCode for instance we can access the lexer and parser output as a table of data and in all of our tools we pass flags to Lua that can then be used in Lua. One example is a debug flag so that if “trace†is enabled in our engine then a debug flag is passed to Lua so that a “trace†flag can also be enabled or not in Lua and then debug statements will write to the output to aid debugging of Lua scripts.
The third thing that we do is provide some Lua libraries written in Lua that can be included in scripts run by the tool (in user space â€" i.e. we do not need to recompile the C code) to help with common tasks such as checking if a file exists or reading and writing files.
The goal with PL/SQL applications including APEX is to do the same thing as Lua with C code applications. We have created a simple language interpreter written in PL/SQL. A few articles on writing a simple language interpreter in PL/SQL are on my website
A simple script showing a nested loop is here:
declare
lv_prog varchar2(32767):=q'[
LET m=2
LET x=1
PRINT "Start of tests"
PRINT "=================="
LOOP
LET y=1
LOOP
PRINT "x, y is [";x;",";y;"]"
IF y>m THEN
EXIT
FI
LET y=y+1
POOL
IF x>m THEN
EXIT
FI
LET x=x+1
POOL
PRINT "=================="
PRINT "End of Tests"
END
]';
begin
--
pfclscript.init(true,1);
pfclscript.run(lv_prog);
--
end;
/
And the output is here:
SQL> @interp
Start of tests
==================
x, y is [1,1]
x, y is [1,2]
x, y is [1,3]
x, y is [2,1]
x, y is [2,2]
x, y is [2,3]
x, y is [3,1]
x, y is [3,2]
x, y is [3,3]
==================
End of Tests
PFCLScript Execution Time (Seconds) : +000000 00:00:11.041386000
SQL>
To add this as a language to any PL/SQL application including Apex we need to do:
- ship the interpreter package header and body with the PL/SQL application in a separate schema
- Lock the interpreter schema, ensure only permission to execute is given to the PL/SQL application. Use other features such as “accessible by†clause to ensure that the interpreter cannot be started outside of its designed use
- Add the init() and run() calls to the interpreter in your PL/SQL application where you choose to use them
- Store and allow scripts to be loaded as data in your PL/SQL application and arrange that they can be chosen and executed as needed
- Create a simple API into your application that can be used from the interpreter as package calls to expose only the features/functionality as needed. These package procedure calls can access data/functions in the PL/SQL application and this API should be in a separate schema. Grant access on the PL/SQL application to this schema
- Create the “register calls†in your PL/SQL application after init() and before run() to register the interpreter function names that map to the API you created above
A simple call sequence inside your application would be:
…
begin
--
pfclscript.init(true,1);
pfclscript.register(‘PFCLFUNCA’,’API.FUNCA’);
pfclscript.register(‘PFCLFUNCB’,’API.FUNCB’);
pfclscript.run(lv_prog);
--
end;
…
The register() function in effect creates a new ID internally in the interpreter for the “PFCLFUNA†and “PFCLFUNCB†and these are mapped to actual PL/SQL functions in the interface PL/SQL API created. This now means that a script can be created that executes in our script engine that uses these functions to access features or data in the actual PL/SQL or Apex application.
To the script, these just look like function calls. The designer of the PL/SQL or Apex application decides where to run the script and what features to expose.
Data can be passed in by the script writer in the form or parameters to the script function and these parameters are then passed to the registered PL/SQL API functions.
Libraries of script code can be added easily, simply by prepending them to the script to run. We do not currently have “includeâ€, “require†type keywords but they can be simulated by pre-pending the library code.
The engine can also pass data to the script engine in a similar way to Lua but in essence in a simpler day. We can do this in two ways. The first by setting up internal data in the API functions or the second by passing data as script variables again pre-pended to the script to run
#oracleace #sym_42 #plsql #apex #scripting #compiler #interpreter #oracle #security
