Derby
  1. Derby
  2. DERBY-3868

dblook generates SET SCHEMA null and fails to produce correct schema for view

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1
    • Fix Version/s: None
    • Component/s: Tools
    • Urgency:
      Normal

      Description

      Scenario: One user creates a table, another user creates a view of the first user's table.
      dblook output show below (reproscript.sh uploaded).

      > – Timestamp: 2008-09-04 18:43:33.186
      > – Source database is: wombat
      > – Connection URL is: jdbc:derby:wombat;user=dag;password=wanvik
      > – appendLogs: false
      >
      > – ----------------------------------------------
      > – DDL Statements for schemas
      > – ----------------------------------------------
      >
      > CREATE SCHEMA "DAG";
      >
      > CREATE SCHEMA "KNUT";
      >
      > – ----------------------------------------------
      > – DDL Statements for tables
      > – ----------------------------------------------
      >
      > CREATE TABLE "DAG"."T" ("I" INTEGER);
      >
      > – ----------------------------------------------
      > – DDL Statements for views
      > – ----------------------------------------------
      >
      > SET SCHEMA null;
      > create view v as select * from dag.t;
      >
      > – Reverting back to default schema 'APP'
      > SET SCHEMA "APP";

      The SET SCHEMA null is illegal and the view "v" is not correctly
      qualified with its schema name ("KNUT").

      1. reproscript-auth.sh
        2 kB
        Dag H. Wanvik
      2. reproscript.sh
        0.5 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          What is the intention with the dblook output anyway? Should it
          generate a runnable script or not? If not, why generate the SET
          SCHEMA statements? In the output above, the table is generated with an
          explicit schema name, but the view is not.

          The docs don't mention it, but dblook will also generate GRANT
          privileges statements if SQL authorization is enabled, but if the
          example in the repro is run with SQL authorization enabled, the GRANT
          SELECT will be generated after the CREATE VIEW statement (see
          uploaded reproscript-auth.sh).

          Also, since no connect statements are generated, all created objects
          would be owed by the same user if one attempted to run the output as
          an ij script, so it seems the output from dblook is far from from a
          runnable script.

          I guess that's ok if the docs make that clear, it's just that the SET
          SCHEMA statements give the impression of a runnable script. The dblook
          docs don't seem to discuss SQL authorization, though. Does anyone know
          what is the current state of dblook with respect to these issues?

          Show
          Dag H. Wanvik added a comment - What is the intention with the dblook output anyway? Should it generate a runnable script or not? If not, why generate the SET SCHEMA statements? In the output above, the table is generated with an explicit schema name, but the view is not. The docs don't mention it, but dblook will also generate GRANT privileges statements if SQL authorization is enabled, but if the example in the repro is run with SQL authorization enabled, the GRANT SELECT will be generated after the CREATE VIEW statement (see uploaded reproscript-auth.sh). Also, since no connect statements are generated, all created objects would be owed by the same user if one attempted to run the output as an ij script, so it seems the output from dblook is far from from a runnable script. I guess that's ok if the docs make that clear, it's just that the SET SCHEMA statements give the impression of a runnable script. The dblook docs don't seem to discuss SQL authorization, though. Does anyone know what is the current state of dblook with respect to these issues?
          Hide
          Dag H. Wanvik added a comment -

          I notice that dblook_test.java does not test grant/revoke of privileges.

          Show
          Dag H. Wanvik added a comment - I notice that dblook_test.java does not test grant/revoke of privileges.
          Hide
          Kristian Waagan added a comment -

          Triaged July 3, 2009: Assigned normal urgency.

          Show
          Kristian Waagan added a comment - Triaged July 3, 2009: Assigned normal urgency.
          Hide
          Dag H. Wanvik added a comment -

          Module the authorization parts of the above comments, the weaknesses can be seen back to Derby 10.0.

          Show
          Dag H. Wanvik added a comment - Module the authorization parts of the above comments, the weaknesses can be seen back to Derby 10.0.

            People

            • Assignee:
              Unassigned
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development