Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3200

Developer's Guide: Add examples showing use of SQL authorization with user authentication

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: 10.5.1.1
    • Component/s: Documentation
    • Labels:
      None

      Description

      This is the followup to DERBY-1823 that Francois Orsini suggested.

      I've been experimenting and reading the Developer's Guide section on SQL authorization (User authorizations, cdevcsecure36595).

      It appears that the only use of SQL authorization mode is to restrict user access, not to expand it.

      For example, if you set the default connection mode to noAccess, a user with fullAccess can't grant any privileges to a user with noAccess. And presumably if the default connection mode is readOnlyAccess, a user with fullAccess can't grant any privileges beyond SELECT, which the user has anyway.

      Only if the default connection mode is fullAccess is SQL authorization mode meaningful. That means that a fullAccess user can use GRANT to restrict another user's privileges on a particular database that the user owns.

      I'm running into a problem at the end, though. At the beginning of the program, as nobody in particular, I was able to create several users, some of them with full access. But at the end of the program, it seems that even a user with full access isn't allowed to turn off those database properties:

      Message: User 'MARY' does not have execute permission on PROCEDURE 'SYSCS_UTIL'.'SYSCS_SET_DATABASE_PROPERTY'.

      This seems a bit extreme. I know that with SQL authorization on, "the ability to read from or write to database objects is further restricted to the owner of the database objects." But the ability to execute built-in system procedures? Can I log in as SYSCS_UTIL? How?

      I realize that having access to SYSCS_SET_DATABASE_PROPERTY would allow me to in effect delete myself – but that's essentially what I do at the end of the program that sets derby.connection.requireAuthentication but not derby.database.sqlAuthorization.

      The documentation does say that once you have turned on SQL authorization, you can't turn it off. But it doesn't say that you can't turn anything else off, either!

      I'll attach the program I've been using. Most of the stacktraces are expected, but I'm stumped by that last one.

        Attachments

        1. sqlauthembedded.txt
          8 kB
          Camilla Haase
        2. sqlauthembedded.txt
          8 kB
          Camilla Haase
        3. sqlauthclientshutdown.txt
          10 kB
          Camilla Haase
        4. sqlauthclient.txt
          5 kB
          Camilla Haase
        5. rdevcsecuresqlauthembeddedex.dita
          19 kB
          Camilla Haase
        6. DERBY-3200-8.zip
          22 kB
          Camilla Haase
        7. DERBY-3200-8.stat
          0.2 kB
          Camilla Haase
        8. DERBY-3200-8.diff
          15 kB
          Camilla Haase
        9. DERBY-3200-7.zip
          20 kB
          Camilla Haase
        10. DERBY-3200-7.stat
          0.2 kB
          Camilla Haase
        11. DERBY-3200-7.diff
          12 kB
          Camilla Haase
        12. DERBY-3200-6.zip
          22 kB
          Camilla Haase
        13. DERBY-3200-6.diff
          91 kB
          Camilla Haase
        14. DERBY-3200-5.zip
          21 kB
          Camilla Haase
        15. DERBY-3200-5.diff
          80 kB
          Camilla Haase
        16. DERBY-3200-4.zip
          22 kB
          Camilla Haase
        17. DERBY-3200-4.diff
          77 kB
          Camilla Haase
        18. DERBY-3200-3.zip
          21 kB
          Camilla Haase
        19. DERBY-3200-3.diff
          74 kB
          Camilla Haase
        20. DERBY-3200-2.zip
          20 kB
          Camilla Haase
        21. DERBY-3200-2.diff
          59 kB
          Camilla Haase
        22. DERBY-3200.zip
          20 kB
          Camilla Haase
        23. DERBY-3200.stat
          0.3 kB
          Camilla Haase
        24. DERBY-3200.diff
          60 kB
          Camilla Haase
        25. AuthExampleEmbeddedSQLAuth.java.dhw
          16 kB
          Dag H. Wanvik
        26. AuthExampleEmbeddedSQLAuth.java
          15 kB
          Camilla Haase
        27. AuthExampleEmbeddedSQLAuth.java
          15 kB
          Camilla Haase
        28. AuthExampleEmbeddedSQLAuth.java
          16 kB
          Camilla Haase
        29. AuthExampleEmbeddedSQLAuth.java
          16 kB
          Camilla Haase
        30. AuthExampleEmbeddedSQLAuth.java
          16 kB
          Camilla Haase
        31. AuthExampleEmbeddedSQLAuth.java
          15 kB
          Camilla Haase
        32. AuthExampleEmbeddedSQLAuth.java
          15 kB
          Camilla Haase
        33. AuthExampleEmbedded-dhw.java
          12 kB
          Dag H. Wanvik
        34. AuthExampleEmbedded.java
          13 kB
          Camilla Haase
        35. AuthExampleEmbedded.java
          13 kB
          Camilla Haase
        36. AuthExampleEmbedded.java
          13 kB
          Camilla Haase
        37. AuthExampleEmbedded.java
          11 kB
          Camilla Haase
        38. AuthExampleEmbedded.java
          11 kB
          Camilla Haase
        39. AuthExampleEmbedded_dhw.java
          12 kB
          Camilla Haase
        40. AuthExampleClientSQLAuth2.java
          12 kB
          Camilla Haase
        41. AuthExampleClientSQLAuth2.java
          12 kB
          Dag H. Wanvik
        42. AuthExampleClientSQLAuth2.java
          10 kB
          Camilla Haase
        43. AuthExampleClientSQLAuth2.java
          10 kB
          Camilla Haase
        44. AuthExampleClientSQLAuth2.java
          11 kB
          Camilla Haase
        45. AuthExampleClientSQLAuth2.java
          11 kB
          Camilla Haase
        46. AuthExampleClientSQLAuth2.java
          11 kB
          Camilla Haase
        47. AuthExampleClientSQLAuth2.java
          10 kB
          Camilla Haase
        48. AuthExampleClientSQLAuth2.java
          10 kB
          Camilla Haase
        49. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        50. AuthExampleClientSQLAuth1.java
          7 kB
          Dag H. Wanvik
        51. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        52. AuthExampleClientSQLAuth1.java
          6 kB
          Camilla Haase
        53. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        54. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        55. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        56. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        57. AuthExampleClientSQLAuth1.java
          7 kB
          Camilla Haase
        58. AuthExampleClient2.java
          8 kB
          Camilla Haase
        59. AuthExampleClient2.java
          8 kB
          Camilla Haase
        60. AuthExampleClient2.java
          8 kB
          Camilla Haase
        61. AuthExampleClient2.java
          6 kB
          Camilla Haase
        62. AuthExampleClient2.java
          6 kB
          Camilla Haase
        63. AuthExampleClient1.java
          6 kB
          Camilla Haase
        64. AuthExampleClient1.java
          7 kB
          Camilla Haase
        65. AuthExampleClient1.java
          7 kB
          Camilla Haase
        66. AuthExampleClient1.java
          6 kB
          Camilla Haase
        67. AuthExampleClient1.java
          7 kB
          Camilla Haase
        68. auth2.log
          5 kB
          Dag H. Wanvik

        Issue Links

          Activity

            People

            • Assignee:
              chaase3 Camilla Haase
              Reporter:
              chaase3 Camilla Haase

              Dates

              • Created:
                Updated:
                Resolved:

                Issue deployment