Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0, Exasol 6.2.x
    • Fix Version/s: None
    • Component/s: EXASolution
    • Labels:
      None
    • Solution:
      Hide

      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

      Show
      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

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: