You are not logged in Log in Join
You are here: Home » Members » vladap » MySQL user folder » README » View Document

Log in





This Zope product has four functions:

- Main function is to authenticate users and set their roles based on
data from the MySQL server.

- It keeps track of sessions (logging is supported).

- It allows reading and writing custom user and session data. That
information is also kept in a MySQL table.

- It has methods that allow users to modify their accounts. It also
allows anonymous user creation (it allows users to create their accounts
through the web). Also, folder's management interface allows user

Folder supports both HTTP and cookie authentication. Session support uses
cookies only.

This version is tested with Zope 2.7.2 (with python 2.3.3) and Plone 2.0.4.
This product requires MySQLdb python module (tested with version 1.1.8),
and MySQL (tested with version 4.0.20). mysqlUserFolder does not use
COMMIT/ROLLBACK - you should use non-transactional tables.

There is a mailing list at:
My email address is:

Security: important note

If you plan to allow users to change their own data and/or create new users,
read carefully the section "User interface".

DTML scripts in given in dtml.user folder are only examples. User input
(anonymous user creation, email data, realname, ...) checks should be added.

mysqlUserFolder functions uses DB API parameter queries when constructing sql
queries, so any special characters in SQL query should be properly escaped.
Also, "manage" dtml scripts use html_quote tag.

But all that is not a replacement for proper user input checking. User input
checking should be added to "user" dtml scripts on any system with untrusted
users. Also, example "user" dtml scripts do not use html_quote tag.

Zope authentication process

Zope performs authentication (and authorization) by searching for
the first object with id 'acl_users' (user folder) in the hierarchy. When such
object is found, Zope calls it's validate method with an object that
represents HTTP request (REQUEST) and a list of roles. Request object embeds
all user cookies (because they are sent with each HTTP request) and HTTP
authentication data (username and password) if client provided that. Zope in
fact asks validate method: "Can this request act as one of roles from the
list ?". User folder should return user object or None. Then, in case folder
returned user object, Zope will include user object in the REQUEST object and
it will pass that REQUEST object to the object that was initially called. If
the response was None, Zope tries to find next user folder.

In case of HTTP authentication everything is simple. If REQUEST does not
contain username and password (or they are wrong), user folder returns None.
If Zope can't validate REQUEST it returns HTTP error to the client
("403 Authorization required") and client then pops up dialog asking user for
the username and password. This works well in case you have mysqlUserFolder
and user enters Zope super username & password. mysqlUserFolder will
return None and Zope's root user folder will authenticate him.

Cookie (with form) authentication is not so simple. The problem is than HTTP
error cannot be returned to the client. If mysqlUserFolder cannot validate
REQUEST it must then raise an exception which shows login screen. In this
case, mysql user folder cannot return None because only mysql user folder
can show login screen. So, there is no way mysql user folder can let some
other user folder to do the authentication. This means than if you are
accessing some object below mysql user folder (with cookie auth) you cannot
use Zope's super (emergency/init) username and password. Therefore you should
have a user with "Manager" role in the database.

User and session authentication and cookies

mysqlUserFolder does authentication (and all other functions) using data in
MySQL tables. For user authentication it supports both cookies and http
authentication. When in cookie mode, it will use also use valid session for
authentication. MySQL User Folder maintains its own sessions and they have
nothing in common with Zope sessions.

Cookie authentication is implemented using random tokens. Each token has two
parameters: time to live and timeout. First one represents absolute period
when token is valid and second represents interval in which token must be
accessed in order to stay valid. Parameters for user and cookie tokens can be
managed through management screen.

Each token is presented by two cookies: one keeping id of the token (id of
the session or user) and other keeping random value for validation. So for
user and session tracking four cookies are needed. Both session and cookie
cookies can be set to be persistent (browser will save the cookie after exit)
or not.

By default cookie names don't depend on realm, so user folders will overwrite
cookies from other user folders. This effectively means that you can't use
different realms under same domain name, since browser will not store cookies
for both user folders. This can be changed by setting option
"Use realm for cookie names". If this option is set, cookie names will contain
realm. In that case all realm names must contain only characters that are valid
in cookie names.

Session tracking is only implemented using cookie tokens (this type of token
should have timeout set). When user accesses a resource protected by mysql
user folder and he doesn't have valid session cookie, new one will be created.
Session is represented by mysqlSession object. It can be accessed through
REQUEST object as REQUEST ['Session'].

If user folder uses session authentication, when user is first authenticated,
his database user id (not username !) is assigned to his session. Later on,
while session cookie is valid, user is authenticated only using session
cookie. When session becomes invalid (timeouts or global limit is reached),
mysql user folder will again try to use cookie for user authentication.

