Rem
Rem $Header: rdbms/demo/schema/order_entry/createUser.sql.sbs /main/3 2009/07/24 13:57:47 celsbern Exp $
Rem
Rem coe_xml.sql.sbs
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      createUser.sql.sbs - Create a user, directory, and XDB folder
Rem
Rem    DESCRIPTION
Rem      .
Rem
Rem    NOTES
Rem      Instantiates createUser.sql. Sets s_oePath
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    celsbern    07/17/09 - added explicit revoke of execute on directory
Rem                           objects
Rem    celsbern    02/24/09 - renamed XMLDIR to SS_OE_XMLDIR
Rem    cbauwens    09/23/04 - cbauwens_bug3031915
Rem    cbauwens    03/16/04 - Created
            


DECLARE
  targetFolder VARCHAR2(256) := '/home';
  result boolean;
BEGIN
  IF (DBMS_XDB.existsResource(targetFolder)) THEN
    DBMS_XDB.deleteResource(targetFolder, DBMS_XDB.DELETE_RECURSIVE);
  END IF;
  
  result := DBMS_XDB.createFolder(targetFolder);
  targetFolder := targetFolder || '/OE';
  result := DBMS_XDB.createFolder(targetFolder);
  DBMS_XDB.setAcl(targetFolder, '/sys/acls/all_all_acl.xml');
  xdb_utilities.createHomeFolder('OE');
END;
/

CONNECT OE/&pass_oe

--Create Oracle directory object
DROP DIRECTORY SS_OE_XMLDIR
/
CREATE DIRECTORY SS_OE_XMLDIR as '%s_oePath%'
/
COMMIT
/

CONNECT sys/&&password_sys AS SYSDBA;
 
revoke execute on directory SS_OE_XMLDIR from OE
/
CONNECT OE/&pass_oe