Re: [Firebird-devel] User semantics vs authentication

[Geoff W. <ge...@te...>]

Hi Jim,

In one email you said:
> I think I understand the motivation for separating user name
> and user indentification, but I don't think it's a good idea.

So you are not a believer in surrogate keys?  I know that not
all database developers use them, but I do.  The arguments for
and against surrogate keys are the arguments for and against
separating the user name from the user id - and I dont expect
we will resolve those arguments conclusively here.

If we do not use a surrogate identifier then we will need to
discover a way of keeping user names synchronised, or ensure
the API prevents changes to logon user names.  Anything else
provides the potential for holes in security.  For example:

  Senior administrator John Citizen with the user name
  JCITIZEN leaves the company, and eventually his user name
  is deleted from the security database.  With no sync of
  user details between databases all his privilegs remain
  defined.

  Sometime later his distant cousin Joe Citizen (from the
  dark side of the Citizen family) fakes his references
  and gets job with the same company.  He is added to the
  security database as JCITIZEN with supposedly minor
  privileges, only to discover he has inherited all the
  privileges of his trustworthy cousin.

  I leave you with the image of Joe rubbing his hands
  together with glee!


In a later email you said:
> Sean, what does the security plugin that emulates the current
> semantics do?  Do it replicate FB$USERS from the security
> database?  Does it try to intercept references to FB$USERS
> and redirect to the security database?  Replication, of
> course, is impossible for a readonly database.  The
> redirection, on the other hand, would be massively
> complicated to implement, requiring a very "rich" security
> plugin API.

If we truly intend to create good and flexible security
options for Firebird then I suspect that a "rich" API will
indeed be necessary.

We separate user authentication from the application databases
completely.  Databases may carry the users-as-objects table,
if we decide username to userid translation is necessary.  And
of course they carry the existing roles/privileges stuff.

It is up to the security API to have its own database of user
definitions (alongs with passwords mgmt etc).  Most of the
systems we are talking about already have their own databases
of one sort or another, so why would we try to carry a
duplication in the application database?

My preference would be that an application database that does
not use the default security.fdb would need to be "registered"
on the server to say what security system should be used to
authenticate access (and so avoid the possibility that users
from two separate security systems may duplicate a user name
or user id and so allow unintended access).  Such registration
may even specify an equivalent security.fdb style database,
just a separate one available on the same server (and so allow
different applications to have different sets of users if this
is desired).


None of this solves how to give access to read-only databases.
It seems to me that the security API may even need to be
rich enough to support its own registration of databases in
which user-to-role definitions can be added.  This implies
that such APIs (if they already have their own user database
for "normal" user authentication requirements) may need to
implement a new/separate database to support this requirement.

(Or perhaps the server can be built to provide this service
on behalf of the security plugins.  Such plugins would register
with the server and use the server to save/read these details
to the server database provided for this purpose.)

The result would be a recommendation that application databases
use roles as the primary way of specifying permissions, as this
allows access to be defined externally even if the database is
made read-only.  User name/id references inside an application
database would be primarily audittrail type references, not
permission references.

Another result is that we would not want username to userid
translation inside the application database.  We would (if we
use user-ids rather than user names) always store security
system user ids (and so such ids provided by the API would
have to fit with whatever field definition we decide on).
The alternative to this would be that the server security
service I spoke of above, would also provide the database user
object name to plugin user id translation - and perhaps this
indirection would be a good idea.


I dont think the final system would be quite as convoluted
as the above appears.  There are many reasons to want the
user-authentication kept separate, just as there are many
reasons to want the distinction between server properties and
database properties isolated more appropriately than they are
now.  Many server-specific things have crept into the database
over the years, its time they were moved back out to the server
where they belong.  (All IMHO of course :-)


-- 
Geoff Worboys
Telesis Computing