Encrypt Database

How to encrypt the database, to check which environment you are in? We have a development database and a production database, when I am in the development server, I want teamsite to use the development database and use production database when I am in the production server.

Find more posts tagged with

Comments

Migrateduser

Not sure about encryption but for environment settings consider adding a

[custom]
database=whatever

Where custom is your company name in iw.cfg, then you can use a command such as iwconfig custom database to retrieve elements from this configuration and make a switch in your code based on that.

aces

Where in the environment setting?
Can you explain in more detail John?

Migrateduser

I am not sure I follow the question. It doesn't matter where in iw.cfg you put this section as long as it is not in the middle of some other section, but I would typically put it at the end. I would use some sort of token as the value of the "database" key, not the database connection string (since it's stored in cleartext in iw.cfg, and therefore potentially elsewhere such as in memory (I have only seen one customer that cared about cleartext in memory)). On Windows I think you could use a DSN identifier without exposing the password, but then just avoid the whole issue by hard-coding the DSN name and have the DSN config on dev point at the dev database and the same DSN on prod point at the prod database. Without DSN, retrieve this token value from iw.cfg with a command such as "iwconfig custom database", then perform a switch on that token to map it to an actual connection string.

You would still have to store your database hostname, username, password, etc. somewhere. Follow your enterprise standards for that.

Or you could implement some other custom solution, like a switch on hostname or whatever. Sorry if I am misunderstanding the question.

Edited by john on 06/03/03 10:57 AM (server time).

aces

Yes in Windows, you can DSN identifier, but how do I refer the DSN connection in Teamsite? Is there a command that I can use in iw.cfg?

Migrateduser

TeamSite uses a filesystem, not a database (well, there is that (Sybase SQLAnywhere?) thing but I don't know anything about it or even really what it is used for). Are you talking about DataDeploy? If so I think this would be in some DataDeploy config file. I avoid DataDeploy if I can. If you are not talking about DataDeploy are you talking about some custom menu item, workflow process, etc.? You would have to check the source code for that item.

Edited by john on 06/03/03 05:00 PM (server time).

aces

Filesystem? No, I'm not talking about DataDeploy. Which source code are you talking about John.

Migrateduser

Source code for your workflow externaltasks, custom menu items, etc. (whatever is accessing your database). What is accessing the database? Because TeamSite won't without DataDeploy.

aces

Sorry if I am confusing you. From what I know, our consultant update the database by calling updateDatabase.ipl. The problem I have right now is for the development server and production server have two different password and user names. Everytime I copy the utils.pm file from development over the production, I have to remember to change the username and password in the utils.pm file in the production so it reads the correct database. Plus, the database information is hard coded and not secure. Like you said before, if I store the database information in DSN in ODBC of the control panel, how do I refer the DSN connection in utils.pm?

Migrateduser

OK, now we have gotten somewhere. Hopefully the subroutine in utils.pm returns a database handle as would be returned by something like DBI->connect( "DBI Smiley Surprised

DBC:$db", $dbu, $pass );. If so you just have to figure out what to pass the connect subroutine to specify a DSN (I have no ideas). Someone on a Perl list may be better suited to help you.

If you don't have a subroutine that is returning a DBH you should probably modify utils.pm to create such a function which could use iw.cfg as I had described, then wherever you need a dbh call this routine. If you remove your username, password, etc. from utils.pm and post it I have a feeling ghoti can help you.

Migrateduser

I am no windows expert, so I have no idea how to get at DSN info from a control pannel, but how you access DSN info *might* depend on the type of database connection being used (DBI/ODBC/etc). If you post the utils.pm and the updateDatabase.ipl (aftering nulling out the username/password info first) someone might be able to give you some tips.

In the past, I have used a separate config file to pull out username/password information. That way, the config file only needs to be set when a username/password/hostname value is changed. To access the config file, I use a routine in a perl module to read in values and return them into a hash. Of course, this will not help you with the plain text problem, but it will prevent you from having to update the .pm file each time it is moved.

I will attach some sample code in a few mintes...

--
Jed Michnowicz
Interwoven Technical Consultant

Migrateduser

Config file sample and routine to read it attached.....

--
Jed Michnowicz
Interwoven Technical Consultant

aces

Attached is the utils.pm

Migrateduser

I did some simple searches and couldn't find a way to connect with a DSN without passing a username and password (but it seems that it should be possible). So maybe keeping the configuration elsewhere is a viable solution.

One thing I don't like about the solution proposed in this post is that it requires that the config file be different on the different servers. If that is acceptable, use something like this, but my typical build process is to copy as many files as possible from the checking point to production (excluding iw.cfg, *.uid, whatever OpenDeploy config files differ by server, etc.). So I would also change the XML to be something more like:

<config>
<environment name="dev">
<db>db1</db>
<dbUser>user</dbUser>
<dbPass>pass</dbPass>
</environment>

<environment name="prod">
<db>db2</db>
<dbUser>user</dbUser>
<dbPass>pass</dbPass>
</environment>
</config>

I would put this in IWHOME/custom/config/env.cfg, then add an "environment" key in the "custom" section of iw.cfg (set environment=dev on dev, environment=prod on prod). I also find the XML parsing hard to follow - I am more familiar with XPath. My Perl is a little rusty but I will give this a try (it would be a surprise if this actually worked as is).

use XML::XPath;

# not sure this one is required?

use XML::XPath::XMLParser;

sub connectDB
{

# if IWHOME/bin is not already in the path, add it or prefix iwconfig with the full path

my $env = `iwconfig custom environment`;

# can't remember if slashes have to be \\ on NT?

my $cfg = "${iwhome}/custom/config/env.cfg";

if ( ! open ( XML, "<$cfg" ))
{
die( "Unable to open $cfg for read : $!" );
}
else
{
local $/=undef;
my $xpath = XML::XPath->new( xml => <XML>, );
close( XML );
my $path = "/config/environment[@name='$env']";
my $database = $xpath->getNodeText( "$path/db" );
my $dbuser = $xpath->getNodeText( "$path/dbUser" );
my $password = $xpath->getNodeText( "$path/dbPass" );

if ( my $dbh = DBI->connect( $database, $dbuser, $password ))
{
return( $dbh );
}
else
{
die( "Error connecting to $database : $DBI::errstr";
}
}
}

Migrateduser

This would work if you needed to store a lot of configurable information about different environments (mailserver, deployment target hosts, etc). But maybe there is a simpler way that is a better fit.

I think even Interwoven's archaic Perl comes with a simple encryption implementation (blowfish? but you could use any encryption method you want). Create a string containing your database, username and password separated by some character that could never appear in any of these values (probably space). Write a Perl script to encrypt the string, then delete the script. Then write the encrypted value to [custom] in iw.cfg as database=encryptedString. Then in your connection, read this value using iwconfig and decrypt it, then split it back into three components using the character you selected.

If you don't like the selected character approach you could encrypt it as three strings (with three lines in iw.cfg) and read and decrypt each in your script.

Then you have to protect iw.cfg (because the encryption is probably weak) and your Perl module (because it shows how to decrypt it and provides a handle to the database, two things you probably want to restrict).

Or keep looking for a way with just the DSN, no username/password.