Issue Details (XML | Word | Printable)

Key: CORE-6147
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Luciano Mendes
Votes: 0
Watchers: 2
Operations

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

PLG$SRP table, PLG$SRP_VIEW View instructions are strangely added in the metadata script extracted when Windows trusted authentication is enabled

Created: 21/Sep/19 01:51 AM   Updated: Friday 06:36 PM
Component/s: ISQL
Affects Version/s: 3.0.4
Fix Version/s: 4.0 RC 1

File Attachments: 1. File WRONG_DDL.SQL (1 kB)

Image Attachments:

1. GRANT_PERMISSIONS.PNG
(21 kB)

2. PLG_SRP.PNG
(13 kB)

3. PLG_SRP_VIEW.PNG
(7 kB)
Environment:
Windows 10 x64
Firebird 3.0.4.33054 (x64)

QA Status: Done with caveats
Test Details:
Two questions remains:
1) test database can not be dropped using 'DROP DATABASE' command, even if we use ALTER EXTERNAL CONNECTIONS POOL CLEAR ALL before it. The only way to do that is to change DB state to full shutdown before remove its file using OS call;
2) user 'SYSDBA' who was added into test DB (which is self-security because of Win_SSPI usage) can create global mapping but can NOT drop it.
Attempt to do this fails with:
=====
Statement failed, SQLSTATE = 28000
unsuccessful metadata update
-DROP MAPPING TRUSTED_AUTH_C6147 failed
-Unable to perform operation
-System privilege CHANGE_MAPPING_RULES is missing
=====
This global mapping can be dropped if we connect to test DB as "common" SYSDBA using Srp auth. method rather than Win_SSpi.

To be investigated later.


 Description  « Hide
ACTUAL RESULT

PLG$SRP table, PLG$SRP_VIEW View instruction are strangely added in the metadata script extracted when Windows trusted authentication is enabled on a database that are using its internal security tables:
================================================================
/* Table: PLG$SRP, Owner: SYSDBA */
CREATE TABLE PLG$SRP (PLG$USER_NAME SEC$USER_NAME NOT NULL,
        PLG$VERIFIER VARCHAR(128) CHARACTER SET OCTETS NOT NULL,
        PLG$SALT VARCHAR(32) CHARACTER SET OCTETS NOT NULL,
        PLG$COMMENT RDB$DESCRIPTION,
        PLG$FIRST SEC$NAME_PART,
        PLG$MIDDLE SEC$NAME_PART,
        PLG$LAST SEC$NAME_PART,
        PLG$ATTRIBUTES RDB$DESCRIPTION,
        PLG$ACTIVE BOOLEAN,
PRIMARY KEY (PLG$USER_NAME));
/* View: PLG$SRP_VIEW, Owner: SYSDBA */
CREATE VIEW PLG$SRP_VIEW (PLG$USER_NAME, PLG$VERIFIER, PLG$SALT, PLG$COMMENT, PLG$FIRST, PLG$MIDDLE, PLG$LAST, PLG$ATTRIBUTES, PLG$ACTIVE) AS
SELECT PLG$USER_NAME, PLG$VERIFIER, PLG$SALT, PLG$COMMENT, PLG$FIRST, PLG$MIDDLE, PLG$LAST, PLG$ATTRIBUTES, PLG$ACTIVE FROM PLG$SRP WHERE CURRENT_USER = 'SYSDBA' OR CURRENT_ROLE = 'RDB$ADMIN' OR CURRENT_USER = PLG$SRP.PLG$USER_NAME;
/* Grant permissions for this database */
GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON PLG$SRP TO VIEW PLG$SRP_VIEW;
GRANT SELECT, UPDATE (PLG$ATTRIBUTES) ON PLG$SRP_VIEW TO PUBLIC;
GRANT UPDATE (PLG$COMMENT) ON PLG$SRP_VIEW TO PUBLIC;
GRANT UPDATE (PLG$FIRST) ON PLG$SRP_VIEW TO PUBLIC;
GRANT UPDATE (PLG$LAST) ON PLG$SRP_VIEW TO PUBLIC;
GRANT UPDATE (PLG$MIDDLE) ON PLG$SRP_VIEW TO PUBLIC;
GRANT UPDATE (PLG$SALT) ON PLG$SRP_VIEW TO PUBLIC;
GRANT UPDATE (PLG$VERIFIER) ON PLG$SRP_VIEW TO PUBLIC;
/* Mapping security objects for this database */
CREATE MAPPING TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY "USER" TO USER ;
/* Global mapping */
CREATE GLOBAL MAPPING TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY "USER" TO USER ;
================================================================




EXPECTED RESULT

The metadata script extracted when Windows trusted authentication is enabled should be added olyn the instruction to enable it:
=========================
CREATE OR ALTER GLOBAL MAPPING TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER;
=========================




