REM @(#) $Id: //bas/722_STACK/src/ccm/rsbr/sapuprof_profile.sql#1 $ SAP
REM #################################################################
REM
REM sapuprof_profile.sql
REM
REM Copyright (c) 2010 Oracle
REM All rights reserved. 
REM
REM    NAME
REM      sapuprof_profile.sql - SAP User Profile
REM
REM    DESCRIPTION
REM      1) Create or update user profile SAPUPROF.
REM      2) Assign user profile SAPUPROF to SAP database accounts
REM      3) Revert secure settings of Oracle DEFAULT user profile
REM
REM      A log file sapuprof_profile.log is created in current directory.
REM
REM      Supported Oracle releases: 11.2.0.2 and above
REM
REM    USAGE
REM      Run as DBA (or SYSDBA)
REM
REM      OS> sqlplus / as sysdba @sapuprof_profile
REM
REM Change history
REM
REM    MODIFIED   (MM/DD/YYYY)
REM    abecker     09/21/2010 - added EXIT
REM    abecker     09/19/2010 - Created for Oracle release 11.2.0.2
REM
REM #################################################################

SET SERVEROUTPUT ON FORMAT WRAPPED;
SET VERIFY OFF
-- SHOW USER

set echo off;
set termout off;
spool sapuprof_profile.log

DECLARE

  p_null          CHAR(1)                ; -- dummy variable
  sql_stmt        varchar2(1024) := ''   ; -- SQL statement
  sapuprof_exists BOOLEAN        := FALSE; -- TRUE if user profile SAPUPROF already exists
  p_cnt           INTEGER                ; -- counter variable
  
  PROFILE_USERS_ASSIGNED  EXCEPTION;
  PRAGMA exception_init(PROFILE_USERS_ASSIGNED, -2382);
  
  PROFILE_NOT_EXIST  EXCEPTION;
  PRAGMA exception_init(PROFILE_NOT_EXIST     , -2380);

