cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
terramedics
Level I

JMP scripts embed DSN parameters; why??

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 SOLUTIONS

Accepted Solutions

Re: JMP scripts embed DSN parameters; why??

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.

View solution in original post

pmroz
Super User

Re: JMP scripts embed DSN parameters; why??

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

View solution in original post

8 REPLIES 8

Re: JMP scripts embed DSN parameters; why??

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
Level I

Re: JMP scripts embed DSN parameters; why??

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

Re: JMP scripts embed DSN parameters; why??

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

Re: JMP scripts embed DSN parameters; why??

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
Level I

Re: JMP scripts embed DSN parameters; why??

Smart solution; thank you for sharing!

msharp
Super User (Alumni)

Re: JMP scripts embed DSN parameters; why??

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

Re: JMP scripts embed DSN parameters; why??

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

JensRiege
Level IV

Re: JMP scripts embed DSN parameters; why??

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...