REM 	File 	: ckgrtrol.sql
REM	Date	: 9/9/96
REM	Usage	: To grant the Repository Role to subordinate users
REM	          This file is invoked by the RAU and is submitted through RM
REM               This file is SQL*Plus compliant
REM    
 
DECLARE
   CURSOR get_user IS
   SELECT su.username
   ,      su.status
   FROM   sdw_users su
   WHERE  su.status is not null
   AND    nvl(su.sdd_owner,'N') != 'Y'
   AND    su.username != 'PUBLIC'
   ORDER BY su.status;
   
   role_name VARCHAR2(50);

   PROCEDURE grant_role (l_user IN varchar2,
                         l_role IN varchar2) is
      csr NUMBER;
   BEGIN
      DBMS_OUTPUT.PUT_LINE('... Granting role '||l_role||' to '||l_user);
      csr := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(csr,'grant '||l_role||' to '||l_user, DBMS_SQL.V7);
      DBMS_SQL.CLOSE_CURSOR(csr);
      DBMS_OUTPUT.PUT_LINE('... Granted');
   EXCEPTION
      WHEN others THEN
         DBMS_OUTPUT.PUT_LINE ('... ERROR: '||sqlerrm);
         DBMS_SQL.CLOSE_CURSOR(csr);
   END;
   PROCEDURE revoke_role (l_user IN varchar2,
                          l_role IN varchar2) is
      csr NUMBER;
      role_not_exists EXCEPTION;
      PRAGMA EXCEPTION_INIT(role_not_exists, -01919);
   BEGIN
      DBMS_OUTPUT.PUT_LINE('... Revoking role '||l_role||' from '||l_user);
      csr := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(csr,'revoke '||l_role||' from '||l_user, DBMS_SQL.V7);
      DBMS_SQL.CLOSE_CURSOR(csr);
      DBMS_OUTPUT.PUT_LINE('... Revoked');
   EXCEPTION
      WHEN role_not_exists THEN
         DBMS_OUTPUT.PUT_LINE ('... Role was not granted');
         DBMS_SQL.CLOSE_CURSOR(csr);
      WHEN others THEN
         DBMS_OUTPUT.PUT_LINE ('...ERROR: '||sqlerrm);
         DBMS_SQL.CLOSE_CURSOR(csr);
   END;
BEGIN
   -- If a particular user does not exist remove it from SDW_USERS	
   -- This will mean that we do not need to verify each time
   -- 953558 - FULL RECONCILE DELETES 'PUBLIC' USER FROM TABLE SDW_USERS. 
	
   DBMS_OUTPUT.PUT_LINE('Reconciling Users ...');
   DELETE FROM sdw_users su
   WHERE  USERNAME !='PUBLIC'
   AND    NOT EXISTS (SELECT 1 
                     FROM   all_users au
                     WHERE  au.username=su.username);
   COMMIT;

   role_name := 'CKR_'||user;
   dbms_output.enable(1000000);
   FOR this_user IN get_user LOOP
       IF this_user.status = 'I' THEN
           grant_role(this_user.username, role_name); 
       ELSE
           revoke_role(this_user.username, role_name); 
       END IF;
   END LOOP;
END;
/
