Changes in 9.3.5 / 9.3.6 System Passwords and Encryption

Encryption, Passwords and the WebLogic Repository

Back in December of 2013 I wrote about the affect of password encryption on the process of performing a database refresh: Agile PLM 9.3.2 Changed The Database Import Process.

Agile PLM has continued to improve the security of passwords throughout the PLM application and its lifecycle. Beginning with PLM V9.3.2, passwords for system level accounts (superadmin, ifsuser, admin and agileuser) were encrypted using a symmetrical key method with the key located in the database.  In Agile PLM 9.3.5, the encryption key was moved to the WebLogic Repository, also called the RCU. This change resulted in making the key value less obvious (security by obscurity, perhaps) and increasing the importance of the Repository. 

The need to capture the encrypted system level account passwords has not changed but now you do not need to capture the encryption key as it remains in the Repository during a refresh and the Repository tables are not changed. The SQL utility SavePasswords.sql that was published then and again in May of 2017, PLM Tech Tips: Oracle AgilePLM 9.3.X Database Refresh Process, is still valid and remains an easy means of extracting the encrypted system passwords while at the same time creating a SQL script (RestorePasswords.sql) to safely restore the passwords after a refresh. The SQL utility will simply not find the encryption key in the database and since the encryption key is in the Repository, it does not need to be included. This does however make backups a bit more difficult. The standard Agile export and import scripts do not capture the Repository tables and therefore do not capture the encryption key. This simplifies refreshes but complicates the database recovery and greatly diminishes the usefulness of database exports for backup purposes. This reliance on the Repository means the Agile application needs to be re-installed to make the exports of any use if the database is lost. This is a consequence of the Repository and its integration with the Agile PLM application.  Under these circumstances (attempting to replace a damaged or corrupt database with an intact application server) it is doubtful that even a full database backup performed with RMAN would enable you to recover the application environment completely.  The best course of action is to re-install the application server (including a fresh Repository) after recovering the database.

As a system administrator or DBA it is important you understand how the application, Repository and database work together and how to reconstruct the system as a whole if necessary.

The following note from the Agile 9.3.5 Installation Guide underscores the importance of the Repository.

http://docs.oracle.com/cd/E69230_08/otn/docset.html

Overview of the WebLogic Repository

The WebLogic Repository (as it relates to Agile PLM) is comprised of five users and attendant schemas. Each schema is named beginning with a system prefix (such as DEV or PROD), an underscore and the standard name of the schema (see chart at the bottom of this article).  The Repository is implemented using the Repository Creation Utility script in the WebLogic home after WebLogic has been fully installed, patched (see patch number 19614859 for WLS 12.1.3) and the Agile schema has been created. The Repository does not reside in the Agile schema nor is it owned by the Agile schema owner. It is important to document the setup of the Repository during installation as the schema names, owners and passwords may be needed later.

Oracle Database Defaults to Expiring Passwords

Another misstep that I have seen really bite organizations who are not fully aware of the new Oracle 12c database used to support Agile 9.3.5 / 9.3.6, is that Oracle 12c defaults to all passwords expiring after 180 days. If the Repository or Agile schema passwords are allowed to expire, your system will fail catastrophically on the next attempted restart. It is simple enough to alter the database default to prevent password expiration but as I said, I have seen this overlooked. If you have properly documented the Repository and Agile schema credential sets then you can reset the password to its original value after altering the expiration defaults saving you many hours of working it out with Oracle Support. Altering the database default PASSWORD_LIFETIME will not have any effect on already expired passwords. To change the expiration time, use the following SQL process.

Connect to the database using the SYS account (as SYSDBA) and run the following statement:

alter profile default limit PASSWORD_LIFE_TIME unlimited;

Additional Documentation to Maintain

If you are responsible for recovering an Agile system in case of disaster or other event, you must practice performing the installation and database recovery. Do not let a disaster recovery scenario be your first attempt at installing an Agile PLM system. On the topic of system recovery, be sure to document any hot fixes that are applied to the system and keep a safe copy of everything you need for reconstruction available in a location that can be accessed under varying degrees of disaster. I have four copies of every Agile installation by version located locally, in the cloud, in cloud back up and on the corporate network. I can always get at least one of these copies when needed. Do not count on being able to download what you may need from Oracle eDelivery. You may not have bandwidth to do it or the downloads may have changed as versions progress through the application lifecycle. Remember, you must be able to fully reconstruct the Agile PLM system while continually being asked “When will it be completed?”.

Was this guide helpful? Please share your comments below. 

Author Bob McDuffee, Certified Ethical Hacker (CEH), has over 30 years experience and is a System Engineer for Zero Wait-State. He is responsible for installing software for clients and overseeing hosted and virtual environments. He provides configuration information for customers and debugs hardware issues both for clients and the company internally. His experience includes implementing, troubleshooting and upgrading PDM systems on Linux, Solaris and Windows servers utilizing both WebLogic and Oracle Application Server.

Zero Wait-State has products and services available to help you maintain your PLM system.

Click here to contact us.