Configuring Schema Password Expiry Warnings and Wallet Refresh

15 Configuring Schema Password Expiry Warnings and Wallet Refresh

This chapter describes how to manage schema password expiry and wallet refreshes to maintain installations that use an Oracle database for the configuration and results schemas.

This chapter contains the following topics:

15.1 Configuring Schema Password Expiry Warnings

When EDQ uses an Oracle database for the configuration and results schemas, a task is run periodically to check the password expiry time of the schema passwords. If an expiry time is found within a defined threshold, EDQ can generate warnings.

This topic covers:

Configuration

To configure the password expiry checks and notifications use the following properties in director.properties within the EDQ local home directory:

Property	Description	Default Value
`schema.password.expiry.check.interval`	Interval between expiry checks. Value must not be less than 60s. Set to 0 to disable expiry checking.	1d Valid suffixes are `d` (days), `h` (hours), `m` (minutes), `s` (seconds) If no suffix is specified, the default suffix will be `d` (days).
`schema.password.expiry.warning.threshold`	Generates warnings when the expiry time is within this interval.	7d Valid suffixes are `d` (days), `h` (hours), `m` (minutes), `s` (seconds) If no suffix is specified, the default suffix will be `d` (days).
`schema.password.expiry.warning.frequency`	Specifies the number of expiry checks after which to generate warnings. Use this option to limit the number of warnings generated. For example, if you want more frequent checks for expiry, but do not want warnings on every check, set the following: `schema.password.expiry.check.interval = 12h` `schema.password.expiry.warning.frequency = 4` EDQ will check for password expiry every 12 hours, but generate warnings every other day.	1
`schema.password.expiry.warning.emails`	Space or comma separated list of email addresses used by built-in trigger.

Triggers

Expiry warnings are generated by running triggers with these paths:

/schema/config/expiring
/schema/results/expiring

There is a built-in trigger that sends mails to the addresses configured with the schema.password.expiry.warning.emails property. You can specify multiple addresses by using commas or spaces. Specify SMTP details in mail.properties with enabled = true for emails to be sent.

You can also define custom triggers for additional flexibility. The arguments to the trigger are:

label - Schema label - "config" or "results"
user - Database username for schema
date - Expiry time

Here's an example that logs a message, generates a push notification, and sends an email:

addLibrary("logging")
addLibrary("webpush")
addLibrary("mail")
 
function getPath() {
 return "/schema/(config|results)/expiring"
}
 
function run(path, id, env, label, user, date) {
  logger.log(Level.INFO, "{0} [{1}] expiring {2}", label, user, date);
 
  var p = WebPush.create(`${label} schema password will expire on ${date}`)
   
  p.title = "Database password expiry warning"
  p.push()
 
  var mh  = Mail.open({enabled : true});
  var msg = mh.newMessage("Database password expiry warning")
 
  msg.text = `${label} schema password will expire on ${date}`
  msg.addTo("admin@example.com")
  msg.type = "text/plain";
  msg.send()
}

15.2 Configuring Schema Password Reset

Note:

This information is applicable to EDQ installations running on Apache Tomcat environments only.

For EDQ running on Tomcat where the database URLs and credentials are configured in director.properties, you can change the schema passwords in the database without the need to edit director.properties and restart the server. You can trigger a schema password reset in any of the following ways:

This topic also covers:

Configuring Automatic Reset of Schema Password

To configure automatic password reset, set the property schema.password.auto.reset.after. This value specifies the number of times password expiry is detected before the password is reset in the database. To disable automatic reset, set the value to -1.

Examples

To reset the password immediately when expiry is detected by automatic checks, set the following:

schema.password.auto.reset.after = 0

To reset the password after 5 warnings, set the following.

schema.password.auto.reset.after = 5

If schema.password.expiry.check.interval is set at one day (the default), this setting gives the administrator five days to update the password manually before the automatic reset occurs.

