Oracle Identity and Access Management

Recently, I conducted a training on Oracle Identity Management related products, just putting various topics I have covered under this.

• Technical Insight on Oracle Access Manager, Oracle Internet Directory, Oracle Virtual Directory, WebGate, AccessGate and Windows Native Authentication. In this topic I mostly talked on the various implementation/Support experience, I have with IDM Products and lesson learned from each.
• Installation and Implementation Steps for Identity Management Components.
• Load Balancer in front of OAM and WebGate
• Basic Performance Tuning on OAM, EAG, OID, HTTP WebTier, OVD
• Proxy SSL Configuration for OAM, eBusiness, EAG Components
• Integration of OAM with eBusiness Suite and WebCentre Content Management.
• Provisioning of Identity Management with Fusion Apps

Keep Learning !!!!!

Security on Oracle Data Dictionary | O7_DICTIONARY_ACCESSIBILITY

Security on Oracle Data Dictionary | O7_DICTIONARY_ACCESSIBILITY

The data dictionary tables and views for a given database are stored in the SYSTEM tablespace for that database. All the data dictionary tables and views for a given database are owned by the user SYS. Connecting to the database with the SYSDBA privilege gives full access to the data dictionary

SQL> SELECT TABLE_NAME FROM DICTIONARY;

INIT Parameter O7_DICTIONARY_ACCESSIBILITY (set as FALSE) enables to secure Oracle Data Dictionary.

Oracle Database provides highly granular privileges. One such privilege, commonly referred to as the ANY privilege, like DROP ANY TABLE. It is possible to protect the Oracle data dictionary from accidental or malicious use of the ANY privilege by setting 07_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE.

For changes in O7_DICTIONARY_ACCESSIBILITY to get reflected Database restart is required.

Concurrent Manager/Request Tuning(Supply Chain Cost Rollup - Print Report)

Concurrent Manager/Request Tuning(Supply Chain Cost Rollup - Print Report)

Recently I was engaged with a Concurrent Processing performance tuning. Queue for "Cost Rollup Manager" was growing rapidly.
This manager is responsible to run only "Supply Chain Cost Rollup - Print Report" and it should not take more than 30 seconds to complete normally.

Though its underlying code was causing the issue, writing in this blog. Same informations can be used to tune other manager and requests as well.

Find the Requests a Manager would Run

select  ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
  from Fnd_Concurrent_Requests t,
       FND_CONCURRENT_PROCESSES k,
       Fnd_Concurrent_Queues_TL QTL,
       Fnd_Concurrent_Programs_TL PTL 
  where k.concurrent_process_id = t.controlling_manager
    and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
    and ptl.concurrent_program_id=t.concurrent_program_id
    AND QTL.LANGUAGE='US'
    AND PTL.USER_CONCURRENT_PROGRAM_NAME LIKE '%'
    AND qtl.user_concurrent_queue_name ='Cost Rollup Manager'
ORDER BY ptl.user_concurrent_program_name DESC;

Find the Requests Running More than 30 Minutes

undefine start_date
undefine end_date

set pages 10000
set verify off
column request_id format 99999999 heading 'REQUEST'
column user_name format a17
column phase format a10
column status format a12
column start_date format a5
column completion_date format a5 heading 'END'
column avg_run_time format 9999 heading 'AVG TIME'
column min_run_time format 9999 heading 'MIN TIME'
column max_run_time format 9999 heading 'MAX TIME'
column program_name format a50

select
    p.user_concurrent_program_name program_name,
    count(r.request_id),
    avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) avg_run_time,
    min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) min_run_time,
    max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) max_run_time
from
    apps.fnd_concurrent_requests r,
    apps.fnd_concurrent_processes c,
    apps.fnd_concurrent_queues q,
    apps.fnd_concurrent_programs_vl p
where
    p.concurrent_program_id = r.concurrent_program_id
    and p.application_id = r.program_application_id
    and c.concurrent_process_id = r.controlling_manager
    and q.concurrent_queue_id = c.concurrent_queue_id
    and p.application_id >= &&ApplicationId
    and r.actual_start_date >= sysdate-31
    and r.status_code = 'C'
    and r.phase_code in ('C')
    and (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 * 60 > 30
    and p.user_concurrent_program_name not like 'Gather%Statistics%'
    and (
      (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 > 16
      or
      (r.actual_start_date-trunc(r.actual_start_date)) * 24 between 9 and 17
      or
      (r.actual_completion_date-trunc(r.actual_completion_date)) * 24 between 9 and 17
    )
group by p.user_concurrent_program_name
/

For a particular Date how many Requests are submitted for a particular concurrent requests and its performance analysis.

SELECT -- /*+ first_rows*/
  fcr.request_id req_id,
  fcp.concurrent_program_name conc_prg,
  PT.USER_CONCURRENT_PROGRAM_NAME USR_CONC_PRG,
  TO_CHAR (FCR.ACTUAL_START_DATE, 'DD-MON-YY HH24:MI:SS') START_DATE,
  ---NVL (TO_CHAR (fcr.actual_completion_date, 'mm-MON-yy HH24:MI:SS'), 'Not complete ') end_date,
  SUBSTR ( DECODE ( TRUNC (actual_completion_date - actual_start_date), 0, NULL, TRUNC (actual_completion_date - actual_start_date)
  || 'D' )
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24, 24 )), 2, 0 )
  || ':'
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24 * 60, 60 )), 2, 0 )
  || ':'
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24 * 60 * 60, 60 )), 2, 0 ), 1, 10 ) TIME,
  flv1.meaning phase,
  flv2.meaning status,
  fcr.argument_text parameters,
  fcr.oracle_process_id
