How to quickly change all the password for all ALM related schemas using Oracle

  • KM03650007
  • 04-Jun-2020
  • 04-Jun-2020

Summary

Oracle passwords might expire on a schedule.

Question

How to quickly change all the password for all ALM related schemas using Oracle

Answer

1) Login to the ALM Site Admin
2) Select the "Site Projects" tab.
3) Select one of the projects
    a. Note the value in the "Database Name". We'll use this in Step 7
    b. Note the value of the "Physical Directory". We'll use this value in Step 9
4) In the main view click the "DB User Password" link
5) It will ask if you wish to deactivate the project. Click Yes
6) Set the password
7) Go to Oracle and specify the password set in Step 6 for the user noted in Step 3a
8) Back in the ALM Site Admin, activate the project
9) Navigate to the path noted in Step 3b
10) Open the "dbid.xml" for viewing or editing and make note of the value in the element "DB_USER_PASS". This is the encrypted value for the new password
    For example: <DB_USER_PASS>QCC:z0Z5aNvQ9ehN/rsRNPDlmg==</DB_USER_PASS>
11) In the ALM deployment path open the "siteadmin.xml" for editing
    Windows: <deployment_path>\ALM\webapps\qcbin\WEB-INF\siteadmin.xml
    Linux: <deployment_path/ALM/webapps/qcbin/WEB-INF/siteadmin.xml
12) Note the value in the element "DbName". This is the schema name for the site admin database. Make note of it
13) In Oracle issue the following query to the schema noted in Step 12
    select * from <site_admin_schema>.PROJECTS
14) Note all the rows in the column/field "DB_NAME"
15) Stop the ALM service
16) In Oracle set the new password for the schema noted in Steps 12 and 14. Be sure to set the same password for all
17) Backup the site admin schema
18) Issue the following query to the <site_admin_schema>.PROJECTS to update the encrypted password noted from Step 10 in the <password>....
    update <site_admin_schema>.PROJECTS set DB_USER_PASS '<password>'
    For example: update qcsiteadmin_db.PROJECTS set DB_USER_PASS = 'QCC:OmB2gqtNdRxovcD7GbULFbKodzcLgqkHc+BvsEbF9e8='
                 commit;
19) In the ALM deployment path open the "siteadmin.xml" for editing
    Windows: <deployment_path>\ALM\webapps\qcbin\WEB-INF\siteadmin.xml
    Linux: <deployment_path/ALM/webapps/qcbin/WEB-INF/siteadmin.xml
20) Change the value for the element "<DefaultUserPassword>" and specify the new encrypted password noted in Step 10
    For example: <DefaultUserPassword>QCC:OmB2gqtNdRxovcD7GbULFbKodzcLgqkHc+BvsEbF9e8=</DefaultUserPassword>
21) Save changes to the "siteadmin.xml"
22) Start ALM, it should come online
23) Login to the Site Administration
24) Select the "DB Servers" tab and select the db server in question
25) Click the link "Application Lifecycle Management User Password"
26) Specify the same password as in Step 6. This so new projects will be created with schemas that have the new password


Next, the "dbid.xml" files for each project will have the old DB_USER_PASS value instead of the new one.
With the new encrypted password in hand, Step 10 above, use the Modify Dbid utility from here: https://support.microfocus.com/kb/kmdoc.php?id=KM02267685
This utility can be used to update some or all project "dbid.xml" files at once