Resetting Schema Password Using REST API

To use a system administration REST API to reset the schema password, use the following interface. Note that the user must have the system administration permission to run this request.

POST https://server/edq/admin/schemas/setpassword

The payload to the request contains the attributes listed in the following table:

Attribute	Description
label	Required. Schema label. The value must be "config" or "results".
password	Required. The new password. Use "" or "%" to specify a random password.

Resetting Schema Password Using a Script Library in a Trigger

Trigger scripts can update schema passwords using a script library. To use the library, add this line to the top of the script:

addLibrary("schemas")

The library publishes the following constant fields:

Name	Description	Value
Schemas.CONFIG	Internal label for config schema.	config
Schemas.RESULTS	Internal label for results schema.	results

and an object schemas with the following method:

schemas.updatePassword(label [, password])

This updates the password for one of the schemas. label identifies the schemas and must be set to "config" or "results". If password is omitted, a random password is used.

The following is an example that uses the expiry warning trigger:

addLibrary("schemas")
 
function getPath() {
 return "/schema/(config|results)/expiring"
}
 
function run(path, id, env, label, user, date) {
  schemas.updatePassword(label);
}

Triggers

Password reset for a schema runs triggers with these paths:

/schema/config/passwordreset
/schema/results/passwordreset

Similar to schema password expiry warnings, there is a built-in trigger that sends mails to the addresses configured with the schema.password.expiry.warning.emails property. You can specify multiple addresses by using commas or spaces. Specify SMTP details in mail.properties with enabled = true for emails to be sent.

You can also define custom triggers for additional flexibility. The arguments to the trigger are:

label - Schema label - "config" or "results"
user - Database username for schema
password - The new password

Note that emails that are generated by the default trigger do not include the new password.

Clustering Considerations

If EDQ is running in a cluster of Tomcat servers, the default update process assumes that the same director.properties file is shared amongst all the servers. The internal data source passwords are updated on all servers, but the properties are written on a single server only. If each server has a distinct director.properties, you need to set the following:

schema.password.shared.properties = false

Password Strength

Random passwords for Oracle are constructed with a fixed length and minimum counts of upper and lower case letters, and fixed counts of digits and special characters (-_#). The counts can be overridden using these properties:

Property	Description	Default Value
oracle.pw.length	Password length.	12
oracle.pw.lower	Minimum number of lower case letters.	2
oracle.pw.upper	Minimum number of lower case letters.	2
oracle.pw.numeric	Number of digits.	2
oracle.pw.special	Number of special characters.	2

15.3 Configuring Automatic Wallet Refresh

Note:

This information is applicable to EDQ installations running on Apache Tomcat environments only.

If EDQ is using an Autonomous Database instance as its repository database with mTLS enabled, the wallet files need periodic refresh since the embedded certificates have a limited lifetime. EDQ can be configured to refresh wallet files that are older than a defined time.

This topic covers the following:

Prerequisites to Configure Automatic Wallet Refresh

To support automatic wallet refresh, you must configure the OCID of the Autonomous Database instance using the following properties:

Property	Description
`dataSource.adb.ocid`	OCID of configuration schema database.
`resultsDataSource.adb.ocid`	OCID of results schema database.

Additionally, the JDBC URLs must be as follows:

jdbc:oracle:thin:@service?TNS_ADMIN=/pathtowalletdirectory

If both schemas use the same database instance and the same wallet directory, set only dataSource.adb.ocid. Do not set resultsDataSource.adb.ocid.

Configuring Automatic Wallet Refresh

To enable automatic wallet refresh, set the property schema.wallet.refresh.interval. This is the minimum wallet age after which a refresh is performed. The age of a wallet is determined from the modification time of the cwallet.sso file. The property value is a duration with d/h/m/s suffixes. If no suffix is present the value is treated as a number of days.

Examples

schema.wallet.refresh.interval = 30d
schema.wallet.refresh.interval = 60