Issue Details (XML | Word | Printable)

Key: CORE-1815
Type: New Feature New Feature
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Roman Simakov
Reporter: Anderson Farias
Votes: 5
Watchers: 7

If you were logged in you would be able to see more operations.
Firebird Core

Ability to grant role to another role

Created: 01/Apr/08 11:50 AM   Updated: 11/Aug/16 07:33 AM
Component/s: None
Affects Version/s: None
Fix Version/s: 4.0 Alpha 1

Environment: All
Issue Links:

QA Status: Done with caveats
Test Details:
Wait for reply from Alex, letter 16-may-2016 19:37 (issue about missing warnings/errors when some user "Boss1" tries to grant privilege to user "Sale1", but this "Sale1" already has this privilege and it was granted to him by user "Boss2"; after this Boss1 can issue 'revoke ... from Sale1' and he will NOT get error but privilege silently remains alive).

11-aug-2016: committed intermediate version of test.

 Description  « Hide
Ability to grant role to another role so that it 'inherits' the former privileges.


create role base_role_1;
grant all on some_table to base_role_1;

create role base_role_2
grant execute on procedure some_proc to base_role_2;

create role master_role;
grant base_role_1 to master_role;
grant base_role_2 to master_role;
grant ... (especific privileges)

so that 'master_role' have the same privileges as 'base_role_1' and 'base_role_2'. AND if priveleges are granted to or revoked from it's assigned roles than it gets atomaticaly 'afected'

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
pabloj added a comment - 26/Feb/10 04:57 PM
It would be really nice to have this feature implemented

Roman Simakov added a comment - 12/May/16 04:43 PM
Read doc/sql.extensions/README.cumulative_roles.txt for description

Pavel Zotov added a comment - 08/Aug/16 06:31 AM
2 Alex, Roman: gentlemens, can you please clarify my issue about 'silent' rejection of REVOKING role if before this statement we have GRANT this role to user who was granted this role by another user (e.g., sysdba) ?

Look at this example (it's also can be found in my letter of 15-may-2016 16:14, subject: "cumulative_roles ... "):
    set echo on;

    create or alter user boss password '123';
    create or alter user acnt password '456';
    set term ^;
    execute block as
      begin execute statement 'drop role rboss'; when any do begin end end
    set term ;^

    create role rboss;

    grant rboss to boss with admin option;
    grant rboss to acnt;

    connect '/:e40' user boss password '123';

    --grant rboss to acnt; ------------------------------------------------------------ [ 1 ]

    revoke rboss from acnt; -------------------------------------------------------- [ 2 ]

    show grants;

    connect '/:e40' user sysdba password 'masterke';
    drop user boss;
    drop user acnt;
    drop role rboss;

When statement [1] is commented, statement [2] will raise:
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-REVOKE failed
-BOSS is not grantor of Role on RBOSS to ACNT.

But if we UNCOMMENT it, then:
1) role RBOSS still be granted to user ACNT - this is expected;
2) but both statements [1] and [2] will be "silently" ignored, neither error nor warning will raise.

Any comments ? It this behaviour expected and should be taken in account in the test for this ticket ?

Roman Simakov added a comment - 08/Aug/16 09:48 AM
Description of what heppens without conclusion for now.
1. SYSDBA grants rboss to acnt. RDB$USER_PRIVILEGES (later UP) has a record about it with grantor SYSDBA
2. If uncomment [1] user boss grants role rboss to acnt again and UP also store a record about it but with grantor BOSS. Note UP now has 2 records!
3. BOSS revokes rboss from acnt which is granted by HIM! UP now still has the only record but it has it!

In case of commented [1] all works correctly.

Pavel Zotov added a comment - 08/Aug/16 11:38 AM
> 2. If uncomment [1] user boss grants role rboss to acnt again and UP also store a record about it but with grantor BOSS. Note UP now has 2 records!

IMHO, some kind of warning is reqeuired here!
The same object (role in our case) is granted twice to the same user. The fact that this lead to appending ONE MORE record in rdb$user_privileges was hardly to imagine (at least for me...).
Consequence of this ("silent revoking" but role still will be granted to this user because there is another grantor) - is one more thing that need to be warned.

Roman Simakov added a comment - 08/Aug/16 02:42 PM
I cannot understand SQL Standard requirements. Regarding ADMIN OPTION it's obvious. If A grant R to U and B grant R to U with admin option it's must be two records about it.
But REVOKE in SQL Stanrard has GRANTED BY clause which may have WHICH grant you wants to revoke!

I see following options:
1) Leave it as is but every grantor need to revoke his grant!
2) Prohibit second grant like Pavel have suggested. Revoke will fail in this case. Maybe to implement GRANTED BY clause.

Roman Simakov added a comment - 09/Aug/16 09:52 AM
I've checked ORACLE and it looks like it has no GRANTOR at all! [1] pass silent and do nothing and [2] revokes rboss completely.
Moreover when it was granted I could see:
SQL> select * from dba_role_privs where grantee='ACNT';

------------------------------ ------------------------------ --- ---

As you can see to there is no GRANTOR field.

Dmitry Yemanov added a comment - 09/Aug/16 05:22 PM - edited
After taking a careful look at the SQL spec, I see that FB has a few mismatches:

- it's impossible to grant role to PUBLIC (allowed by the standard)
- only users can be grantors (roles can also be grantors in the standard: GRANTED BY {CURRENT_USER | CURRENT_ROLE})
- GRANTED BY clause is implemented differently (the standard does not allow arbitrary user names there)
- <drop behavior> is not supported (the standard declares [CASCADE | RESTRICT])

The standard declares that REVOKE destroys only privileges granted by CURRENT_USER or CURRENT_ROLE, it does not touch privileges granted by others. This means that storing multiple descriptors (records in UP) is correct. So AFAIU the Pavel's test works almost correctly. By "almost" I mean that with the commented line [1] the error should either be different (e.g. "nothing to revoke") or not being raised at all (acting as silent no-op).

Grantors are also needed for supporting the CASCADE option: in this case the entire graph is destroyed, e.g. if R is granted to U2 by U1 WITH ADMIN OPTION and then R is granted to U3 by U2 and then U1 executes REVOKE ROLE R FROM USER U2 CASCADE, this means that both U1->U2 and U2->U3 descriptors are removed.

Dmitry Yemanov added a comment - 09/Aug/16 06:27 PM
Or maybe a better error message would be something like "no match found for role RBOSS granted to ACNT by BOSS".