Database passthrough

In some situations each user is provided a database login.  Specifically, each user has an account in the Oracle, MS or other SQL server.  While each user must have a Crash Magic account, the login validation can be performed by your SQL database.  This is accomplished by creating a simply query which returns few, if any rows.  

To accomplish this, create a query in the group's .shared account called "DBVerify".  The connection for that query will generally be the same connection as is used by all the user queries. (usually "Default")  The DBVerify query should issue a very simple query against a small table in the database that valid users have access to, and others will not.  

An example query might be:

SELECT LookupColumn

FROM DirectionLookups

WHERE 1=0

This query will never return any rows.  However, it will raise an error if the user doesn't have database permission to issue the query.  So, how does the system know the user?

The secret is in the connection object.  The connection object used for all user queries should specify %USER.LOGIN% for the login field.  The password field may be left blank.  (this can be easily set by clicking on the "Set for DBVerify login" link on that screen.

The result is that upon login, the system records the user's login name and password and user group.  The login name and password are used to create a database connection, and the DBVerify query is issued.  If the query succeeds, the user is logged in to the Crash Magic account they specified by user group and login name.  In addition, the password they specified is maintained in memory to re-create the connection as needed.  The password is never stored on disk or database, just in memory and just for the duration of the user's session.

In this way, each user is logged into their own account on the SQL server, and if validated, they are mapped to the correct Crash Magic account.  When passwords change on the SQL server, nothing needs to be done in Crash Magic, as the passwords are simply passed-through to the SQL server for validation.