Subject Re: [firebird-support] Delegating SYSDBA and enumerating users
Author
Post date 2017-02-21T23:36:06Z
Thanks for this Alan.

Unfortunately when connected under the ROLE RDB$ADMIN, this still results in an error as RDB$USERS is an unknown table. Same goes for replacing this with USERS. As stated in Helen Borrie's The Firebird Book (p.729):
"From Firebird 2.0 onward, the USER table is replaced by one named RDB$USERS and is
not accessible by any user, even SYSDBA. All access to RDB$USERS is done via a view
named USERS." 

In case you meant what many posts suggest using RDB$USER_PRIVILEGES, there are a similar number of responses which clearly state that doing is not the correct approach. If a user has no privileges, they won't appear in the list. Of course the additional USER fields (first/middle/last names) aren't accessible either.

Any other suggestions?


On Tuesday, February 21, 2017 1:32 PM, "'Alan McDonald' alan@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:


 
In a production environment using Firebird v2.5, we need to delegate authority of USER CRUD operations to more than one person without these admins sharing the SYSDBA user and password.
 
These admins have been created as users with ADMIN ROLE, and are logged in under the RDB$ADMIN ROLE (eg in Flame Robin or via the .NET Provider, or '-admin' switch in gsec). With this ROLE, it is possible to perform Creation, Update, and Deletion operations of CRUD via Flame Robin as well as gsec.
 
The roadblock, however, is not being able to list/enumerate the users (ie Read). In gsec when logged in as SYSDBA all users are displayed via the 'display' command, whereas using another RDB$ADMIN superuser only the logged in user is displayed. The latter is also the case when using the .NET Provider and making the call to FirebirdSql.Data.Services.FbSecurity.DisplayUsers(). Via Flame Robin menu Server | Manager Users, you are prompted with the Database Credentials dialog with Username pre-populated with 'SYSDBA' and read-only.
 
To delegate user management, it is necessary to have users other than SYSDBA to be able to list and enumerate them. This is required even simply to avoid name clashes.
 
Any suggestions would be greatly appreciated on how it is possible to do this, or if it isn't possible, potential alternative(s). Lastly, are there differences and/or does this problem disappear with Firebird v3.0?
 
My end goal here is implementation in Visual Studio C# using SQL through FirebirdSql.Data.FirebirdClient or directly through FirebirdSql.Data.Services, although solutions through other approaches would be welcome.
 
Thanks in advance,
David


CREATE OR ALTER VIEW USERS(
    USER_NAME,
    SYS_USER_NAME,
    GROUP_NAME,
   UID,
    GID,
    PASSWD,
    PRIVILEGE,
    COMMENT,
    FIRST_NAME,
    MIDDLE_NAME,
    LAST_NAME,
    FULL_NAME)
AS
SELECT RDB$USER_NAME, RDB$SYS_USER_NAME, RDB$GROUP_NAME, RDB$UID, RDB$GID, RDB$PASSWD,
                                RDB$PRIVILEGE, RDB$COMMENT, RDB$FIRST_NAME, RDB$MIDDLE_NAME, RDB$LAST_NAME,
                                COALESCE (RDB$first_name || _UNICODE_FSS X'20', '') ||
                                COALESCE (RDB$middle_name || _UNICODE_FSS X'20', '') ||
                                COALESCE (RDB$last_name, '')
                FROM RDB$USERS
/* to allow listing of all users for all users
    see also GRANT UPDATE ON USERS TO PUBLIC
                WHERE CURRENT_USER = 'SYSDBA'
                   OR CURRENT_USER = RDB$USERS.RDB$USER_NAME
*/
;
 
 
 
 
/******************************************************************************/
/****                              Privileges                              ****/
/******************************************************************************/
 
 
/* Privileges of users */
GRANT SELECT, INSERT, UPDATE(FIRST_NAME,GID,GROUP_NAME,LAST_NAME,MIDDLE_NAME,PASSWD,UID), DELETE ON USERS TO PUBLIC;
 
/* Privileges of roles */
GRANT ALL ON USERS TO RDB$ADMIN;
 
/* Privileges of views */
GRANT ALL ON RDB$USERS TO VIEW USERS;