Geoff Worboys wrote:

>I am following so far, but...
>
>
>
>>Real applications must connect to a database in order the
>>authenticate a client. There are privileges that belong to
>>the application, and different privileges that belong to the
>>client. An application should be able to control database
>>access based on what it knows of the client. This requires
>>multiple roles and the ability to activate/deactivate
>>specific roles.
>>
>>
>
>...now you have started to lose me.
>
>I understand that, especially with multi-tier, applications
>may have their own privilege controls and requirements. What
>I am not following is how you suggest this would be supported
>by roles (or whatever) in the database.
>
>
>

Let me explain how Netfrastructure applications use roles with a
diversion into row level access control. If you're only interested in
standards compliance, stop reading now. You can pick it up again in 5
years.

The Netfrasite application uses roles for access control, application
dispatch, and row level access control. Some roles are site wide and
other are "group" specific (a site "member" can be in any number of
groups). The site-wide wide roles are:

* Webmaster -- manages site wide content
* Designer -- manages site appearance
* Gatekeeper -- manages site membership and roles
* Wheel -- application instance pooh-bah
* Member -- client identified as a site member

The group specific roles are:

* Group_Member -- a client is a member of the current group
* Group_Post -- the member has post privileges
* Registrar -- manages group membership and privileges
* Editor -- manages group page
* Moderator -- approves content

Finally, there are a few infrastructure related roles:

* Public -- everyone
* Application -- privileges given to application code but not clients
* Admin -- magic powers, never delegated

Depending on credentials and context, a client can have any possible
combination of these roles.

A database logon account has an arbitrary set of roles associated with
it, some declared active, others latent. The general purpose logon
account used by web server module usually has the active role Public and
Application, and latent roles for everything else except admin. During
the processing of an application step, additional roles are activated as
the client is authenticated, his site privileges determined, and if the
context involves a specific group, the members group privileges. The
roles that can be activated is limited to those associated with the
account. The general purpose logon account, for example, can never get
the Admin role.

Security administration, both table and row level, is based on active
roles; a latent (or deactivated) role is ignored. Database access
during an application falls into two categories: application driven (the
application itself knows exactly what it's doing) and user driven (data
access based on client supplier information that could be forged). Most
table privileges are accorded by the Application role. When preparing a
user driven statement, however, the application temporarily drops the
role Application, so access control is based only on site and group roles.

On even a large site with hundreds of groups, it is likely that no two
site members will have exactly the same set of roles. The idea of a
logon account with a single associated role is a non-starter.

The second part of the puzzle is row level access control. There are
very few application that give client unrestricted read access to tables
within a database. Netfrasite controls access to most tables on a
per-row basis using "filtersets". A filterset is a named list of
<table-name, boolean-expression> pairs. An application can activate or
deactivate filtersets at will. When compiling any table reference, the
SQL compiler checks all active filtersets for references to that table.
The boolean expression supplied is "and"ed to the SQL boolean (the
algorithm is applied recursively, so any table referenced in a subquery
within a filterset boolean has any active filterset applied to it as
well). From the application's perspective, rows that fail a filter
expression don't exist (exception: primary an unique keys are still
enforced).

Netfrasite uses filtersets to control group visibility (a private group
is visible only to its members), moderation (a row under moderation is
visible only to its author and group moderators), accessibility (content
belonging to an invisible group is invisible), and role related (a row
can have specific access control). Some filtersets are generic -- used
for all users except admins -- while others are differentially activated
for site members and non-members. Here are parts of a few filtersets:

The "user_moderated" filter set is used to keep non-site members from
accessing content pending moderation:

upgrade filterset USER_MODERATED (
ARTICLES: pending='N',
EVENTS: pending='N',
PROTOTYPE: pending='N',
PAGES: pending='N',
PROCEDURES: pending='N',
PUBLIC_EVENTS: pending='N')

The analog for site members allows content pending moderation be visible
to its auther and group moderators:

upgrade filterset MEMBER_MODERATED (
ARTICLES t:
pending='N' or
author=connection.userId or
(pending <> 'N' and
exists (select * from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
and m.moderator='Y')),
EVENTS t:
pending='N' or
author=connection.userId or
(pending <> 'N' and
exists (select * from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
and m.moderator='Y')),

Group visibility for the public is controlled by:

upgrade filterset ORGPUBLICFILTERSET (
groups : type = 'Public')

For site members, however, group visiblity is controlled by:

upgrade filterset ORGMEMBERFILTERSET (
groups : (type='Public' or
type='Restricted' or
group_id in (select group_id from membership
where person_id = connection.userId))
)

The most complex of the filtersets controls general content visibility.
Most content tables contain a "mandatory_role" field controlling access
control. At first glance, it would seems that a simple check for role
active would suffice, but active roles only reflect a "current group".
To get around his, the filterset must brute force the role checking for
group roles as follows:

upgrade filterset ROLE_FILTERSET (
ARTICLES t :
case
when 'MEMBER' is active_role
then case mandatory_role
when 'REGISTRAR' then
select registrar from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
when 'MODERATOR' then
select moderator from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
when 'EDITOR' then
select editor from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
when 'GROUP_POST' then
select moderator from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
when 'GROUP_MEMBER' then
select 'Y' from membership m
where m.group_id=t.group_id
and m.person_id=connection.userId
else
case when mandatory_role is active_role then 'Y'
else 'N' end
end
else case when mandatory_role is active_role then 'Y' else 'N' end
end = 'Y'
,

After filtersets have been applied, even the simplest SQL select
statement translates into an incredibility hairy statement, but the
optimizer does its thing, and the runtime cost is negligible.

There is no doubt in my mind that client-server (aka two-tiered)
applications are dinosaurs that will soon go the way of the card punch.
Any and all three-tier applications (including all web applications)
require a more flexible security model than accorded by the SQL
statement as well as row level access control.

My long term attitude towards the SQL standard can be summed up as "no
arbitrary differences". Modern application requires a richer set of
semantics than offered by the SQL standard. In this particularly case,
a diversion is both justified and necessary.


[Non-text portions of this message have been removed]