Hi all,

I don't watch this list consistently enough to be sure this
hasn't been discussed in the past - I can't find a tracker
or other reference - but do tell me if I'm covering old ground.


There is a great reliance in Firebird on the "locksmith"
(SYSDBA or database owner or rdb$admin), and no way is given
to delegate some of these tasks to alternative identities or
roles WITHOUT also giving them full access.

Three key examples come to mind:

. nbackup must use a locksmith for its alter database
access.

. monitoring connections other than their own requires
a locksmith

. user management

Are all users/applications that might want access to these
features also users that you're happy to give unlimited
modification access to on the database?

This isn't about controlling read-access to the database data,
since obviously these features have a pretty wide impact. It's
about managing those with access to modify with the production
system as part of their day-to-day work.

The problem with people having super user access is that they
will use it more often than necessary, and this opens a system
to accidents that could have been avoided if specific tasks
were limited to have only the access they needed. We need a
way to ensure that locksmith is not required for regular
end-user activity.


Inline with the existing RDB$ADMIN role, my theory is that we
should have roles for specific tasks to allow delegation of
just those tasks. Of course the current limitations of roles
makes it less than perfect - since they can't be cumulative.
But for the three features noted above it would probably be an
acceptable situation anyway - since the first two at least are
best used from independent connections, anyway.

If something like this:
http://tracker.firebirdsql.org/browse/CORE-1815
was ever implemented then it could become ideal.


RDB$BACKUP would be available for delegating backup access
(and automatically becomes the login role for users connecting
through the existing backup tools).

RDB$MONITOR would be available for delegating monitoring
of all user sessions. I'm uncertain whether there should be
separate monitor-only (see what's happening) and monitor-full
(can delete connections/statements) roles.

RDB$USERMANAGER would be available for adding and removing
users and assigning roles other than RDB$ADMIN. (Okay, so this
one is possibly a more complex subject - although, a more
limited RDB$USERMANAGER is still better than giving RDB$ADMIN
for such a regular task.)


But I do have some reservations about my theory ...

Firstly, it seems likely that something as simplistic as this
has already been discussed ... and discarded? If so, why?

Secondly, I've got my three pet issues noted above (because
they happen to be something I'm working on for my application
now), but there are probably other delegations that might be
requested. Those, and perhaps user management, might be
better solved in some other way.


I did have another thought, an alternative solution, but I'm
less sure of its practicality. Since roles aren't cumulative,
the same effect might be gained by having dummy objects
called DBT$BACKUP, DBT$MONITOR, DBT$USERMANAGER (DBT for
"DB Task" or similar) and the locksmith would then delegate
access to these task features by GRANT statements to other
roles. At connection, I imagine, the engine would check
these task objects and set flags indicating what tasks are
permitted.


Comments?

--
Geoff Worboys
Telesis Computing Pty Ltd