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

firebird-automations · 2016-04-23T20:55:22Z

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

The text was updated successfully, but these errors were encountered:

firebird-automations · 2016-04-23T22:45:33Z

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

firebird-automations · 2016-04-23T23:25:26Z

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-automations · 2016-04-23T23:25:26Z

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-automations · 2016-04-24T00:16:39Z

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-automations · 2016-04-24T00:17:17Z

Modified by: @pavel-zotov

Attachment: c5207.PNG [ 12940 ]

firebird-automations · 2016-04-24T00:25:09Z

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-automations · 2016-04-24T07:01:52Z

Commented by: @dyemanov

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

firebird-automations · 2016-04-24T07:01:52Z

Modified by: @dyemanov

Fix Version: 4.0 Alpha 1 [ 10731 ]

firebird-automations · 2016-04-24T09:24:03Z

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-automations · 2016-04-24T09:31:31Z

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.1 [ 10730 ]

firebird-automations · 2016-04-24T10:24:43Z

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

firebird-automations · 2016-04-24T10:24:51Z

Modified by: @pavel-zotov

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

firebird-automations closed this as completed Apr 24, 2016

firebird-automations added affect-version: 3.0.0 affect-version: 4.0 Initial fix-version: 4.0 Alpha 1 fix-version: 3.0.1 resolution: fixed priority: major component: engine type: bug qa: done successfully labels Apr 25, 2021

firebird-automations assigned dyemanov Apr 25, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

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

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

Comments

firebird-automations commented Apr 23, 2016

1. Create script:

create domain dm_int as int; commit;

Check final script:

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

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 23, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

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;

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

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

firebird-automations commented Apr 24, 2016

create domain dm_int as int;
commit;

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

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

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

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;