Rem
Rem $Header: db_corr_actions.sql 12-aug-2006.01:35:49 rmadampa Exp $
Rem
Rem db_corr_actions.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      db_corr_actions.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rmadampa    08/01/06 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

DECLARE
    l_job_params            MGMT_JOB_PARAM_LIST;
    l_job_id                RAW(16);
    l_ca_name_db            VARCHAR2(64);
    l_ca_name_rac           VARCHAR2(64);
    l_ca_db_target_type     VARCHAR2(64) := 'oracle_database';
    l_ca_rac_target_type    VARCHAR2(64) := 'rac_database';
  
BEGIN
    ------------------------------------------------------------------
    --Bug 4878413: Corrective Action for Profiles with Unlimited Failed
    --Login Attempts policy
    ------------------------------------------------------------------
    --The final name stored in job system is capitalized irrespective
    --of whatever is given while creating the CA, be careful while
    --referring to this name from the policy if not capitalized.
    l_ca_name_db := 'ALLOWED FAILED LOGIN ATTEMPTS (DATABASE INSTANCE)';
    l_ca_name_rac := 'ALLOWED FAILED LOGIN ATTEMPTS (CLUSTER DATABASE)';
    
    l_job_params := MGMT_JOB_PARAM_LIST();
    l_job_params.extend(1);
   
    l_job_params(1) := 
      MGMT_JOB_PARAM_RECORD('sql_script',
                MGMT_JOBS.PARAM_TYPE_SCALAR,
                'ALTER PROFILE %context_value_profile% LIMIT FAILED_LOGIN_ATTEMPTS 10;', 
                null);
  
       
    BEGIN
     --for oracle_database
     MGMT_CA.delete_user_ca(
        p_ca_name => l_ca_name_db, 
        p_owner => MGMT_USER.get_repository_owner, 
        p_commit => 0
     );
     COMMIT;
     
     --for rac_database
     MGMT_CA.delete_user_ca(
        p_ca_name => l_ca_name_rac, 
        p_owner => MGMT_USER.get_repository_owner, 
        p_commit => 0
     );
     COMMIT;
    EXCEPTION
     WHEN OTHERS THEN
          NULL;
    END;                    
   

    BEGIN
     --for oracle_database
     MGMT_CA.create_user_ca(
        p_owner => MGMT_USER.get_repository_owner,
        p_ca_name => l_ca_name_db, 
        p_target_type => l_ca_db_target_type,
        p_description => 'Fixes the FAILED_LOGIN_ATTEMPTS '||
        ' resource setting in profiles violating the '||
        '''Profiles with Excessive Failed Login Attempts'''||
        ' policy for Database Instance targets',
        p_job_type => 'SQLScript', 
        p_job_params => l_job_params,
        p_job_creds => null, 
        p_job_id_out => l_job_id
     );
     COMMIT;
     
     --for rac_database
     MGMT_CA.create_user_ca(
        p_owner => MGMT_USER.get_repository_owner,
        p_ca_name => l_ca_name_rac, 
        p_target_type => l_ca_rac_target_type,
        p_description => 'Fixes the FAILED_LOGIN_ATTEMPTS '||
        ' resource setting in profiles violating the '||
        '''Profiles with Excessive Failed Login Attempts'''||
        ' policy for Cluster Database targets',
        p_job_type => 'SQLScript', 
        p_job_params => l_job_params,
        p_job_creds => null, 
        p_job_id_out => l_job_id
     );
     COMMIT;

    EXCEPTION
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
      
                      
END;
/
COMMIT;
SET SERVEROUTPUT OFF