/*=======================================================================+
 |  Copyright (c) 1998 Oracle Corporation Redwood Shores, California, USA|
 |                            All rights reserved.                       |
 +=======================================================================+
$Header: wfdemou.sql 26.5 2005/02/02 23:51:07 dlam ship $
 | FILENAME
 |   wfdemou.sql
 |
 | DESCRIPTION
 |	Create Workflow Demonstration users
 | NOTE: must be run from sys account so as to create users
 |      USAGE sqlplus sy/sys_pw @wfdemou <WF_ACCT_NAME> <SYS_PWD>
 =======================================================================*/

WHENEVER SQLERROR CONTINUE

connect sys/&2 

column  default_tablespace new_value dft 
column  temporary_tablespace new_value dft_temp
 
select default_tablespace 
from dba_users 
where username=upper('&1');

select temporary_tablespace 
from dba_users 
where username=upper('&1');

create user SPIERSON identified by SPIERSON;
create user BLEWIS   identified by BLEWIS;
create user KWALKER  identified by KWALKER;
create user CDOUGLAS identified by CDOUGLAS;
create user WFADMIN  identified by WFADMIN;
create user SYSADMIN identified by SYSADMIN;

alter  user SPIERSON default tablespace &dft;
alter  user BLEWIS   default tablespace &dft;
alter  user KWALKER  default tablespace &dft;
alter  user CDOUGLAS default tablespace &dft;
alter  user WFADMIN  default tablespace &dft;
alter  user SYSADMIN default tablespace &dft;

alter  user SPIERSON temporary tablespace &dft_temp;
alter  user BLEWIS   temporary tablespace &dft_temp;
alter  user KWALKER  temporary tablespace &dft_temp;
alter  user CDOUGLAS temporary tablespace &dft_temp;
alter  user WFADMIN  temporary tablespace &dft_temp;
alter  user SYSADMIN temporary tablespace &dft_temp;

grant connect,resource to SPIERSON;
grant connect,resource to BLEWIS;
grant connect,resource to KWALKER;
grant connect,resource to CDOUGLAS;
grant connect,resource to WFADMIN;
grant connect,resource to SYSADMIN;

alter  user SPIERSON account LOCK password expire;
alter  user BLEWIS   account LOCK password expire;
alter  user KWALKER  account LOCK password expire;
alter  user CDOUGLAS account LOCK password expire;
alter  user WFADMIN  account LOCK password expire;
alter  user SYSADMIN account LOCK password expire;

--duplicate users may exist as database users and rows 
--in wf_local_users. Check and delete from wf_local_users.
delete from &1..wf_local_roles partition (WF_LOCAL_ROLES) lu
where 2 = (select count(*) from &1..wf_users
           where name = lu.name)
and   lu.user_flag = 'Y';


commit;
exit;