On 10/19/14 18:09, Geoff Worboys geoff@...
[Firebird-Architect] wrote:

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

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

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

This does require support except setting grants in database.

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

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

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

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

It was just delayed till next version.

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

We need to have basic list of cases when specific access rights are needed.

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