Description
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