Roles granting/revoking logic (differs between 2.0 and 2.1) [CORE1869]

[firebird-automations]

Submitted by: Konstantin Dombrugov (abracadabra)

There is a difference between role granting/revoking between 2.0.4 and 2.1.0 wich is currently undocumented.
EPISODE ONE: grant/revoke

//login as sysdba
create role "role01";
grant "role01" to user01 with admin option
//login as user01
grant "role01" to user02
//login as sysdba
grant "role01" to user02 with admin option
//login as user01
revoke "role01" from user02

<last command works ok (removes record from RDB$USER_PRIVILEGES wich grants role01 to user02 by user01) for 2.0 but fails in 2.1 persisting record in RDB$USER_PRIVILEGES with message
*This operation is not defined for system tables.Unsuccessful metadata update.
USER01 is not grantor of <Unknown> on Role01 to USER02.* >

//and if in FB 2.1.0 sysdba execute
revoke "role01" from user02
//role01 will be unavailable to user02 (access granted by user01 will be removed too)

Please explain how it works or how it should realy work, because I cannot find description of such changes in release notes.

EPISODE TWO: admin option
//as sysdba
create role "role01";
grant "role01" to user01 with admin option;
//as user01
grant "role01" to user03 with admin option;
//as sysdba
grant "role01" to user02 with admin option;
//as user01
//this removes admin option from user02
grant "role01" to user02;
//as user03
grant "role01" to user02 with admin option;
//as user02
grant "role01" to public
<Last command fails with *This operation is not defined for system tables.Unsuccessful metadata update.
User USER02 has no grant admin option on SQL role Role01.*>

Should it work like that?

Commits: 5f0278b

[firebird-automations]

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

[firebird-automations]

Commented by: @AlexPeshkoff

No sure for the rest, but unability for SYSDBA to revoke something was definitely a bug in 2.0.

[firebird-automations]

Commented by: @AlexPeshkoff

To solve this problem I had to add new clause to GRANT and REVOKE commands - GRANTED BY. Only using it it's possible to avoid conflicts with roles (and other rigths) assignment when performed by many users.
This also means backporting is problematic - we do not add new features in old versions.

[firebird-automations]

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 Alpha 1 [ 10224 ]

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully