Firebird is freezing when trying to manage users via triggers -------------------------------------------------------------
Key: CORE-6412 URL: http://tracker.firebirdsql.org/browse/CORE-6412 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 3.0.6 Environment: Windows 10 (x64) Version 2004 Firebird 3.0.6.33328 Firebird 3.0.7.33365 (snapshot) Reporter: Luciano Mendes 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 } -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel