PLG$SRP table, PLG$SRP_VIEW View instructions are strangely added in the
metadata script extracted when Windows trusted authentication is enabled
-------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-6147
URL: http://tracker.firebirdsql.org/browse/CORE-6147
Project: Firebird Core
Issue Type: Bug
Components: ISQL
Affects Versions: 3.0.4
Environment: Windows 10 x64
Firebird 3.0.4.33054 (x64)
Reporter: Luciano Mendes
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
