Skip to content
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

Closed
firebird-automations opened this issue Sep 21, 2019 · 22 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @luronumen

Attachment: GRANT_PERMISSIONS.PNG [ 13386 ]

Attachment: PLG_SRP.PNG [ 13387 ]

Attachment: PLG_SRP_VIEW.PNG [ 13388 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

This issue does not happening when the database is using the security3.fdb as its Security Database

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

This issue is still reproducible using ISQL from Firebird 3.0.5.33212 (Snapshot).

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

Retest result on Firebird 3.0.5.33220 (Official Firebird 3.0.5): - FAILED (#⁠motorolablocker)

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @luronumen

Attachment: WRONG_DDL.SQL [ 13460 ]

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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?

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

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).
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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Open [ 1 ] => Open [ 1 ]

QA Status: No test => Deferred

Test Details: Can not reproduce or (perhaps) did not properly understand what exactly was fixed.
Sent letter to Alex, 01.07.2020 21:38, waiting for reply.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: 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.
Sent letter to Alex, 01.07.2020 21:38, waiting for reply.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.7 [ 10940 ]

@firebird-automations
Copy link
Collaborator Author

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.
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

@firebird-automations
Copy link
Collaborator Author

Commented by: Basil A. Sidorov (basid)

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).

@firebird-automations
Copy link
Collaborator Author

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

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @luronumen

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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).

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Link: This issue is duplicated by CORE6410 [ CORE6410 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants