Derby
  1. Derby
  2. DERBY-5395

By default, only the DBO should be allowed to run several of the diagnostic VTIs.

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed
    • Bug behavior facts:
      Security

      Description

      Only the DBO should be allowed to run the following VTIs:

      syscs_diag.statement_cache
      syscs_diag.transaction_table
      syscs_diag.error_log_reader( )
      syscs_diag.statement_duration()

      1. SafeCacheViewer.java
        0.4 kB
        Rick Hillegas
      2. releaseNote.html
        5 kB
        Rick Hillegas
      3. derby-5395-01-ac-protectVTIs.diff
        11 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          I can imagine two approaches to fixing this issue:

          1) Easy but inflexible – In this approach, Derby would raise an error if someone other than the DBO tried to use these VTIs. For instance, the constructors for the underlying VTIs could raise an error if the current user wasn't the DBO. This could be implemented for the 10.8.2 maintenance release and could be backported to older branches. However, it would not be possible to grant other users SELECT privilege on statement_cache and transaction_table and it would not be possible to grant other users EXECUTE privilege on error_log_reader() and statement_duration().

          2) Involved but flexible – In this approach, we would re-model these vtis as table functions. The error_log_reader and statement_duration vtis would be re-modelled as table functions and statement_cache and transaction_table would be remodelled as views on table functions. Corresponding metadata tuples would be stuffed into SYSALIASES, SYSVIEWS, and SYSTABLEPERMS. This would let the DBO grant EXECUTE privilege on the table functions. However, the metadata changes would mean that we could not implement this solution in a maintenance release like 10.8.2. and we could not backport the fix to older branches.

          I am leaning toward an incremental, hybrid approach: implement (1) in the 10.8.2 timeframe, close this issue, and create a new issue for the work on (2). I would be happy to do (1). We could consider implementing (2) if users clamor for it.

          Thoughts?

          Show
          Rick Hillegas added a comment - I can imagine two approaches to fixing this issue: 1) Easy but inflexible – In this approach, Derby would raise an error if someone other than the DBO tried to use these VTIs. For instance, the constructors for the underlying VTIs could raise an error if the current user wasn't the DBO. This could be implemented for the 10.8.2 maintenance release and could be backported to older branches. However, it would not be possible to grant other users SELECT privilege on statement_cache and transaction_table and it would not be possible to grant other users EXECUTE privilege on error_log_reader() and statement_duration(). 2) Involved but flexible – In this approach, we would re-model these vtis as table functions. The error_log_reader and statement_duration vtis would be re-modelled as table functions and statement_cache and transaction_table would be remodelled as views on table functions. Corresponding metadata tuples would be stuffed into SYSALIASES, SYSVIEWS, and SYSTABLEPERMS. This would let the DBO grant EXECUTE privilege on the table functions. However, the metadata changes would mean that we could not implement this solution in a maintenance release like 10.8.2. and we could not backport the fix to older branches. I am leaning toward an incremental, hybrid approach: implement (1) in the 10.8.2 timeframe, close this issue, and create a new issue for the work on (2). I would be happy to do (1). We could consider implementing (2) if users clamor for it. Thoughts?
          Hide
          Rick Hillegas added a comment -

          Attaching derby-5395-01-ac-protectVTIs.diff. This patch implements approach (1), raising an error at instantiation time if these VTIs are invoked by someone other than the DBO when authorization is turned on. I have written a regression test but need to run the full suite.

          The regression test I wrote for this fix revealed that the StatementDuration and ErrorLogReader VTIs were reading a system property outside a privileged block. I wrapped those reads in a privileged block as part of this patch.

          Touches the following files:

          -------------------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java
          A java/engine/org/apache/derby/diag/DiagUtil.java

          Logic to raise an exception if authorization is enabled and the current user isn't a DBO.

          -------------------

          M java/engine/org/apache/derby/diag/StatementCache.java
          M java/engine/org/apache/derby/diag/StatementDuration.java
          M java/engine/org/apache/derby/diag/TransactionTable.java
          M java/engine/org/apache/derby/diag/ErrorLogReader.java

          Wires that check into the VTI constructors.

          -------------------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/DBOAccessTest.java

          New regression test for this behavior.

          Show
          Rick Hillegas added a comment - Attaching derby-5395-01-ac-protectVTIs.diff. This patch implements approach (1), raising an error at instantiation time if these VTIs are invoked by someone other than the DBO when authorization is turned on. I have written a regression test but need to run the full suite. The regression test I wrote for this fix revealed that the StatementDuration and ErrorLogReader VTIs were reading a system property outside a privileged block. I wrapped those reads in a privileged block as part of this patch. Touches the following files: ------------------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java A java/engine/org/apache/derby/diag/DiagUtil.java Logic to raise an exception if authorization is enabled and the current user isn't a DBO. ------------------- M java/engine/org/apache/derby/diag/StatementCache.java M java/engine/org/apache/derby/diag/StatementDuration.java M java/engine/org/apache/derby/diag/TransactionTable.java M java/engine/org/apache/derby/diag/ErrorLogReader.java Wires that check into the VTI constructors. ------------------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/DBOAccessTest.java New regression test for this behavior.
          Hide
          Rick Hillegas added a comment -

          Attaching SafeCacheViewer.java. This is a table function which selects the safe columns from the statement cache vti. If the DBO registers this table function with definer's rights and grants EXECUTE privilege to PUBLIC, then anyone can view the safe columns of the statement cache. This technique can be used to grant other users privilege to view the safe bits of the diagnostic vtis whose access will be controlled when the patch is committed. Here's a script which shows this technique in action:

          ij version 10.9
          ij> connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword' as admin_conn;
          ij> create function safeCacheViewer()
          returns table
          (
          id char( 36 ),
          schemaName varchar( 128 ),
          valid boolean,
          compiled_at timestamp
          )
          language java parameter style derby_jdbc_result_set reads sql data
          external security definer
          external name 'SafeCacheViewer.safeCacheViewer';
          0 rows inserted/updated/deleted
          ij> grant execute on function safeCacheViewer to public;
          0 rows inserted/updated/deleted
          ij> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth_conn;
          ij(RUTH_CONN)> – fails permissions hurdle
          select * from syscs_diag.statement_cache;
          ERROR 4251D: Only the database owner can view this data.
          ij(RUTH_CONN)> – succeeds
          select * from table( test_dbo.safeCacheViewer() ) s;
          ID |SCHEMANAME |VALID|COMPILED_AT
          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          ace4c0a3-0132-211b-dc8c-0000042be988|RUTH |true |NULL
          4d3680a5-0132-211b-dc8c-0000042be988|TEST_DBO |true |NULL
          341cc09e-0132-211b-dc8c-0000042be988|TEST_DBO |true |NULL
          0b5b0099-0132-211b-dc8c-0000042be988|TEST_DBO |true |NULL

          4 rows selected

          Show
          Rick Hillegas added a comment - Attaching SafeCacheViewer.java. This is a table function which selects the safe columns from the statement cache vti. If the DBO registers this table function with definer's rights and grants EXECUTE privilege to PUBLIC, then anyone can view the safe columns of the statement cache. This technique can be used to grant other users privilege to view the safe bits of the diagnostic vtis whose access will be controlled when the patch is committed. Here's a script which shows this technique in action: ij version 10.9 ij> connect 'jdbc:derby:memory:db;create=true;user=test_dbo;password=test_dbopassword' as admin_conn; ij> create function safeCacheViewer() returns table ( id char( 36 ), schemaName varchar( 128 ), valid boolean, compiled_at timestamp ) language java parameter style derby_jdbc_result_set reads sql data external security definer external name 'SafeCacheViewer.safeCacheViewer'; 0 rows inserted/updated/deleted ij> grant execute on function safeCacheViewer to public; 0 rows inserted/updated/deleted ij> connect 'jdbc:derby:memory:db;user=ruth;password=ruthpassword' as ruth_conn; ij(RUTH_CONN)> – fails permissions hurdle select * from syscs_diag.statement_cache; ERROR 4251D: Only the database owner can view this data. ij(RUTH_CONN)> – succeeds select * from table( test_dbo.safeCacheViewer() ) s; ID |SCHEMANAME |VALID|COMPILED_AT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ace4c0a3-0132-211b-dc8c-0000042be988|RUTH |true |NULL 4d3680a5-0132-211b-dc8c-0000042be988|TEST_DBO |true |NULL 341cc09e-0132-211b-dc8c-0000042be988|TEST_DBO |true |NULL 0b5b0099-0132-211b-dc8c-0000042be988|TEST_DBO |true |NULL 4 rows selected
          Hide
          Rick Hillegas added a comment -

          Regression tests passed cleanly for me on derby-5395-01-ac-protectVTIs.diff. Committed at subversion revision 1163740.

          Show
          Rick Hillegas added a comment - Regression tests passed cleanly for me on derby-5395-01-ac-protectVTIs.diff. Committed at subversion revision 1163740.
          Hide
          Rick Hillegas added a comment -

          Attaching the first rev of a release note for this issue.

          Show
          Rick Hillegas added a comment - Attaching the first rev of a release note for this issue.
          Hide
          Rick Hillegas added a comment -

          Ported 1163740 to the following branches:

          10.8 at subversion revision 1164198

          10.7 at subversion revision 1164200

          10.6 at subversion revision 1164214

          10.5 at subversion revision 1164225

          I did not backport this fix further than 10.5 because it became difficult to reconcile the newly added test with the old test machinery.

          Show
          Rick Hillegas added a comment - Ported 1163740 to the following branches: 10.8 at subversion revision 1164198 10.7 at subversion revision 1164200 10.6 at subversion revision 1164214 10.5 at subversion revision 1164225 I did not backport this fix further than 10.5 because it became difficult to reconcile the newly added test with the old test machinery.
          Hide
          Rick Hillegas added a comment -

          The simple solution has been ported as far back as the 10.5 branch. I do not intend to do any more work on this issue. I will create a related issue in case someone wants to implement the more complicated solution.

          Show
          Rick Hillegas added a comment - The simple solution has been ported as far back as the 10.5 branch. I do not intend to do any more work on this issue. I will create a related issue in case someone wants to implement the more complicated solution.
          Hide
          Kim Haase added a comment -

          I believe the Reference Manual topic "SYSCS_DIAG diagnostic tables and functions" should be updated to include this new information for the four functions named in this issue. I'll file a JIRA for this.

          Show
          Kim Haase added a comment - I believe the Reference Manual topic "SYSCS_DIAG diagnostic tables and functions" should be updated to include this new information for the four functions named in this issue. I'll file a JIRA for this.

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development