Changing the passwords of Database users

Hi All,

Last week a question turned up in OTN forums about changing the passwords of Database users (Schemas).

It’s very simple, just issuing ‘ALTER USER XXX IDENTIFIED BY PASSWORD YYY’. Isn’t it? Why we have to spend so much time on a strategy to change passwords?

It’s not so simple as we think. Consider a scenario where the application stores the passwords, of course in encrypted form, and tries to connect to the Database with those. If we change the passwords without making the application aware of the changes, Your entire application will become unusable.

Leave about the application users. We’ll change their passwords in a consistent way.  Can we change the passwords of users like  SYSMAN, DBSNMP through the ALTER USER… command? If your answer is YES, you are wrong. Even they’re not application users, they too have some dependencies. My intention in writing this article is to address those. BTW, I’m taking some of the commands and procedures to be followed from Metalink.

Steps to change SYSMAN password [270516.1]

Steps for Changing the SYSMAN Password in 10.x Grid Control

The below steps are applicable to 10.1.0.x to 10.2.0.4 version of Grid Control:

  1. Stop the Agent monitoring the ‘OMS and Repository’ target, to avoid the SYSMAN account in the repository database from getting locked, during the password change.

    –  Issue the query below to Find which Grid Agent is Monitoring the “OMS and Repository” Target?

              SELECT target_name, target_type, host_name, emd_url FROM mgmt_targetswhere target_type =  ‘oracle_emrep’;

–  Connect to the node where the above Agent is installed and stop the agent by doing,

                           cd <AGENT_HOME>/bin

                           emctl stop agent

2. Stop all the OMS instances that are associated with the repository. From each node where the OMS is running,

                            cd <OMS_HOME>/opmn/bin

                            opmnctl stopall

3. Change the SYSMAN password in the file emoms.properties:

–  Take a backup of the <OMS_HOME>/sysman/config/emoms.properties file.

– Edit the file emoms.properties and change the following lines

                           oracle.sysman.eml.mntr.emdRepPwd=new_password

                           oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

Note:

1. The oracle.sysman.eml.mntr.emdRepPwdSeed parameter should not be modified.
2. Do not create an additional entry for the oracle.sysman.eml.mntr.emdRepPwd / oracle.sysman.eml.mntr.emdRepPwdEncrypted, but modify the existing parameter values.
3. Do not add any extra space to the password in clear text or the value for oracle.sysman.eml.mntr.emdRepPwdEncrypted parameter.

Now we’re done with the Password changes on the application file. It’s time to change them in the Database and some other places

  1. Change the SYSMAN password in the Database hosting the repository:

– Set the $ORACLE_SID environment variable to point to the EM Repository Database.

– Logon to the Repository Database with SQL*Plus as a dba user and issue the command:

SQL> alter user sysman identified by <newpassword>;

2. Restart each of the OMS:

From each node where an OMS is running,execute:

                          cd <OMS_HOME>/opmn/bin

                          opmnctl startall

3. Change the SYSMAN password for the “OMS and Repository” target:

Restart the Agent monitoring the “OMS And Repository” target, that was stopped in Step 1:

                     cd <AGENT_HOME>/bin

                               emctl start agent

Login to the Grid Control Console and navigate to Setup ->Management Services and Repository.

From the bottom of the page in the “Overview”, click on the link “Monitoring Configuration”.
If the monitoring Agent is still down, you will get the following error message:

               Communication from the Oracle Management Service host to the Agent host failed. Refer to help for details.                 IOException in sending Request :: Connection refused 

In the Monitoring Configuration page, change the value in the field “Repository password”
Click OK

This will update the <AGENT_HOME>/sysman/emd/targets.xml of the monitoring Agent with the new SYSMAN   password.

Steps for Changing the SYSMAN Password in 10.2.0.5 and 11.1 Grid Control

When using 10.2.0.5 or 11.1 OMS, all the above steps can be performed by executing a single command:

cd <OMS_HOME>/bin

emctl config oms -change_repos_pwd -change_in_db

Example of output for OMS 10.2.0.5 running on Windows:

C:\app\oracle\oms10g>emctl config oms -change_repos_pwd -change_in_db
Oracle Enterprise Manager 10g Release 5 Grid Control
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
Enter Repository User's Current Password :
Enter Repository User's New Password :

Changing password in backend ...
Password changed in backend successfully.
Changing monitoring credentials ...
Monitoring credentials changed successfully.
Updating emoms.properties....
emoms.properties updated successfully....
Please bounce OMS.
Successfully changed repository password.
If you have multiple OMS's in your environment, please run this cmd on all of them

Note:

1. The above command
– will prompt you for the current password and the new password.
– will modify the sysman password in the repository database, in the emoms.properties configuration file / WLS Credential store and the monitoring credentials for the ‘OMS and Repository’ target.
– will ask you to bounce the OMS once the command is completed successfully
– for 11G, the command will modify the password for sysman and sysman_mds users in the repository database, modify the password in the credential store and change the monitoring credentials for the ‘OMS and Repository’ target.

2. The ‘-change_in_db’ parameter is optional and is used to change the SYSMAN password in the repository database.

– If the sysman password is manually modified in the repository database, this option need not be specified.
– When this parameter is specified, the command will prompt for the current sysman password.
– If the current sysman password is lost / unknown then:

a) Log in to your Repository database as SYS and run:
SQL> ALTER USER SYSMAN IDENTIFIED BY <newpassword>;

where newpassword is the new password you want for SYSMAN.

b) From a command prompt run:
cd $OMS_HOME/bin
./emctl config oms -change_repos_pwd
                     Provide newpassword when prompted.

3. If using multiple OMS, then execute the above command on one of the OMS.
On the Additional OMS, execute:

<OMS_HOME>/bin/emctl config oms -change_repos_pwd

Reminder to update the emcli, on every monitoring host, with a new password if the sysman password is changed,

as this will allow emcli to login with the new password.
Reference:
Note:1090075.1: EM CLI: How to Install and Setup emcli (Enterprise Manager Command Line Interface)?

$> emcli setup -url=http://omsmachine.domain:port/em -username=em_user

Steps to change DBSNMP password [on multiple hosts] [886984.1]

We can change the DBSNMP password on multiple target Databases through EMCLI (Enterprise Manager Command Line interface).

Please go through the link here for the installation instructions of EMCLI: https://querycsm.wordpress.com/2012/08/13/adding-targets-in-oem-12c-in-silent-mode/

1. Using input_file option with update_password EMCLI Verb

emcli update_password
-target_type=oracle_database
-target_name=database
-credential_type=DBCreds
-key_column=DBUserName:oracle
-non_key_column=”FILE1″
-input_file=”FILE1:/tmp/user_passwd”
And the contents of the user_passwd file is
DBPassword:oracle:oracle1

2. Another way of using the files option with other password management verbs like update_db_password, update_host_password, update_target_password
You can create a chng_DB_mon_passwd (Shown below) with multiple targets to change the password. May be changing the dbsnmp password on 10 db target:
And Execute like –
emcli login -user=sysman -pass=xxxxxxxx
emcli argfile chng_DB_mon_passwd

This will help in change the password at multiple targets in one emcli execution.

Chng_DB_mon_passwd File :
update_db_password -target_name=database1 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database2 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database3 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes
update_db_password -target_name=database4 -user_name=dbsnmp -old_password=dbsnmp -new_password=snmpdb -retype_new_password=snmpdb -change_at_target=yes

Have a nice day.
CSM

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: