SET ECHO OFF
SET AUTOCOMMIT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET TRIMSPOOL OFF
SET SHOW OFF

-- ESR_CodeCR7191 Currently the three "super user" accounts are being pre-populated in 
-- the database but the super users have all capabilities but no roles.  There are a few
-- actions, including running batch processes and creating user accounts that require the 
-- Administrator and/or System Administrator roles being assigned.  

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.put_line (' ');
   DBMS_OUTPUT.put_line ('ESR CR 7191 Add the roles Administor and System Administor to the bea user; Update password change date.');
   DBMS_OUTPUT.put_line (' ');
END;
/
SET TERMOUT OFF

-- 1. Update password change date

Update USERS set PASSWORD_CHANGE_DATE=sysdate WHERE USERNAME in ('bea', 'esr', 'weblogic');

-- 2. Add the roles Administor and System Administor to the user users (bea, esr, weblogic)

DECLARE 
   CURSOR uc IS
      SELECT USERID, USERNAME FROM USERS WHERE USERNAME IN ('bea','esr','weblogic');
   CURSOR rc IS
         SELECT ROLE_ID, ROLE_NAME FROM ROLES WHERE ROLE_NAME IN ('Administrator','System Administrator');
   cnt NUMBER;

BEGIN  

FOR user_rec IN uc LOOP
   FOR role_rec IN rc LOOP
 
SELECT COUNT(*) INTO cnt 
            FROM USER_ROLES UR JOIN USERS U 
                                 ON UR.USERID=U.USERID
                               JOIN ROLES R
                                 ON UR.ROLE_ID=R.ROLE_ID
           WHERE U.USERNAME = user_rec.USERNAME
             AND R.ROLE_NAME = role_rec.ROLE_NAME;
 
      IF ( cnt = 0 ) 
        THEN
 
         INSERT INTO ADR.USER_ROLES
                (USER_ROLE_ID, 
                USERID, 
                ROLE_ID, 
                INACTIVE_FLAG, 
                RECORD_CREATED_BY, 
                RECORD_CREATED_DATE, 
                RECORD_MODIFIED_BY, 
                RECORD_MODIFIED_DATE, 
                RECORD_MODIFIED_COUNT)
         Values (TRANSACTION_S.NEXTVAL, 
                (user_rec.USERID), 
                (role_rec.ROLE_ID), 
                0, 
                'ESR_CLEANUP', 
                sysdate, 
                'ESR_CLEANUP', 
                sysdate, 
                0);
      END IF;
   END LOOP;
END LOOP;
 
END;
/

@@uow.sql &1

SET TERMOUT ON
BEGIN
   DBMS_OUTPUT.PUT_LINE('Done.');
END;
/
SET TERMOUT OFF
-- Reset environment parameters
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON



