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

Reply via email to