ISQL -X may generate invalid GRANT USAGE statements for domains [CORE5207]

[firebird-issue-importer]

Submitted by: @pavel-zotov

Attachments:
c5207.PNG

1. Create script:

set term ^;
execute block as
begin
execute statement 'drop domain dm_int';
when any do begin end
end
^
set term ;^
commit;

create domain dm_int as int;
commit;

2. Create empty database on FB 2.5 and apply this script

3. Make gbak of this database (i.e. use gbak from FB 2.5)

4. Make restore using FB 3.0, let target file will be 'e30.fdb'

5. Run %FB_30_HOME%\isql -X host/port path/e30.fdb

Check final script:

SET SQL DIALECT 3;

/* CREATE DATABASE '/:C:\MIX\firebird\QA\fbt-repo\tmp\E40TMP.FDB' PAGE_SIZE 4096 DEFAULT CHARACTER SET NONE; */

/* Domain definitions */
CREATE DOMAIN DM_INT AS INTEGER;
COMMIT WORK;

/* Grant permissions for this database */
GRANT USAGE ON DOMAIN DM_INT TO PUBLIC;

Last statement (GRANT USAGE ON DOMAIN ... ) can not be compiled:

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 16
-DOMAIN

Commits: 8669877 fd5b922

[firebird-issue-importer]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-issue-importer]

Commented by: @dyemanov

I suspect the owner of the database was different in v2.5 and v3.0 (created and restored under different user names).

[firebird-issue-importer]

Modified by: @dyemanov

summary: ISQL -X (from FB 3.0) creates invalid command related to grant usage of domain when database is restored from FB 2.5 where this domain was created => ISQL -X may generate invalid GRANT USAGE statements for domains

[firebird-issue-importer]

Commented by: @pavel-zotov

> I suspect the owner of the database was different in v2.5 and v3.0 (created and restored under different user names).

No, all was done by SYSDBA (see attached screen).
But FB 3.0 config uses Legacy_Auth plugin - maybe this could affect ?

[firebird-issue-importer]

Modified by: @pavel-zotov

Attachment: c5207.PNG [ 12940 ]

[firebird-issue-importer]

Commented by: @pavel-zotov

> owner of the database was different in v2.5 and v3.0 (created and restored under different user names).

BTW, I've found that ISQL -X during restore metadata from 2.5 can create such strange statements:

GRANT ON TOO_LONG_CONNECT TO PUBLIC;
GRANT ON TOO_LONG_CONNECT TO USER SYSDBA WITH GRANT OPTION;

NOTE: there is _no_ privilege(s) between 'grant' and 'on'!

"TOO_LONG_CONNECT" - this is external table which DDL (in 2.5) is:

create table too_long_connect external 'too_long_connect.txt' (
msg char(29)
);
grant insert on too_long_connect to calibrator;
grant all on too_long_connect to db_owner with grant option;

Unfortunately, I can't reproduce this yet.

[firebird-issue-importer]

Commented by: @dyemanov

Fixed in v4, please validate before it's backported into v3.

[firebird-issue-importer]

Modified by: @dyemanov

Fix Version: 4.0 Alpha 1 [ 10731 ]

[firebird-issue-importer]

Commented by: @pavel-zotov

> Fixed in v4, please validate

All fine on current sources of 4.0:

$ /opt/fb40ss/bin/gbak -rep /var/db/fb40/e25tmp.fbk localhost/3400:/var/db/fb40/e25tmp.fdb ; /opt/fb40ss/bin/isql -x localhost/3400:/var/db/fb40/e25tmp.fdb

SET SQL DIALECT 3;

/* CREATE DATABASE 'localhost/3400:/var/db/fb40/e25tmp.fdb' PAGE_SIZE 4096 DEFAULT CHARACTER SET NONE; */

/* Domain definitions */
CREATE DOMAIN DM_INT AS INTEGER;
COMMIT WORK;

===

ISQL Version: LI-T4.0.0.142 Firebird 4.0 Unstable
Server version:
LI-T4.0.0.142 Firebird 4.0 Unstable
LI-T4.0.0.142 Firebird 4.0 Unstable/tcp (oel64)/P13
LI-T4.0.0.142 Firebird 4.0 Unstable/tcp (oel64)/P13

[firebird-issue-importer]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.1 [ 10730 ]

[firebird-issue-importer]

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

[firebird-issue-importer]

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]