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: Ascending order - Click to sort in descending order
Alexander Peshkov made changes - 23/Dec/19 09:17 AM
Field Original Value New Value
Assignee Alexander Peshkov [ alexpeshkoff ]
Alexander Peshkov made changes - 27/Dec/19 05:19 PM
Fix Version/s 4.0 Beta 2 [ 10888 ]
Pavel Zotov made changes - 28/Dec/19 03:01 PM
Status Open [ 1 ] Open [ 1 ]
Test Specifics [Architecture (SS/CS) specific]
Test Details Embedded mode required. Could not reproduce using local or remote protocol with restoring to DB that distincts from "security.db". Checked on 3.0.1, 3.0.4, 3.0.5, 4.0.0.
QA Status No test Cannot be tested
Pavel Zotov made changes - 05/Jan/20 12:16 PM
Status Open [ 1 ] Open [ 1 ]
Test Specifics [Architecture (SS/CS) specific] [Architecture (SS/CS) specific, Custom configuration required]
Test Details Embedded mode required. Could not reproduce using local or remote protocol with restoring to DB that distincts from "security.db". Checked on 3.0.1, 3.0.4, 3.0.5, 4.0.0.
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).
QA Status Cannot be tested Done with caveats
Alexander Peshkov made changes - 13/Jan/20 02:31 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.0.6 [ 10889 ]
Resolution Fixed [ 1 ]
Dmitry Yemanov made changes - 28/Apr/20 08:40 AM
Summary Grant lost in security.db after backup/restore cycle CREATE DATABASE grant is lost in security.db after backup/restore cycle
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.

Basil A. Sidorov made changes - 14/Jul/20 10:39 AM
Attachment CORE-6208_reopen.zip [ 13476 ]
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 - 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