Derby
  1. Derby
  2. DERBY-5299

Document what you should expect to see if you enable authentication/authorization on a database which was created without those safeguards.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: Documentation
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Patch Available
    • Bug behavior facts:
      Security

      Description

      It is possible to deploy the first version of your Derby-powered application without enabling authentication and authorization. In a later version of your application, you may want to boost security by enabling these features. It would be good to tell users what they should expect when they enable authentication/authorization on a legacy database.

      1. derby-5299-01-ab-securingOldDatabase.tar
        40 kB
        Rick Hillegas
      2. derby-5299-01-ab-securingOldDatabase.diff
        5 kB
        Rick Hillegas
      3. derby-5299-01-aa-securingOldDatabase.tar
        40 kB
        Rick Hillegas
      4. derby-5299-01-aa-securingOldDatabase.diff
        5 kB
        Rick Hillegas
      5. 5299_withAuth.sql
        2 kB
        Rick Hillegas
      6. 5299_setup.sql
        0.4 kB
        Rick Hillegas

        Activity

        Hide
        Rick Hillegas added a comment -

        I will describe the expected behavior on this JIRA.

        Show
        Rick Hillegas added a comment - I will describe the expected behavior on this JIRA.
        Hide
        Rick Hillegas added a comment -

        Attaching 5299_setup.sql and 5299_withAuth.sql. These scripts demonstrate what happens when you create a database without enabling authentication/authorization and then what happens when you enable those features later on.

        To see what happens, run the first script through ij without setting any system properties. After that, run the second script through ij, setting the following system properties:

        -Dderby.connection.requireAuthentication=true \
        -Dderby.authentication.provider=BUILTIN \
        -Dderby.user.admin=adminpassword \
        -Dderby.user.alice=alicepassword \
        -Dderby.user.ruth=ruthpassword \
        -Dderby.user.test_dbo=test_dbopassword \
        -Dderby.user.app=apppassword \
        -Dderby.database.sqlAuthorization=true \

        You will see this:

        1) In a database created without authentication/authorization and where a user name is not specified in the connection URL, the default user is APP. The APP user owns all of the system schemas in the database in addition to owning its own user schema named APP. The APP user can run all system routines.

        2) The second script connects to the database as username APP, supplying a password. The first thing that this user does is turn on authorization by setting the database property derby.database.sqlAuthorization to true. For this setting to take effect, the database must be brought down and back up again.

        3) After that, users can't access data in one another's schemas unless they are granted privilege. In addition, sensitive system functions/procedures can only be run by the APP user. APP is the database owner.

        4) Derby does not provide a way to change the database owner.

        This behavior is largely described in the fifth version of the functional spec attached to DERBY-464. That spec also describes which system routines can be run by all users and which system routines can only be run by the database owner. I don't know where in our user documentation we describe how enabling authorization restricts the running of system routines.

        Show
        Rick Hillegas added a comment - Attaching 5299_setup.sql and 5299_withAuth.sql. These scripts demonstrate what happens when you create a database without enabling authentication/authorization and then what happens when you enable those features later on. To see what happens, run the first script through ij without setting any system properties. After that, run the second script through ij, setting the following system properties: -Dderby.connection.requireAuthentication=true \ -Dderby.authentication.provider=BUILTIN \ -Dderby.user.admin=adminpassword \ -Dderby.user.alice=alicepassword \ -Dderby.user.ruth=ruthpassword \ -Dderby.user.test_dbo=test_dbopassword \ -Dderby.user.app=apppassword \ -Dderby.database.sqlAuthorization=true \ You will see this: 1) In a database created without authentication/authorization and where a user name is not specified in the connection URL, the default user is APP. The APP user owns all of the system schemas in the database in addition to owning its own user schema named APP. The APP user can run all system routines. 2) The second script connects to the database as username APP, supplying a password. The first thing that this user does is turn on authorization by setting the database property derby.database.sqlAuthorization to true. For this setting to take effect, the database must be brought down and back up again. 3) After that, users can't access data in one another's schemas unless they are granted privilege. In addition, sensitive system functions/procedures can only be run by the APP user. APP is the database owner. 4) Derby does not provide a way to change the database owner. This behavior is largely described in the fifth version of the functional spec attached to DERBY-464 . That spec also describes which system routines can be run by all users and which system routines can only be run by the database owner. I don't know where in our user documentation we describe how enabling authorization restricts the running of system routines.
        Hide
        Rick Hillegas added a comment -

        Attaching a derby-5299-01-aa-securingOldDatabase.diff patch and corresponding tarball of generated html pages: derby-5299-01-aa-securingOldDatabase.tar. This patch adds a new section to the Developer's Guide, nested under the existing "Setting the SQL standard authorization mode" topic. The new section is titled "Upgrading an old database to use SQL standard authorization". The new section explains how to enable authentication and SQL authorization on an old, unsecured database. The new section also lists some behavioral differences which you will notice after upgrading your database this way.

        Touches the following files:

        M src/devguide/derbydev.ditamap
        A src/devguide/cdevcsecuresqlauthupgrade.dita

        Show
        Rick Hillegas added a comment - Attaching a derby-5299-01-aa-securingOldDatabase.diff patch and corresponding tarball of generated html pages: derby-5299-01-aa-securingOldDatabase.tar. This patch adds a new section to the Developer's Guide, nested under the existing "Setting the SQL standard authorization mode" topic. The new section is titled "Upgrading an old database to use SQL standard authorization". The new section explains how to enable authentication and SQL authorization on an old, unsecured database. The new section also lists some behavioral differences which you will notice after upgrading your database this way. Touches the following files: M src/devguide/derbydev.ditamap A src/devguide/cdevcsecuresqlauthupgrade.dita
        Hide
        Knut Anders Hatlen added a comment -

        The new topic looks good to me. Just one question: Most places in the topic we're saying that schemas are owned by users, except one place where we're talking about tables belonging to a user: "In particular, other users cannot access data in tables belonging to the database owner." Should it say "schemas" here too?

        Show
        Knut Anders Hatlen added a comment - The new topic looks good to me. Just one question: Most places in the topic we're saying that schemas are owned by users, except one place where we're talking about tables belonging to a user: "In particular, other users cannot access data in tables belonging to the database owner." Should it say "schemas" here too?
        Hide
        Rick Hillegas added a comment -

        Thanks for the quick review, Knut. Attaching a second rev of the patch, which hopefully clarifies the point you raised. Committed at subversion revision 1141084.

        Show
        Rick Hillegas added a comment - Thanks for the quick review, Knut. Attaching a second rev of the patch, which hopefully clarifies the point you raised. Committed at subversion revision 1141084.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Rick. The updated patch looks good.

        Show
        Knut Anders Hatlen added a comment - Thanks, Rick. The updated patch looks good.
        Hide
        Rick Hillegas added a comment -

        Ported 1141084 from trunk docs to 10.8 docs at subversion revision 1141126.

        Show
        Rick Hillegas added a comment - Ported 1141084 from trunk docs to 10.8 docs at subversion revision 1141126.

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development