This sounds like a bug to me. Can you create bug report in the tracker at http://tracker.firebirdsql.org/browse/CORE
Mark On 09-03-2020 03:50, FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] wrote: > > > 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; -- Mark Rotteveel
