When converting a FB3 database to FB4 using backup + restore, the value assigned to field RDB$SYSTEM_PRIVILEGES from table RDB$ROLES seems to be uninitialized. That lead to a very serious security issue, when a user connected using an assigned role can access or modify data from a table where neither the user nor the role has rights.
Test system:
- Windows 11 22H2 x64
- Firebird-3.0.10.33601-0_x64.zip , extracted into C:\Firebird\3.0\
- Firebird-4.0.2.2816-0-x64.zip , extracted into C:\Firebird\4.0\
First, add user "GUEST" on both Firebird installs:
C:\Firebird\3.0>isql -user SYSDBA security3.fdb
Database: security3.fdb, User: SYSDBA
SQL> create user GUEST password '1234';
SQL> exit;
C:\Firebird\4.0>isql -user SYSDBA security4.fdb
Database: security4.fdb, User: SYSDBA
SQL> create user GUEST password '1234';
SQL> exit;
Create a small test database in FB3:
C:\Firebird\3.0>isql -user SYSDBA -q
SQL> create database 'C:\Firebird\Test3.fdb';
SQL> create table TABLE1(ID integer, INFO varchar(10));
SQL> insert into TABLE1(ID,INFO) values (1,'hello');
SQL> create role VISITORS;
SQL> grant VISITORS to GUEST;
SQL> commit;
SQL> exit;
Now connect to newly created database using user GUEST and role VISITORS.
Of course, the user has no privileges granted and cannot select from TABLE1.
C:\Firebird\3.0>isql -user GUEST -password '1234' -role VISITORS C:\Firebird\TEST3.FDB
Database: C:\Firebird\TEST3.FDB, User: GUEST, Role: VISITORS
SQL> select * from TABLE1;
Statement failed, SQLSTATE = 28000
no permission for SELECT access to TABLE TABLE1
SQL> exit;
Backup in FB3 then restore in FB4:
C:\Firebird\3.0>gbak -t -v -user SYSDBA C:\Firebird\TEST3.FDB C:\Firebird\TEST3.FBK
C:\Firebird\4.0>gbak -c -v -user SYSDBA C:\Firebird\TEST3.FBK C:\Firebird\TEST4.FDB
Connect to the restored FB4 database using user GUEST and role VISITORS.
Without any privileges, the user can select or modify data from TABLE1.
C:\Firebird\4.0>isql -user GUEST -password '1234' -role VISITORS C:\Firebird\TEST4.FDB
Database: C:\Firebird\TEST4.FDB, User: GUEST, Role: VISITORS
SQL> show grants TABLE1;
There is no privilege granted on table TABLE1 in this database
SQL> select * from TABLE1;
ID INFO
============ ==========
1 hello
SQL> update TABLE1 set INFO='world' where ID=1;
SQL> commit;
SQL> select * from TABLE1;
ID INFO
============ ==========
1 world
SQL> exit;
Now connect as SYSDBA to investigate (and fix)
C:\Firebird\4.0>isql -user SYSDBA C:\Firebird\TEST4.FDB
SQL> select RDB$ROLE_NAME,RDB$SYSTEM_PRIVILEGES from RDB$ROLES;
RDB$ROLE_NAME RDB$SYSTEM_PRIVILEGES
=============================================================== =====================
RDB$ADMIN FFFFFFFFFFFFFFFF
VISITORS 40226B0500000000
As you can see, the RDB$SYSTEM_PRIVILEGES for role VISITORS contain a strange value (40226B0500000000). The upper bits (>26) are not documented, so I don't know what effect they have. Check here for more info:
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref-appx04-roles
The fastest way to fix the issue is to drop system privileges for the role
SQL> alter role VISITORS drop system privileges;
SQL> commit;
After that, the issue seems to be fixed
C:\Firebird\4.0>isql -user GUEST -password '1234' -role VISITORS C:\Firebird\TEST4.FDB
Database: C:\Firebird\TEST4.FDB, User: GUEST, Role: VISITORS
SQL> select RDB$ROLE_NAME,RDB$SYSTEM_PRIVILEGES from RDB$ROLES;
RDB$ROLE_NAME RDB$SYSTEM_PRIVILEGES
=============================================================== =====================
RDB$ADMIN FFFFFFFFFFFFFFFF
VISITORS 0000000000000000
SQL> select * from TABLE1;
Statement failed, SQLSTATE = 28000
no permission for SELECT access to TABLE TABLE1
-Effective user is GUEST
When converting a FB3 database to FB4 using backup + restore, the value assigned to field RDB$SYSTEM_PRIVILEGES from table RDB$ROLES seems to be uninitialized. That lead to a very serious security issue, when a user connected using an assigned role can access or modify data from a table where neither the user nor the role has rights.
Test system:
First, add user "GUEST" on both Firebird installs:
Create a small test database in FB3:
Now connect to newly created database using user GUEST and role VISITORS.
Of course, the user has no privileges granted and cannot select from TABLE1.
Backup in FB3 then restore in FB4:
Connect to the restored FB4 database using user GUEST and role VISITORS.
Without any privileges, the user can select or modify data from TABLE1.
Now connect as SYSDBA to investigate (and fix)
As you can see, the RDB$SYSTEM_PRIVILEGES for role VISITORS contain a strange value (40226B0500000000). The upper bits (>26) are not documented, so I don't know what effect they have. Check here for more info:
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref-appx04-roles
The fastest way to fix the issue is to drop system privileges for the role
After that, the issue seems to be fixed