[SOL-621] Preprocessor Scripts explained Created: 23.08.2018  Updated: 09.08.2020  Resolved: 09.08.2020

Status: Obsolete
Project: Solution Center
Component/s: EXASolution
Affects Version/s: EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0, Exasol 6.2.x
Fix Version/s: None

Type: How To
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: None

Solution:

Note: This solution is no longer maintained. For the latest information, please refer to our documentation:

https://community.exasol.com/t5/database-features/preprocessor-scripts-explained/ta-p/1730

Background

What does a preprocessor script do?

Well, it preprocesses .
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 scripts can execute 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 the caller's context and 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
  • Macro magic: Expanding predefined keywords server-side
  • "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.

Prerequisites

As a preprocessor script is a schema object, you will need to find or create a schema to create the script in:

create schema if not exists PREPROCESSOR;

Preconditions:

  • CREATE SCHEMA privilege or pre-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:

alter session set sql_preprocessor_script = null;

Step 2: Deploy

Create the preprocessor script. Syntax "around" may depend on the SQL client you are using:

--/
create or replace Lua script MY_PREPROCESSOR()
as
    ...
    ...sqlparsing.getsqltext()
    ...
    ...sqlparsing.setsqltext(...)
    ...
    return
/

Preconditions:

  • CREATE SCRIPT privilege
  • ownership of the schema or CREATE ANY SCRIPT privilege

Step 3: Activate locally

Now activate the preprocessor for your local session:

alter session set sql_preprocessor_script = PREPROCESSOR.MY_PREPROCESSOR;

Step 4: TEST IT!

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):

alter system set sql_preprocessor_script = PREPROCESSOR.MY_PREPROCESSOR;

Preconditions:

  • ALTER SYSTEM privilege

Step 6: No wait, we forgot something important!

We just locked out (more or less) everyone else from the database: They don't have EXECUTE permissions on the script!

grant EXECUTE on PREPROCESSOR.MY_PREPROCESSOR to public;

Preconditions:

  • ownership of the schema or GRANT ANY OBJECT PRIVILEGE privilege

Additional Notes

Best Practice:

As step (3) replaces the script, all privileges on it are lost in that step.
To avoid this problem, the EXECUTE privilege should be put on schema level:

grant EXECUTE on SCHEMA PREPROCESSOR to public;

Just make sure you don't put anything dangerous/secret into that schema

Category 1: Scripting
Generated at Tue Dec 07 18:31:47 CET 2021 using Jira 7.13.18#713018-sha1:e1230154f8ff8cc9272975bf568fc732e806fd68.