Subscribe Bookmark RSS Feed

JMP scripts embed DSN parameters; why??

terramedics

Community Trekker

Joined:

Jan 6, 2014

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?

7 REPLIES
briancorcoran

Joined:

Jun 23, 2011

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.

terramedics

Community Trekker

Joined:

Jan 6, 2014

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

4719_DSN.JPG

pmroz

Super User

Joined:

Jun 23, 2011

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));

pmroz

Super User

Joined:

Jun 23, 2011

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);

terramedics

Community Trekker

Joined:

Jan 6, 2014

Smart solution; thank you for sharing!

msharp

Super User

Joined:

Jul 28, 2015

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.

pmroz

Super User

Joined:

Jun 23, 2011

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