Derby
  1. Derby
  2. DERBY-1782

When a privilege is revoked at table level, Derby should only drop objects that require that particular privilege and not all the objects that require some form of privilege on that table.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Deviation from standard

      Description

      Views/triggers/constraints can depend on different privileges at table level. As per SQL specification, if the required privilege is revoked, the dependent objects(Views, triggers and constraints) should get dropped automatically. Derby 10.2 supports this partially. In Derby 10.2, when any granted privilege is revoked at a table level, all the objects(views, triggers and constraints) that require any kind of privilege on that table get dropped.
      eg
      user1
      create table t1
      grant select, update on t1 to user2
      user2
      create view v1 as select * from user1.t1 – this view requires SELECT privilege on user1.t1 and doesn't rely on UPDATE privilege on user1.t1
      user1
      revoke update on t1 from user2 – in Derby 10.2, this ends up dropping the view user2.v1 even though it does not rely on that particular privilege on user1.t1

      Similar behavior exists for column level privileges. When a privilege is revoked at column level, we should only drop objects that require that privilege on that particular column list. In Derby 10.2, we end up dropping all the objects that rely on that privilege type even though they do not require the particular columns on which privilege is getting revoked.
      eg
      user1
      create table t1(c11 int, c12 int, c13 int)
      grant select(c11,c12) on t1 to user2
      user2
      create view v1 as select c11 from user1.t1
      – the view above requires SELECT privilege on column c11 in user1.t1 and doesn't rely on SELECT privilege on column c12 in user1.t1
      user1
      revoke select(c12) on t1 from user2
      – in Derby 10.2, the above revoke ends up dropping the view user2.v1 even though it does not rely on SELECT privilege on column c12 in user1.t1

      Both of these behavior manifest from how the permission related system tables are designed and how Derby keeps the permission dependency information for the objects. Some solutions for this problem are discussed in DERBY-1539.

      1. repro.sql
        0.4 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Mamta A. Satoor added a comment -

          Whoever works on this issue should make sure that the fix for this jira entry has taken care of remaining task of following jira entries and we should add appropriate tests for them
          DERBY-1330
          DERBY-1582
          DERBY-1611
          DERBY-1612
          DERBY-1539

          Show
          Mamta A. Satoor added a comment - Whoever works on this issue should make sure that the fix for this jira entry has taken care of remaining task of following jira entries and we should add appropriate tests for them DERBY-1330 DERBY-1582 DERBY-1611 DERBY-1612 DERBY-1539
          Hide
          Rick Hillegas added a comment -

          Assigning to 10.2 since the ANSI spec requires this behavior.

          Show
          Rick Hillegas added a comment - Assigning to 10.2 since the ANSI spec requires this behavior.
          Hide
          Mamta A. Satoor added a comment -

          Laura is documenting the behavior of this jira entry as part of DERBY-1646. Once someone fixes this issue, we should change the documentation to remove the restriction imposed by this jira entry.

          Show
          Mamta A. Satoor added a comment - Laura is documenting the behavior of this jira entry as part of DERBY-1646 . Once someone fixes this issue, we should change the documentation to remove the restriction imposed by this jira entry.
          Hide
          Rick Hillegas added a comment -

          Downgrading the urgency of this issue. Although it violates the ANSI rules, I don't think this issue should block the release at this point.

          Show
          Rick Hillegas added a comment - Downgrading the urgency of this issue. Although it violates the ANSI rules, I don't think this issue should block the release at this point.
          Hide
          Rick Hillegas added a comment -

          Downgrading the urgency of this issue. Although we are violating the ANSI rules, I don't think that this issue should block the release of 10.2.

          Show
          Rick Hillegas added a comment - Downgrading the urgency of this issue. Although we are violating the ANSI rules, I don't think that this issue should block the release of 10.2.
          Hide
          Rick Hillegas added a comment -

          Moving to 10.2.2.0.

          Show
          Rick Hillegas added a comment - Moving to 10.2.2.0.
          Hide
          Rick Hillegas added a comment -

          Move to 10.2.3.0.

          Show
          Rick Hillegas added a comment - Move to 10.2.3.0.
          Hide
          Andrew McIntyre added a comment -

          Unsetting Fix Version for unassigned issues.

          Show
          Andrew McIntyre added a comment - Unsetting Fix Version for unassigned issues.
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2.

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2.
          Hide
          Knut Anders Hatlen added a comment -

          I wrapped the repro in a script to make it easier to run it. To reproduce the bug:

          $ java -Dderby.database.sqlAuthorization=true org.apache.derby.tools.ij repro.sql
          .
          .
          .
          ij(C2)> set connection c1;
          ij(C1)> revoke update on t1 from user2;
          0 rows inserted/updated/deleted
          WARNING 01501: The view V1 has been dropped.
          ij(C1)> set connection c2;
          ij(C2)> select * from v1;
          ERROR 42X05: Table/View 'V1' does not exist.

          Show
          Knut Anders Hatlen added a comment - I wrapped the repro in a script to make it easier to run it. To reproduce the bug: $ java -Dderby.database.sqlAuthorization=true org.apache.derby.tools.ij repro.sql . . . ij(C2)> set connection c1; ij(C1)> revoke update on t1 from user2; 0 rows inserted/updated/deleted WARNING 01501: The view V1 has been dropped. ij(C1)> set connection c2; ij(C2)> select * from v1; ERROR 42X05: Table/View 'V1' does not exist.

            People

            • Assignee:
              Unassigned
              Reporter:
              Mamta A. Satoor
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development