Derby
  1. Derby
  2. DERBY-4829

dblook fails if there is a column privilege on a table whose name contains a quote

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.6.2.1
    • Fix Version/s: 10.7.1.1
    • Component/s: Tools
    • Labels:
      None
    • Issue & fix info:
      Repro attached

      Description

      Create a table with a column privilege like this in ij:

      connect 'jdbc:derby:db;create=true';
      call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true');
      create table """" (x int);
      disconnect;
      connect 'jdbc:derby:db;shutdown=true';
      connect 'jdbc:derby:db';
      grant select on """" to someone;
      exit;

      If you then run dblook -d jdbc:derby:db, you'll see this:

      – ----------------------------------------------
      – GRANT statements for columns
      – ----------------------------------------------

      – Note: At least one unexpected error/warning message was
      – encountered during DDL generation. See dblook.log
      – to review the message(s).

      And in dblook.log you'll find this:

      java.sql.SQLException: Invalid cursor state - no current row.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.newSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.newSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedResultSet.checkOnRow(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedResultSet.getColumn(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedResultSet.getString(Unknown Source)
      at org.apache.derby.impl.tools.dblook.DB_GrantRevoke.mapColumnsToNames(Unknown Source)
      at org.apache.derby.impl.tools.dblook.DB_GrantRevoke.columnPrivStatement(Unknown Source)
      at org.apache.derby.impl.tools.dblook.DB_GrantRevoke.generateColumnPrivs(Unknown Source)
      at org.apache.derby.impl.tools.dblook.DB_GrantRevoke.doAuthorizations(Unknown Source)
      at org.apache.derby.tools.dblook.go(Unknown Source)
      at org.apache.derby.tools.dblook.<init>(Unknown Source)
      at org.apache.derby.tools.dblook.main(Unknown Source)
      Caused by: java.sql.SQLException: Invalid cursor state - no current row.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 16 more
      – **--> DEBUG: Invalid cursor state - no current row.

      1. derby-4829-1a.diff
        15 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Knut Anders Hatlen created issue -
          Hide
          Knut Anders Hatlen added a comment -

          The reason for this is that DB_GrantRevoke.generateColumnPrivs() generates a query:

          String queryCols = "SELECT COLUMNNUMBER, COLUMNNAME " +
          "FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S " +
          "WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID "+
          "and T.TABLENAME = '"tableName"' AND SCHEMANAME = '"+schemaName +
          "' ORDER BY COLUMNNUMBER";

          tableName and schemaName have been pre-processed so that all their double quotes have been expanded to two double quotes. This would be correct if they were used as identifiers in the query, but since they are used as string literals, it's their single quotes that should have been expanded, not their double quotes.

          This query therefore doesn't produce any results. If the table names had contained single quotes, the query would have failed with a syntax error.

          Show
          Knut Anders Hatlen added a comment - The reason for this is that DB_GrantRevoke.generateColumnPrivs() generates a query: String queryCols = "SELECT COLUMNNUMBER, COLUMNNAME " + "FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S " + "WHERE T.TABLEID = C.REFERENCEID and S.SCHEMAID = T.SCHEMAID "+ "and T.TABLENAME = '" tableName "' AND SCHEMANAME = '"+schemaName + "' ORDER BY COLUMNNUMBER"; tableName and schemaName have been pre-processed so that all their double quotes have been expanded to two double quotes. This would be correct if they were used as identifiers in the query, but since they are used as string literals, it's their single quotes that should have been expanded, not their double quotes. This query therefore doesn't produce any results. If the table names had contained single quotes, the query would have failed with a syntax error.
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          Hide
          Knut Anders Hatlen added a comment -

          Made the summary more general since this bug affects both single and double quotes.

          Show
          Knut Anders Hatlen added a comment - Made the summary more general since this bug affects both single and double quotes.
          Knut Anders Hatlen made changes -
          Summary dblook fails if there is a column privilege on a table whose name contains a double quote dblook fails if there is a column privilege on a table whose name contains a quote
          Hide
          Knut Anders Hatlen added a comment -

          And when the query is fixed, the generated GRANT statement that's printed by dblook won't be usable because

          a) column names are not quoted

          b) the table name is missing

          Show
          Knut Anders Hatlen added a comment - And when the query is fixed, the generated GRANT statement that's printed by dblook won't be usable because a) column names are not quoted b) the table name is missing
          Hide
          Knut Anders Hatlen added a comment -

          When I'm adding a test case for this in dblook_test, I also see that the GRANT statements for the column privileges are not correctly excluded if dblook is called with -t. This makes dblook_test print an error message. I think this also affects other GRANT statements produced by dblook, but dblook_test doesn't currently test any privileges, so that's not exposed. I'll just let dblook_test's canons expect these errors for now, and file a separate issue for fixing the exclusion logic for GRANT statements.

          Show
          Knut Anders Hatlen added a comment - When I'm adding a test case for this in dblook_test, I also see that the GRANT statements for the column privileges are not correctly excluded if dblook is called with -t. This makes dblook_test print an error message. I think this also affects other GRANT statements produced by dblook, but dblook_test doesn't currently test any privileges, so that's not exposed. I'll just let dblook_test's canons expect these errors for now, and file a separate issue for fixing the exclusion logic for GRANT statements.
          Hide
          Knut Anders Hatlen added a comment -

          Here's a patch with a fix and a test case.

          Description of the changes:

          • impl/tools/dblook/DB_GrantRevoke.java:

          generateColumnPrivs:
          Use a prepared statement instead of a statement so that the schema name and table name can be parameters. This avoids the trouble with having to quote them as string literals with single quotes. (We still need to quote them as identifiers with double quotes some places, but that was done correctly in the existing code.)

          generateColumnPrivs:
          Also remember to close the statement and result set.

          mapColumnsToNames:
          Quote the column names in the generated GRANT statement.

          columnPrivStatement:
          Add the missing "ON <schema name>.<table name>" part of the generated GRANT statement.

          • tests/tools/dblook_makeDB.sql:

          Created table with special characters in name/columns and granted column privileges. Without the fix, this made the different variants of dblook_test fail because the dblook error log was not empty, and also because the dblook output got some extra statements.

          • functionTests/master/*/dblook_test.out:

          Updated the canons for the dblook_test variants so that they expect the extra output caused by the new database objects added in dblook_makeDB.sql. Some of the canons also got this error text added, because of the bug in the table exclusion logic mentioned in my previous comment:

          +FAILED: to execute cmd from DDL script:
          +GRANT SELECT("'Single' and ""double"" quoted column name") ON "'Single' and ""double"" quoted schema name"."'Single' and ""double"" quoted table name" TO "SOMEONE"
          +Schema ''Single' and "double" quoted schema name' does not exist

          I have only run the following tests yet:

          tools/dblook_test.java
          tools/dblook_test_territory.java
          derbynet/dblook_test_net.java (client and JCC)
          derbynet/dblook_test_net_territory.java (client and JCC)

          I'll run the full regression test suite and come back with the results.

          Show
          Knut Anders Hatlen added a comment - Here's a patch with a fix and a test case. Description of the changes: impl/tools/dblook/DB_GrantRevoke.java: generateColumnPrivs: Use a prepared statement instead of a statement so that the schema name and table name can be parameters. This avoids the trouble with having to quote them as string literals with single quotes. (We still need to quote them as identifiers with double quotes some places, but that was done correctly in the existing code.) generateColumnPrivs: Also remember to close the statement and result set. mapColumnsToNames: Quote the column names in the generated GRANT statement. columnPrivStatement: Add the missing "ON <schema name>.<table name>" part of the generated GRANT statement. tests/tools/dblook_makeDB.sql: Created table with special characters in name/columns and granted column privileges. Without the fix, this made the different variants of dblook_test fail because the dblook error log was not empty, and also because the dblook output got some extra statements. functionTests/master/* /dblook_test .out: Updated the canons for the dblook_test variants so that they expect the extra output caused by the new database objects added in dblook_makeDB.sql. Some of the canons also got this error text added, because of the bug in the table exclusion logic mentioned in my previous comment: +FAILED: to execute cmd from DDL script: +GRANT SELECT("'Single' and ""double"" quoted column name") ON "'Single' and ""double"" quoted schema name"."'Single' and ""double"" quoted table name" TO "SOMEONE" +Schema ''Single' and "double" quoted schema name' does not exist I have only run the following tests yet: tools/dblook_test.java tools/dblook_test_territory.java derbynet/dblook_test_net.java (client and JCC) derbynet/dblook_test_net_territory.java (client and JCC) I'll run the full regression test suite and come back with the results.
          Knut Anders Hatlen made changes -
          Attachment derby-4829-1a.diff [ 12456105 ]
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests ran cleanly.

          Show
          Knut Anders Hatlen added a comment - All the regression tests ran cleanly.
          Knut Anders Hatlen made changes -
          Issue & fix info [Repro attached] [Patch Available, Repro attached]
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-4838 [ DERBY-4838 ]
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 1005362.

          I've logged the bug in the table exclusion logic as DERBY-4838.

          Closing this issue.

          Show
          Knut Anders Hatlen added a comment - Committed revision 1005362. I've logged the bug in the table exclusion logic as DERBY-4838 . Closing this issue.
          Knut Anders Hatlen made changes -
          Status In Progress [ 3 ] Closed [ 6 ]
          Issue & fix info [Repro attached, Patch Available] [Repro attached]
          Fix Version/s 10.7.0.0 [ 12314971 ]
          Resolution Fixed [ 1 ]
          Rick Hillegas made changes -
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Fix Version/s 10.7.1.0 [ 12314971 ]
          Gavin made changes -
          Workflow jira [ 12521991 ] Default workflow, editable Closed status [ 12800475 ]

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development