User who is allowed to manage other users must have this ability WITHOUT need to grant him RDB$ADMIN role (which is related to admin tasks in "main" database rather than in security_db) [CORE5770]

[firebird-automations]

Submitted by: @pavel-zotov

It will be good if SYSDBA may to grant other user (say, "foo") to manage other users but do this WITHOUT granting him rdb$admin role.
Currently it is impossible.

case-1:

set list on;
connect 'localhost:employee' user sysdba password 'masterkey';
create or alter user foo password '123' using plugin Srp grant admin role;
create or alter user bar password '456' inactive using plugin Srp;
commit;

connect 'localhost:employee' user foo password '123';
select current_user as who_am_i, current_role as whats_my_role from rdb$database;

WHO_AM_I FOO
WHATS_MY_ROLE NONE

commit;

alter user bar active using plugin Srp; ---------- this statement relates to security_db where 'foo' was granted to work as admin
Statement failed, SQLSTATE = 28000
modify record error
-no permission for UPDATE access to COLUMN PLG$SRP_VIEW.PLG$ACTIVE
commit;

case-2:

connect 'localhost:employee' user sysdba password 'masterkey';

create or alter user foo password '123' using plugin Srp grant admin role;
grant rdb$admin to foo;
create or alter user bar password '456' inactive using plugin Srp;
commit;

connect 'localhost:employee' user foo password '123' role 'RDB$ADMIN';
select current_user as who_am_i, current_role as whats_my_role from rdb$database;

WHO_AM_I FOO
WHATS_MY_ROLE RDB$ADMIN

commit;

alter user bar active using plugin Srp; -------------------------- [1]
commit;

connect 'localhost:employee' user sysdba password 'masterkey';
drop user foo using plugin Srp;
drop user bar using plugin Srp;
commit;

Statement [1] passed only when 'foo' has TWO roles:
1) admin in security_db and
2) rdb$admin in employee

But this statement (alter user ...) has not anything with 'employee' database.
Granting role 'rdb$admin' looks here as excessive and even dangerous: we allow to foo make admin tasks not only in security_db but also in the "main" database.

Commits: e6a89af

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

2do: check result of fb_run when issue related "GetThreadId could not be located in the dynamic link library KERNEL32.dll" will be fixed on Win XP (see letter to dimitr, hvlad 18.03.2018)
See also test for CORE2004

[firebird-automations]

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

[firebird-automations]

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

[firebird-automations]

Commented by: @AlexPeshkoff

After this improvement user with admin provileges in security.db need not pass explicitly rdb$admin role in order to manage users.

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done with caveats

Test Details: 2do: check result of fb_run when issue related "GetThreadId could not be located in the dynamic link library KERNEL32.dll" will be fixed on Win XP (see letter to dimitr, hvlad 18.03.2018)

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

Test Details: 2do: check result of fb_run when issue related "GetThreadId could not be located in the dynamic link library KERNEL32.dll" will be fixed on Win XP (see letter to dimitr, hvlad 18.03.2018) => 2do: check result of fb_run when issue related "GetThreadId could not be located in the dynamic link library KERNEL32.dll" will be fixed on Win XP (see letter to dimitr, hvlad 18.03.2018)
See also test for CORE2004