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
PLG$SRP table, PLG$SRP_VIEW View instructions are strangely added in the metadata script extracted when Windows trusted authentication is enabled [CORE6147] #6396
Comments
Modified by: @luronumenAttachment: GRANT_PERMISSIONS.PNG [ 13386 ] Attachment: PLG_SRP.PNG [ 13387 ] Attachment: PLG_SRP_VIEW.PNG [ 13388 ] |
Commented by: @luronumen This issue does not happening when the database is using the security3.fdb as its Security Database |
Commented by: @luronumen This issue is still reproducible using ISQL from Firebird 3.0.5.33212 (Snapshot). |
Commented by: @luronumen Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#motorolablocker) |
Modified by: @AlexPeshkoffassignee: Alexander Peshkov [ alexpeshkoff ] |
Modified by: @luronumenAttachment: WRONG_DDL.SQL [ 13460 ] |
Commented by: @luronumen 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, |
Commented by: @AlexPeshkoff 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? |
Commented by: @luronumen 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, |
Commented by: @AlexPeshkoff 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. |
Modified by: @AlexPeshkoffFix Version: 4.0 RC 1 [ 10930 ] |
Commented by: @luronumen 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). Thank you very much for your attention and your work on this bug! Best Regards, |
Modified by: @pavel-zotovstatus: Open [ 1 ] => Open [ 1 ] QA Status: No test => Deferred Test Details: Can not reproduce or (perhaps) did not properly understand what exactly was fixed. |
Modified by: @pavel-zotovstatus: Open [ 1 ] => Open [ 1 ] QA Status: Deferred => Done with caveats Test Details: Can not reproduce or (perhaps) did not properly understand what exactly was fixed. => Two questions remains:
|
Modified by: @AlexPeshkoffstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0.7 [ 10940 ] |
Commented by: @luronumen Hi Alexander, I just retested this issue in Firebird 3.0.7.33347 (Snapshot) and I confirmed that references to the table (PLG$SRP), view (PLG$SRP_VIEW) and permissions of the SRP plugin are still being added to the metadata extracted from the database. Thanks in advanced, |
Commented by: Basil A. Sidorov (basid) isql -q -z -user sysdba -x security.db SET SQL DIALECT 3; /* CREATE DATABASE 'security.db' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE; /* Domain definitions */ /* Table: PLG$SRP, Owner: SYSDBA */ All metadata are extracted from default security database. |
Commented by: @luronumen Hi Basil, It is not because a issue is 100% reproducible in all databases that the SRP plugin was enabled (security3.fdb, user database etc.) that this is enough to classify a defect as expected behavior. The questions that should be asked are: It was answering these questions that made me open this bug and wait for its solution to start using the security database in the database itself. But if even after answering the questions above you are still convinced that the metadata of the SRP plugin must be in the metadata extracted from the database, unfortunately I will have to continue not to use the security database in the database itself :( Best Regards, |
Commented by: @AlexPeshkoff Luciano, I suggest you to move discussion to fb-devel. Just adding new ticket(s) is quite pointless - on my mind this is not a bug but expected, normal, correct behavior. |
Commented by: @luronumen Hi Alexander Thank you very much for your prompt reply! Thank you so much again for your work! Best Regards, |
Commented by: @AlexPeshkoff Are you kidding? :) Well: |
Modified by: @AlexPeshkoff |
Submitted by: @luronumen
Is duplicated by CORE6410
Attachments:
GRANT_PERMISSIONS.PNG
PLG_SRP.PNG
PLG_SRP_VIEW.PNG
WRONG_DDL.SQL
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
Commits: 66499fd dbc28a8
====== 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.
The text was updated successfully, but these errors were encountered: