spool ckupdprv.log
rem
rem   PURPOSE : To grant/revoke a particular privilege(ex MANAGE WORKAREA)
rem				to/from multiple sub-ordinate users in Oracle Designer 6.5 
rem				Repository using the RAU one would have to grant/revoke the 
rem				privilege for each user at a time.
rem				This Script is a template for repository administrators
rem				who want to mass update user privileges on sub-ordinate
rem				users in Oracle Designer 6.5 Repository
rem  			
rem				
rem   ***************************************************************************
rem   * IMPORTANT : Please make changes to this script ONLY as advised below.   *
rem   *             Otherwise it will not be supported.                         *
rem	  *																			*
rem   *                                                                         *
rem   * For each of the privileges(MANAGE WORKAREA, MANAGE CONTAINER etc.)      *
rem   * in the declaration section, change the assigned value to a 'N' if   	*
rem   * you want the particular privilege to be revoked from the users.			*
rem   * By default all the privileges are that can be granted in a non 			*
rem	  * versioned repository are initialized to 'Y'.							*
rem   *	In a versioned repository full privileges can be granted i.e., all the	*
rem   * can be set to 'Y'.														*
rem   * IMPORTANT: Do not change the privileges that cannot be granted			*
rem   * in a non versioned repository to 'Y'.									*
rem	  *																			*
rem   *	This script can be run for an individual user or multiple users by		*
rem	  * making use of the wildcard %. 											*
rem   * Example:  If you have multiple users that have usernames starting with	*
rem   * 'J', pass J as a user input parameter.  Otherwise use the full username *
rem   * of the repository user jdoe.											*
rem	  * The where clause may be replaced with an in statement. 			  		*
rem	  *	Example:  select username												*
rem   *	  				from sdw_users											*
rem   *			 		where username in ('jdoe','sbhagava')					*
rem   *																		    *
rem   * To execute this script on your Designer 6.5 Repository                  *
rem   *                                                                         *
rem   * Syntax, from within SQL*Plus connected as the Repository owner:         *
rem   * SQL> @ckupdprv.sql    				                                    *
rem   *                                                                         *
rem   ***************************************************************************

begin
  
-- Initialize all the privileges that can be granted in a non versioned 
-- repository to 'Y' and the rest of the privileges to 'N'.
--
-- Change the value of the privilege to an 'Y'/'N' to grant/revoke
-- a privilege listed below.
-- IMPORTANT: Do not change the privileges that cannot be granted
-- in a non versioned repository to 'Y'.

 Declare
  	l_manage_workarea 		varchar2(1) := 'N';
	l_manage_configuration 	varchar2(1) := 'N';
	l_manage_container 		varchar2(1) := 'Y';
	l_manage_user 			varchar2(1) := 'N';
	l_manage_branch_label 	varchar2(1) := 'N';
	l_manage_dependency 	varchar2(1) := 'Y';
	l_manage_vvc			varchar2(1) := 'Y';
	l_register_schema 		varchar2(1) := 'N';
	l_set_policy			varchar2(1) := 'N';
	l_purge					varchar2(1) := 'N';
	l_force					varchar2(1) := 'Y';
	l_global_purge			varchar2(1) := 'N';
	l_connect_via_ron		varchar2(1) := 'Y';
	l_connect_via_rau		varchar2(1) := 'Y';
	l_connect_via_matrix	varchar2(1) := 'Y';
	l_connect_via_reports	varchar2(1) := 'Y';
	l_connect_via_cli		varchar2(1) := 'Y';
	l_connect_via_vhv		varchar2(1) := 'N';
	l_connect_via_vev		varchar2(1) := 'N';

-- Modify the where clause in the following select statement
-- as necessary by adding more users to in the clause using 
-- the guideline shown below.

	cursor c_get_user is 
	 select username
		  	from sdw_users
			where username like upper(decode('&&user', null, '%%', '%&&user%'));

-- or use an in the where clause
--			where username in ('jdoe','sbhagava')

	begin	
	  for c_get_user_rec in c_get_user loop 
  	    begin
		  if l_manage_workarea = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'MANAGE_WORKAREA');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'MANAGE_WORKAREA');
		  end if;  
		  if l_manage_configuration = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'MANAGE_CONFIGURATION');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'MANAGE_CONFIGURATION');
		  end if;
		  if l_manage_user = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'MANAGE_USER');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'MANAGE_USER');
		  end if;
		  if l_manage_branch_label = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'MANAGE_BRANCH_LABEL');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'MANAGE_BRANCH_LABEL');
		  end if;
		  if l_manage_dependency = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'MANAGE_DEPENDENCY');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'MANAGE_DEPENDENCY');
		  end if;
		  if l_manage_vvc = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'MANAGE_VVC');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'MANAGE_VVC');
		  end if;		  
		  if l_register_schema = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'REGISTER_SCHEMA');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'REGISTER_SCHEMA');
		  end if;
		  if l_set_policy = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'SET_POLICY');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'SET_POLICY');
		  end if;
		  if l_purge = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'PURGE');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'PURGE');
		  end if;
		  if l_force = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'FORCE');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'FORCE');
		  end if;
		  if l_global_purge = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'GLOBAL_PURGE');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'GLOBAL_PURGE');
		  end if;
		  if l_connect_via_ron = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_RON');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_RON');
		  end if;
		  if l_connect_via_rau = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_RAU');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_RAU');
		  end if;
		  if l_connect_via_matrix = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_MATRIX');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_MATRIX');
		  end if;
		  if l_connect_via_reports = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_REPORTS');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_REPORTS');
		  end if;
		  if l_connect_via_cli = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_CLI');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_CLI');
		  end if;
		  if l_connect_via_vhv = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_VHV');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_VHV');
		  end if;
		  if l_connect_via_vev = 'Y' then
		    jr_sys_privs.grant_priv(c_get_user_rec.username, 'CONNECT_VIA_VEV');
		  else
		    jr_sys_privs.revoke_priv(c_get_user_rec.username, 'CONNECT_VIA_VEV');
		  end if;							
  	    end;
		commit;
  	  end loop; -- End For Loop
	end;
end; --End PL/SQL block	
/

spool off