When users are authenticated using HTTP auth method, session is not used for
user authentication.

mysqlUserFolder has a parameter that specifies a list of ports. If this is set
and request comes on one of these ports mysqlUserFolder will always use
username/password authentication regardless of the cookie settings. This allows
FTP to work (FTP server listens on different port) even if user folder
uses cookies.

MySQL database

mysqlUserFolder expects all tables to be found in the database that is a
parameter to mysqlUserFolder. Other connection parameters can be specified
through management interface.

Realm is used to allow keeping users from different user folders
in the same table. PasswordType specifies password type in the Password
field. Currently only 0 (clear text password)i and 1 (UNIX crypt) is used.

Sessions, Tokens and Users are referenced using MySQL autoincrement id (not
username/realm pair).

Token table is used for session/user authentication. Records keep a random
string that client must send in a form of a cookie in order to have valid
session or to be automatically authenticated as valid user. Class field
determines what Id represents: if Class is 'session', Id represents Id of a
session. If Class is 'user', Id represents Id of a user.
Primary key for Token table is (Id, Class).

This is important to understand in order to keep referential integrity. If
you delete session record, you must delete a Tokens record that has same Id
and Class = 'session'. If you delete a user, you must delete all
corresponding sessions and their tokens, as well as Token that has Id =
user's id and Class = 'User'. If you delete a user from mysqlUserFolder's
management screen, this is done automatically.

Session and Token tables are growing and need to be cleaned periodically.


mysqlUserFolder will cache session and user data in order to avoid repeated
sql lookups. However, user folder is designed not to store any runtime
persistent data (in ZODB), so some data cannot safely be cached because of
multithreading/ZEO (non persistent data are private for threads).

There are six variables that control caching: CACHE_SESSIONS,
CACHE_TOKENS_LIFE. Life parameters specify for how long entries are going to
be kept in cache.

If CACHE_SESSIONS is enabled, only sessions having authenticated user are
cached (these are used for authentication too if user folder uses cookie
auth). If you delete sessions from sessions table, session might be still
cached for LIFE period.

if CACHE_USERS is enabled, usernames, user database ids and passwords are
going to be cached. mysqlUserFolder will always refresh user info if password
is wrong.

CACHE_TOKENS cache is used for caching tokens that are stored in cookies.
They are used both for session and user cookies (if mysqlUserFolder is
configured to use them both). There are two parameters for tokens, timeout
and life. Accessing a cached token does not update access time. If caching is
used, thread is going to update access time only when it reads a token from
database. That's why TOKENS_CACHE_LIFE should be much smaller than
TOKEN_TIMEOUT parameters.

So the consequences of are caching are:

- User/Session token might expire before it's timeout time (but difference
can't be greater then TOKENS_CACHE_LIFE seconds.

- When user/session token is deleted from the database, it might still be
cached for TOKENS_CACHE_LIFE period.

- When password is changed, old password might be used for CACHE_USERS_LIFE
period. New password will always work.

- When user is deleted from SQL database, it might still be cached for
CACHE_USERS_LIFE period (also for sessions).

- All sorts of weird things might happen if you delete a user and create
another one with the same Id.

Persistence, threads and table locks

All objects used by mysqlUserFolder are not persistent (in ZODB sense) so
there are no needs for python locks. When data are changed/accesses from
multiple MySQL tables, MySQL locks are used (single queries are atomic).

