User authentication example in a client/server environment

In this example, Derby is running in a user-designed application server. Derby provides the user authentication, not the application server. The server is running in a secure environment, the application server encrypts the passwords, and a database administrator is available. The administrator configures security using system-level properties in the derby.properties file and has protected this file with operating system tools. Derby connects to an existing LDAP directory service within the enterprise to authenticate users.

The default access mode for all databases is set to fullAccess (the default).

The derby.properties file for the server includes the following entries:

# turn on user authentication
derby.connection.requireAuthentication=true
# set the authentication provider to an external LDAP server
derby.authentication.provider=LDAP
# the host name and port number of the LDAP server
derby.authentication.server=godfrey:389
# the search base for user names
derby.authentication.ldap.searchBase=o=oakland.mycompany.com
# explicitly show the access mode for databases (this is default)
derby.database.defaultAccessMode=fullAccess

With these settings, all users must be authenticated by the LDAP server in order to access any Derby databases.

The database administrator has determined that one database, accountingDB, has additional security needs. Within a connection to that database, the database administrator uses database-wide properties (which override properties set in the derby.properties file) to limit access to this database. Only the users prez, cfo, and numberCruncher have full (read-write) access to this database, and only clerk1 and clerk2 have read-only access to this database. No other users can access the database.

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.defaultAccessMode', 'noAccess')

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.fullAccessUsers',
    'prez,cfo,numberCruncher')

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.readAccessUsers', 'clerk1,clerk2')

The database administrator then requires all current users to disconnect and re-connect. These property changes do not go into effect for current connections. The database administrator can force current users to reconnect by shutting down the database

Related reference
User authentication example in a single-user, embedded environment