Derby
  1. Derby
  2. DERBY-4551

Allow database user to execute stored procedures with same permissions as database owner and/or routine definer

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • 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
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None

      Description

      Curretnly there is no way to hide data and database structure in embedded derby from the end user.

      One way to accomplish the above requirement is as follows:
      1. Create encrypted database so data is protected
      2. Enable authentication and sql authorization in database
      3. Create two users, dbUser and dbOwner
      4. Store application logic as stored procedure in the databse so dbUser does not know what tables are accecced by the application logic, thus hiding table structure
      5. Revoke select permission from dbUser so he cannot describe tables thus protecting table structures
      6. Give only Execute permissions on stored procedures to dbUser

      The above steps will ensure that data and data structure is hidden when application is delivered to end user.

      The problem is, if user does not have select permission, the stored procedures will not execute. So I am requesting the following enhancement to Derby:

      If dbOwner has given Execure permission to stored procecure to a dbUser, then allow stored procedure to execute even if the dbUser has no select permission.

      In otherwords, When dbUser calls stored procedure, database will use dbOwners authorization to execute stored procedure rather than dbUsers.

      This may be implemented by creating new permission called RunAsDbOwner.

      DbOwner can then grant permission to dbUser to execute a stored procedure with RunAsDbOwner.

      If this is implemented, applications can be created which will truely hide the database structure and data from end users. Database will behave as a blackbox with only in/out data exposed in stored procedures.

      1. reproTH-derby-4551.7z
        606 kB
        Thomas Hill
      2. derby4551-trial.diff
        4 kB
        Dag H. Wanvik
      3. derby-4551-followup-1b.stat
        0.2 kB
        Dag H. Wanvik
      4. derby-4551-followup-1b.diff
        9 kB
        Dag H. Wanvik
      5. derby-4551-followup-1a.stat
        0.1 kB
        Dag H. Wanvik
      6. derby-4551-followup-1a.diff
        5 kB
        Dag H. Wanvik
      7. derby-4551-4.stat
        4 kB
        Dag H. Wanvik
      8. derby-4551-4.diff
        92 kB
        Dag H. Wanvik
      9. derby-4551-3b.stat
        4 kB
        Dag H. Wanvik
      10. derby-4551-3b.diff
        91 kB
        Dag H. Wanvik
      11. derby-4551-3.stat
        3 kB
        Dag H. Wanvik
      12. derby-4551-3.diff
        87 kB
        Dag H. Wanvik
      13. derby-4551-2.stat
        3 kB
        Dag H. Wanvik
      14. derby-4551-2.diff
        83 kB
        Dag H. Wanvik
      15. derby-4551-1.txt
        4 kB
        Dag H. Wanvik
      16. derby-4551-1.stat
        3 kB
        Dag H. Wanvik
      17. derby-4551-1.diff
        76 kB
        Dag H. Wanvik
      18. definers_rights.html
        18 kB
        Dag H. Wanvik
      19. definers_rights.html
        22 kB
        Dag H. Wanvik
      20. definers_rights.html
        22 kB
        Dag H. Wanvik
      21. definers_rights.html
        23 kB
        Dag H. Wanvik
      22. definers_rights.html
        25 kB
        Dag H. Wanvik
      23. definers_rights.html
        26 kB
        Dag H. Wanvik
      24. definers_rights.html
        26 kB
        Dag H. Wanvik
      25. definers_rights_typos-1.diff
        3 kB
        Kristian Waagan

        Issue Links

          Activity

          Tushar Kale created issue -
          Dag H. Wanvik made changes -
          Field Original Value New Value
          Summary Allow database user to execute stored procedures with same permissions as database owner Allow database user to execute stored procedures with same permissions as database owner and/or routine definer
          Urgency Urgent
          Component/s SQL [ 11408 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444429 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444429 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444431 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444431 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444433 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444495 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444505 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12444809 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12445171 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12445201 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-1.diff [ 12445210 ]
          Attachment derby-4551-1.stat [ 12445211 ]
          Attachment derby-4551-1.txt [ 12445212 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          Assignee Dag H. Wanvik [ dagw ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-1.txt [ 12445212 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-1.txt [ 12445242 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-2.diff [ 12445583 ]
          Attachment derby-4551-2.stat [ 12445584 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-3.diff [ 12445659 ]
          Attachment derby-4551-3.stat [ 12445660 ]
          Dag H. Wanvik made changes -
          Link This issue requires DERBY-4680 [ DERBY-4680 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-3b.diff [ 12445692 ]
          Attachment derby-4551-3b.stat [ 12445693 ]
          Kristian Waagan made changes -
          Attachment definers_rights_typos-1.diff [ 12446336 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12446442 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12446442 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12446443 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12446443 ]
          Dag H. Wanvik made changes -
          Attachment definers_rights.html [ 12446444 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-4.diff [ 12446447 ]
          Attachment derby-4551-4.stat [ 12446448 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available]
          Thomas Hill made changes -
          Attachment reproTH-derby-4551.7z [ 12450392 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-followup-1a.diff [ 12454981 ]
          Attachment derby-4551-followup-1a.stat [ 12454982 ]
          Dag H. Wanvik made changes -
          Link This issue is related to DERBY-3327 [ DERBY-3327 ]
          Dag H. Wanvik made changes -
          Attachment derby-4551-followup-1b.diff [ 12455001 ]
          Attachment derby-4551-followup-1b.stat [ 12455002 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          Affects Version/s 10.6.1.0 [ 12313727 ]
          Affects Version/s 10.5.2.0 [ 12314116 ]
          Affects Version/s 10.5.1.1 [ 12313771 ]
          Affects Version/s 10.4.2.0 [ 12313345 ]
          Affects Version/s 10.4.1.3 [ 12313111 ]
          Affects Version/s 10.3.3.0 [ 12313142 ]
          Affects Version/s 10.3.2.1 [ 12312876 ]
          Affects Version/s 10.3.1.4 [ 12312590 ]
          Affects Version/s 10.2.2.0 [ 12312027 ]
          Affects Version/s 10.2.1.6 [ 11187 ]
          Affects Version/s 10.1.3.1 [ 12311953 ]
          Affects Version/s 10.1.2.1 [ 12310615 ]
          Affects Version/s 10.1.1.0 [ 10993 ]
          Affects Version/s 10.0.2.1 [ 10991 ]
          Affects Version/s 10.0.2.0 [ 10920 ]
          Dag H. Wanvik made changes -
          Attachment derby4551-trial.diff [ 12455050 ]
          Dag H. Wanvik made changes -
          Fix Version/s 10.7.0.0 [ 12314971 ]
          Issue & fix info [Patch Available]
          Dag H. Wanvik made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Resolved [ 5 ]
          Dag H. Wanvik made changes -
          Comment [ Cf. current plans here: http://wiki.apache.org/db-derby/DerbyTenSevenOneRelease ]
          Rick Hillegas made changes -
          Fix Version/s 10.7.1.1 [ 12315564 ]
          Fix Version/s 10.7.1.0 [ 12314971 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12499209 ] Default workflow, editable Closed status [ 12802737 ]

            People

            • Assignee:
              Dag H. Wanvik
              Reporter:
              Tushar Kale
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development