Upgrading an old database to use SQL standard authorization

An old, unprotected database can be shielded with authentication and SQL authorization later on.

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 the section on Working with 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--see the section on 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 shutdown 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:

Related concepts
Using SQL standard authorization
Privileges on views, triggers, and constraints
Using SQL roles
Related reference
SQL standard authorization exceptions