Issue Details (XML | Word | Printable)

Key: CORE-5804
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Roman Simakov
Reporter: Roman Simakov
Votes: 0
Watchers: 1
Operations

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

Multiple error in REVOKE operator

Created: 23/Apr/18 09:23 AM   Updated: 24/Apr/18 08:39 PM
Component/s: None
Affects Version/s: 4.0 Alpha 1
Fix Version/s: 3.0.4, 4.0 Beta 1

File Attachments: 1. Text File revoke-bug.txt (8 kB)
2. Text File revoke-fixed.txt (8 kB)
3. File revoke-test.sql (5 kB)

Environment: All platforms

QA Status: Done successfully


 Description  « Hide
A logic of rvoking options, especially for field permissions is very complicated and wrong. Several examples:
================EXAMPLE 1====================
grant update(f1, f2) on table t to u with grant option;
commit;show grants;

/* Grant permissions for this database */
GRANT UPDATE (F1) ON T TO USER U WITH GRANT OPTION
GRANT UPDATE (F2) ON T TO USER U WITH GRANT OPTION

revoke grant option for update on table t from u;
commit;show grants;

/* Grant permissions for this database */
GRANT UPDATE (F2) ON T TO USER U

But should be:

/* Grant permissions for this database */
GRANT UPDATE (F1) ON T TO USER U
GRANT UPDATE (F2) ON T TO USER U

================EXAMPLE 2====================
grant update(f1, f2) on table t to u with grant option;
commit;show grants;

/* Grant permissions for this database */
GRANT UPDATE (F1) ON T TO USER U WITH GRANT OPTION
GRANT UPDATE (F2) ON T TO USER U WITH GRANT OPTION

revoke grant option for update(f1) on table t from u;
commit;show grants;

/* Grant permissions for this database */
GRANT UPDATE ON T TO USER U
GRANT UPDATE (F2) ON T TO USER U WITH GRANT OPTION

But should be

/* Grant permissions for this database */
GRANT UPDATE (F1) ON T TO USER U
GRANT UPDATE (F2) ON T TO USER U WITH GRANT OPTION

=====================EXAMPLE 3================
grant default r1 to role r2;
commit; show grants;

/* Grant permissions for this database */
GRANT DEFAULT R1 TO R2

revoke default r1 from role r2;-- revoke only default option
commit; show grants;

/* Grant permissions for this database */
GRANT DEFAULT R1 TO R2

But should be:

/* Grant permissions for this database */
GRANT R1 TO R2

ETC.

It's necessary to fix a logic and make a code more readable in this place.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Roman Simakov added a comment - 23/Apr/18 09:25 AM
revoke-test.sql is a test script
revoke-bug.txt - a result of test script before fix
revoke-fixed.txt - a result of test script after fix

Pavel Zotov added a comment - 24/Apr/18 03:57 PM
Please look in file "revoke-fixed.txt", lines 311...323:

===
grant default r1 to role r2 with admin option;
show grants;

/* Grant permissions for this database */
GRANT SELECT ON T TO USER U WITH GRANT OPTION
GRANT DEFAULT R1 TO R2 WITH ADMIN OPTION

grant default r1 to role r2;
show grants;

/* Grant permissions for this database */
GRANT SELECT ON T TO USER U WITH GRANT OPTION
GRANT DEFAULT R1 TO R2 WITH ADMIN OPTION -- <<<<<<<<<<<<<< [ ? ] Why "WITH ADMIN OPTION" is still here ?
===

We:
1) gave to role R2 ability to work with role R1 (+ set is as default) with ADMIN option;
2) gave to role R2 ability to work with role R1 (+ set is as default) but *WITHOUT* specifying admin option.

My question: should "WITH GRANT OPTION" still be displayed after action "2)" ? I thought (earlier) that any grant statement first implicitly revokes any other options that could present before it (i mean options that could be used with the same grant, of course)


Pavel Zotov added a comment - 24/Apr/18 05:05 PM
IMO, something like 'REVOKE ADMIN OPTION' will be useful for GRANT ROLE statement - just like we can do it in CREATE USER for explicit prohibition to use admin privileges:

SQL> create or alter user foo password '123' using plugin srp grant admin role;
SQL> commit;
SQL> set list on;
SQL> select * from sec$users where sec$user_name='FOO';

SEC$USER_NAME FOO
...
SEC$ADMIN <true>
SEC$DESCRIPTION <null>
SEC$PLUGIN Srp

SQL> commit;

SQL> create or alter user foo password '456' using plugin srp; ------ here we do NOT specify anything related to admin role
SQL> commit;
SQL> select * from sec$users where sec$user_name='FOO';

SEC$USER_NAME FOO
. . .
SEC$ADMIN <true> -- it remains the same; Ok, because we have ability to drop it (see next statement)
SEC$DESCRIPTION <null>
SEC$PLUGIN Srp



SQL> commit;
SQL> create or alter user foo password '789' using plugin srp revoke admin role;
SQL> commit;
SQL> select * from sec$users where sec$user_name='FOO';

SEC$USER_NAME FOO
. . .
SEC$ADMIN <false>
SEC$DESCRIPTION <null>
SEC$PLUGIN Srp

Roman Simakov added a comment - 24/Apr/18 05:06 PM
It's correct from my POV and I've discussed it with Alex. It's clear even in code implementation. All versions of Firebird check if this privilege even with more wide rights already granted and SKIP grant in such case. For example if a privilege on an object is already granted to a subject with grant option, granting the same privilege on the same object to the same subject WITHOUT GRANT OPTION just will be ignored. And I find it logical. There are no implicit revokes.

Roman Simakov added a comment - 24/Apr/18 05:18 PM
Granting privilege is not regular DDL operation. Maybe this job for some potential grant or alter operator:)

Roman Simakov added a comment - 24/Apr/18 05:22 PM
Also remember it's a legacy behaviour and changing it may break backward compatibility

Pavel Zotov added a comment - 24/Apr/18 05:58 PM
> just will be ignored. And I find it logical. There are no implicit revokes.

OK, thank you for explanation.