Interchange user database

Introduction

Interchange has a "user database" facility that allows users to save certain values from their session.  It also allows the setting of database table or file access control lists for use in controlling access to those facilities on a user-by-user basis.

The column names in the userdb table correspond with <form> variable names in the user's session;  If there is a column named "address", when the user logs in, then the contents of that column will read, and will be available for display using [value address].  Similarly, the column's value would be available with the following:

The userdb table's column names to be used are not set in stone;  They may be changed with options, and columns may be added or subtracted at any time.  Most users will choose to keep the default Standard demo column names for simplicity, as they cover most common needs.  The Standard e-commerce demo makes use of the following columns:

code accounts acl address address_book b_address b_city b_country b_name b_nickname b_phone b_state b_zip

carts city country db_acl email email_copy fax fax_order file_acl mv_credit_card_exp_month mv_credit_card_exp_year mv_credit_card_info mv_credit_card_type

mv_shipmode name order_numbers p_nickname password phone_day phone_night preferences s_nickname state time zip

A few of those columns have special names.  although all can be changed via an option.  A couple of the columns are reserved for Interchange's use.

Note If you are not running with GnuPG, PGP or another form of encryption for credit card details (which is never recommended), it is important that the "mv_credit_card_info" column is removed from the table so that the value can be ignored.  Failure to remove this column will result in encryption-related errors.

The special columns are as follows:

Name	Description
carts	Storage area for saved shopping carts.
address_book	Storage area for shipping address book.
accounts	Storage area for billing accounts book.
preferences	Storage area for user preferences.
nickname	Nickname for a saved cart record.
s_nickname	Nickname for a saved shipping address record.
b_nickname	Nickname for a saved billing account record.
p_nickname	Nickname for a saved preferences record.
acl	Storage for simple integrated access control.
db_acl	Storage for database access control lists.
file_acl	Storage for file access control lists.

If a column not present, the corresponding capability will not be available.

Note The "accounts", "address_book", "carts" and "preferences" columns should be defined using the BLOB datatype, if using SQL.  This is also recommended for the various acl columns, as those lists could grow to be quite large.

Reserved fields include:

Name	Description
code	The username (also the key for the table).
password	Password storage.
time	Last login date/time.

Access to the user database table(s)

Interchange provides a [userdb] tag, which allows access to the Interchange user database table(s).

Interchange also provides a UserDB local configuration directive, which allows various defaults to be set so that they don't have to be supplied to the [userdb] tag on every call.  The UserDB directive also allows you to rename remap most of the userdb columns, rename the userdb table itself and even use other tables to store and look up userdb data.

User database functions

The user database features are implemented as a series of functions available via the [userdb] tag.  The functions are as follows:

General

change_pass

Change the password on the currently logged-in account.

Active [userdb] parameters:  username, password, verify oldpass and cgi.

This function requires the "username", "password", "verify" and "oldpass" parameters, which are, by default, expected to be contained in the $Values space, in the "mv_username", "mv_password", "mv_verify" and "mv_password_old" respectively.  If the "cgi" parameter is set true then $CGI will be used instead of $Values.

login

Log in to an Interchange user account.

Active [userdb] parameters:  username, password, crypt, md5, pass_field, ignore_case, indirect_login and cgi.

By default, the "username" is expected to be contained in "mv_username" and the "password" in "mv_password", both in the $Values space.  If the "cgi" parameter is set true then $CGI will be used instead of $Values.

If "indirect_login" is used, it should be set to a column name that can be used as a lookup for the real username.  The "email" column is a common "indirect_login" setting.  This also calls the "new_account" function to create a user account based, upon an assigned username, and so the "assign_username" parameter should always be set true when using the "indirect_login" facility.

If the login is successful, the "username" session value ([data session username]) will be set to the value of the "userdb.username" column.  Well, that is unless "indirect_login" is used, in which case [data session username] will be set to the value of the named indirect column.

The login will recall the values of all non-special columns in the user's userdb row and place them in correspondingly-named variables, for lookup using the [value] (or possibly [scratch]) tag.

The CookieLogin local configuration directive allows users to save their username and password in a browser cookie.  The cookie expiration time is set by SaveExpire, and renewed every time they log in.  To cause the cookie to be generated originally, the "mv_cookie_password" or "mv_cookie_username" values must be set true (usually set via the login form).  The former causes both "username" and "password" values to be saved, the latter just the "username".

logout

Log out of the current Interchange user account.

No additional parameters are required, although can specify the "clear=1" parameter, which will clear the user's session of all of the form values and scratchpad variables that were loaded/saved by the [userdb] tag.

You can also specify the "clear_session=1" parameter, which will force an entirely new session to be created for the user.

new_account

Create a new Interchange user account.

Active [userdb] parameters:  username, password, verify, assign_username, username_mask, ignore_case indirect_login and cgi.

This function requires the "username", "password" and "verify" parameters which are, by default, expected to be contained in the $Values space, in the "mv_username", "mv_password", "mv_verify" respectively If the "cgi" parameter is set true then $CGI will be used instead of $Values.

If the "assign_username" parameter is true then a sequential username will be assigned.  The "counter" parameter can be used to set the filename (which must be relative to the website's home directory, as configured using the Catalog global directive), or the default of "etc/username.counter will be used.  Using the default counting mechanism, the first username will be "U0001" if the counter file doesn't already exist.

If assign_username is used, you can choose to have a pseudo-username that is different from the real username.  (The user's email address is commonly used for this.) The pseudo-username column name can specified using the "indirect_login" parameter.

Warning When the user logs in, this column will be used to find the real username, so you must ensure that a UNIQUE index is present on the column.  If you do not have a UNIQUE index on the column, or another way to ensure that duplicates are disallowed, then you risk getting "user already exists" errors.

The "no_login" parameter prevents the user from being automatically logged into the new account.  This may be useful in some applications.

The "ignore_case" parameter forces the username and password to lower-case in the table, rendering the username and password case-insensitive.  This is recommended if using email addresses as usernames.  In fact, it's recommended anyway.

If the "username_mask" parameter is set to a valid Perl regular expression (without the surrounding "/ /"), then any username containing a matching string will be barred from use.  For example, to screen out order numbers from being used by a random user, you could use the following:

The CookieLogin local configuration directive allows users to save their username and password in a browser cookie.  The cookie expiration time is set by SaveExpire, and renewed every time they log in.  To cause the cookie to be generated originally, the "mv_cookie_password" or "mv_cookie_username" values must be set true (usually set via the login or "create new account" form).  The former causes both "username" and "password" values to be saved, the latter just the "username".

To automatically create an account for every order, you could set the following in the OrderReport file:

This would be coupled with a login form that asks for the order number and zip code, thereby allowing the display of the content of a transaction, with (presumably updated) order status information or a shipping company tracking number.

save

Saves all non-special form values that have columns in the userdb table(s).

Active [userdb] parameters:  nickname and cgi.

If a value is defined as "scratch" then it will be retrieved from the [tags/scratch|scratchpad]], otherwise it will be retrieved from the $Values or $CGI space, depending upon the "cgi" parameter.  If the column is defined as outboard the it will be saved in the outboard table using the "outboard_key_col value as the key.

Shipping

See the Address book section, below.

delete_shipping

Delete an entry from the shipping Address book.

Active [userdb] parameters:  nickname and addr_field.

get_shipping

Recall an entry from the shipping Address book.

Active [userdb] parameters:  nickname, shipping and addr_field.

get_shipping_names

Gets the names of shipping address book entries and places them in the "address_book" variable.

Active userdb parameters:  addr_field and show.

By default this does not return the values.  To have the values returned, set the "show" to "1".  For example:

set_shipping

Place an entry in the shipping address book.

Active userdb parameters:  nickname, shipping and addr_field.

Billing

See the Accounts book section, below.

delete_billing

Delete an entry from the billing Address book.

Active [userdb] parameters:  nickname and bill_field.

get_billing

Recall an entry from the billing Address book.

Active [userdb] parameters:  nickname, billing and bill_field.

get_billing_names

Gets the names of billing address book entries and places them in the "accounts" variable.

Active [userdb] parameters:  bill_field and show.

By default this does not return the values.  To have the values returned, set the "show" to "1".  For example:

set_billing

Place an entry in the billing address book.

Active userdb parameters:  nickname, billing and bill_field.

Shopping carts

See the Carts section, below.

delete_cart

Delete a saved shopping cart.

Active [userdb] parameters:  nickname and cart_field.

get_cart

Recall a saved shopping cart.

Active [userdb] parameters:  nickname, cart_field and target.

The "target" parameter causes the saved items to be loaded to a shopping cart different from the one currently in use.  The "cart_field" parameter names the database column used for cart storage.

get_cart_names

Gets the names of saved shopping cart entries and places them in the "address_book" variable.

Active userdb parameters:  cart_field and show.

By default this does not return the values.  To have the values returned, set the "show" to "1".  For example:

set_cart

Save the contents of a shopping cart.

Active [userdb] parameters:  nickname, cart_field and target.

The "target" parameter causes the saved items to be loaded to a shopping cart different from the one currently in use.  The "cart_field" parameter names the database column used for cart storage.

Access control lists

check_acl

Checks the simple access control listing for a location, returning "1" if allowed, or returning nothing at all if not allowed.

Active [userdb] parameters:  location and acl_field.

check_file_acl and check_db_acl

Checks a complex access control value and returns a true or false value.

Active [userdb] parameters:  location, mode, db_acl_field and file_acl_field.

In the above example, "mode" is any value to be checked with "check_file_acl".

This function will return true if the specified "mode" string is contained within the entry for the specified "location".  The return code could be checked as follows:

See the Controlling page access with UserDB section, below.

set_acl

Set a simple acl.

Active [userdb] parameters:  location, acl_field and delete.

The following would allow the current user access to the "somedir/somepage", if it is protected using the MV_USERDB_ACL_TABLE mechanism:

The following would delete access:

You can use the "show" parameter to display the setting at the same time:

set_file_acl and set_db_acl

Sets a complex access control values.

Active [userdb] parameters:  location, mode, db_acl_field, file_acl_field and delete.

In the above example, "mode" is any value to be checked with "check_file_acl".  As with the simple ACL, use delete=1 to delete the entry altogether.

Address book

This refers to the shipping address book, as opposed to the billing address book.  The shipping address book saves information relevant to order shipments.  In its simplest form, this can be the only address book needed.  By default the following value, cgi or scratchpad values (depending upon your configuration) are included:

s_nickname name fname lname address address1 address2

address3 city state zip country phone_day mv_shipmode

The first field is always the name of the variable that contains the key for the entry.  The values are saved with the [userdb function=set_shipping] tag call, and are recalled with [userdb function=get_shipping].  A list of the available keys is kept in the "address_book" variable, suitable for iteration and display in an HTML <select> box.  or as a set of links.

To get the "nicknames" assigned to the addresses, use the get_shipping_names function, as follows:

By default, the retrieved nicknames are placed in the "address_book" variable.  Here is a little snippet that builds a select box to recall shipping data:

The same principle works with accounts, carts and preferences.

To restore a cart, based on the above, define a mv_check callback routine, such as the following:

When the mv_check callback is executed, the shipping address information will be inserted into the user's session.  This is destructive of any current values of those user session variables, of course.

To change the columns fields that are recalled or saved, use the [userdb] tag's shipping parameter:

Only the values of the "city" and "state" variables will be replaced in this case.

Accounts book

This refers to the billing address book, as opposed to the shipping address book.  The accounts address book saves information relevant to order billing.  In its simplest form, this can be the only accounts book needed.  By default the following value, cgi or scratchpad values (depending upon your configuration) are included:

b_nickname b_name b_fname b_lname b_address b_address1 b_address2 b_address3 b_city

b_state b_zip b_country b_phone purchase_order mv_credit_card_type mv_credit_card_exp_month mv_credit_card_exp_year mv_credit_card_info

The first field is always the name of the variable that contains the key for the entry.  The values are saved with the [userdb function=set_billing] tag call, and are recalled with [userdb function=get_billing].  A list of the available keys is kept in the "accounts" variable, suitable for iteration and display in an HTML <select> box.  or as a set of links.

See the usage examples in the Address book section, which can be easily adapted for use with the accounts book.

Preferences

Preferences are miscellaneous session information.  By default the following value, cgi or scratchpad values (depending upon your configuration) are included:

email fax phone_night

fax_order email_copy

The p_nickname acts as a key to select the set of preferences.  You can use the following to change the values that are included in the preferences list:

Or in the UserDB local (catalog.cfg) configuration:

See the usage examples in the Address book section, which can be easily adapted to load and save user preferences.

Carts

The contents of shopping carts may be saved or recalled in much the same fashion as the address book.  See the usage examples in the Address book section, which can be easily adapted to load and save the cart contents.

Controlling page access with UserDB

Interchange can implement a simple access control scheme, using the userdb table(s).  Controlled pages must reside in a directory which has an empty file called ".access".  If it is not empty then only the RemoteUser or MasterHost may access files in that directory.

Set the following variables in your local configuration (catalog.cfg):

The MV_USERDB_ACL_TABLE is the table that controls access and, likewise, the MV_USERDB_ACL_COLUMN names the column which will be checked for authorisation.

The table entry should contain the complete Interchange-style page name of the page to be allowed.  It will not match substrings.

For example, if the flycat user followed this link:

Access would be allowed if the content of the userdb entry was:

and disallowed if it was:

Access can be enabled with the following:

And can be disabled with the following:

Of course, a pre-existing table with the ACL values will work as well.  It need not be in the UserDB setup.

Using more than one database table

You can save/retrieve userdb information from more than one table by using "outboard" specifier.  This is a comma-separated series of quoted key and value field specifications.  For instance, if the billing address is to be stored in a separate table named "billing", you could set up the following:

When the user logs in, Interchange will access the "first_name" column, in the "billing" table, to get the value of "b_fname".  When [userdb save] is used, it will be saved there as well.  If you want to make the columns read-only, you can use UserDB default scratch "b_fname b_lname ...", and the values will be retrieved/saved from there.  To initialise the values for a form, you could use something like the following after the user logs in:

If the columns in the outboard table use another key, besides username, then you can specify the column in the userdb that contains the key value.  For example:

Category:  Interchange database

Last modified by: Gert van der Spoel Modification date: Saturday 26 January 2008 at 3:02 PM (EST)