STEPS TO REPRODUCE THE ISSUE:

1- Setup the database to use its internal security tables:
databases.conf
TEST = E:\TEST.FDB
{
SecurityDatabase = TEST
}
2 - Connect with the database (SYSDBA) and run the following SQL instruction to enable use of Windows trusted authentication:
CREATE OR ALTER GLOBAL MAPPING TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER;
COMMIT;
3- Extract the metadata script and compare it with the original database metadata script
"%ProgramFiles%\Firebird\Firebird_3_0\isql.exe" -user SYSDBA -password masterkey -extract -output "FB30_DDL.SQL" "TEST"




FIREBIRD SETUP

firebird.conf
#SETUP
AuthClient = Legacy_Auth, Srp, Win_Sspi
AuthServer = Legacy_Auth, Srp, Win_Sspi
ServerMode = Super
UserManager = Legacy_UserManager, Srp
WireCrypt = Enabled
#PERFORMANCE
DefaultDbCachePages = 100K
FileSystemCacheThreshold = 2M
LockHashSlots = 30011
LockMemSize = 15M
RemoteServicePort = 3050
TempBlockSize = 2M
TempCacheLimit = 1000M
TracePlugin = fbtrace

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Luciano Mendes added a comment - 21/Sep/19 02:00 AM
This issue does not happening when the database is using the security3.fdb as its Security Database

Luciano Mendes added a comment - 24/Dec/19 12:05 PM
This issue is still reproducible using ISQL from Firebird 3.0.5.33212 (Snapshot).

Luciano Mendes added a comment - 20/Jan/20 12:43 PM
Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#motorolablocker)

Luciano Mendes added a comment - 21/Jun/20 05:54 PM
Hi Alexander Peshkov,

I just confirmed that this issue also happens when we create users in the database (SRP) and not just when we are mapping users as initially described:

ECHO CREATE OR ALTER USER SYSDBA SET PASSWORD 'SYSDBAPWD' USING PLUGIN SRP;COMMIT; | "%ProgramFiles%\Firebird\Firebird_3_0\isql.exe" -user SYSDBA "TEST" -quiet >NUL

I have attached all statements that are mistakenly added to the database's metadata script (WRONG_DDL.SQL) to facilitate fix.

This issue is blocking our company from using the security database in the database itself. This new functionality in Firebird 3 is very important since migrating or upgrading the version of Firebird would not require us to recreate all users again.

Would it be possible to fix this issue before Firebird 3.0.6 is released? I can help you to validate the issue as soon as it is available!

I thank you for all your effort and work;

Best Regards,
Luciano

Alexander Peshkov added a comment - 29/Jun/20 11:29 AM
Except an error when creating global mapping all the rest is as designed behavior - resulting script should be applied to empty database, it will create data structures, required for plugin's operation. What a problem?

Luciano Mendes added a comment - 29/Jun/20 12:53 PM
Hi Alexander,

Thank you very much for your prompt reply!

All the metadata present in the WRONG_DLL.SQL file are tables, views and system permissions created in the user database when it defines that the security database will be in the database itself and users are created in this database. Just as the other tables and system permissions do not appear, and should not appear, in the metadata extracted from a database, in my humble opinion the metadata present in the WRONG_DLL.SQL file should also not be added to the extracted metadata.

Imagine the situation where the tables, views and permissions listed in the WRONG_DLL.SQL file change in future versions of Firebird and the DBA is recreating its database with the information listed in the WRONG_DLL.SQL file in its metadata? Any improvements in this area of Firebird could be dangerously replaced by the metadata described in the WRONG_DLL.SQL file.

In my humble opinion, for security and compatibility with future versions of Firebird, the metadata described in the WRONG_DLL.SQL file should be omitted from the metadata extracted from the database.

Thank you very much for your work!

Best Regards,
Luciano

Alexander Peshkov added a comment - 01/Jul/20 03:09 PM
I've fixed a bug when extracting global mappings - they are rather 'thin' issue, and should be created with care.

What about plugins metadata - don;t worry, bringing plugin's data in accordance with plugin's version is anyway plugin's job. They are _not_ system data.

Luciano Mendes added a comment - 01/Jul/20 05:02 PM
Hi Alexander,

Thank you very much for your prompt reply!

What I meant in my last comment is that the metadata extracted from the database must contain only the source code designed by the DBA. System metadata, plug-in metadata, etc. should be omitted when extracting metadata from a database because it is information dependent on the version and configuration of the data management system being used. The metadata for any database needs to be as portable as possible not only between versions of the same database management system but also between different database management systems (sql compliance).
In other words, it is not necessary to have the plugin's metadata in the metadata extracted from a database whose security database is in the database itself, as this is not part of the source code designed by the DBA, but a configuration option of the security database.

Thank you very much for your attention and your work on this bug!

Best Regards,
Luciano