It is quite possible that there are deadlocks, especially in case of SQL
errors (Zope thread will keep it's lock on the database). But errors should
happen only if connection to the SQL server is lost in the middle of the
operation. And since if execute query command fails, mysqlUserFolder will
automatically reconnect before next query, only few requests should be

Also, in the case of the lost connection, mysqlUserFolder will try to
reconnect and reexecute the current query. So, it is safe to restart the mysql
server, or to set connection timout in the mysql server.

Connection objects are stored per thread (this is a leftover from the time
there were threading issues). This means there can be many open connections to
the mysql server. It this is a problem, connection timeout can be activated
inside the mysql server.

User and Session MiscData

mysqlUserFolder allows keeping custom information connected to users and
sessions. That data is kept in SQL table. User and session objects
have methods:

setMiscData (string_key, (int_value, string_value))
(int_v, string_v) = getMiscData (string_key)

As an example, u_UserPage.dtml keeps "visited" count for each user and each
session. Important thing is that these methods don't exist in the ordinary
user object that is returned by Zope's user folder. Method u_UserPage.dtml
checks if user object is mysqlUser.

User Interface

mysqlUserFolder object has two sets of functions that allow user (and folder)
management: manage_* functions and user_* functions. Manage functions allow
manager to change parameters of the user folder and to manage users. They are
protected with "Manage" permissions.

User functions are: user_login (), user_logout (), user_change_other_data (),
user_change_password (), user_create ().

First two are accessible to everyone, but they will raise an error if
mysqlUserFolder is not configured to use cookie auth.

Two user_change_* methods allow users to change their data. Method
user_create () allows anonymous creation of new users. They are protected by
'Edit MySQL User Data'. Also, method user_create () will raise
an error if anonymous user creation is not allowed in MySQL user folder

Methods user_change_other_data () and user_change_password () will only change
information about the current user (as specified in REQUEST).

MySQL User Folder also contains 6 DTML methods that are user interface to
to folder functions:

- docLogin. This represents a login page if cookie authentication is used.
User Folder will display (by raising exception) this method if user
must be authenticated.

- docUserPage. Default user page. User will be redirected here after login
if no other path is specified.

- docNewUser. Example form for creating new users (anonymously).

- actChangeOther. Method that calls user_change_other () to change user's

- actChangePassword. Method that calls user_change_password () to change
user's password.

- actCreateUser. Method that calls user_create () to create new user.

Note about docLogin: This method can be shown both as a normal page (in case
of failed logins, or if user goes directly there) and inside exception
template. If user accesses protected resource mysqlUserFolder will raise
an exception with docLogin as value. Zope will then call
standard_error_message and this method will (by default) display docLogin,
but it will also display HTML header/footer. mysqlUserFolder will pass a
parameter "exception_raised" to docLogin if it raised an exception so this
method can ensure proper interaction with standard_error_message.

Since act* methods call user_* methods they need to "have"
"Edit MySQL User Data". By default, they don't have it and this will
not work. In order to allow ordinary users to perform these operations one
of the following must be done:

a) Grant this permission to 'Anonymous'. This is not so safe since users will
be able to call user_* methods directly bypassing all checks in act* DTML

b) Use of proxy role for act methods (highly recommended).
This is more safe, since users will not be able to call user_* methods, only
act* DTML methods where proper checks can be placed. The procedure for setting
proxy roles is the following:

1) Create a special role (for example 'sysScriptRole'). Assign this role
to the current manager user (restart Zope so cache is cleared).

2) Create a special user ('sysScriptOwner'). Assign the previous role to
this user. User should have non guessable password (random).

3) Enter this data in "Setup user action methods" form (in "Advanced" tab).
This will change the owner of all act* methods and it will assign them
created role as a proxy role. Also, it will assign permission
'Edit MySQL User Data' to the specified role.

Note that in both cases user will be able only to change its own data. However
if a) is used, user will be able to bypass all checks inside act* methods.

It is also very important to add proper input checking inside act* methods.
All DTML methods inside MySQL user folder are given just as examples, they must
be changed for the specific usage if ordinary users are going to have access
to them.

Another security setting that can be changed is the access to the docUserPage
method. "View" permission of the docUserPage method should be granted only to
role "mysqlRole". In this way, Zope will force the authentication if user goes
directly to the user page. Method docUserPage requires that REQUEST object
contains mysqlUser object, so user must be authenticated.

VALIDATE_ configuration parameters

VALIDATE_ parameters (in can alter authentication process.

If VALIDATE_ALWAYS_SUPER is set to 1, mysqlUserFolder will always authenticate
Super user. In order for this to work, you might need to create Zope
emergency user. This authentication will work even if there is no connection
to the MySQL server.

If VALIDATE_ALWAYS_ANONYMOUS is set to 1, mysqlUserFolder will always
authenticate Anonymous user regardless of cookies or HTTP user/pass

List VALIDATE_IGNORE_ROLES can contain list of roles that are not going to be
visible to mysqlUserFolder even if they are specified in SQL tables.

Folder's configuration parameters

Basic user folder properties (like the mode of the operation and connection to
the database) are defined when folder is created and they are later accessible
through Properties tab.

Cookie and logging parameters are managed through Parameters tab (in previous
versions these parameters were hard-coded in

Finally, caching parameters are still hard-coded in In the same file
it is possible to set VALIDATE_ control variables, to turn on debugging output
(DEBUG=1, DEBUG=2) and to define fields of the REQUEST variable that contain
remote IP address (for Zope versions prior to 2.7).

Source code

Initial version of mysqlUserFolder was based on etcUserFolder product.

Source code is created using tabs for alignment, and tab value was 4 spaces.
Since python sees tabs as 8 spaces, care needs to be taken when changing
source code. For python, one alignment level in is 8

File contains different configuration options.

Directory dtml/ contains "manage" dtml files.
Directory dtml.user/ contains example "user" dtml scripts.

Directory sql/ contains sql script that generates database and tables.