Uploaded image for project: '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
    • Status: Closed
    • Priority: 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.

        Attachments

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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: