Can not DROP two users via ES if they have same name (with diff. plugins) and one of them has been granted with admin role: ISQL hangs, unable to stop FB service after this

[pavel-zotov]

Consider following script:

set bail on;
set list on;
shell del c:\temp\tmp4test.fdb 2>nul;
create database 'localhost:c:\temp\tmp4test.fdb';

create or alter user boss password '123' using plugin Srp 
grant admin role -- <<<<<<<<<<<<<<<<<<<<<  ::: NB ::: <<<<<<<<
;
create or alter user boss password '456' using plugin Legacy_UserManager;

commit;
set transaction no wait;

set term ^;
execute block as
    declare stt varchar(4096) character set utf8;
    declare usr_name varchar(64) character set utf8;
    declare sec_plugin varchar(64) character set utf8;

    declare c_users cursor for
    (
        select '"' || trim(s.sec$user_name) || '"' as sec$user_name, s.sec$plugin
        from sec$users s
        where upper(s.sec$user_name) <> 'SYSDBA'
    );
begin

  open c_users; ----------  d r o p   u s e r s   e x c e p t   S Y S D B A  ----
  while (1=1) do
  begin
    fetch c_users into usr_name, sec_plugin;
    if (row_count = 0) then leave;

    begin
        -- Privileges for GRANT / DROP database remain even when user is droppped.
        -- We have to use REVOKE ALL ON ALL in order to cleanup them:
        stt = 'revoke all on all from '|| usr_name;
        execute statement (:stt);
        when any do
        begin
           --- suppress warning ---
        end
    end

    stt = 'drop user '|| usr_name || ' using plugin ' || sec_plugin;
    execute statement (:stt);
  end
  close c_users; -- c_local_mapping;

end
^
set term ;^
commit;

Note that to reproduce problem:

• clause "grant admin role" must present in the first 'CREATE USER' statement;
• second user (which is created via Legacy plugin) must have the same name as first one: 'BOSS'.
• DROP USER must be done using EXECUTE STATEMENT. There is no problem if DSQL is used.

On WI-V3.0.8.33474, WI-V4.0.1.2517 and WI-T5.0.0.79 it will hang: 1) ISQL does not return to OS prompt. Trace shows that last executed statement was:

2021-06-18T11:36:24.6350 (3620:0000000006DE3DC0) PREPARE_STATEMENT
        C:\FB\50SS\SECURITY5.FDB (ATT_13, SYSDBA:NONE, NONE, <internal>)
                (TRA_41, CONCURRENCY | WAIT | READ_WRITE)

-------------------------------------------------------------------------------
REVOKE RDB$ADMIN FROM "BOSS"
      0 ms

2021-06-18T11:36:24.6350 (3620:0000000006DE3DC0) EXECUTE_STATEMENT_START
        C:\FB\50SS\SECURITY5.FDB (ATT_13, SYSDBA:NONE, NONE, <internal>)
                (TRA_41, CONCURRENCY | WAIT | READ_WRITE)

-------------------------------------------------------------------------------
REVOKE RDB$ADMIN FROM "BOSS"

2021-06-18T11:36:34.1440 (3620:0000000006DE0040) ROLLBACK_TRANSACTION
        C:\FB\50SS\SECURITY5.FDB (ATT_7, SYSDBA:NONE, NONE, <internal>)
                (TRA_28, READ_COMMITTED | REC_VERSION | WAIT | READ_ONLY)
      0 ms

2) if we attempt to stop FB service then FB will load one of CPUs up to 100% and service will not be stoppped (one need to kill it using ProcessExplorer or taskkill /f /t).

For 5.0.0.79 I've created dumps, stack traces, lock-print results and other info when: 1) ISQL hanging but before trying to stop FB service; 2) after trying to stop FB service and got 100% CPU load.

All these files (together with FB snapshot) can be found here :

https://drive.google.com/drive/folders/16CtBVSsjEBaeZD_DKD6IJ5CgmSkNZs7Z?usp=sharing

Stack traces are attached here directly in order to preserve them in case of external link to google drive will gone (prefix "point-1" -- before trying to stop FB service; "point-2" - after this try):

point1_-_firebird.created_210618_113549.PID_3620.stack_trace.113728.txt.zip point1_-_isql.created_210618_113624.PID_5692.stack_trace.113840.txt.zip point2_-_firebird.created_210618_113549.PID_3620.stack_trace.114112.txt.zip point2_-_isql.created_210618_113624.PID_5692.stack_trace.114231.txt.zip

PS.

Used firebird.conf:

BugCheckAbort = 1
RemoteServicePort = 3500
FileSystemCacheThreshold = 65536K
IpcName = xnet_fb50ss
RemotePipeName = wnet_fb50ss
UDFAccess = Restrict UDF
ServerMode = Super
DefaultDbCachePages = 5000
TempBlockSize = 2M
TempCacheLimit = 2147483647
LockHashSlots = 22111
LockMemSize = 5M
ExtConnPoolSize = 10
ExtConnPoolLifeTime = 10
ReadConsistency = 0
TempDirectories = d:\temp
MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1
AuthClient = Srp, Srp256, ExtAuth, Win_Sspi, Legacy_Auth
AuthServer = Srp, Srp256, ExtAuth, Win_Sspi, Legacy_Auth
UserManager = Srp, Legacy_UserManager
ExternalFileAccess = Full
WireCrypt = Enabled
KeyHolderPlugin = KeyHolder
WireCryptPlugin = ChaCha, Arc4

[pavel-zotov]

PS Script can be simplified: one may to comment out block that tries to revoke all on all, i.e.: / begin -- Privileges for GRANT / DROP database remain even when user is droppped. -- We have to use REVOKE ALL ON ALL in order to cleanup them: stt = 'revoke all on all from '|| usr_name; execute statement (:stt); when any do begin --- suppress warning --- end end /

[AlexPeshkoff]

Execute statement is not important here - the only important thing is that 2 users with admin rights are dropped in single transaction:

set bail on; set list on; shell rm -f /mnt/db/tmp4test.fdb; create database 'localhost:/mnt/db/tmp4test.fdb' user 'sysdba' password 'masterkey';

create or alter user boss password '123' using plugin Srp grant admin role -- <<<<<<<<<<<<<<<<<<<<< ::: NB ::: <<<<<<<< ; create or alter user boss password '456' using plugin Legacy_UserManager;

commit; set transaction no wait;

SET AUTO off;

drop user boss using plugin Srp; drop user boss using plugin Legacy_UserManager;

commit;