Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Ability to grant role to another role [CORE1815] #2245

Closed
firebird-automations opened this issue Apr 1, 2008 · 18 comments
Closed

Ability to grant role to another role [CORE1815] #2245

firebird-automations opened this issue Apr 1, 2008 · 18 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Anderson Farias (afarias)

Replaces CORE751

Votes: 5

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

Eg.

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'

Commits: 238fff3

====== 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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: pabloj (pabloj)

It would be really nice to have this feature implemented

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ]

Fix Version: 3.0 Alpha 1 [ 10331 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 1 [ 10332 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @romansimakov

assignee: Alexander Peshkov [ alexpeshkoff ] => Roman Simakov [ roman-simakov ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

Read doc/sql.extensions/README.cumulative_roles.txt for description

@firebird-automations
Copy link
Collaborator Author

Modified by: @romansimakov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue replaces CORE751 [ CORE751 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Deferred

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).

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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';
commit;
set term ^;
execute block as
begin
  begin execute statement 'drop role rboss'; when any do begin end end
end^
set term ;^
commit;

create role rboss;
commit;

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

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

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

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

show grants;

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

===

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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> 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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

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';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
ACNT RBOSS NO YES

As you can see to there is no GRANTOR field.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Or maybe a better error message would be something like "no match found for role RBOSS granted to ACNT by BOSS".

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Deferred => 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). => 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants