Request for feedback on proposal for role management

["Chris Travers" <..hidden..>]

Hi all;

As we get close to 1.2, I am revisting areas that I am considering for
1.3 and requesting feedback to make sure that they will really meet
the needs of the userbase.  I have done a lot of work on role
management in another program that I can move over if it works for
people.  The general framework is actually pretty flexible and would
only require some slight expansion to meet our needs (most notably to
handle functions).

In the abstract, the model works as follows:

Database objects are organized into entity groups.

Users/roles/groups are granted permission levels to these entity groups:
0 = no permission
1 = SELECT only
2 = SELECT, INSERT
3 = SELECT, INSERT, UPDATE
4 = SELECT, INSERT, UPDATE, DELETE

Insert-only permissions are rare enough I don;t think we need to
revise this model (one could put inserting functions into one group
and select functions into another for the time being).

Permissions are stored in a separate permission catalog table and when
changed are used to build database-level permissions.

The administrative interface can be used to assign permissions to db utilities.

Also at login, and when entering a flagged admin area of the
application, the applicatoin would do a secondary check to see if the
logged in user has general user permissions or admin privileges.

> From here we have a choice.  The simplest option would be to use db
native roles.  When the permissions change, a stored proc is called to
grant/revoke appropriate permissions on the role.  This would be a
security invoker function naturally.  A user could change his/her
password using the change_my_password('newpasswd') function which is a
security definer one which invokes ''ALTER USER '' ||SESSION_USER
appropriately.  This would mean we could have real permissions
implemented in the db level by 1.3.  In general, my experience is that
this tends to be pretty easy to debug and manage.  And it has the
advantage of putting a lot more emphasis on properly securing the
information on the back-end (i.e. not trusting the web app).

The other option is to generally deny most non-administrative users
access to the base tables and then implement views, stored procs, etc
which run a permission check against the permission catalog tables.
The big advantage is that this works well in hosted environments.
However, this actually becomes more complicated because authorization
information either has to be a) stored in a temporary table (which
makes connection pooling impossible-- this is far worse than SET
AUTHORIZATION) or the username and password have to be passed to every
function at every call.  As for connection pooling, I think this is
something we would do well to avoid in the core app for most purposes
and only consider it when we have a customer who really needs it.

Now, the remaining issue for db roles is session handling.  If we were
to go with db roles, the password would need to be retrieved from
somewhere on every page load, and ideally the session handler would
not be able to provide arbitrary lookups on this information.  The
easiest way to do this would be to cache the password in the session,
and only allow the global dbh to access that table through stored
proceedures:
1)  Set the session with appropriate information (hash, db login, etc)
2)  Retrieve the session by specifying the login name and hash
3)  Delete the hash by whatever criteria we need.  Thus the db login
info would only be available if you already have the cookie.  Note
however, that this would potentially cause an issue because database
superusers could get the db passwords (this would not be worse than
what we have now).

The main drawbacks to either system include:
1) If db entities are not categorized in the entity group tables,
users are never granted permissions and hence nobody can touch them.
Occasionally, this leads to debugging issues.  In general, I think I
can use the information schema as a bit of a safeguard, however.

2)  It adds a fair bit of work in terms of categorizing what we have.
Shouldn't be too bad though.

Any specific ideas?  Needs not met in this proposal?
Best Wishes,
Chris Travers