BEGIN
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Configuration of database user profile SAPUPROF');

  -- Checking user profile configuration of SAP accounts
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking user profile configuration of SAP accounts ...');
  FOR obj in (SELECT username, profile FROM DBA_USERS WHERE (USERNAME = 'SAPR3') OR (USERNAME LIKE 'SAP%' AND LENGTH(USERNAME)=6) OR (USERNAME LIKE 'SAP%DB' AND LENGTH(USERNAME)=8)) loop
    DBMS_OUTPUT.PUT_LINE('USER (PROFILE): ' || obj.username || ' (' || obj.profile || ')');
  END LOOP;

  -- Drop SAPUPROF
  -- ORA-02382: profile SAPUPROF has users assigned, cannot drop without CASCADE
  -- ORA-02380: profile SAPUPROF does not exist
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Dropping user profile SAPUPROF ...');
  begin
    sql_stmt := 'DROP PROFILE SAPUPROF';
    DBMS_OUTPUT.PUT_LINE('Executing SQL command ''' || sql_stmt || '''');
    execute immediate sql_stmt;
    DBMS_OUTPUT.PUT_LINE('User profile SAPUPROF dropped.');
    EXCEPTION
      WHEN PROFILE_NOT_EXIST THEN
      	NULL;
      WHEN PROFILE_USERS_ASSIGNED THEN
      	begin
      	  sql_stmt := 'DROP PROFILE SAPUPROF CASCADE';
          DBMS_OUTPUT.PUT_LINE('Executing SQL command ''' || sql_stmt || '''');
          execute immediate sql_stmt;
          DBMS_OUTPUT.PUT_LINE('User profile SAPUPROF dropped.');
        exception
          when others then
            DBMS_OUTPUT.PUT_LINE(SQLCODE);
            DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
        end;
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
  end;

  -- Checking user profile configuration of SAP accounts
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking user profile configuration of SAP accounts ...');
  FOR obj in (SELECT username, profile FROM DBA_USERS WHERE (USERNAME = 'SAPR3') OR (USERNAME LIKE 'SAP%' AND LENGTH(USERNAME)=6) OR (USERNAME LIKE 'SAP%DB' AND LENGTH(USERNAME)=8)) loop
    DBMS_OUTPUT.PUT_LINE('USER (PROFILE): ' || obj.username || ' (' || obj.profile || ')');
  END LOOP;

  -- Check whether profile exists
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking for user profile SAPUPROF ...');
  BEGIN
    SELECT NULL INTO p_null FROM DBA_PROFILES WHERE PROFILE = 'SAPUPROF' AND RESOURCE_NAME = 'PASSWORD_LIFE_TIME';
      sapuprof_exists := TRUE;
      DBMS_OUTPUT.PUT_LINE('User profile SAPUPROF already exists.');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('User profile SAPUPROF does not exist.');
  END;

  -- Check which users have been assigned to SAPUPROF
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking for database users with user profile SAPUPROF ...');
  p_cnt := 0;
  if ( sapuprof_exists ) then 
    FOR obj in (SELECT username FROM DBA_USERS WHERE PROFILE = 'SAPUPROF') loop
    	DBMS_OUTPUT.PUT_LINE(obj.username);
    	p_cnt := p_cnt + 1;
    END LOOP;
  end if;
  DBMS_OUTPUT.PUT_LINE('Number of users with SAPUPROF profile: ' || p_cnt);
  
  -- Creating user profile SAPUPROF
  DBMS_OUTPUT.PUT_LINE('.');
  --DBMS_OUTPUT.PUT_LINE('Creating/Updating user profile SAPUPROF ...');
  if (sapuprof_exists) then 
    DBMS_OUTPUT.PUT_LINE('Updating user profile SAPUPROF ...');
    sql_stmt := 'ALTER PROFILE SAPUPROF LIMIT ';
  else
    DBMS_OUTPUT.PUT_LINE('Creating user profile SAPUPROF ...');
    sql_stmt := 'CREATE PROFILE SAPUPROF LIMIT '; 
  end if;
  sql_stmt := sql_stmt
           || ' FAILED_LOGIN_ATTEMPTS UNLIMITED'
           || ' PASSWORD_LIFE_TIME    UNLIMITED';
  /***
           || ' PASSWORD_REUSE_TIME   UNLIMITED'
           || ' PASSWORD_REUSE_MAX    UNLIMITED'
           || ' PASSWORD_VERIFY_FUNCTION NULL  '
           || ' PASSWORD_GRACE_TIME   UNLIMITED'
           || ' PASSWORD_LOCK_TIME    1        '
   ***/
  begin
    DBMS_OUTPUT.PUT_LINE('Executing SQL command ''' || sql_stmt || '''');
    execute immediate sql_stmt;
    DBMS_OUTPUT.PUT_LINE('User profile SAPUPROF created/updated.');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE);
      DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
  END;
  
  -- Assigning user profile SAPUPROF to SAP accounts
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Assigning user profile SAPUPROF to SAP users ...');
  FOR obj in (SELECT username, profile FROM DBA_USERS 
                WHERE (PROFILE <> 'SAPUPROF') 
                  AND ((USERNAME = 'SAPR3') OR (USERNAME LIKE 'SAP%' AND LENGTH(USERNAME)=6) OR (USERNAME LIKE 'SAP%DB' AND LENGTH(USERNAME)=8))) loop
    DBMS_OUTPUT.PUT_LINE('Assigning SAPUPROF to ' || obj.username || ' ...');
    sql_stmt := 'ALTER USER ' || obj.username || ' PROFILE SAPUPROF';
    begin
      DBMS_OUTPUT.PUT_LINE('Executing SQL command ''' || sql_stmt || '''');
      execute immediate sql_stmt;
      DBMS_OUTPUT.PUT_LINE('OK.');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
    END;
  END LOOP;

  -- Checking user profile configuration of SAP accounts
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking user profile configuration of SAP accounts ...');
  FOR obj in (SELECT username, profile FROM DBA_USERS WHERE (USERNAME = 'SAPR3') OR (USERNAME LIKE 'SAP%' AND LENGTH(USERNAME)=6) OR (USERNAME LIKE 'SAP%DB' AND LENGTH(USERNAME)=8)) loop
    DBMS_OUTPUT.PUT_LINE('USER (PROFILE): ' || obj.username || ' (' || obj.profile || ')');
  END LOOP;

  -- Checking DEFAULT user profile
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking DEFAULT user profile ...');
  For obj in(SELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT
               FROM DBA_PROFILES 
               WHERE PROFILE = 'DEFAULT' 
                 AND RESOURCE_TYPE = 'PASSWORD'
                 AND RESOURCE_NAME in ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME')) loop
    DBMS_OUTPUT.PUT_LINE(obj.profile || ': ' || obj.resource_name || '=' || obj.limit );

    if (obj.resource_name = 'FAILED_LOGIN_ATTEMPTS' AND obj.limit = 'UNLIMITED') then
      sql_stmt := 'ALTER PROFILE ' || obj.profile || ' LIMIT ' || obj.resource_name || ' 10 ';
      begin
      	DBMS_OUTPUT.PUT_LINE('Restoring Oracle 11.2 default ...');
        DBMS_OUTPUT.PUT_LINE('Executing SQL command ''' || sql_stmt || '''');
        execute immediate sql_stmt;
        DBMS_OUTPUT.PUT_LINE('OK.');
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE);
          DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
      END;
    end if;

    if (obj.resource_name = 'PASSWORD_LIFE_TIME' AND obj.limit = 'UNLIMITED') then
      sql_stmt := 'ALTER PROFILE ' || obj.profile || ' LIMIT ' || obj.resource_name || ' 180 ';
      begin
      	DBMS_OUTPUT.PUT_LINE('Restoring Oracle 11.2 default ...');
        DBMS_OUTPUT.PUT_LINE('Executing SQL command ''' || sql_stmt || '''');
        execute immediate sql_stmt;
        DBMS_OUTPUT.PUT_LINE('OK.');
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE);
          DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
      END;
    end if;
    
  END LOOP;

  -- Checking SAPUPROF user profile
  DBMS_OUTPUT.PUT_LINE('.');
  DBMS_OUTPUT.PUT_LINE('Checking SAPUPROF user profile ...');
  For obj in(SELECT PROFILE, RESOURCE_NAME, RESOURCE_TYPE, LIMIT
               FROM DBA_PROFILES 
               WHERE PROFILE = 'SAPUPROF' 
                 AND RESOURCE_TYPE = 'PASSWORD') loop
    DBMS_OUTPUT.PUT_LINE(obj.profile || ': ' || obj.resource_name || '=' || obj.limit || case obj.limit when 'DEFAULT' then ' ' else ' (*)' end);
  END LOOP;
END;
/

SET SERVEROUTPUT OFF
SPOOL OFF

EXIT