Issue Details (XML | Word | Printable)

Key: CORE-6208
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Basil A. Sidorov
Votes: 0
Watchers: 4
Operations

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

CREATE DATABASE grant is lost in security.db after backup/restore cycle

Created: 16/Dec/19 02:18 AM   Updated: 15/Jul/20 03:56 PM
Component/s: None
Affects Version/s: 3.0.4, 4.0 Beta 1
Fix Version/s: 4.0 Beta 2, 3.0.6

File Attachments: 1. Zip Archive CORE-6208_reopen.zip (145 kB)


QA Status: Done with caveats
Test Details:
NOTES, for SUPERSERVER and SUPERCLASSIC.

Following values of AuthClient:
    AuthClient = Srp256, Srp, Win_Sspi, Legacy_Auth
or
    AuthClient = Legacy_Auth, Srp, Srp256, Win_Sspi

-- force delay to be inserted after point when database is overwritten and before we do connect in order to verity grant.
This delay must be significant: 9s for SS and 11s for SC, at least on Windows 8.1 x64 (cpu 3 GHz, ram 12 gb, no workload from other processes).

If we skip this delay then connect will fail with:
=====
    Statement failed, SQLSTATE = 08006
    Error occurred during login, please check server firebird.log for details
=====
-- and firebird.log will contain:
=====
    Authentication error
    cannot start transaction for password database
    Error in isc_start_transaction() API call when working with legacy security database
    connection shutdown
    Database is shutdown.
=====

THE ONLY way to avoid delay is to put 'Srp' ( NOT 'Legacy_Auth' and even not 'Srp256' ! ) in the head of this list.
No such trouble on Classic Server (checked all modes on 4.0.0.1714, build of 05-jan-2020).
Test Specifics: Architecture (SS/CS) specific, Custom configuration required


 Description  « Hide
Check for 3.0.4.33054, 3.0.5.33208, 4.0.0.1689 on windows.
Step to reproduce (embedded mode).

set ISC_USER=sysdba

isql security.db
create user owner password 'owner';
grant create database to user owner;
commit; exit;

echo show grant;|isql -q security.db|findstr OWNER
GRANT CREATE DATABASE TO USER OWNER
-- all ok: grant present

ren security3.fdb security3.bak
gbak -b security3.bak stdout|gbak -c stdin security.db
echo show grant;|isql -q security.db|findstr OWNER
-- empty output: grant lost

 All   Comments   Change History   Subversion Commits      Sort Order: Descending order - Click to sort in ascending order
Basil A. Sidorov added a comment - 15/Jul/20 03:56 PM
echo show grant;|isql -q -user sysdba security.db|findstr -c:"CREATE DATABASE"
GRANT CREATE DATABASE TO USER OWNER
gbak -z -user sysdba -st dtrw -v -b -g security.db security3.fbk|findstr "bytes delta grant total version"
gbak:gbak version WI-V3.0.7.33346 Firebird 3.0
        Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.7.33346 Firebird 3.0"
        on disk structure version 12.0
gbak: time delta reads writes
gbak: 0.046 0.001 25 1 database security.db has a page size of 8192 bytes.
gbak: 0.053 0.000 2 0 writing database create grants
gbak: 0.053 0.000 2 0 writing database create grants
gbak: 0.053 0.000 0 0 database create grant for OWNER
gbak: 0.055 0.001 0 0 closing file, committing, and finishing. 9728 bytes written
gbak: 0.055 0.000 167 1 total statistics
del security3.fdb
gbak -z -user sysdba -st dtrw -v -c security3.fbk security.db|findstr "bytes delta grant total version"
gbak:gbak version WI-V3.0.7.33346 Firebird 3.0
gbak: time delta reads writes
gbak: 0.020 0.000 0 0 backup version is 10
        Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.7.33346 Firebird 3.0"
        on disk structure version 12.0
gbak: 0.093 0.072 156 676 created database security.db, page_size 8192 bytes
gbak: 0.103 0.000 10 0 restoring database create grant for IMARY2
gbak: 0.103 0.000 2 0 restoring database create grant for OWNER
gbak: 0.146 0.000 518 870 total statistics
echo show grant;|isql -q -user sysdba security.db|findstr -c:"CREATE DATABASE"
GRANT CREATE DATABASE TO OWNER
GRANT CREATE DATABASE TO USER

Alexander Peshkov added a comment - 15/Jul/20 03:15 PM
Did not reproduce:
fbs3 /usr/home/firebird/HEAD/gen/Debug/firebird/bin # echo 'show grant;'|./isql -q -user sysdba security.db | grep "CREATE DATABASE"
GRANT CREATE DATABASE TO USER OWNER
fbs3 /usr/home/firebird/HEAD/gen/Debug/firebird/bin # ./gbak -b security.db sec.fbk
fbs3 /usr/home/firebird/HEAD/gen/Debug/firebird/bin # cp ../security4.fdb sec.fdb
fbs3 /usr/home/firebird/HEAD/gen/Debug/firebird/bin # ./gbak -rep sec.fbk security.db
fbs3 /usr/home/firebird/HEAD/gen/Debug/firebird/bin # echo 'show grant;'|./isql -q -user sysdba security.db | grep "CREATE DATABASE"
GRANT CREATE DATABASE TO USER OWNER
fbs3 /usr/home/firebird/HEAD/gen/Debug/firebird/bin #

PS. isql -z
ISQL Version: LI-V4.0.0.2100-dev Firebird 4.0 Release Candidate 1

Basil A. Sidorov added a comment - 14/Jul/20 10:38 AM - edited
Before backup:
> echo show grant;|isql -q -user sysdba security.db | findstr -c:"CREATE DATABASE"
GRANT CREATE DATABASE TO USER OWNER

After restore from backup:
> echo show grant;|isql -q -user sysdba security.db | findstr -c:"CREATE DATABASE"
GRANT CREATE DATABASE TO OWNER
GRANT CREATE DATABASE TO USER

Grant lost - leave only object name, but lost object type (USER in my case).

P.S.
Impossible revoke type-less grants.