Notifications for Expiring DBSNMP Passwords
- by Courtney Llamas
Most user accounts these days have a password profile on
them that automatically expires the password after a set number of days. Depending on your company’s security
requirements, this may be as little as 30 days or as long as 365 days, although
typically it falls between 60-90 days. For a normal user, this can cause a small interruption in your day as
you have to go get your password reset by an admin. When this happens to privileged accounts,
such as the DBSNMP account that is responsible for monitoring database
availability, it can cause bigger problems.
In Oracle Enterprise Manager 12c you may notice the error
message “ORA-28002: the password will expire within 5 days” when you
connect to a target, or worse you may get “ORA-28001: the password has expired". If you
wait too long, your monitoring will fail because the password is locked
out. Wouldn’t it be nice if we could get an alert
10 days before our DBSNMP password expired? Thanks to Oracle Enterprise Manager 12c Metric
Extensions (ME), you can! See the Oracle
Enterprise Manager Cloud Control Administrator’s Guide for more information
on Metric Extensions.
To create a metric extension, select Enterprise / Monitoring / Metric Extensions, and then click on Create.
On the General Properties screen select either Cluster
Database or Database Instance, depending on which target you need to
monitor. If you have both RAC and Single
instance you may need to create one for each. In this example we will create a Cluster Database metric. Enter a Name for the ME and a Display Name.
Then select SQL for the Adapter. Adjust
the Collection Schedule as desired, for this example we will collect this
metric every 1 day. Notice for metric
collected every day, we can determine the exact time we want to collect.
On the Adapter page, enter the query that you wish to execute. In this example we will use the query below
that specifically checks for the DBSNMP user that is expiring within 10 days. Of course, you can adjust this query to alert
for any user that can cause an outage such as an application account or service
account such as RMAN.
select
username, account_status, trunc(expiry_date-sysdate) days_to_expirefrom
dba_userswhere
username = 'DBSNMP'and
expiry_date is not null;
The next step is to create the columns to store the data
returned from the query. Click Add and
add a column for each of the fields in the same order that data is returned. The table below will help you complete the column additions.
Name
Display Name
Column Type
Value Type
Metric Category
Unit
Username
User Name
Key
String
Security
AccountStatus
Account Status
Data
String
Security
DaysToExpire
Days Until Expiration
Data
Number
Security
Days
When creating the DaysToExpire column, you can add a default
threshold here for Warning and Critical (say < 10 and 5).
When all columns have been added, click Next.
On the Credentials
page, you can choose to use the default monitoring credentials or specify new
credentials. We will use the default
credentials established for our target (dbsnmp).
The next step is to test your Metric Extension. Click on Add to select a target for testing,
then click Select. Now click the button
Run Test to execute the test against the selected target(s). We can see in the example below that the
Metric Extension has executed and returned a value of 68 days to expire. Click Next to proceed.
Review the metric extension in the final screen and click
Finish.
The metric will be created in Editable status. Select the metric, click Actions and select
Deployable Draft. You can do this once
more to move to Published. Finally, we want to apply this metric to a
target. When managing many targets, it’s
best to add your metric to a template, for details on adding a Metric Extension
to a template see the Administrator’s
Guide. For this example, we will deploy
this to a target directly. Select
Actions / Deploy to Targets. Click Add
and select the target you wish to deploy to and click Submit.
Once deployment is complete, we can go to the target and
view the Metric & Collection Settings to see the new metric and its
thresholds.
After some time, you will
find the metric has collected and the days to expiration for DBSNMP user can be
seen in the All Metrics view. For metrics collected once per day, you may
have to wait up to 24 hours to see the metric and current severity. In the example below, the current severity is
Clear (green check) as it is not scheduled to expire within 10 days.
To test the notification, we can edit the thresholds for the
new metric so they trigger an alert. Our
password expires in 139 days, so we’ll change our Warning to 140 and leave
Critical at 5, in our example we also changed the collection time to every 5
minutes. At the next collection, you’ll find that the current
severity changes to a Warning and any related Incident Rules would be triggered
to create an Incident or Notification as desired.
Now that you get a notification that your DBSNMP passwords
is about to expire, you can use OEM Command Line Interface (EM CLI) verb update_db_password to change it at
both the database target and the OEM target in one step. The caveat is you must know the existing
password to use the update_db_password command. To learn more about EM CLI, see the Oracle Enterprise Manager Command Line Interface Guide. Below is an example of changing the password with the update_db_password verb.
$
./emcli update_db_password -target_name=emrep -target_type=oracle_database
-user_name=dbsnmp -change_at_target=yes -change_all_references=yes
Enter
value for old_password :Enter
value for new_password :Enter
value for retype_new_password :Successfully
submitted a job to change the password in Enterprise Manager and on the target
database: "emrep"Execute
"emcli get_jobs -job_id=FA66C1C4D663297FE0437656F20ACC84" to check
the status of the job.Search
for job name "CHANGE_PWD_JOB_FA66C1C4D662297FE0437656F20ACC84" on the
Jobs home page to check job execution details.
The subsequent job created will typically run quickly enough
that a blackout is not needed, however if you submit a script with many targets
to change, your job may run slower so adding a blackout to the script is
recommended.
$
./emcli get_jobs -job_id=FA66C1C4D663297FE0437656F20ACC84
Name Type Job ID Execution ID Scheduled Completed TZ Offset Status Status ID Owner Target Type Target Name
CHANGE_PWD_JOB_FA66C1C4D662297FE0437656F20ACC84 ChangePassword FA66C1C4D663297FE0437656F20ACC84 FA66C1C4D665297FE0437656F20ACC84 2014-05-28 09:39:12 2014-05-28 09:39:18 GMT-07:00 Succeeded 5 SYSMAN oracle_database emrep
After implementing the above Metric Extension and using the EM CLI update_db_password verb, you will be able to stay on top of your DBSNMP password changes without experiencing an
unplanned monitoring outage.
