Firebird - how to enumerate users

Question

Our company uses more applications that are working with Firebird databases. In some installations we are using database services installed on more machines. Now I am trying to centralize user management for all of them. For this purpose I am writing new application. What I need is to connect to selected Firebird service in localhost or in network and enumerate all users. Traditionally "gsec" utility is used for this purpose. "gsec" is part of Firebird installation. I could use command line output of this utility like this:

 user name                    uid   gid admin     full name
------------------------------------------------------------------------------------------------
SYSDBA                              0     0           Sql Server Administrator
TECHNICIAN                          0     0           Technician  Technician
TECHNIK                             0     0           Technik  Technik

but it seems cumbersome to me. Also the format of text output can change in next release of Firebird. So I would prefer to use directly Firebird api located in fbclient.dll library. Also "gsec" utility uses internally fbclient.dll as can be seen in ProcessExplorer utility from SysInternals. Unfortunately there is not any kind of documentation for this. All that I know is that I should include ibase.h header file to my project. This header file is very poorly documented. I have also downloaded source code of "gsec" utility but it is very chaotic. The project cant be compiled in Visual Studio 2008 because of missing import library for fbclient.dll. I have tried to generate import library using these instructions but failed.

So the question is: how to enumerate users defined in firebird database machine using client library fbclient.dll?

Answer 1

With Firebird 3, you can now query users using SQL. To query users you need to be SYSDBA or have the RDB$ADMIN role (otherwise you can only list your own account), using the SEC$USERS virtual table:

select SEC$USER_NAME, SEC$FIRST_NAME, SEC$MIDDLE_NAME, SEC$LAST_NAME, 
       SEC$ACTIVE, SEC$ADMIN, SEC$DESCRIPTION, SEC$PLUGIN 
from SEC$USERS

Earlier options to manage users, like using the services API (described below) or gsec are now considered deprecated (see SQL Features for Managing Access in the Firebird 3 release notes).

There is documentation for this API, see the API Guide that is part of the Interbase 6 documentation set that is downloadable from the Firebird website. Newer features are usually less thoroughly documented, but obtaining information on users (isc_info_svc_get_users) hasn't changed much in the last 15 years.

The retrieval of users specifically is documented on page 232 of the API Guide (+ some code that is shown on page 221):

char spb_buffer[6], *spb = spb_buffer;
char request_buffer[] = {
    isc_action_svc_display_user,
    isc_info_svc_get_users};
char result_buffer[1024], *p = result_buffer;
*spb++ = isc_info_svc_timeout;
ADD_SPB_NUMERIC(spb, 60); /* 1 minute timeout */

if (isc_service_query (
    status,
    &service_handle,
    NULL,
    spb - spb_buffer, spb_buffer,
    sizeof(request_buffer), request_buffer,
    sizeof(result_buffer), result_buffer))
{
    isc_print_status(status);
    isc_service_detach(status, &svc_handle);
    return;
}
do
{
    switch (*p++)
    {
    case isc_info_svc_get_users:
    {
        ISC_USHORT len, loop;
        ISC_ULONG id;
        char buffer[50], *buf = buffer;
        loop = (ISC_USHORT) isc_vax_integer (p, sizeof (ISC_USHORT));
        p += sizeof (ISC_USHORT);
        while (*p != isc_info_end)
        {
            switch (*p++)
            {
            case isc_spb_sec_username:
                len = (ISC_USHORT) isc_vax_integer(p, sizeof(ISC_USHORT));
                p += sizeof (ISC_USHORT);
                strncpy (buf, p, len);
                p += len;
                buffer[len] = 0;
                printf ("Username: %s\n", buffer);
                loop -= (len + sizeof(ISC_USHORT)+1);
                break;
            // Removed some information items for brevity
            case isc_spb_sec_groupid:
                id = isc_vax_integer (p, sizeof (ISC_ULONG));
                p += sizeof (ISC_ULONG);
                printf ("Group ID: %d\n", id);
                loop -= (len + sizeof(ISC_ULONG)+1);
                break;
            case isc_spb_sec_userid:
                id = isc_vax_integer (p, sizeof (ISC_ULONG));
                p += sizeof (ISC_ULONG);
                printf ("User ID: %d\n", id);
                loop -= (len + sizeof(ISC_ULONG)+1);
                break;
            default:
                *x = *p;
                break;
            } /* end switch */
        } /* end while */
        break;
    }
    // Other cases
}

Answer 2

You're not writing Firebird version. But you can use Services API (service_mgr) to get list of users etc. Check the isc_action_svc_display_user and isc_info_svc_get_users.