Issue Details (XML | Word | Printable)

Key: CORE-6147
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
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: 06/Oct/20 04:51 PM
Component/s: ISQL
Affects Version/s: 3.0.4
Fix Version/s: 3.0.7, 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)
Issue Links:
Duplicate
 

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

Luciano Mendes added a comment - 16/Jul/20 04:04 PM
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.
Should I open a new bug referencing this issue because the SRP is a plugin distributed/maintained by the firebird project and not by the DBA or was it decided not to fix it?

Thanks in advanced,
Luciano

Basil A. Sidorov added a comment - 17/Jul/20 05:57 AM
isql -q -z -user sysdba -x security.db
ISQL Version: WI-V3.0.6.33328 Firebird 3.0
Server version:
WI-V3.0.6.33328 Firebird 3.0
Server version:
WI-V3.0.6.33328 Firebird 3.0

SET SQL DIALECT 3;

/* CREATE DATABASE 'security.db' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE;
ALTER DATABASE SET LINGER TO 60; */


/* Domain definitions */
CREATE DOMAIN PLG$ID AS INTEGER;
CREATE DOMAIN PLG$PASSWD AS VARCHAR(64) CHARACTER SET OCTETS;
COMMIT WORK;

/* Table: PLG$SRP, Owner: SYSDBA */
CREATE TABLE PLG$SRP (PLG$USER_NAME SEC$USER_NAME NOT NULL,
....
/* Table: PLG$USERS, Owner: SYSDBA */
CREATE TABLE PLG$USERS (PLG$USER_NAME SEC$USER_NAME NOT NULL,
...
/* View: PLG$VIEW_USERS, Owner: SYSDBA */
CREATE VIEW PLG$VIEW_USERS (PLG$USER_NAME, PLG$GROUP_NAME, PLG$UID, PLG$GID, PLG$PASSWD, PLG$COMMENT, PLG$FIRST_NAME, PLG$MIDDLE_NAME, PLG$LAST_NAME) AS
...
/* 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
...
/* Grant permissions for this database */
...

All metadata are extracted from default security database.
Why not all metadata should be extracted from another security database?
N(ot)AB(ug).

Luciano Mendes added a comment - 17/Jul/20 10:00 AM
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 reproducibility of an issue (0% ~ 100%) only measures how easy it is to arrive at the described behavior.

The questions that should be asked are:
- Why should the SRP plugin metadata be added to the metadata extracted from the database?
- What is the reason why the metadata of the SRP plugin is in the metadata extracted from the database if it is a data structure automatically recreated when configuring the security database in the database itself?
- Why should the SRP plugin's metadata mix with the metadata developed by the DBA if the DBA can choose to use this same metadata to build other databases where the security database is not in the database itself?
- Why the metadata of the SRP plugin should be in the metadata extracted from the database if this metadata can be used in other database management systems (SQL Server, MySQL, PostgreSQL, etc.) that do not use the SRP plugin data structure for authentication?

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,
Luciano

Alexander Peshkov added a comment - 17/Jul/20 10:03 AM
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.

Luciano Mendes added a comment - 17/Jul/20 10:18 AM
Hi Alexander

Thank you very much for your prompt reply!
How do I move this discussion to fb-devel? Could you forward this case to them?

Thank you so much again for your work!

Best Regards,
Luciano

Alexander Peshkov added a comment - 17/Jul/20 10:24 AM
Are you kidding? :)

Well:
1. Make sure you are subscribed to developers list.
2. Sent email to it describing a problem, links to tracker are accepted (if you need one).