Category: Databases

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.


Test the report.


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