FROM applsys.fnd_concurrent_programs fcp,
  applsys.fnd_concurrent_programs_tl pt,
  applsys.fnd_concurrent_requests fcr,
  fnd_lookup_values flv1,
  fnd_lookup_values flv2
WHERE FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND TRUNC (fcr.ACTUAL_START_DATE) BETWEEN TO_DATE ('17-JAN-2014', 'DD-MON-YYYY') AND TO_DATE ('26-JAN-2014', 'DD-MON-YYYY')
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id         = pt.application_id
AND fcp.concurrent_program_id  = pt.concurrent_program_id
AND pt.LANGUAGE                = 'US'
AND fcr.phase_code             = flv1.lookup_code
AND flv1.lookup_type           = 'CP_PHASE_CODE'
AND flv1.LANGUAGE              = 'US'
AND flv1.view_application_id   = 0
AND fcr.status_code            = flv2.lookup_code
AND flv2.lookup_type           = 'CP_STATUS_CODE'
AND flv2.LANGUAGE              = 'US'
AND FLV2.VIEW_APPLICATION_ID   = 0
AND PT.USER_CONCURRENT_PROGRAM_NAME LIKE '%Supply Chain Cost Rollup - Print Report%'
--ORDER BY FCR.ACTUAL_START_DATE DESC;
ORDER BY time DESC;

Solutions are outlined in following notes

Supply Chain Cost Rollup - Print Report Run Very Long time (Doc ID 1463306.1)
Supply Chain Cost Rollup - Print Report Performance Issues (Doc ID 1588101.1)

Enterprise Password Management/Self Service Password Management using Oracle Enterprise SSO

Enterprise Password Management/Self Service Password Management using Oracle Enterprise SSO

Recently I got chance to work on a Enterprise Password Management related activities using ESSO.

It should be broadly categorized as 
■ Application Password Change
■ Self-Service Windows Password Reset

I would be elaborating "Self-Service Windows Password Reset" in this section.

■ Self-Service Windows Password Reset

● Provides a fully integrated self-service Windows password reset solution for end-users, eliminating help desk calls and speeding the reset process. The user is challenged with a series of challenge questions which must be answered correctly in order for password reset to succeed.
● Challenge questions and acceptable answers, including the “weight” of each question, are administrator-configurable. 
● Self-service password reset functionality is accessed directly from the Windows logon dialog (integrated via GINA or credential provider link, depending on the OS version), and remotely via Web browser.

Questions and answers can be either specified by the administrator and stored directly within the ESSO-PR data store or retrieved dynamically via standard APIs from external systems, such as HR databases. Furthermore the ESSO PR Client can direct a user to the OIM KBA authentication engine to facilitate change password via that system. 

The weight of each question can be individually configured by the administrator using ESSO-PR’s confidence-based rating system so that one question can count more towards granting the user access than another. Correct answers add to the user’s quiz core, while incorrect answers subtract from it but not necessarily disqualify the user. Once the user correctly answers enough questions to pass the quiz, access to the account unlock and/or password reset functionality is granted. 

Required questions
Eliminator questions
Optional questions

The administrator can assign individual questions to specific users or groups using the ESSO-PR Administrative Console.

Architecture diagram is presented below for your reference:

Default Password Security Settings and Various Audits in 11g

Default Password Security Settings  and Various Audits in 11g

If applications use the default password security settings from Oracle Database 10g Release 2 (10.2), then you can revert to these settings until you modify them to use the Release 11g password security settings. To do so, run the undopwd.sql script.

undopwd.sql: This script is called by DBCA to undo the 11g secure configuration changes to the password portion of the default profile. It reverts to the default 10gR2 settings. It is not intended to be run during upgrade, since that would undo all customer settings as well.

secconf.sql: This script would enable the 11g default password security related settings and enable various auditing parts,

select resource_name, limit from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

Oracle Database 10gR2 Settings:

ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
;

Oracle Database 11g Settings:

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
;

Audit alter any table by access;
Audit create any table by access;
Audit drop any table by access;
Audit Create any procedure by access;
Audit Drop any procedure by access;
Audit Alter any procedure by access;
Audit Grant any privilege by access;
Audit grant any object privilege by access;
Audit grant any role by access;
Audit audit system by access;
Audit create external job by access;
Audit create any job by access;
Audit create any library by access;
Audit create public database link by access;
Audit exempt access policy by access;
Audit alter user by access;
Audit create user by access;
Audit role by access;
Audit create session by access;
Audit drop user by access;
Audit alter database by access;
Audit alter system by access;
Audit alter profile by access;
Audit drop profile by access;
Audit database link by access;
Audit system audit by access;
Audit profile by access;
Audit public synonym by access;
Audit system grant by access;
Audit directory by access;

Oracle Database Security - Steps to achieve high Security

Oracle Database Security - Steps to achieve high Security

Recently, I was engaged with an Oracle Database Security related tasks, would provide the details in multiple post. As of now just posting the road map, I used for this.