Submitted by: @luronumen

ACTUAL RESULT
- Firebird is freezing when trying to manage users via triggers

EXPECTED RESULT
- Firebird should NOT freeze when trying to manage users via triggers

STEPS TO REPRODUCE THE ISSUE
1- Make sure that the FirebirdSQL 3.0 service is NOT running:
net stop FirebirdServerDefaultInstance

2- Run the following command to create the TEST database:
ECHO SET SQL DIALECT 3;CREATE DATABASE 'TEST' PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1252; | "C:\Program Files\Firebird\Firebird_3_0\isql.exe" -user SYSDBA

3- Run the following command to create the SYSDBA user inside the TEST database using the SRP plugin:
ECHO CREATE OR ALTER USER SYSDBA SET PASSWORD 'masterkey' USING PLUGIN SRP;COMMIT; | "C:\Program Files\Firebird\Firebird_3_0\isql.exe" -user SYSDBA "TEST"

4- Restart the firebird 3.0 service:
net start FirebirdServerDefaultInstance

5- Create the following USERS table:

CREATE TABLE USERS
(
AUTHENTICATION VARCHAR(32) CHARACTER SET WIN1252 COLLATE WIN_PTBR
);
COMMIT;

6- Create the following triggers for USERS table for after Insert or Update or Delete:

SET TERM ^ ;
CREATE TRIGGER USERS_AI_AU_AD FOR USERS
ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0
AS
BEGIN
IF ((OLD.AUTHENTICATION IS NOT NULL) AND ((NEW.AUTHENTICATION IS NULL) OR (OLD.AUTHENTICATION<>NEW.AUTHENTICATION)))
THEN BEGIN
EXECUTE STATEMENT 'REVOKE RDB$ADMIN FROM "' || OLD.AUTHENTICATION || '" GRANTED BY "SYSDBA"';
EXECUTE STATEMENT 'DROP USER "' || OLD.AUTHENTICATION || '" USING PLUGIN SRP';
END
IF ((NEW.AUTHENTICATION IS NOT NULL) AND ((OLD.AUTHENTICATION IS NULL) OR (OLD.AUTHENTICATION<>NEW.AUTHENTICATION)))
THEN BEGIN
EXECUTE STATEMENT 'GRANT RDB$ADMIN TO "' || NEW.AUTHENTICATION || '" GRANTED BY "SYSDBA"';
EXECUTE STATEMENT 'CREATE OR ALTER USER "' || NEW.AUTHENTICATION || '" SET PASSWORD ''123456'' USING PLUGIN SRP GRANT ADMIN ROLE';
END
END^
SET TERM ; ^
COMMIT;

7- Run the following command add a register on USERS table (No issue so far):

INSERT INTO USERS (AUTHENTICATION) VALUES ('AAA');
COMMIT;

7- Try to run the following command to update the previous register (Firebird Freeze!):

UPDATE USERS SET AUTHENTICATION='BBB' WHERE AUTHENTICATION='AAA';
COMMIT;

IMPORTANT NOTES
- This issue is NOT reproducible if the security database is security3.fdb instead of the database itself (TEST);

ENVIRONMENT SETUP

firebird.conf

#⁠SETUP
UserManager = Srp, Legacy_UserManager
WireCrypt = Enabled

databases.conf

TEST = D:\APPLICATIONS\LURONUMEN\DB\TEST.FDB
{
AuthServer = Srp
SecurityDatabase = TEST
UserManager = Srp
}

Commits: e57f0c8 98e8418 1cecd8e 0cfdd3d

====== Test Details ======

Checked on 4.0.0.2249 - no hang, but if this test runs in loop, w/o pauses for at least ~4s, then starting from 2nd run following fail raises:
Statement failed, SQLSTATE = 08006
Error occurred during login, please check server firebird.log for details
Error occurred during login, please check server firebird.log for details
Content of firebird.log will be added with following lines:
Srp Server
connection shutdown
Database is shutdown.

Problem exists on SS and SC.
Classic is not affected.

Sent report to Alex et al, 09.11.2020.