Forum Discussion

Sergey's avatar
Sergey
Contributor III
2 years ago

SQL table editor : parametrize the source of data ?

Hello,

In the SQL table editor component, we can define namely the Schema Name, Table Name, Table Column Names, Where Clause and Order By Clause :

However, I am using a Table that has encrypted data, so I would need to use my symmetric key when loading the table. For that, I need to write an SQL statement beforehand :

OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCertificate;

and a CLOSING statement :

CLOSE SYMMETRIC KEY MyKey;

 However, the SQL table editor component doesn't allow to add these fields.

--> Is it possible to add these statements when the SQL table editor opens ? Could it be done with a business rule ? More generally, how can we handle encrypted data ?

Regards,

Security
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    You can tweak saving actions in rules, but I don't see how you could possibly inject rules when first loading SQLTableEditor (unless one went the full "v8 way" with service factories and all that, which is kinda experimental still - and even then I don't know if it'd be possible).

    What you might be able to do though, is create a separate External Connection and stuff your encripytion requirements in its connection string - which seems doable with stuff like Always Encrypted (warning: I've not tested it). Even if it's in the same db as the application, the system should just reconnect using the relevant keys and handle everything transparently.

    By the way, kudos for trying to do The Right Thing - in 2023 we should be encrypting everything everywhere all the time...

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    2 years ago

    This is untested: can you create a SQL View in the background (via a BR), then reference that View in the standard table editor component?  You would likely need to create a custom save function as well since you're now looking at data from a View rather than the raw table.

    • Sergey's avatar
      Sergey
      Contributor III
      2 years ago

      That's something I will definitely test !

      I don't know if one can actually change the table name used for saving data, but that would accomodate with the code needed in order to encrypt / decrypt.

      I'll keep you posted on that one !