This blog contains experience gained over the years of implementing (and de-implementing) large scale IT applications/software.

Tag: OEM

Lock/Unlock OEM Grid Control Accounts From Excel Macro


As a complimentary post to my previous blog post on Reporting All Oracle User Accounts Through OEM Grid Control, I have decided to post the code for an Excel VBA macro which uses three buttons to call the specific OEM Grid Control URLs that will enable you Lock/UnLock or Edit an Oracle user account using the OEM Grid Control user interface, but from a link on an Excel spreadsheet.

This sounds confusing so maybe I should explain the reason behind this.
I needed to know about all Oracle user accounts throughout the Oracle landscape so that I could compare them to a dump of Active Directory accounts.
The AD accounts get locked when people leave, but the leavers process didn’t always include the DBA in the ‘cc list.
So, armed with the Excel spreadsheet of AD users, I needed to marry the list to the Oracle accounts and then go and lock the Oracle accounts where the AD account had been locked/expired.

Sound OK so far?
Good.  So once I’ve got the Excel sheet with the ~200 Oracle accounts I need to lock, here’s how it could be done using OEM Grid Control (you can give the spreadsheet to your 1st line support people, if they have OEM access).

Three buttons on the spreadsheet:  Lock, UnLock and Edit.
Just paste the code into the sheet’s VBA section, then change the text “<your_oem_server>” and add the three buttons and hook them to the sub routines.
I should mention that this was an Oracle Enterprise Manager Grid Control 10g implementation.

Enjoy.

Private Sub CommandButton1_Click()
' Call to open IE with the URL to lock the user based on the data on the current sheet on the current selected row.

Dim str_URLpart1 As String
Dim str_URLpart2 As String
Dim str_URLpart3 As String
Dim str_URLpart4 As String
Dim str_URLpart5 As String
Dim str_complete_URL As String
Dim str_sys As String
Dim str_user As String
Dim IE As Object

str_URLpart1 = "https://<your_oem_server>/em/console/database/security/user?oname="
str_URLpart2 = "&amp;event=lockUser&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database"
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL

' Statusbar
Application.StatusBar = "URL is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Application.StatusBar = ""

End Sub

Private Sub CommandButton2_Click()

' Call to open IE with the URL to unlock the user based on the data on the current sheet on the current selected row.

Dim str_URLpart1 As String
Dim str_URLpart2 As String
Dim str_URLpart3 As String
Dim str_URLpart4 As String
Dim str_URLpart5 As String
Dim str_complete_URL As String
Dim str_sys As String
Dim str_user As String
Dim IE As Object

str_URLpart1 = "https://<your_oem_server>/em/console/database/security/user?oname="
str_URLpart2 = "&amp;event=unlockUser&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database"
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL

' Statusbar
Application.StatusBar = "URL is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Application.StatusBar = ""

End Sub

Private Sub CommandButton3_Click()

' Call to open IE with the URL to edit the user based on the data on the current sheet on the current selected row.

Dim str_URLpart1 As String
Dim str_URLpart2 As String
Dim str_URLpart3 As String
Dim str_URLpart4 As String
Dim str_URLpart5 As String
Dim str_complete_URL As String
Dim str_sys As String
Dim str_user As String
Dim IE As Object

str_URLpart1 = "https://<your_oem_server>/em/console/database/security/user?oname="
str_URLpart2 = "&amp;event=edit&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database"
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = True

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL

' Statusbar
Application.StatusBar = "URL is loading. Please wait..."

' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop

Application.StatusBar = ""

End Sub

Report All Oracle User Accounts Through OEM Grid Control

You know the problem: You have Oracle Enterprise Manager Grid Control 10g installed and all your databases are configured in it.
It allows you to manage your entire landscape.  Great!

All of a sudden, a “leaver form” pops on your desk from HR with a long list of people who have left the company recently.
Do they have accounts in your Oracle databases?
If you have only one or two Oracle databases to check, then you can use the power of Grid Control to sort out the list in a few minutes.
What if you’ve got 50+ databases?
What if you’ve got 100+ databases?
Don’t they say: “The little jobs are the most time consuming”?

You may maintain an external spreadsheet of Oracle accounts, or you may not.  If you do, or even if you don’t, this article will show you how to configure a Oracle Enterprise Manager Grid Control 10g report that will show you all users accounts across the landscape at the click of a button.  You could use this technique to extend it to almost anything that can be done in an SQL or PL/SQL session.

Architecture overview:

We will be creating a new OEM Grid Control job called LIST_USER_ACOUNT_DETAILS, and a new report called LIST_USER_ACCOUNT_DETAILS.
The job is executed on the monitored database targets and it stores it’s output in the usual MGMT$ table in the OEM repository database.
The report simply pulls and re-connects the job output.

In OEM Grid Control create a new job called “LIST_USER_ACCOUNT_DETAILS”.
Add the database targets or target group.

Set the SQL script to be:

