Password encryption allows you to store encrypted passwords in the sqlrelay.conf file so that they are not publicly visible. Passwords for SQL Relay users and database passwords may both be encrypted.
Encryption and decryption are achieved via loadable modules. A section of the sqlrelay.conf file indicates which modules to load and parameters in the user and connection tags indicate which module to use with the password defined in that same tag.
For example, the rot (rotate) module encrypts by performing a simple character rotation. To use this module you would include a section in the sqlrelay.conf file like:
<instances>
...
<instance ... >
...
<passwordencryptions>
<passwordencryption module="rot" id="rot13" count="13"/>
</passwordencryptions>
...
</instance>
...
</instances>
The module attribute specifies which module to load. The count attribute is a parameter for the module. Different modules may have different parameters, or may have no parameters. In this case, "13" tells the module to rotate by 13 characters. The id parameter is defined by you and just assigns this particular configuration an id that may be referred to later.
To use this module with with an SQL Relay password, you would specify:
<instances>
...
<instance ... >
...
<users>
<user user="test" password="grfg" passwordencryption="rot13"/>
</users>
...
</instance>
...
</instances>
Note that the password is encrypted. Unencrypted, it would just be "test". A command line program (described later) is provided to encrypt passwords.
Note also that the passwordencryption attribute refers to the id of the module as set using the id parameter in the passwordencryption tag (rot13), not the module name (rot).
To use password encryption with a database password, you would specify:
<instances>
...
<instance ... >
...
<connection connectionid="db" string="user=testuser;password=grfgcnffjbeq;..." passwordencryption="rot13" metric="6"/>
...
</instance>
...
</instances>
Again, the password is encrypted. Unencrypted, it would just be "testpassword".
It is possible to load multiple modules and use each one with a different password. For example, you might want to use the rot module with a count of 13 for the SQL Relay password and a count of 10 for the database password.
<instances>
...
<instance ... >
...
<passwordencryptions>
<passwordencryption module="rot" id="rot13" count="13"/>
<passwordencryption module="rot" id="rot10" count="10"/>
</passwordencryptions>
...
<users>
<user user="test" password="grfg" passwordencryption="rot13"/>
</users>
...
<connection connectionid="db" string="user=testuser;password=docdzkccgybn;..." passwordencryption="rot10" metric="6"/>
...
</instance>
...
</instances>
Encryption modules may be either two-way or one-way. Two-way encryption modules can both encrypt and decrypt a password. One-way encryption modules can only encrypt a password.
Symmetric and asymmetric key encryption techniques are two-way. The rot encryption is an example of symmetric key encryption. Asymmetric key encryptions generally use a public/private key pair, where the publicly available key is be used to encrypt the data but a privately held key is required to decrypt it. SQL Relay can use two-way encryption modules with passwords for SQL Relay users and database passwords.
One-way encryption techniques include DES, MD5 and SHA1 hashes. When using those techniques, the password can be encrypted but cannot effectively be decrypted. SQL Relay can only use one-way encryption modules with passwords for SQL Relay users.
The command line tool sqlr-pwdenc is provided to help encrypt passwords for inclusion in the sqlrelay.conf file. Given an encryption module and password, it will print out the encrypted password.
sqlr-pwdenc [-config configfile] -id id -pwdencid passwordencryptionid -password password
For example:
[dmuse@localhost ~]$ sqlr-pwdenc -id oracletest -pwdencid rot13 -password testpassword
grfgcnffjbeq
The resulting string "grfgcnffjbeq" can now be put in the sqlrelay.conf file as the password.
One final thing to note is that when using the command line utilities which take a -id parameter like sqlrsh or sqlr-import against an instance of SQL Relay where the SQL Relay password has been encrypted, on the same machine that the SQL Relay server is running, you should not use the -id parameter, but should specify connection parameters directly instead. For example, do not do:
sqlrsh -id oracletest
Instead, do:
sqlrsh -host localhost -port 9000 -user test -password test
The -id parameter causes the program to open the sqlrelay.conf file and extract the host, port, socket, user and password from the specified instance. If the password is encrypted, the encrypted password will be extracted and passed to the server, already encrypted, causing it not to match.
Currently, the following password encryption modules are available:
The rot module is a two-way encryption module that performs a character rotation, similar to the popular ROT13 algorithm, though it can rotate by any amount specified in the count attribute, not just 13 and rotates digits as well as upper and lower-case characters. See the Introduction for example usage.
The md5 module is a one-way encryption module that encrypts the password using the MD5 algorithm.
The crypt module is a one-way encryption module that encrypts the password using the DES algorithm using a salt specified in the salt attribute. The salt is required and must be a 2 digit alphanumeric code.
You can create your own custom password encryption modules too.
SQL Relay provides a base class for password encryption modules, called sqlrpwdenc, defined in the header file: sqlrpwdenc.h, most likely installed in /usr/local/firstworks/include/sqlrelay if you built from source or /usr/include/sqlrelay if you installed a package:
// Copyright (c) 2012 David Muse
// See the file COPYING for more information
#ifndef SQLRPWDENC_H
#define SQLRPWDENC_H
#include <rudiments/xmldomnode.h>
class sqlrpwdenc {
public:
sqlrpwdenc(rudiments::xmldomnode *parameters);
virtual ~sqlrpwdenc();
virtual const char *getId();
virtual bool oneWay();
virtual char *encrypt(const char *value);
virtual char *decrypt(const char *value);
protected:
rudiments::xmldomnode *parameters;
};
#endif
The default implementaion of the constructor just sets the member variable parameters to the passed-in value parameters which is a representation of the XML tag in the sqlrelay.conf file that loaded the module.
By default, the destructor does nothing.
getId() returns the ID for this instance of the password encryption module, as defined in the sqlrelay.conf file.
oneWay() returns false by default.
encrypt() and decrypt() return NULL by default.
The custom module module must contain a class that inherits from sqlrpwdenc, implements the necessary methods and implements a function to return an instance of the class.
Lets say we want to create a custom encryption module that reverses the case of the password.
We can create the file reversecase.cpp:
#include <sqlrelay/sqlrpwdenc.h>
#include <rudiments/charstring.h>
#include <rudiments/character.h>
using namespace rudiments;
class reversecase : public sqlrpwdenc {
public:
reversecase(xmldomnode *parameters);
char *encrypt(const char *value);
char *decrypt(const char *value);
private:
char *reverse(const char *value);
};
reversecase::reversecase(xmldomnode *parameters) : sqlrpwdenc(parameters) {
}
char *reversecase::encrypt(const char *value) {
return reverse(value);
}
char *reversecase::decrypt(const char *value) {
return reverse(value);
}
char *reversecase::reverse(const char *value) {
char *retval=charstring::duplicate(value);
for (char *c=retval; *c; c++) {
if (character::isUpperCase(*c)) {
*c=character::toLowerCase(*c);
} else if (character::isLowerCase(*c)) {
*c=character::toUpperCase(*c);
}
}
return retval;
}
extern "C" {
sqlrpwdenc *new_reversecase(xmldomnode *parameters) {
return new reversecase(parameters);
}
}
Here, the reversecase class inherits from sqlrpwdenc. The constructor just calls the parent constructor. Since the encryption and decryption operations are the same for case-reversal, we define a private reverse() method to actually perform the operation and call it from the public encrypt() and decrypt() methods. We don't need to implement the oneWay() method because it returns false by default, and our module implements a two-way encryption strategy.
Note that the encrypt() and decrypt() methods allocate the result string internally and then return it. The calling program will deallocate these values. It is also safe to return NULL.
Note also, the "new_reversecase()" function which just allocates an instance of reversecase and returns it. This function is essential to provide, and it is essential that it be named "new_modulename" and take a single xmldomnode * parameter. It is also essential that it be wrapped with an extern "C" clause to prevent it from being name-mangled by the compiler.
To build the module on a Linux or Unix system, run a command like:
gcc -shared `sqlrserver-config --cflags` `rudiments-config --cflags` -o sqlrpwdenc_reversecase.so reversecase.cpp `sqlrserver-config --libs` `rudiments-config --libs`
On Mac OSX, run a command like:
gcc -bundle `sqlrserver-config --cflags` `rudiments-config --cflags` -o sqlrpwdenc_reversecase.bundle reversecase.cpp `sqlrserver-config --libs` `rudiments-config --libs`
This will create the file sqlrpwdenc_reversecase.so (or sqlrpwdenc_reversecase.bundle on OSX).
It is essential that the file be named "sqlrpwdenc_modulename.so" (or sqlrpwdenc_modulename.bundle on OSX).
The sqlrserver-config and rudiments-config scripts must be in your PATH and will supply the appropriate compile options for those libraries.
To install the new module, copy it to the SQL Relay "modules" directory, most likely: /usr/local/firstworks/libexec/sqlrelay if you built from source, or /usr/libexec/sqlrelay if you installed a package.
To configure an instance of SQL Relay to use your module, you will need to update the sqlrelay.conf file to include a "passwordencryptions" section and add passwordencryption attributes to the user and/or connection tag as appropriate:
<instances>
...
<instance ... >
...
<passwordencryptions>
<passwordencryption module="reversecase" id="rc"/>
</passwordencryptions>
...
<users>
<user user="test" password="TEST" passwordencryption="rc"/>
</users>
...
<connection connectionid="db" string="user=testuser;password=TESTPASSWORD;..." passwordencryption="rc" metric="6"/>
...
</instance>
...
</instances>
The server will see the module="reversecase" attribute in the sqlrelay.conf file, look in the "modules" directory for sqlrpwdenc_reversecase.so, load it and and run the new_reversecase() method to get an instance of the reversecase class and then run the various methods of that class.
This example module doesn't have any parameters, but if you create a module that does then you can access them via the the protected "parameters" member variable. For example:
const char *count=parameters->getAttributeValue("count");
Refer to the rudiments documentation for more info on the xmldomnode class.