                            mysqlUserFolder
                            ---------------

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. These
      informations are 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
      over the internet). Also, folder's management interface allows user
      management.

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

This version is tested with Zope 2.5.0. This product requires MySQLdb
python module (tested with version 0.9.1), and MySQL (tested with version
3.23.36). mysqlUserFolder does not use COMMIT/ROLLBACK.

There is a mailing list at: http://www.egroups.com/group/mysqlUserFolder
My email address is: vladap@criticalpublics.com


Security: important note
------------------------

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 initially does not support sessions (user folder needs to define
sessions), and it will try to authenticate every request which is denied to
Anonymous. 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 user 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 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
-------------------------------

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. 

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 are in
cfg.py (currently hard coded).

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 COOKIE_USE_REALM
variable in cfg.py. If this variable 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 users are authenticated using cookies, 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 on cookie settings. This allows
that FTP can 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) is used. Future releases will
probably add support for md5 hashed password.

Sessions, Tokens and Users are connected 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.


Caching
-------

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 four cfg.py variables that control caching: CACHE_SESSIONS,
CACHE_SESSIONS_LIFE, CACHE_USERS, CACHE_USERS_LIFE, CACHE_TOKENS,
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 do so). 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_LIFE
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 LIFE period.
	New password will always work.
	
- When user is deleted from SQL database, it might still be cached for LIFE
	period (also for sessions).

- All sort of weird things might happen if you delete a user and create
	another one with the same Id. Always use incrementing Ids (I'm not sure
	if MySQL autoincrement will always use increasing id, or it might use
	already deleted one)..


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.


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 has three user DTML methods: docLogin, docUserPage and
docNewUser (they are generated from u_xxx.dtml files) and three "action"
method. All these methods are created inside mysqlUserFolder and can be
edited through Zope interface. Permissions for these methods will not be set
automatically and that needs to be done manually - this is important for
docUserPage and action methods.

First user method is called when cookie authentication needs to ask
user for username and password. Second one acts as "user's service page"
(user can change it's password or other data), and third allows anonymous
user to create new account. These dtml files are given only as an example,
real work is done by user_xxx methods of the user folder. These documents
should be designed for specific system that is using mysqlUserFolder.

docUserPage requires that REQUEST object contains mysqlUser object, so user
must be authenticated. In order to achieve that if user goes immediately on
the user page (just enters: "http://<host>/<path>/acl_users/docUserPage")
there is one special role "mysqlRole" and all authenticated users have that
role (although it is not in SQL table). That role is needed for accessing
docUserPage and it's point is only that Zope activates authentication
process. Note that this should be set manually, since mysqlUserFolder will not
set that. This role is needed for View operation on docUserPage.

Creation of new accounts by unauthenticated users needs to be explicitly
allowed in the Properties screen of the user folder. Currently, user enters
it's new password, but it is trivial to change u_UserNew.dtml to randomly
generate new password and send it by email. Newly created user's role is a
parameter of the user folder. Currently only single role is supported.


Management interface
--------------------

Management interface allows changing user folder parameters and
creating/modifying/deleting users. Currently it is only possible to specify
one role and than role can't be changed (through management interface).
Of course, using sql client it is possible to assign more then one role to the
user or to delete and add user's roles. Also, it is not possible to
create/delete roles. That needs to be done using sql client.


VALIDATE_ configuration parameters
----------------------------------

VALIDATE_ parameters (in cfg.py) 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.

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

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.


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 mysqlUserFolder.py is 8
spaces.

File cfg.py 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.





