How to create a database owned by an user other than SYSDBA?

Question

When I issue an isql command:

create database "localhost:testdb.fdb" user milenio password "secret";

I get the following error:

Statement failed, SQLSTATE = 28000
no permission for CREATE access to DATABASE testdb.fdb

what am I missing? How do I grant the necessary permissions to user milenio?

Answer 1

In Firebird 3 a user either needs to have (and be logged in with) the RDB$ADMIN role, or the user (or his current role) needs to have the CREATE DATABASE privilege to create a database.

See also the Firebird 3 Release Notes User Privileges for Metadata Changes:

GRANT CREATE DATABASE TO [USER | ROLE] <user-name> | <role-name>;

Or specifically, to grant this privilege to user milenio:

grant create database to user milenio;

followed by a commit.

This needs to be executed by SYSDBA or a user with the RDB$ADMIN role on the security database.