Setting User Authorization

Typically, you configure user authorization for a particular database. However, Derby also allows you to configure user authorization for the system, which is useful during development and for specifying a secure default access for all databases in the system.

To control access to a particular database, set database-level properties that specify which users have full (read-write) access to the database and which users have read-only access to the database. Users not specified by either property inherit the default access for the database (none, read-only, or full read-write access). When not explicitly set, the default access for a database is full (read-write) access.

To specify the default access mode for the database, use the derby.database.defaultConnectionMode property. You can set the property to the following values:

• noAccess
• readOnlyAccess
• fullAccess (the default)

Derby validates the authorization configuration properties when users set them. It raises an exception if a user attempts to set the properties to invalid values (see Exceptions).

To specify which particular users have full (read-write) access to a database, use the derby.database.fullAccessUsers property. For example:

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.fullAccessUsers', 'sa,mary')

To specify which particular users have read-only access to a database, use the derby.database.readOnlyAccessUsers property. For example:

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.readOnlyAccessUsers', 'guest,"Fred!"')

For these properties, you specify users as a comma-separated list (no spaces between the comma and the next user).

For users not specified with either property the access is specified by the derby.database.defaultConnectionMode property.

Note:

It is possible to configure a database so that it cannot be changed (or even accessed) using the derby.database.defaultConnectionMode property. If you set this property to noAccess or readOnlyAccess, be sure to allow at least one user full access.

Note:

Derby provides a utility to make it easier to create and maintain the lists of read-only and full-access users. This utility is org.apache.derby.database.UserUtility. The following example shows how to use the utility to add a full access user:

CALL org.apache.derby.database.UserUtility.add("mary",
        UserUtility.FULL_ACCESS_PERMISSION);

See the Javadoc for the utility or Examples for more details.

All the authorization properties are set for a connection when it is created. Changing any of the authorization properties does not affect existing connections. However, all future connections are affected by the change.

For more information about authorization identifiers, see Users and Authorization Identifiers.

If a user is not authorized to connect to the database specified in the connection request, SQLException 04501 is raised.

If a user with readOnlyAccess attempts to write to a database, SQLException 08004 - connection refused is raised.