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

CREATE DATABASE grant is lost in security.db after backup/restore cycle [CORE6208] #6453

Closed
firebird-automations opened this issue Dec 16, 2019 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Basil A. Sidorov (basid)

Attachments:
CORE-6208_reopen.zip

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

Commits: 8211f7e f47472f c6df8f4 808688a

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Cannot be tested

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.

Test Specifics: [Architecture (SS/CS) specific]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Cannot be tested => Done with caveats

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

Test Specifics: [Architecture (SS/CS) specific] => [Architecture (SS/CS) specific, Custom configuration required]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.6 [ 10889 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Grant lost in security.db after backup/restore cycle => CREATE DATABASE grant is lost in security.db after backup/restore cycle

@firebird-automations
Copy link
Collaborator Author

Commented by: Basil A. Sidorov (basid)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: Basil A. Sidorov (basid)

Attachment: CORE6208_reopen.zip [ 13476 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Basil A. Sidorov (basid)

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

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