MySQL Technical Track

Change of database passwords through OEM

3 min read

May 4, 2017 12:00:00 AM

Once in a while passwords for system database accounts should be changed due to different requirements; it is a boring routine task, but it still needs to be completed. However, with the help of OEM it can be automated and varied based on life cycle status of database targets and/or their belonging to different groups. If you are not making all passwords unique across all databases, you're most likely following some password pattern for system user like "system_pwd_[db_type]" where db type can be for instance d, t or p. If LifeCycle status is properly configured for each of databases, you do not need to rely on database names if they have dev, test, prod inside of names (which is not always true) but rather on target property. Having OEM properly configured for preferred credentials change of passwords can be done through OEM job, emcli script. For OEM jobs you would need to add all database targets and have SQL, which would be extracting LifeCycle status and apply in relation to the type of database password, similar to that: [code lang="sql" gutter="false"] declare v_user varchar2(32) := 'SYSTEM'; v_grp varchar2(1000) := '%orcl_gtp_lifecycle_status%'; v_sql varchar2(1000); v_pass varchar2(1000); begin 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 in '||'%TargetName%'); end if; end; / [/code] The code is self-explanatory, and with proper configured notification for the job you can get output to the e-mail with results of changes. The emcli scripting approach is the same - to rely on orcl_gtp_lifecycle_status variable. However, for emcli there is even the function update_db_password and an example in the documentation about how to use it. Note that the function uses old password value as input as well, so that might be an issue in some cases. The code can be like: [code lang="python" gutter="false"] from emcli import * from emcli.exception import VerbExecutionError import sys import time set_client_property('EMCLI_OMS_URL','https://oem:7802/em') set_client_property('EMCLI_TRUSTALL','true') login(username='sysman',password='sysman_pwd') def check_job_status(job, p_target_name): count=0 while (count < 10): count = count + 1 obj = emcli.get_jobs(job_id=job) for entry in obj.out()['data']: l_status = entry['Status ID'] l_exec_id = entry['Execution ID'] if (l_status == '5'): print "SYSTEM password successfully changed on database: " + p_target_name count=100 elif (l_status == '4'): l_resp = get_job_execution_detail(execution=l_exec_id, showOutput=True, xml=True) print "Job failed, error details " print "Output " + str(l_resp.out()) count=100 else: time.sleep(2) def update_system_pwd_for_target(p_target_name, p_old_password, p_new_password): l_target_name = p_target_name print "Changing SYSTEM password for database: " + l_target_name try : l_resp = update_db_password (target_name=l_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, l_target_name) except emcli.exception.VerbExecutionError, e: print "ERROR : Change of SYSTEM password failed for database: " + l_target_name print "ERROR : " + e.error() l_d_old_pwd = "old_pwd_d" l_d_new_pwd = "system_pwd_d" l_t_old_pwd = "old_pwd_t" l_t_new_pwd = "system_pwd_t" l_p_old_pwd = "old_pwd_p" l_p_new_pwd = "system_pwd_p" 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']: print "Updating SYSTEM password on database: " + target['TARGET_NAME'] if (target['PROPERTY_VALUE'] == "Production"): l_old_pwd = l_p_old_pwd l_new_pwd = l_p_new_pwd elif (target['PROPERTY_VALUE'] == "Test"): l_old_pwd = l_t_old_pwd l_new_pwd = l_t_new_pwd elif (target['PROPERTY_VALUE'] == "Development"): l_old_pwd = l_d_old_pwd l_new_pwd = l_d_new_pwd update_system_pwd_for_target(target['TARGET_NAME'], l_old_pwd, l_new_pwd) [/code] However, you can certainly use the verb execute_sql and with sysdba credentials (or normal credentials if they are for system user) you can accomplish the same as above: [code lang="python" gutter="false"] from emcli import * from emcli.exception import VerbExecutionError import sys import time set_client_property('EMCLI_OMS_URL','https://oem:7802/em') set_client_property('EMCLI_TRUSTALL','true') login(username='sysman',password='sysman_pwd') l_d_new_pwd = "system_pwd_d" l_t_new_pwd = "system_pwd_t" l_p_new_pwd = "system_pwd_p" 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']: print "Updating system password on database: " + target['TARGET_NAME'] 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(sql=l_sql + l_new_pwd, targets=target['TARGET_NAME']+":oracle_database", credential_set_name="DBCredsSYSDBA") [/code] Whatever approach you use, it is always beneficial to have OEM properly configured for target properties and tested for usage of named credentials. Have a good day!

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Change of database passwords through OEM

Share this

Share this

More resources

OEM 13c – Ultimate monitoring guide

How to fix an AWR Warehouse after credentials change

How to Use EM CLI to Verify Data Guard Configurations

Ready to unlock value from your data?