-- Usage:  sqlplus sys/syspassword as sysdba @create_oracle_user.sql USERNAME
-- 
--   Where USERNAME is the oracle account name to be created.  The USERNAME
--   will also be the account password.
-- 
-- If you wish to use tablespaces other than USERS and TEMP for the account
-- defaults, change these variables:

define defaulttbs = USERS
define temptbs = TEMP


SET VERIFY OFF
define username = &1

DROP USER &username CASCADE;
CREATE USER &username IDENTIFIED BY &username
       DEFAULT TABLESPACE &defaulttbs
       TEMPORARY TABLESPACE &temptbs;
ALTER USER &username QUOTA UNLIMITED ON &defaulttbs;
GRANT CREATE SESSION,
      ALTER SESSION,
      CREATE CLUSTER,
      CREATE INDEXTYPE,
      CREATE OPERATOR,
      CREATE PROCEDURE,
      CREATE SEQUENCE,
      CREATE SYNONYM,
      CREATE TABLE,
      CREATE TRIGGER,
      CREATE TYPE,
      CREATE VIEW,
      CREATE ANY DIRECTORY,
      DROP ANY DIRECTORY,
      ADMINISTER DATABASE TRIGGER,
      QUERY REWRITE
   TO &username;
GRANT EXECUTE ON dbms_pipe
   TO &username;

EXIT
