Hi Alex,

Thanks for your responses.

[...]

>> Three key examples come to mind:
>>
>> . nbackup must use a locksmith for its alter database
>> access.

> This particular issue is already resolved in fb3. Use "grant
> alter database to thatUser" and let thatUser to use nbackup.

I had checked the release notes for FB3 but that particular
detail hasn't made it in yet. But it is good news. Thanks.

>> . monitoring connections other than their own requires
>> a locksmith

> This does require support except setting grants in database.

I was able to allow it to happen in FB v2.5.3 by changing just
one line in the snapshot code:
...
else if (rid == rel_mon_attachments)
{
if (fid == f_mon_att_user)
{
attachment_charset = ttype_none;
charset = ttype_metadata; // already in Unicode
att_allowed = locksmith || !userName.compare(source, length);
...

Setting that att_allowed variable to true lets it happen for
all users (not recommending, just saying) - which seems to
indicate that (in FB v2.5.3 at least) there are no other blocks
in the way. So an alternative check for something other than
locksmith and current user (like checking a role) would work
there. (Which makes it an easy hard-code fix for my own build.)

>> . user management

> Currently this does require some support except setting
> appropriate access rights, but plugin can be easily rewritten
> in order to support poor SQL setup.

It did occur to me that FB3 changes to security may make this
part of my post potentially redundant.

[...]

>> 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.)

> If one can manage users that means he can change SYSDBA's
> password, yes?

As I said, the user management stuff was likely to be more
complicated. I would imagine that SYSDBA would be excluded,
and also the database owner (though if an installation has many
database owners that restriction isn't going to be effective).
Also that the user/role given this ability would ALSO have
to have been granted appropriate WITH GRANT/ADMIN OPTION in the
various databases for it to be permitted to give other users
access. There would be no point in doing it if the capability
enabled the user to make themselves locksmith equivalents.

Given how long we've lived without this, and the new features
of FB3, it's probably as well to ignore this particular item
on my list - at least until I've spent time with FB3.


[...]

>> 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.
>>

> Such "dummy" objects are referred as capabilities in unix OS
> family. One can read more with 'man capabilities'.
> I plan to implement permissions raising using something
> similar to unix capabilities in firebird.

That sounds good. It might offer a more flexible way to manage
the various advanced features being offered by FB these days.
It would be nice to see something like this as a better
solution to widespread "if (locksmith)" checks in the code.

Since the only item off my list still outstanding for FB3 is
delegation of monitoring, maybe you might think about how that
could fit in with your plans (if you haven't already).


I would have added DDL permissions to my list except for the
new db trigger features in FB3. While the triggers offer a
great deal of flexibility, some form of permissions feature
(via role or capabilities) would have been neater (and more
obvious) for most common requirements.


Thanks again.

--
Geoff Worboys
Telesis Computing Pty Ltd