Firebird 5.0 users and permissions

[Nick Vaughan]

In a way this might be a follow-on from the topic " Looking for help migrating LibreOffice embedded HSQLDB to Firebird" as I'm trying to do the same thing. 

The environment is a single Windows 10 PC with a single Windows user (SALIS).

In my case I've set up a Firebird 5 environment. The plan is to create users, databases, tables and insert all the data through isql. After that to connect LO Base 26.02 using JDBC (although I'm looking at (https://help.libreoffice.org/latest/sq/text/sdatabase/dabawiz02firebird.html).

Using SYSDBA I can create a user with "GRANT ADMIN ROLE" ("CREATE USER fbbatch PASSWORD 'fbbatch' FIRSTNAME 'Run' LASTNAME 'Batch' GRANT ADMIN ROLE ACTIVE TAGS (Interactive = 'No', LibreOffice = 'Yes', isql = 'Yes');").

With this user I can create a database

CONNECT employee user 'fbbatch' password 'fbbatch';

Server version:

WI-V5.0.3.1683 Firebird 5.0

create database 'C:/Users/salis/Documents/DBs/TestDB/teste.fdb' page_size 8192 SET NAMES 'NONE' DEFAULT CHARACTER SET NONE; COMMIT;

SQL> show database;
Database: C:/Users/salis/Documents/DBs/TestDB/teste.fdb
        Owner: SALIS
PAGE_SIZE 8192
Number of DB pages allocated = 224
Number of DB pages used = 216
Number of DB pages free = 8
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 1
Transaction - oldest active = 2
Transaction - oldest snapshot = 2
Transaction - Next = 6
ODS = 13.1
Database not encrypted
Embedded connection
Creation date: Mar 9, 2026 12:34:38
Replica mode: NONE
Default Character set: NONE
Publication: Disabled

exit;

This database is already set up in database.conf as 

teste = C:/Users/salis/Documents/DBs/TestDB/teste.fdb

isql

CONNECT teste user 'fbbatch' password 'fbbatch';

When I try to create a table 

CREATE TABLE "ZZ_contact" ("ZZ_contact_id" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0 INCREMENT BY 1) PRIMARY KEY,  "contact_name" VARCHAR(50) DEFAULT 'Contact Name',  "contact_email" VARCHAR(50),  "contact_role" VARCHAR(50));

I get

Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-CREATE TABLE ZZ_contact failed
-No permission for CREATE TABLE operation

If I then try to confirm the permissions for fbbatch, using 

SELECT
    RDB$USER AS USER_NAME,
    RDB$PRIVILEGE AS PRIVILEGE,
    RDB$RELATION_NAME AS OBJECT_NAME,
    RDB$OBJECT_TYPE AS OBJECT_TYPE,
    RDB$GRANT_OPTION AS WITH_GRANT_OPTION,
    RDB$GRANTOR AS GRANTED_BY
FROM RDB$USER_PRIVILEGES
ORDER BY RDB$RELATION_NAME;

The only users I see are "PUBLIC" and "SALIS".

I can create the table using SYSDBA but would like to manage users and permissions more effectively.

Why might fbbatch be able to create databases and yet not tables in those databases?

How can I find out what permissions fbbatch has?

Thank you for any help that can be offered.

[Dimitry Sibiryakov]

Nick Vaughan wrote 09.03.2026 14:30:
> With this user I can create a database
> CONNECT employee user 'fbbatch' password 'fbbatch';

>

> Why might fbbatch be able to create databases and yet not tables in those databases?

`CONNECT` doesn't create databases, it attaches to existing database.

> SQL> show database;
> Database: C:/Users/salis/Documents/DBs/TestDB/teste.fdb
> Owner: SALIS

This database was created by `SALIS`, not `fbbatch`.

> How can I find out what permissions fbbatch has?

ISQL has command `SHOW GRANTS` that shows every permission granted in
database. You can filter result with grep.

--
WBR, SD.

[Mark Rotteveel]

On 09-03-2026 14:30, Nick Vaughan wrote:
> In a way this might be a follow-on from the topic " Looking for help
> migrating LibreOffice embedded HSQLDB to Firebird" as I'm trying to do
> the same thing.
> The environment is a single Windows 10 PC with a single Windows user
> (SALIS).
> In my case I've set up a Firebird 5 environment. The plan is to create
> users, databases, tables and insert all the data through isql. After
> that to connect LO Base 26.02 using JDBC (although I'm looking at
> (https://help.libreoffice.org/latest/sq/text/sdatabase/
> dabawiz02firebird.html).
> Using SYSDBA I can create a user with "GRANT ADMIN ROLE" ("CREATE USER
> fbbatch PASSWORD 'fbbatch' FIRSTNAME 'Run' LASTNAME 'Batch' GRANT ADMIN
> ROLE ACTIVE TAGS (Interactive = 'No', LibreOffice = 'Yes', isql = 'Yes');").

Creating a user with GRANT ADMIN ROLE grants the user administrator
privileges in the security database.

You have not granted the administrator role in the desired database.

> With this user I can create a database
> CONNECT employee user 'fbbatch' password 'fbbatch';
> Server version:
> WI-V5.0.3.1683 Firebird 5.0
>
> create database 'C:/Users/salis/Documents/DBs/TestDB/teste.fdb'
> page_size 8192 SET NAMES 'NONE' DEFAULT CHARACTER SET NONE; COMMIT;

You don't need to be connected to a database to execute CREATE DATABASE.
In fact, doing so will just close the current connection and then run
CREATE DATABASE.

Given you haven't specified a username, nor a hostname, you're executing
this with embedded connection, using your OS username as the username.
Embedded connections do not perform authentication.

They have no permissions at all, as
1) The database was created with your OS username (presumably SALIS), so
fbbatch is not the owner
2) You have only granted fbbatch administrator privileges (role
RDB$ADMIN) in the security database, not in that new database

Either you need to ensure that database is created by the user fbbatch,
by specifying the USER clause of CREATE DATABASE, so it becomes the
owner, or you need to explicitly grant the RDB$ADMIN role in that
specific database.

Also, unless the role is granted as a default role, you must specify the
role explicitly when connecting (ROLE clause of CONNECT, or equivalent
connection property when using other means of connecting) for to user to
be able to use the privileges of a role.

Mark

--
Mark Rotteveel

[Nick Vaughan]

I had thought that I had replied to this but cannot see that response.

Thank you for all the comments. It looks as though I have options to

1, Use a username and password when creating a database

2, GRANT the RBS$ADMIN role to the created database

3, Specify a suitable role in the CONNECT statement.

Using SHOW GRANTS may help future investigations.

Thanks again.

[Mark Rotteveel]

On 09-03-2026 23:58, Nick Vaughan wrote:
> I had thought that I had replied to this but cannot see that response.

That is because you replied privately to me instead of to the group.
Most of us are, BTW, subscribed by mail and reply by mail, instead of
using the Google Groups webinterface.

Mark
--
Mark Rotteveel