To be more specific, an active preprocessor script is hooked before Exasol's SQL parser. This allows it to intercept and modify any SQL text sent to the database (or executed within a procedure script) before passing it on to the actual parser.
What can I do with it?
Those scripts are using the Lua language, so basically you can do anything with the preprocessor; here's a few facts and limitations, though:
Exasol's Lua library is stripped and can not be extended with binary libraries for security reasons
Preprocessor scripts do not take parameters; the "current" SQL text can be retrieved through a function call
Preprocessor scriptscanexecute statements using (p)query
Preprocessor scripts do not return any values; they "return" the modified SQL text through another function call
While often preprocessor scripts are enabled on system level, any user can disable this in his or her session (see (2) below)
Preprocessor scripts are executed in thecaller's contextand privileges. Also, if user can EXECUTE the script (which is a necessity), he/she can also READ it. Security by obscurity won't work.
Typical Use Cases
Compatibility layer for a frontend that produces SQL not suitable for Exasol
"human knows more" optimizations of queries and filters
Row-Level Security (
💡re-read the last two points above)
Syntax and Semantics
Please see the Exasol User Manual (Section 3.8) for details.
As a preprocessor script is aschema object, you will need to find or create a schema to create the script in:
CREATE SCHEMAprivilegeorpre-existing schema
How to work with Preprocessor Script?
Step 1: Safety
"CREATE SCRIPT" statements are also preprocessed. As the preprocessor script you are going to (re-)deploy is very likely to contain the keywords it should react on, it is advisable to disable the preprocessor before deployment:
altersessionset sql_preprocessor_script = null;
Step 2: Deploy
Create the preprocessor script. Syntax "around" may depend on the SQL client you are using:
Run a few statements to verify success. Best done with Auditing or Profiling enabled, so you can see the resulting SQL texts. When things go very wrong, go back to step (2) – This is the only SQL statement not passed through the preprocessor...
Step 5: Activate globally
Now that things went well, we can activate the script for other users (new sessions):