Change of database passwords through OEM

Changing passwords for system database accounts is a necessary but often tedious routine. However, with Oracle Enterprise Manager (OEM), you can transform this boring task into a streamlined, automated process.

By leveraging target properties like the LifeCycle Status, you can ensure that the right password patterns are applied to the right environments (Development, Test, or Production) without relying on inconsistent database naming conventions.

Leveraging LifeCycle Status for Automation

Most organizations follow a specific password pattern for system users, such as system_pwd_[db_type]. If your OEM targets have the LifeCycle Status properly configured, you don't need to parse strings like "dev" or "prod" from the database name. Instead, you can rely on the orcl_gtp_lifecycle_status property.

Method 1: Using OEM SQL Jobs

If you have configured preferred credentials, you can use an OEM job to execute a PL/SQL block across multiple targets. The script below extracts the LifeCycle status of the specific target and applies the corresponding password.

PL/SQL Script for OEM Jobs

The following code uses OEM variables (enclosed in %) to dynamically identify the target and its properties:

declare      v_user varchar2(32) := 'SYSTEM';      v_grp varchar2(1000) := '%orcl_gtp_lifecycle_status%';      v_sql varchar2(1000);      v_pass varchar2(1000);  begin      -- Map LifeCycle Status to specific password patterns     select decode(v_grp,                    'Production', 'system_pwd_p',                    'Test', 'system_pwd_t',                    'Development', 'system_pwd_d',                    '') into v_pass from dual;                         v_sql := 'alter user '||v_user||' identified by '''||v_pass||'''';           if v_pass is null then          dbms_output.put_line('LifeCycle status is not properly configured for '||'%TargetName%');      else          execute immediate v_sql;          dbms_output.put_line('New password set for '||'%TargetName%');      end if;  end;  / 

By configuring job notifications, you can automatically receive an email report detailing which databases were successfully updated.

Method 2: Python Scripting with EM CLI

The Enterprise Manager Command Line Interface (EM CLI) provides more granular control through Python scripting. One approach is to use the built-in update_db_password function.

Using the update_db_password Function

This function is powerful but requires the old password as input, which can be a limitation if passwords have drifted or are unknown.

from emcli import * # ... (initial login and setup)  def update_system_pwd_for_target(p_target_name, p_old_password, p_new_password):     try :         # Update password at both the target and in OEM credentials         l_resp = update_db_password (             target_name=p_target_name,              change_at_target="yes",              user_name="SYSTEM",              old_password=p_old_password,              new_password=p_new_password,              retype_new_password=p_new_password         )         l_job_submitted = l_resp.out()['JobId']         check_job_status(l_job_submitted, p_target_name)     except emcli.exception.VerbExecutionError, e:         print "ERROR : " + e.error()  # Logic to loop through targets based on orcl_gtp_lifecycle_status # ... (refer to full blog for the target list loop) 

Method 3: Streamlined SQL Execution via EM CLI

An alternative EM CLI approach is using the execute_sql verb. This is often more flexible because it can use SYSDBA credentials to "force" a password change without needing to know the previous password.

Simplified EM CLI Python Script

This script finds all databases with a specific LifeCycle property and executes the ALTER USER command.

from emcli import * # ... (login and setup)  l_d_new_pwd = "system_pwd_d" l_t_new_pwd = "system_pwd_t" l_p_new_pwd = "system_pwd_p"  # Search for database targets with a LifeCycle Status property search_list = ['TARGET_TYPE= \'oracle_database\'', 'PROPERTY_NAME=\'orcl_gtp_lifecycle_status\''] l_targets = list(resource="TargetProperties", search=search_list, columns="TARGET_NAME,TARGET_TYPE,PROPERTY_VALUE")  for target in l_targets.out()['data']:     l_sql = "alter user system identified by "     if (target['PROPERTY_VALUE'] == "Production"):         l_new_pwd = l_p_new_pwd     elif (target['PROPERTY_VALUE'] == "Test"):         l_new_pwd = l_t_new_pwd     elif (target['PROPERTY_VALUE'] == "Development"):         l_new_pwd = l_d_new_pwd              # Execute SQL using named credentials     execute_sql(sql=l_sql + l_new_pwd,                  targets=target['TARGET_NAME']+":oracle_database",                  credential_set_name="DBCredsSYSDBA") 

Conclusion

Regardless of the method you choose, the key to success is a well-maintained OEM environment. By ensuring that your target properties are accurate and your named credentials are tested, you can turn a manual security requirement into a robust, automated workflow.

MySQL Database Consulting

Looking to optimize your MySQL use?