Recreating Database Links after Password Rotation

To support the scenario where database links are used with automated password rotation, XTAM needs to re-create these links after the password has been updated.

 

The following article describes how to configure XTAM to enable this functionality. OLTP and DWH are generic terms representative of the database schemas used in the creation of the link (CREATE DATABASE LINK).

 

Concepts

1. A script that will reset the password in OLTP or DWH database schema and trigger another task to update the link in TARGET database.
2. A script that will update the database link based on the password on record.
3. A record for schema at OLTP database (similar one could be created for DWH or in other databases) with the script #1 to reset its password and trigger link update.
4. A record for shadow account in TARGET database (sysdba) to perform link update for record #5. This shadow record might be reused for all DB links in TARGET.
5. A record for schema at TARGET database describing the link parameters with the script to update DB Link from #2 run as a shadow account with permissions from #4. This record is a link-signal for a trigger where to update the db link. (similar one could be created for DWH or in other databases).

Configuration

Below are more details about the setup. Feel free to modify certain aspects like names, scripts, variables to match your environment.

 

1. A script that will reset the password in OLTP or DWH database schema and trigger another task to update the link in TARGET database. Note that the script below will trigger the script called Oracle Update DB Link after password reset. If you will choose another name for that script, you will need to update the trigger too.

   • Create a script named: Oracle Password Reset with Trigger

   • Job Execution Strategy: Oracle DB

   • SQL:

     Copy

     alter user {{LOGIN}} identified by "{{NEWPWD}}"
     
     -- #XTAM TRIGGER REF Oracle Update DB Link

     

2. A script that will update the database link based on the password on record. Note that the link name here is linksrc. You need to use your link name here.

   • Create a script named: Oracle Update DB Link
   • Job Execution Strategy: Oracle DB
   • SQL:
   Copy

   ALTER PUBLIC DATABASE LINK linksrc CONNECT TO {{LOGIN}} IDENTIFIED BY {{OLDPWD}}

   

3. A record for schema at OLTP database (similar one could be created for DWH or in other databases) with the script to reset its password and trigger link update.

   Create a regular Oracle DB record and add the task Oracle Password Reset with Trigger from #1. You can add task to a record itself or create a special record type for such records. In our example we called it Oracle Source for Oracle Link.

   

4. A record for shadow account in TARGET database (sysdba) to perform link update for record #5. This shadow record might be reused for all DB links in TARGET.

   Create a regular Oracle DB record describing SYS account in the TARGER database. Note how we named a user here: SYS as sysdba. We named this record Oracle Target Shadow for Oracle Link.

   

5. A record for schema at TARGET database describing the link parameters with the script to update DB Link. (similar one could be created for DWH or in other databases).

   Create a regular Oracle record for the target database. When creating this record use Reference record you created in #4. It will fill all parameters. For this record, Manage / Tasks and define Shadow record for sysdba you created in #4. Also, add Oracle Update DB Link script we created in #2. This is the record that links it all together.

    

   We named this record Oracle Target for Oracle Link. Below is the edit screen demonstrating how does it reference the Source record.

   

   The screen shot below is the Manage / Task list definition for this record. Notice Shadow Account and a task added.

   

 

Operations

1. Execute Oracle Password Reset with Trigger script on the record Oracle Source for Oracle Link we have created in #3. This task could be run on demand of based on any other policy such as periodic or after certain event (like unlock).
2. After successful execution the job will schedule new Oracle Update DB Link task for the record Oracle Target for Oracle Link we created in #5.
3. The job Oracle Update DB Link will run to login as sysdba as a shadow account and update the password on the link because it is updated already after #a since target record references the source one and they have the same user / password on record.