Upgrading an old database to use SQL standard authorization

Upgrading authentication and authorization

To protect a single-user database and convert it to a shared, multi-user database, simply enable authentication and SQL authorization. To do this, first turn on user authentication as described in Configuring user authentication. Make sure that you supply login credentials for the Database Owner. In most single-user databases, the Database Owner is APP. However, the Database Owner could be some other user if the original database creation URL specified a user name; for details, see Database Owner. If you are unsure about who owns the database, run the following query:

select authorizationid from sys.sysschemas where schemaname = 'SYS'

After enabling user authentication, turn on SQL authorization. To do this, connect to the database as the Database Owner and issue the following command:

call syscs_util.syscs_set_database_property( 'derby.database.sqlAuthorization', 'true' )

Now shut down the database to activate the new value of derby.database.sqlAuthorization. The next time you boot the database, it will be protected by authentication and SQL authorization.

Behavior of upgraded databases

You will notice the following behavior changes in your upgraded database:

• Data: Users can access data in their own schemas. However, users cannot access data in schemas owned by other users. In particular, other users cannot access data in schemas belonging to the Database Owner. The Database Owner may need to GRANT access to that data.
• Database Maintenance: In a single-user database, anyone can run maintenance procedures to backup/restore and import/export data. In the upgraded multi-user database, only the Database Owner can perform these sensitive operations.