Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Date: Tue, 05 Jun 2007 19:54:47 +0200
From: Dag.Wanvik@Sun.COM (Dag H. Wanvik)
Subject: Re: Can't upgrade from 10.1 to 10.3
Sender: Dag.Wanvik@Sun.COM
To: derby-dev@db.apache.org
Message-id: <18021.41831.713952.549770@gargle.gargle.HOWL>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
References: <4660BEFC.9090606@gmail.com> <x1oxy7iztyb7.fsf@sun.com>
 <466450A4.8020307@apache.org>

Daniel John Debrunner <djd@apache.org> writes:

> Shouldn't an upgrade from 10.1 change the database owner to the one
> performing the upgrade? That's what DERBY-1544 states.
>
> http://issues.apache.org/jira/browse/DERBY-1544

Yes, and I have not touched that functionality. My comment about "APP"
is only relevant for 10.2 and 10.3; previously the owner of the system
schema was the pseduo user "DBA", sorry, not sure I ever knew that..

There is a bit of a bootstrap issue hidden here: the enforcement of
dbo powers for upgrade relies on the dbo concept being in place, which
really only happened with 10.2. Since we now with patch 8 of
DERBY-2264 don't enforce unless sqlAuthorization is active, the
upgrade from 10.1 is little impacted since upgrade is not restricted
to the database owner because there is no sqlAuthorization in 10.1 -
but there is one see snag I found, see below.
I experimented a bit and found that with patch 8, upgrade from 10.1 ->
10.3 works with or without authentication as follows:

*Without* authentication the new dbo gets to be "APP" or connecting
user if given, and *with* authentication it gets to be the connecting
user, as expected. It will even gladly make a bogus user the database
owner, since authentication happens *after* the upgrade boot (happens
for both 10.2 and 1.3 upgrade; I will file a separate issue for
this..).

SNAG: I found this can happen when upgrading from 10.1 to 10.3:

  - 10.1 database created with authentication on
  - in 10.1 or in soft upgrade mode with 10.3,
    set sqlAuthorization to true by calling SYSCS_SET_DATABASE_PROPERTY.
  - shut down
  - the database now refuses to boot unless hard upgrade is specified.
    That is, it will not do a soft boot as long as the property
    derby.database.sqlAuthorization is true.

    ERROR XCL47: Use of 'sqlAuthorization' requires database to be
                 upgraded from version 10.1 to version 10.2 or later.

    It can still be booted in 10.1, of course.
  - This behavior is the same for 10.1 -> 10.2, btw.

  - Now, in 10.3 with DERBY-2264, when the user has specified hard
    upgrade, in order to verify that the user is dbo and thus allowed
    to upgrade, we boot first *without* the upgrade attribute to check
    credentials (i.e. a soft boot), and if OK, shutdown and reboot
    with hard upgrade.  Unfortunately, the first boot will now fail,
    since soft boot is barred by the sqlAuthorization being set. But
    the user *is* attempting a hard upgrade, so the error message is
    meaningless.

I see two options for handling this:

1)  Make the exception XCL47 specific to the sqlAuthorization feature
    and catch it when the credentials boot fails, then throw a more
    meaningful error message asking the user to unset the
    sqlAuthorization property before attempting upgrade.

    Cons: step back from 10.2, which handled the hard upgrade
          maybe be awkward to have to reboot in older release to fix
          it.
    Pros: simple fix

2)  Make the exception XCL47 specific to the sqlAuthorization feature
    and catch it when the credentials boot fails, realizing that this
    is an upgrade from a release prior to 10.2 (for which dbo powers
    enforcement is not really meaningful); and go ahead with the hard
    upgrade as requested, falling back on checking credentials after
    boot time, as in 10.2.

    Pros: Principle of least surprise; do what user asks.
    Cons: May lead user to think dbo powers checking was indeed
    performed when it was not.

I think I prefer solution 2); the dbo powers checking could be seen as
providing a guarantee for future upgrades, it is not obvious that it
would or should apply to upgrades from older versions anyway.

Thoughts?

Dag