Using NATIVE authentication

Derby's simplest authentication mechanism is NATIVE authentication.

When you use NATIVE authentication, user names and encrypted passwords are stored in a database. You can specify a dedicated credentials database for this purpose, or you can store the credentials in the same database you use for your application data. The credentials are stored in the SYSUSERS system table of the database.

To specify NATIVE authentication, specify one of the following values for the derby.authentication.provider property:

NATIVE:credentialsDB
This value tells Derby to use credentialsDB, a dedicated database, to store user credentials. This value must be set by using system-wide Java Virtual Machine (JVM) properties or by using the derby.properties file; it cannot be set in the database by using the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY procedure. When this system-wide value is set, credentialsDB is used to authenticate all operations. If an individual database holds credentials for the database owner, the global credentials database is used only to authenticate system-wide operations such as engine shutdown.

The value of credentialsDB must be a valid name for a database.
NATIVE:credentialsDB:LOCAL
This value tells Derby to use credentialsDB for system-wide operations, but to use an individual database's SYSUSERS system table to authenticate connections to that database. This value must be set by using system-wide JVM properties or by using the derby.properties file; it cannot be set in the database by using the SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY system procedure.

Working with a credentials database

With NATIVE authentication, a database can become a credentials database in any of the following ways:

When the database is being created, it is identified as the credentials database by the system-level property setting derby.authentication.provider=NATIVE:credentialsDB.
When the database is being created, LOCAL authentication of connections is specified by the system-level property setting derby.authentication.provider=NATIVE:credentialsDB:LOCAL.
When the database already exists, the database owner calls the SYSCS_UTIL.SYSCS_CREATE_USER system procedure to store the database owner's credentials in the database. If the database owner calls this procedure to store another user's credentials first, an error is raised.

When a database becomes a credentials database, the following things happen:

The value of derby.authentication.provider=NATIVE::LOCAL is stored in the database, marking it as a credentials database.
From this point forward, the value of derby.authentication.provider cannot be overridden or changed for connections to this database.
If the database is being newly created, the database owner's credentials (provided in the connection arguments) are stored in the database's SYSUSERS system table.
All future connections to the database are authenticated against the credentials in its SYSUSERS system table.

NATIVE authentication and other database properties

When NATIVE authentication is enabled, Derby behaves as if the derby.connection.requireAuthentication and derby.database.sqlAuthorization properties are also set. That is, a user name and password must be specified whenever a user connects to a database, and object owners control access to database objects. See Setting the SQL standard authorization mode for more information, and see NATIVE authentication and SQL authorization example for an example of the use of NATIVE authentication.

For maximum security, the passwords that users specify when they connect to databases have an expiration date that you can modify by using the property derby.authentication.native.passwordLifetimeMillis. The password of the database owner never expires. By default, ordinary user passwords expire after 31 days.

If a password is about to expire, or if the database owner's password is near what would be the expiration date, Derby issues a warning that the password will soon expire (or, in the database owner's case, that the password is stale). By default, the warning is issued if the password is due to expire in one-eighth of the password's lifetime. For example, if the password has a 31-day lifetime, the warning will be issued 3.875 days before the expiration date. You can change this proportion by using the property derby.authentication.native.passwordLifetimeThreshold.

Use the derby.authentication.builtin.algorithm property to change the way passwords are encrypted when they are stored in the SYSUSERS system table. The default algorithm is SHA-256. Two related properties are derby.authentication.builtin.saltLength and derby.authentication.builtin.iterations, which can be used to make the hashed passwords harder for attackers to crack.

Managing users and passwords

To manage users and passwords, Derby provides a group of system procedures:

To create users for a database, the database owner calls SYSCS_UTIL.SYSCS_CREATE_USER, which takes a user name and password as arguments. This procedure can also be executed by a user or role to which the database owner has granted sufficient privileges.
To remove a user, the database owner calls SYSCS_UTIL.SYSCS_DROP_USER, which takes one argument, the user name of the user. This procedure can also be executed by a user or role to which the database owner has granted sufficient privileges.
To reset a forgotten or expired password, the database owner calls SYSCS_UTIL.SYSCS_RESET_PASSWORD, with a user name and password as arguments. This procedure can also be executed by a user or role to which the database owner has granted sufficient privileges.
To change a user's own password, any user can call the system procedure SYSCS_UTIL.SYSCS_MODIFY_PASSWORD, which takes only one argument, the password. Typically, a user calls this procedure when their password is about to expire.

Converting an existing database to use NATIVE authentication

If you wish to apply NATIVE authentication to a database that was created without it, the procedure is slightly different depending on whether you specify NATIVE:credentialsDB or NATIVE:credentialsDB:LOCAL:

If you specify NATIVE:credentialsDB, add users of the existing database to the credentialsDB. For instance, if the old database was created without any authentication, then its default user name is APP, and you could do the following:
```
CALL SYSCS_UTIL.SYSCS_CREATE_USER('app', 'app');
```
If you plan to specify NATIVE:credentialsDB:LOCAL, first connect to the existing database as its database owner using its old authentication scheme. Call SYSCS_UTIL.SYSCS_CREATE_USER to add credentials for the database owner. For example, if the existing database was created with no authentication, the database owner is APP, and you would add credentials for APP as shown above.