Hi – I have another question re user mapping…
If I create a new database with a user->role mapping, I can back up and restore
it and the mapping is still present.
But if I back up the new copy and restore that, the mapping is gone… is there
an issue with the procedure I'm using or is this a bug?
(for context, we have Firebird databases here that have inherited a regular
backup/restore cycle from when we were using InterBase 5 or 6… apparently they
had a tendency to slow down if not refreshed this way, but that is from before
my time!)
Here's a demonstration using Firebird 3.0.5:
1. Create database with mapping
PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> create role foo;
SQL> create mapping sjd_foo using plugin win_sspi from user "foodstuff\pgmrsd1"
to role foo;
SQL> exit;
2. Check mapping
PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe
C:\temp\mapping-backup-restore\FOO.FDB
Database: C:\temp\mapping-backup-restore\FOO.FDB, User: PGMRSD1
SQL> show users;
Users in the database
1 #PGMRSD1
SQL> show roles;
FOO
SQL> show mapping;
SJD_FOO USING PLUGIN WIN_SSPI FROM USER foodstuff\pgmrsd1 TO ROLE FOO
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;
3. Back up database – verbose output shows mapping saved
PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -b
C:\temp\mapping-backup-restore\FOO.FDB
C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: time delta reads writes
gbak: 0.104 0.104 40 0 readied database
C:\temp\mapping-backup-restore\FOO.FDB for backup
gbak: 0.107 0.002 0 0 creating file
C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: 0.112 0.004 0 0 starting transaction
gbak: 0.115 0.003 21 1 database
C:\temp\mapping-backup-restore\FOO.FDB has a page size of 8192 bytes.
gbak: 0.118 0.003 0 0 writing domains
gbak: 0.122 0.003 6 0 writing shadow files
gbak: 0.123 0.001 0 0 writing character sets
gbak: 0.125 0.001 0 0 writing collations
gbak: 0.127 0.001 1 0 writing tables
gbak: 0.128 0.001 0 0 writing types
gbak: 0.131 0.002 5 0 writing filters
gbak: 0.132 0.001 2 0 writing id generators
gbak: 0.134 0.001 3 0 writing exceptions
gbak: 0.135 0.001 2 0 writing functions
gbak: 0.137 0.001 2 0 writing stored procedures
gbak: 0.139 0.002 2 0 writing packages
gbak: 0.141 0.001 2 0 writing triggers
gbak: 0.143 0.002 0 0 writing trigger messages
gbak: 0.145 0.002 12 0 writing security classes
gbak: 0.148 0.002 2 0 writing table constraints
gbak: 0.150 0.002 0 0 writing referential constraints
gbak: 0.152 0.002 1 0 writing check constraints
gbak: 0.154 0.001 0 0 writing SQL roles
gbak: 0.155 0.001 0 0 writing SQL role: FOO
gbak: 0.157 0.002 0 0 writing names mapping
gbak: 0.159 0.001 3 0 writing map for SJD_FOO
gbak: 0.199 0.002 104 1 total statistics
4. Restore database – verbose output shows mapping preserved
PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -c
C:\temp\mapping-backup-restore\FOO.FDB.bak
C:\temp\mapping-backup-restore\BAR.FDB
gbak: time delta reads writes
gbak: 0.004 0.004 0 0 opened file
C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: 0.219 0.214 0 0 transportable backup -- data in XDR format
gbak: 0.222 0.002 0 0 backup file is compressed
gbak: 0.224 0.001 0 0 backup version is 10
gbak: 0.362 0.137 0 666 created database
C:\temp\mapping-backup-restore\BAR.FDB, page_size 8192 bytes
gbak: 0.365 0.003 0 2 started transaction
gbak: 0.366 0.001 0 0 restoring SQL role: FOO
gbak: 0.368 0.001 0 0 restoring names mapping
gbak: 0.370 0.002 0 0 restoring map for SJD_FOO
gbak: 0.375 0.004 0 13 creating indexes
gbak: 0.402 0.027 0 34 committing metadata
gbak: 0.404 0.002 0 10 fixing views dbkey length
gbak: 0.407 0.002 0 0 updating ownership of packages, procedures
and tables
gbak: 0.409 0.002 0 0 adding missing privileges
gbak: 0.411 0.002 0 0 adjusting system generators
gbak: 0.418 0.006 0 3 finishing, closing, and going home
gbak: 0.421 0.002 0 728 total statistics
gbak:adjusting the ONLINE and FORCED WRITES flags
5. Confirm mapping present in restored database
PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe
C:\temp\mapping-backup-restore\BAR.FDB
Database: C:\temp\mapping-backup-restore\BAR.FDB, User: PGMRSD1
SQL> show users;
Users in the database
1 #PGMRSD1
SQL> show roles;
FOO
SQL> show mapping;
SJD_FOO USING PLUGIN WIN_SSPI FROM USER foodstuff\pgmrsd1 TO ROLE FOO
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;
6. Back up database – verbose output does not show mapping
PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -b
C:\temp\mapping-backup-restore\BAR.FDB
C:\temp\mapping-backup-restore\BAR.FDB.bak
gbak: time delta reads writes
gbak: 0.124 0.124 40 0 readied database
C:\temp\mapping-backup-restore\BAR.FDB for backup
gbak: 0.126 0.002 0 0 creating file
C:\temp\mapping-backup-restore\BAR.FDB.bak
gbak: 0.130 0.003 0 0 starting transaction
gbak: 0.133 0.003 21 1 database
C:\temp\mapping-backup-restore\BAR.FDB has a page size of 8192 bytes.
gbak: 0.135 0.002 0 0 writing domains
gbak: 0.137 0.002 6 0 writing shadow files
gbak: 0.141 0.003 0 0 writing character sets
gbak: 0.143 0.002 0 0 writing collations
gbak: 0.145 0.002 1 0 writing tables
gbak: 0.147 0.001 0 0 writing types
gbak: 0.150 0.002 5 0 writing filters
gbak: 0.152 0.001 2 0 writing id generators
gbak: 0.153 0.001 3 0 writing exceptions
gbak: 0.155 0.001 2 0 writing functions
gbak: 0.156 0.001 2 0 writing stored procedures
gbak: 0.159 0.002 2 0 writing packages
gbak: 0.160 0.001 2 0 writing triggers
gbak: 0.162 0.001 0 0 writing trigger messages
gbak: 0.165 0.002 12 0 writing security classes
gbak: 0.168 0.003 2 0 writing table constraints
gbak: 0.170 0.002 0 0 writing referential constraints
gbak: 0.172 0.001 1 0 writing check constraints
gbak: 0.174 0.001 0 0 writing SQL roles
gbak: 0.175 0.001 0 0 writing SQL role: FOO
gbak: 0.177 0.002 0 0 writing names mapping
gbak: 0.218 0.041 3 0 closing file, committing, and finishing.
512 bytes written
gbak: 0.221 0.002 104 1 total statistics
7. Restore database – verbose output does not show mapping
PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -c
C:\temp\mapping-backup-restore\BAR.FDB.bak
C:\temp\mapping-backup-restore\BAZ.FDB
gbak: time delta reads writes
gbak: 0.005 0.005 0 0 opened file
C:\temp\mapping-backup-restore\BAR.FDB.bak
gbak: 0.072 0.067 0 0 transportable backup -- data in XDR format
gbak: 0.074 0.001 0 0 backup file is compressed
gbak: 0.076 0.001 0 0 backup version is 10
gbak: 0.196 0.120 0 666 created database
C:\temp\mapping-backup-restore\BAZ.FDB, page_size 8192 bytes
gbak: 0.200 0.003 0 2 started transaction
gbak: 0.202 0.002 0 0 restoring SQL role: FOO
gbak: 0.208 0.005 0 9 creating indexes
gbak: 0.234 0.026 0 34 committing metadata
gbak: 0.237 0.002 0 10 fixing views dbkey length
gbak: 0.238 0.001 0 0 updating ownership of packages, procedures
and tables
gbak: 0.240 0.001 0 0 adding missing privileges
gbak: 0.242 0.001 0 0 adjusting system generators
gbak: 0.247 0.005 0 3 finishing, closing, and going home
gbak: 0.249 0.001 0 724 total statistics
gbak:adjusting the ONLINE and FORCED WRITES flags
8. Confirm mapping not present in restored database
PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe
C:\temp\mapping-backup-restore\BAZ.FDB
Database: C:\temp\mapping-backup-restore\BAZ.FDB, User: PGMRSD1
SQL> show users;
Users in the database
1 #PGMRSD1
SQL> show roles;
FOO
SQL> show mapping;
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;
------------------------------------------------------------------------------------------------------------------------
Note:
This e-mail message has been inspected for malicious content.
Attention:
The information contained in this message and or attachments is intended only
for the person
or entity to which it is addressed and may contain confidential and/or
privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon,
this information by persons or entities other than the intended recipient is
prohibited. If you
received this in error, please contact the sender and delete the material from
any system and
destroy any copies.
Please note that the views and opinions expressed in this message may be those
of the
individual and not necessarily those of Foodstuffs South Island Ltd.
Thank you.
------------------------------------------------------------------------------------------------------------------------
