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:
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.