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.
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.
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.
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.
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.
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) 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.
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") 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.
Looking to optimize your MySQL use?