How to handle user password information in JSL ( ODBC connection strings )

[#1]

I am connecting to a database using ODBC, which has a specific user and password created for that purpose (not possible to use windows Active Directory, or other).

When the query is built, the table script stores the user ID.

Password is asked but it can also be coded directly into the connecting string in JSL.

What are the best options to handle such situations in JSL?

JMP files may be shared but updates should ask for specific user and password when executed for the first time.

[#2]

I like to put the password and userid in a separate file in the user's documents folder perhaps. Something like this:

credentials.jsl

{"fred","1234"}

You can encrypt it if you like, but that is pretty meaningless except to keep your dog from looking at it...

From the application, use

{username,password} = include("$documents/credentials.jsl");
... use the credentials ...
username = .; password = .; // clear the variables when done with them

The include function returns the result of the last statement executed in the file, which constructed the 2-element list.

I do this mostly to make sure I'm not checking in my own credentials to a source control system. Since the file lives outside of the project directory, it won't be picked up by accident. I do encrypt it, hoping anyone that hacks my computer doesn't have JMP. You might want to add a bit of info to the file name...the app that needs it, the service the credentials apply to.

[#3]

I think that encrypting the file will be better than having this info as plain text.

What would be the complete answer, including a very simple encryption/decryption step?

[#4]

Refer to my comments above: this is not secure. But here's how you could put a small speed bump in an attacker's path.

Encrypt this file with the encrypt() function, then run the encrypted file.

// Pre-encrypt this using edit->encrypt script. There
// is no need to use a run password, and no reason to
// make a complicated edit password. "a" is good enough.
//
// Caution: this is not secure. No password is needed to
// view this data, though JMP is required to decrypt it.
// If you need a decrypt password+prompt, you'll need to 
// manually go through the GUI for encrypting scripts.
// https://community.jmp.com/t5/Discussions/How-to-handle-user-password-information-in-JSL-ODBC-connection/m-p/627942#M82631
encrypt = Function( {name, pass}, {x},
	x = Expr( 1 + 1 ); // throw an error if not working
	// https://community.jmp.com/t5/Discussions/How-to-check-if-the-running-script-is-encrypted/m-p/624766#M82350
	If( Char( Name Expr( x ) ) == "1 + 1",
		Throw( "the encrypt function needs to be encrypted to work correctly." )
	);
	// strings generally stay unencrypted. expressions 
	// that are not simple strings or numbers try to stay
	// encrypted. The concatenation operator is enough to
	// keep the expression's strings encrypted.
	Char( Eval Expr( {Expr( name ) || "", Expr( pass ) || ""} ) ); // return value
);

Then run this to see how it works

// use the encrypted encrypt function to make an encrypted list
name = "fred";
pass = "1234";
crypt = encrypt( name, pass );
If( length(crypt) < 500 + Length( pass ) + Length( name ),
	Throw( "not encrypted. Did you execute the *encrypted* file containing the encrypt function?" )
);
file = Save Text File( "$temp/credentials.jsl", crypt );
name = .;
pass = .;
///////////////////////////////////////////////////
{name, pass} = Eval List( Parse( Load Text File( file ) ) );
write("\!nsaved file contains\!n",Load Text File( file ),"\!n\!ndecrypted is");
show(name,pass);

saved file contains
{JSL Encrypted("//-e12.1\!r\!n0,\!r\!n208,4193eJwNytmBhSAMAMB+tgWCCRBUkMuLgP0X8na+B3NI2evLjsG330/RUttTTEpOYhEyJ7JlBa2j1uAnpks3G3wTgpF7tJ8jqYrL\!r\!nPvBetVXPxNjKIWh06B9i+P/zfrd+bo6IP7eUOA3kSsGoaGA1x0ovXovyGmy481u4wqIGYKnEhvuG6XAE4Qk19cmunKo32uGJkuTv\!r\!nB+khNtw=\!r\!n,0,"), JSL Encrypted("//-e12.1\!r\!n0,\!r\!n208,4193eJwNzIkBxBAQAMB+rgWE9R1hQ/z0X8jdFDAcfUTDilxLV+PeyWbK7YEYHKE3A1bfMa7gUzjO2CxkyPwsVDPxS0TN9WNptpRC\!r\!nLxnShIpDYKmeKzWIoUTSF6XX/fjReuqKrYKwJ9yptTwR2DfaI2j/AlFVHd/bP3Mqa0cFcuV1KCuTGp9XmOmvrXe9+Dai8GNZN+Hz\!r\!nAxwZN40=\!r\!n,0,")}

decrypted is
name = "fred";
pass = "1234";

JMP does not include a function for programmatically encrypting scripts, only the interactive GUI. This workaround does not allow specifying the run or the edit password. There is no run password (any one can run it) and the edit password is unknown. You might prefer a different solution that would put {"fred","1234"} in an edit window and let the user manually encrypt (or not) before saving it. The user could then decide if they want a prompt for a run password, which would be a little more secure.

[#5 · Accepted solution]

On Windows I like to use the credential manager:

Set and get credentials in the Credential Manager via PowerShell on Windows 

[#6 · Accepted solution]

I outline an approach that uses encrypted credentials in this Discovery Summit talk:

https://community.jmp.com/t5/Discovery-Summit-Americas-2022/JMP-and-Oracle-Tips-and-Tricks-for-a-Hap...

Look for the section "Hiding the password".

[#7]

Accepting this as one of the databases was Oracle. Thanks for the detailed tutorial.

[#8]

I found a similar approach in this discussion from 2018:
Database Connection with login prompt 

What is the benefit of encrypting the code to connect to the data base?

[#9]

If there is JSL code available which can be executed to open a data base connection - every user with this code (encrypted or not) can access the database.

So, it's essential to set a Run Password to prevent users from using the snipet without permission?

On the other hand, with the run password, it gets quite uncomfortable to use the code - automatically.

[#10]

I would say don't share such a code which shouldn't be used by everyone (if you don't want to set run password). Creating specific users might also help (at least you can then more easily change password/disable that user) and getting IT onboard so they can check who is performing which queries with what and taking actions accordingly.

Also you could most likely use credential manager (if on windows) to manage those passwords like @ih did suggest (so you don't have to store them to the jsl file). I haven't (yet) used credential manager with JMP but I have used it when building RPA (robotic process automation) and it did work nicely.