/*=======================================================================+
 |        Copyright (c) 1998, 2004 Oracle.    All rights reserved.       |
 +=======================================================================+
 | $Header: wfdemou9.sql 26.5 2005/02/02 23:51:33 dlam ship $
 | FILENAME
 |   wfdemou9.sql
 |
 | DESCRIPTION
 |  Create Workflow Demonstration users
 |  For 9i or later database only, modeled after wfdemou.sql v26.2
 | USAGE 
 |  sqlplus system/system_pw @wfdemou <WF_ACCT> <SYS_PW> <DEF TABLE> <TMP TABLE>
 =======================================================================*/

WHENEVER SQLERROR CONTINUE

connect sys/&2 as sysdba

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 &3;
alter  user BLEWIS   default tablespace &3;
alter  user KWALKER  default tablespace &3;
alter  user CDOUGLAS default tablespace &3;
alter  user WFADMIN  default tablespace &3;
alter  user SYSADMIN default tablespace &3;

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

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.
-- WF_LOCAL_USERS has been made obsolete from 2.6.3 onwards
-- Replaced by wf_local_roles where user_flag = 'Y'
delete from &1..wf_local_roles lu
where 2 = (select count(*) from &1..wf_users
           where name = lu.name)
and lu.user_flag = 'Y';

commit;
exit;