SET HEAD OFF;
SET PAGESIZE 9999;
SET LINESIZE 999;
SET TRIMOUT ON;
SET TRIMSPOOL ON;
SET FEEDBACK OFF;
SET COLSEP ' ';
COL MARKER FORMAT A2;
COL USERNAME FORMAT A30;
COL ACCOUNT_STATUS FORMAT A25;
COL LOCK_DATE FORMAT A12;
COL EXPIRY_DATE FORMAT A12;
COL HAS_REMOVED_ROLE FORMAT A30;
COL HAS_DBA_ROLE FORMAT A20;
WHENEVER SQLERROR EXIT FAILURE;
SELECT
'@|' marker,
username||'|' username,
account_status||'|' account_status,
'|'||TO_CHAR(lock_date,'DD-MM-RRRR')||'|' lock_date,
'|'||TO_CHAR(expiry_date,'DD-MM-RRRR')||'|' expiry_date,
'|'||DECODE((SELECT 'X' FROM dba_role_privs WHERE grantee = username AND granted_role ='REMOVED_ACCOUNTS'),'X','HAS ROLE "REMOVED_ACCOUNTS"','')||'|' has_removed_role,
'|'||DECODE((SELECT 'X' FROM dba_role_privs WHERE grantee = username AND granted_role ='DBA'),'X','HAS DBA ROLE!!','') has_dba_role
from dba_users;

Set the Parameters to be “-s” (silent):

Set the Credentials to use an account capable of querying DBA_USERS and DBA_ROLE_PRIVS (you could change this to use ALL_ views and use a lower priviledged account).
Set the schedule for the job. However frequently you wish to query the account details.

Submit the job for execution.

Once the job runs, the job output will be visible in the OEM MGMT$JOB_STEP_HISTORY table.
The actual SQLPlus output is stored in the OUTPUT column which is a CLOB field.
We have had to insert a marker in the SQL so that the output contains a ‘@|’ at the beginning of each line. This is used to determine the start of each record in the OUTPUT CLOB.
We just need to write a report to pull out the details.

Create a new report called “LIST_USER_ACCOUNT_DETAILS”.
On the “General” tab, select the “Use the specified target” option and enter the hostname of your OEM Grid Control repository server (DB server).
Select the “Run report using target privileges of the report owner (SYSMAN)” tick box.

On the “Elements” tab, add a “Table from SQL” item and then set the header, then paste in the statement below:

SELECT TARGET_NAME,JOB_TIME,ACCOUNT_DETAILS FROM (
SELECT '===============' TARGET_NAME,'================' JOB_TIME,'==================================================' ACCOUNT_DETAILS FROM DUAL
UNION
SELECT
TARGET_NAME,
TO_CHAR(END_TIME,'DD-MON-RRRR HH24:MI:SS') JOB_TIME,
-- non-regexp option -- TO_CHAR(REPLACE(SUBSTR(mh.OUTPUT,INSTR(mh.OUTPUT,'@| ',1,ct.x),INSTR(mh.OUTPUT,'@| ',1,ct.x+1) - INSTR(mh.OUTPUT,'@| ',1,ct.x)),'@| ','')) ACCOUNT_DETAILS
TO_CHAR(REGEXP_SUBSTR(mh.OUTPUT,'[^@]+', 1,ct.x)) ACCOUNT_DETAILS
FROM MGMT$JOB_STEP_HISTORY mh,
(SELECT rownum x FROM ALL_OBJECTS WHERE rownum<=5000) ct
WHERE TARGET_TYPE='oracle_database'
AND JOB_OWNER='SYSMAN'
AND JOB_NAME LIKE 'LIST_USER_ACCOUNT_DETAILS.%'
AND STEP_NAME='Command'
)
WHERE ACCOUNT_DETAILS IS NOT NULL
ORDER BY TARGET_NAME, JOB_TIME;

NOTE: In the report SQL, we have assumed a maximum number of 5000 user accounts across all database targets.
If you exceed this (if you have a lot of user accounts) then you will need to increase the number.
We have also assumed that SYSMAN is the owner of the job that created the output. You should change this if necessary.

You can choose to schedule the report if you wish (you should take into account the source job schedule).
It's probably best to secure the report so that only administrators can see it.

Test the report.

The output format consists of the following columns:
 TARGET NAME
 JOB TIME
 ACCOUNT DETAILS {
                 USERNAME
                 ACCOUNT STATUS
                 LOCK DATE
                 EXPIRY DATE
                 HAS REMOVED ROLE
                 HAS DBA ROLE
                }

The "HAS REMOVED ROLE" column relates to a "flag" role that I use to denote that an Oracle account has been de-activated purposely.
Instead of the account being deleted, it is retained for audit purposes and the REMOVED_ACCOUNTS role granted to it. You can choose to ignore or adapt this column.

Once you have the output, you can extract it to Excel for comparison with an Active Directory dump or any other means.