Incorrect removal of privileges [CORE5506]

[firebird-automations]

Submitted by: Akinshin Petr (sirius220)

If you assign a privilege to update a specific column, rather than the entire table, and then delete this column, then the entry for this column is not deleted from the RDB $ USER_PRIVILEGES table.
If you make a backup of the database, an error occurs when restoring this backup. You need to manually delete the privilege in the RDB $ USER_PRIVILEGES table and then everything works just fine.

[firebird-automations]

Commented by: @hvlad

Could you provide sample script to reproduce it ?

[firebird-automations]

Commented by: Akinshin Petr (sirius220)

It's strange. When creating a new database and trying to reproduce the situation, nothing happened. The error does not repeat. I will try to discover the dependency

[firebird-automations]

Commented by: @hvlad

Any news on this ?

[firebird-automations]

Commented by: Akinshin Petr (sirius220)

I did not deal with this issue, the topic can be closed

[firebird-automations]

Modified by: @hvlad

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

resolution: Cannot Reproduce [ 5 ]

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Commented by: @pavel-zotov

Ticket should be re-opened.
Reference on tabel column remains in RDB$USER_PRIVILEGE if we _rename_ column before dropping it. Or just rename column and thats all (i.e. do not drop it).

Consider this script:

shell del C:\\MIX\\firebird\\QA\\fbt\-repo\\tmp\\c5506\.fdb 2\>nul;
create database 'localhost:C:\\MIX\\firebird\\QA\\fbt\-repo\\tmp\\c5506\.fdb';

set list on;
set count on;

create view v\_priv as
select cast\(rdb$user as varchar\(31\)\) as who, rdb$privilege as has\_privilege, cast\(rdb$relation\_name as varchar\(31\)\) as on\_what\_table, rdb$field\_name as on\_what\_field 
from rdb$user\_privileges 
where rdb$user='TMP$C5506';
commit;

set term ^;
execute block as
begin
    execute statement 'drop user tmp$c5506' with autonomous transaction;
    when any do begin end
end^
set term ;^
commit;
create user tmp$c5506 password '123';
commit;

create table test\(x int, y int, z int\);

grant update\(x\) on test to tmp$c5506; \-\- this privilege will NOT touched later
grant update\(z\) on test to tmp$c5506; \-\- this privilege MUST be 'auto\-updated' or 'auto\-removed', see below
commit;

set echo on;

\-\- Check initial permissions:
show grants;

alter table test alter z to a;
commit;

\-\- Check permissions 2nd time, after column was renamed:
show grants;


show table test;

select \* from v\_priv;

alter table test drop a;
commit;

\-\- Check permissions 3rd time, after column was dropped:
show grants;

show table test;

select \* from v\_priv;
commit;

===

Note at following part from its output:

\-\- Check initial permissions:
show grants;

/\* Grant permissions for this database \*/
GRANT UPDATE \(X\) ON TEST TO USER TMP$C5506
GRANT UPDATE \(Z\) ON TEST TO USER TMP$C5506

alter table test alter z to a; \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- TABLE 'TEST' HAS \_NO\_ COLUMN 'Z' SINCE THIS POINT\.
commit;

\-\- Check permissions 2nd time, after column was renamed:
show grants;

/\* Grant permissions for this database \*/
GRANT UPDATE \(X\) ON TEST TO USER TMP$C5506
GRANT UPDATE \(Z\) ON TEST TO USER TMP$C5506 \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- ???

===

Now make backup - no errors will be in the log.
Then try to restore - you willl get:

gbak:restoring table TEST
gbak:    restoring column X
gbak:    restoring column Y
gbak:committing metadata
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user SYSDBA
gbak:    restoring privilege for user TMP$C5506
gbak:    restoring privilege for user TMP$C5506
gbak: ERROR:action cancelled by trigger \(1\) to preserve data integrity
gbak: ERROR:    could not find column for GRANT
gbak:Exiting before completion due to errors

===

PS.

The most interesting will be later.

1) check restored database using gfix -v -full. There will be NO errors.

CSPROG Fri Apr 07 09:17:19 2017
Database: C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C5506R.FDB
Validation started

CSPROG Fri Apr 07 09:17:19 2017
Database: C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C5506R.FDB
Validation finished: 0 errors, 0 warnings, 0 fixed

2) Now make connect to jusrt restored database.

isql /:C:\MIX\firebird\QA\fbt-repo\tmp\C5506R.FDB

Database: /:C:\MIX\firebird\QA\fbt-repo\tmp\C5506R.FDB, User: SYSDBA
SQL> set list on;
SQL> set count on;
SQL> select current_user as whoami from rdb$database; -- just to ensure once again that we are SYSDBA

WHOAMI SYSDBA

Records affected: 1
SQL> show view;
V_PRIV

SQL> select * from v_priv;
Statement failed, SQLSTATE = 42S22
cannot access column ON_WHAT_TABLE in view V_PRIV --------------------------------- ??? HOW CAN IT BE ???
SQL>
SQL> show view v_priv;
WHO VARCHAR(31) Nullable
HAS_PRIVILEGE (RDB$PRIVILEGE) CHAR(6) Nullable
ON_WHAT_TABLE VARCHAR(31) Nullable
ON_WHAT_FIELD (RDB$FIELD_NAME) CHAR(63) Nullable
View Source:
==== ======
select cast(rdb$user as varchar(31)) as who, rdb$privilege as has_privilege, cast(rdb$relation_name as varchar(31))
as on_what_table, rdb$field_name as on_what_field
from rdb$user_privileges
where rdb$user='TMP$C5506'
SQL>
SQL> select
CON> cast(rdb$user as varchar(31)) as who,
CON> rdb$privilege as has_privilege,
CON> cast(rdb$relation_name as varchar(31)) as on_what_table,
CON> rdb$field_name as on_what_field
CON> from rdb$user_privileges
CON> where rdb$user='TMP$C5506';

WHO TMP$C5506
HAS_PRIVILEGE U
ON_WHAT_TABLE TEST
ON_WHAT_FIELD X

Records affected: 1 -------------- Expected. Only one field remains ('X') that user 'TMP$C5506' can update in table 'TEST'.
SQL>

PPS.

WI-T4.0.0.584, but the same will be on WI-T4.0.0.584 (16-jul-2016) ==> this seems to be old bug....

[firebird-automations]

Modified by: @pavel-zotov

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

resolution: Cannot Reproduce [ 5 ] =>