Is there any way to access GLOP from Java UDF and External Procedure?

[ericsun2]

The use case looks like this:

• For each session, I need to invoke an External Procedure to get a key string from the https end point of KMS.
• Then store the key string into the the Session GLOP
• Run multiple SQL DML. A UDF is used to calculate the encrypted value based on the key string stored in the Session GLOP
• Once the session ends, the Session GLOP is discarded

I'd like to use Java UDF and Java External Procedure.

 @tomnolan do you have any suggestion?

[tomnolan]

You can use GLOP from a UDF written in C. The Teradata Database offers C functions to access and manage GLOP data, but does not offer Java methods to access GLOP data.

For more information, please refer to the Teradata Database Reference / SQL External Routine Programming / Chapter 7: Global and Persistent Data.

The SQL External Routine Programming book Appendix A includes an example C UDF to access GLOP data.

[ericsun2]

Thank @tomnolan as usual.

I've examed those document, it is good to confirm that Java UDF will not have access to GLOP at all.

Assume the UDF needs both the input column and key phrase. Here is more elaboration with respect to encryption key handling:

• Is it a better approach to use a "Session GLOP"? I mean, the JDBC code will insert the key phrase into Session GLOP right after the session is connected, and then the C UDF invoked in DML statement will retrieve the key phrase and perform the encryption, and once the JDBC connection ends the GLOP is discarded
• is it a better apprach to pass the key phrase as a binding parameter for the C UDF in the DML statement (to avoid the key phrase show up in Query Log)
  

  DELETE EncTable;
  
  INSERT INTO EncTable (id,name,amount,address,modified_time)
  SELECT
    obfuscate_id(id, ?),
    obfuscate_name(name, ?),
    amount,
    obfuscate_address(address, ?),
    current_timestamp(0)
  );

What is the security risk of using binding parameter? Or is that even possible?

[tomnolan]

It's risky to specify passwords as parameter values of SQL requests, because with Teradata Database 15.0 and later, the BEGIN QUERY LOGGING WITH PARAMINFO command will save SQL request parameter values in DBQLParamTbl.

[ericsun2]

So we will ensure the JDBC session will not set 

BEGIN QUERY LOGGING WITH PARAMINFO

Will that make the 2nd approach (binding parameter) viable?

[tomnolan]

Sorry, I can't answer your question about "viability". Only you can properly assess the security risk of the software architecture that you choose.