JMP scripts embed DSN parameters; why??

[#1]

JMP scripts embed data connection parameters inside the JSL file.  Is there any reason for such improper programming practice?  Imagine you have hundreds of JSLs and SQL admin decided to change the server or  database credentials.  Why not use DSNs as an intermediary?

[#2 · Accepted solution]

The majority of our customers find this a useful feature, and in fact it was added due to customer requests.  It is easily suppressed by selecting Preferences->Tables->ODBC Hide Connection String.  This could be disseminated to users via a preference file specific to your organization.

[#3]

before posting this comment I could not find any info about managing ODBC.  My version of JMP 10 doesn't have this option.

[#4 · Accepted solution]

It doesn't appear to be in the preferences for JMP 10.  It's in the JSL scripting guide on page 480.  You have to set it in JSL.  Here's the relevant part:

Note: When you import data from an ODBC database, a table variable is added that can contain user ID

and password information. To prevent this from happening, set the following JSL-only preference:

pref(ODBC Hide Connection String(1));

[#5]

I have hundreds of JSL scripts that make calls to an Oracle database via ODBC.  However I don't embed separate data connection parameters inside each JSL file.  Instead I use an INCLUDE statement to run a single JSL file that defines the DSN string.

Here's what my code looks like:

I have one JSL script, called Get DSN_CREDENTIALS.jsl:

// Get DSN_CREDENTIALS.jsl
::my_dsn_credentials = "Driver={Oracle in OraClient11g64_home1};Dbq=MY_DATABASE;UID=MYUSERID;PWD=MYPASSWORD;";
// Return nothing
.;

I encrypt the script using JSL's code encryption capability, btw.

Then, each time I need the connection string I use code that looks like this:

// Get database access string
if (is empty(::my_dsn_credentials),
      include("$ADDIN_HOME(com.mydept.myapp)\Get DSN_CREDENTIALS.jsl");
);
 
dbc = create database connection(::my_dsn_credentials);
sql_statement = "SELECT SYSDATE FROM DUAL";
dt = execute sql(dbc, sql_statement);
close database connection(dbc);

[#6]

Smart solution; thank you for sharing!

[#7]

This is a poor security solution.  All I would have to do to get the username and password to your database would be to run:

include("$ADDIN_HOME(com.mydept.myapp)\Get DSN_CREDENTIALS.jsl");

::my_dsn_credentials;

Whats stored in ::my_dsn_credentials will be displayed in the log file in plain text.  Not to mention storing the credentials all in one place as global variables, I could easily run this:

include("$ADDIN_HOME(com.mydept.myapp)\Get DSN_CREDENTIALS.jsl");

show globals();

Now I have all the database information stored in Get DSN_CREDENTIALS.jsl, even databases not used in the original script I received.  This second portion can be fixed easily by changing all "::" to "__" (double-underscore), thus, ::my_dsn_credentials becomes __my_dsn_credentials.  This will hide the global variables from being displayed in the show globals(); function. However, this still doesn't solve the issue where the specified database is compromised. This can also be fixed following the format:

Get DSN_CREDENTIALS.jsl:

// Get DSN_CREDENTIALS.jsl

__my_dsn_credentials = "Driver={Oracle in OraClient11g64_home1};Dbq=MY_DATABASE;UID=MYUSERID;PWD=MYPASSWORD;";

__dbc = create database connection(__my_dsn_credentials);

// Return nothing

__my_dsn_credentials = .;

This fixes two issues.  One you can pass __dbc to another script.  If one runs __dbc; it will pass 'Database( "Oracle in OraClient11g64_home1(MY_DATABASE)" )' to the log for the above script.  Displaying the driver and database is far less of a security threat as passing a username and password.  Second, and this is important, __my_dsn_credentials is written over.  This means even if they were to GUESS the variable the information is still safe.

[#8]

That's better than what I've been doing.  Thanks for sharing.  I have the "luxury" of working with non-techie users.

[#9]

I tried this solution and got 90% of the result...

I am getting a successful connection to my database, but even though the DSN information imported with the include function has the UID and PWD values, the connection still prompts me to enter the DSN name and the password in a ODBC popup window.

Any ideas of why this happens?

//Get DSN Credentials

::Get DSN Credentials = "DRIVER={Oracle in OraClient11g_home1};DSN=myDB;UID=myUID;PWD=myPWD;DBQ=MyDB;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;";

// Return nothing

;

My Jmp Script successfully connects:

If (is empty(::Get DSN Credentials),

include("C:\_JMP Scripts\JMP DSN\Get DSN Credentials.jsl");

);

CONNECT = create database connection (::Get DSN Credentials); 　

SQL1 = "SELECT * FROM DB.TABLE"; 　

OUTFILE1 = "MyFilename";

　

dt1 = Open Database(CONNECT, SQL1, OUTFILE1);

But still asks me for my DB name and my Password...