SQL Relay provides two built-in authentication mechanisms, configurable by the authtier attribute of the instance tag. If authtier="connection" then users are authenticated against a static list. If authtier="database" then users are authenticated against the database itself and users must log into SQL Relay using a user/password combination that would be valid to log into the database directly.
Authentication modules allow the SQL Relay server programs to authenticate users in some other manner.
The authentications section of the sqlrelay.conf file indicates which authentication modules to load and what parameters to use when executing them.
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<authentications>
<authentication module="userlist">
<user user="user1" password="password1"/>
<user user="user2" password="password2"/>
<user user="user3" password="password3"/>
<authentication>
</authentications>
...
</instance>
...
</instances>
In this example, the module attribute specifies which module to load and the user tags specify the list of valid users and passwords.
Module configurations may have attributes and/or nested tags. How these elements are interpreted is module-specific.
Authentication modules can be "stacked". Multiple modules may be loaded and multiple instances of the same type of module, with different configurations, may also be loaded.
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<authentications>
<authentication module="userlist">
<user user="user1" password="password1"/>
<user user="user2" password="password2"/>
<user user="user3" password="password3"/>
</authentication>
<authentication module="sqlrelay" socket="/tmp/auth.socket" user="auth" password="auth"/>
<authentication module="userlist">
<user user="user4" password="password4"/>
<user user="user5" password="password5"/>
</authentication>
</authentications>
...
</instance>
...
</instances>
At startup, the SQL Relay server creates instances of the specified authentication modules and initializes them. When a client connects, the server passes the supplied credentials to each module, in the order that they were specified in the config file. If a module successfully authenticates a user, then the client is allowed to continue its session. If a module fails to authenticate a user, then the credentials are passed on to the next module. If all modules fail to authenticate a user, then the client is not allowed to continue its session.
Currently, the following authentication modules are available:
The userlist module authenticates a user against a static list of valid user/password combinations. In fact, it has the same behavior as the default behavior of SQL Relay. The only difference is that the syntax in the config file is a little different.
When using the userlist authentication module, rather than using the users tag to specify valid users...
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<users>
<user user="user1" password="password1"/>
<user user="user2" password="password2"/>
<user user="user2" password="password2"/>
</users>
...
</instance>
...
</instances>
...you would list the users inside of the tag for the module:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<authentications>
<authentication module="userlist">
<user user="user1" password="password1"/>
<user user="user2" password="password2"/>
<user user="user3" password="password3"/>
<authentication>
</authentications>
...
</instance>
...
</instances>
Password encryption modules may also be used.
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<passwordencryptions>
<passwordencryption module="rot" id="rot13" count="13"/>
<passwordencryption module="rot" id="rot16" count="16"/>
</passwordencryptions>
...
<authentications>
<authentication module="userlist">
<user user="user1" password="cnffjbeq4" passwordencryptionid="rot13"/>
<user user="user2" password="cnffjbeq5" passwordencryptionid="rot13"/>
<user user="user3" password="fqiimeht8" passwordencryptionid="rot16"/>
<authentication>
</authentications>
...
</instance>
...
</instances>
Since the userlist authentication module appears to provide the exact same functionality as the built-in users tag, why would you want to use it instead of just using the built-in users tag?
The users tag is evaluated prior to evaluating any modules. So, you must use the userlist module if you are stacking authentication modules, and want to use a different authentication module first, falling back to a static list of users if it fails.
The database module authenticates a user against the database itself. This causes SQL Relay to switch which user it is logged into the database as.
When using SQL Relay with Oracle 8i or higher, it switches users without logging out. The database connection must be configured to log into the database as a user that can proxy other users and the client must attempt to log in to SQL Relay as one of the users that can be proxied. See this document for more information including instructions for configuring Oracle.
When SQL Relay is used with any other database, it simply logs out and logs back in as a different user. This is somewhat ineffecient and defeats the value of keeping persistent database connections but might be useful for particular applications.
NOTE: Database authentication should not be used in an instance where dbase="router". It's OK for the instances that the router uses to use database authentication but not the router instance itself. If database authentication is used for that instance, authentication will fail.
Using the database module has the same behavior as setting authtier="database" in the config file. The only difference is that the syntax in the config file is different.
When using the database authentication module, rather than using authtier="database"...
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... authtier="database" ... >
...
</instance>
...
</instances>
...you would use the module instead:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<authentications>
<authentication module="database"/>
</authentications>
...
</instance>
...
</instances>
Since the userlist authentication module appears to provide the exact same functionality as the built-in authtier attribute, why would you want to use it instead of just using the built-in authtier attribute?
There is no specific advantage to using the module instead of the attribute, other than consistency. If you're using other authentication modules in other instances, then you might also want to use the database authentication module in this instance.
Note also, that the database authentication module should not be used in a stacked configuration. Or, at least, using it in a stacked configuration could lead to unexpected results. When using database authentication, each connection is left logged in as the most recently authenticated user. If an SQL Relay user is authenticated by some other method, then there is no guarantee what user it will access the database as.
The sqlrelay module authenticates a user against a table in a database, using another instance of SQL Relay to access that database.
The module's host, port, socket, user and password paramters define which instance of SQL Relay to talk to. The table, usercolumn and passwordcolumn parameters define which table and which columns within the table to authenticate against. If passwords aren't stored in plain text, then an optional passwordfunction parameter may specify the stored procedure used to encrypt the password.
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<authentications>
<authentication module="sqlrelay" socket="/tmp/auth.socket"
user="auth" password="auth"
table="user" usercolumn="User" passwordcolumn="Password"
passwordfunction="password"/>
</authentications>
...
</instance>
...
</instances>
You can create your own custom authentication modules too.
SQL Relay provides a base class for authentication modules, called sqlrauth, defined in the header file: sqlrserver.h
class SQLRSERVER_DLLSPEC sqlrauth {
public:
sqlrauth(xmldomnode *parameters,
sqlrpwdencs *sqlrpe);
virtual ~sqlrauth();
virtual bool authenticate(sqlrserverconnection *sqlrcon,
const char *user,
const char *password);
protected:
xmldomnode *parameters;
sqlrpwdencs *sqlrpe;
};
On non-Windows platforms, it's most likely installed in /usr/local/firstworks/include/sqlrelay if you built from source or /usr/include/sqlrelay if you installed a package. On Windows platforms, it's most likely installed in C:\Program Files\Firstworks\include\sqlrelay.
The default implementaion of the constructor just sets the member variables parameters and sqlrpe to the corresponding passed-in values. parameters is a representation of the XML tag in the sqlrelay.conf file that loaded the module.
By default, the destructor does nothing.
authenticate() does nothing by default and returns false, indicating that authentication failed.
A custom module module must contain a class that inherits from sqlrauth, implements the necessary methods, and implements a function to return an instance of the class.
Lets say we want to create a custom authentication module that authenticates a user against a hardcoded list of users.
We can create the file hardcoded.cpp:
#include <sqlrelay/sqlrserver.h>
#include <sqlrelay/sqlrclient.h>
#include <rudiments/charstring.h>
class SQLRSERVER_DLLSPEC hardcoded : public sqlrauth {
public:
hardcoded(xmldomnode *parameters,
sqlrpwdencs *sqlrpe);
bool authenticate(sqlrserverconnection *conn,
const char *user,
const char *password);
};
hardcoded::hardcoded(xmldomnode *parameters,
sqlrpwdencs *sqlrpe) :
sqlrauth(parameters,sqlrpe) {
}
struct cred_t {
const char *user;
const char *password;
};
static cred_t credentials[]={
{"userone","passwordone"},
{"usertwo","passwordtwo"},
{"userthree","passwordthree"},
{NULL,NULL}
};
bool hardcoded::authenticate(sqlrserverconnection *sqlrcon,
const char *user, const char *password) {
for (const cred_t *c=credentials; c->user; c++) {
if (!charstring::compare(user,c->user) &&
!charstring::compare(password,c->password)) {
return true;
}
}
return false;
}
extern "C" {
SQLRSERVER_DLLSPEC sqlrauth *new_sqlrauth_hardcoded(
xmldomnode *users,
sqlrpwdencs *sqlrpe) {
return new hardcoded(users,sqlrpe);
}
}
Here, the hardcoded class inherits from sqlrauth. The constructor just calls the parent constructor. The authenticate() method compares the user and password to a hardcoded list and returns true if it matches or false if it doesn't.
Note the "new_sqlrauth_hardcoded()" function which just allocates an instance of hardcoded and returns it. This function is essential to provide, and it is essential that it be named "new_sqlrauth_modulename" and take xmldomnode * and sqlrpwdencs * parameters. 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 sqlrauth_hardcoded.so hardcoded.cpp `sqlrserver-config --libs` `rudiments-config --libs`
This will create the file sqlrauth_hardcoded.so
On Mac OSX, run a command like:
gcc -bundle `sqlrserver-config --cflags` `rudiments-config --cflags` -o sqlrauth_hardcoded.bundle hardcoded.cpp `sqlrserver-config --libs` `rudiments-config --libs`
This will create the sqlrauth_hardcoded.bundle
On Windows, run commands like:
cl /I"C:\Program Files\Firstworks\include" /c hardcoded.cpp
link -out:sqlrauth_hardcoded.dll /LIBPATH:"C:\Program Files\Firstworks\lib" libsqlrserver.lib librudiments.lib
This will create the sqlrauth_hardcoded.dll
It is essential that the file be named "sqlrauth_modulename.extension".
To install the new module, copy it to the SQL Relay "modules" directory. On non-Windows platforms, that is most likely /usr/local/firstworks/libexec/sqlrelay if you built from source, or /usr/libexec/sqlrelay if you installed a package. On Windows platforms, that is most likely C:\Program Files\Firstworks\libexec\sqlrelay
To configure an instance of SQL Relay to use your module, you will need to update the sqlrelay.conf file to include a "authentications" section:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
...
<instance id="example" ... >
...
<authentications>
<authentication module="hardcoded"/>
</authentications>
...
</instance>
...
</instances>
The server will see the module="hardcoded" attribute in the sqlrelay.conf file, look in the "modules" directory for sqlrauth_hardcoded.extension, load it and and run the new_sqlrauth_hardcoded() method to get an instance of the hardcoded 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.