firebirdisqlfirebird-3.0

Create user fails in Firebird 3 with isql-fb


I am trying to create a user in a firebird 3 database (on Fedora 27). When I connect to my database with isql-fb with the role RDB$ADMIN, I cannot create new users.

[user@host]$ isql-fb -z
ISQL Version: LI-V3.0.3.32900 Firebird 3.0
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect localhost:mydb user myuser password 'mypassword' role RDB$ADMIN;
Server version:
LI-V3.0.3.32900 Firebird 3.0
LI-V3.0.3.32900 Firebird 3.0/tcp (host)/P15:C
LI-V3.0.3.32900 Firebird 3.0/tcp (host)/P15:C
Database: localhost:mydb , User: MYUSER
SQL> create user NEWUSER password 'dasgfsdfdasf';
Statement failed, SQLSTATE = 28000
add record error
-no permission for INSERT access to TABLE PLG$SRP_VIEW

I'm confident that the user myuser has been granted the admin role. In fact I can see this if I connect to the security3.fdb database.

SQL> connect /home/firebird/secdb/security3.fdb user sysdba password 'myseeeecritpassword';
SQL> show grants;
/* Grant permissions for this database */
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON PLG$SRP TO VIEW PLG$SRP_VIEW
GRANT SELECT, UPDATE (PLG$ATTRIBUTES) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$COMMENT) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$FIRST) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$LAST) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$MIDDLE) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$SALT) ON PLG$SRP_VIEW TO PUBLIC
GRANT UPDATE (PLG$VERIFIER) ON PLG$SRP_VIEW TO PUBLIC
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON PLG$USERS TO VIEW PLG$VIEW_USERS
GRANT SELECT, UPDATE (PLG$FIRST_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$GID) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$GROUP_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$LAST_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$MIDDLE_NAME) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$PASSWD) ON PLG$VIEW_USERS TO PUBLIC
GRANT UPDATE (PLG$UID) ON PLG$VIEW_USERS TO PUBLIC
GRANT RDB$ADMIN TO SYSDBA
GRANT RDB$ADMIN TO MYUSER
GRANT CREATE DATABASE TO USER MYUSER

What do I need to do to allow the appropriate permissions to the MYUSER acount so I can create users?


Solution

  • Firebird has administrator roles on two levels:

    1. In a normal user database. This allows you to do everything inside that database (create objects, grant or revoke rights to users, etc)
    2. In the security database. This allows you to create or alter users and grant or revoke certain global privileges like 'create database', but also user administration (creating, altering or dropping users).

    Having the admin role in a normal database is not sufficient to create, alter or drop a user (unless that normal database is also used as a security database). You will also need to have the admin role in the security database to be able to create or alter users.

    To grant a user the admin role in the security database, a user with admin rights in the security database needs to execute the following statement (while connected using the RDB$ADMIN role):

    alter user MYUSER grant admin role
    

    or while creating the user

    create user MYUSER password 'somepassword' grant admin role
    

    A user with the admin role in the security database can only use that role when connecting to a normal database with the RDB$ADMIN role, and by necessity will need to have the RDB$ADMIN role in that normal database as well.

    Granting the RDB$ADMIN role in a normal database is done using

    grant role RDB$ADMIN to MYUSER
    

    This role applies to that database only.

    In Firebird 4 and later, this last requirement has been removed, and administrators in the security database no longer need to be administrator in the current database to be able to create, alter or delete user accounts.