#!/bin/sh
#*******************************************************************************
#             PACOR II SOFTWARE
#       Property of the U.S. Government
#            NASA/GSFC/Code 560
#*******************************************************************************
#PURPOSE: Creates roles for table access so that the tables in the PICS database
#         account can be shared.
#
#INVOCATION METHOD: ins_createroles system/pw owneracct logfile
#
#
#DEVELOPMENT HISTORY:
#
#       Author          Change-ID  Release     Date    Description of Change
#       ------          ---------  -------     ----    ---------------------
#    M.Rauschenberger              B4R2.1    08/25/94  Initial release
#    P. Matthews                   B4R2.1    01/06/95  Added admin option to roles
#    I. Horowitz        n/a        ACE R1.0  10/19/95  Added developer role
#    I. Horowitz        ACE0615    ACE R2.0  10/19/95  Changed roles to be consistent
#							with Pacor II db security guidelines
#
#
#NOTES: This script is run by each Oracle Database account owner.
#*********************************************************************************
if test $# -ne 3
then
   echo "****************************************************"
   echo "Error, you must enter an Oracle System/Password," 
   echo "          db_owner_account, and log file name "
   echo "****************************************************"
   exit
fi
echo ""
echo "************************************************************"
echo "Creating roles so users can share be assigned to a group ..."
echo "************************************************************"
echo ""

sqlplus /nolog <<ENDORACLE >> $3
connect $1
clear breaks
#set showmode off
#set term off
#set echo off
set heading off
set pagesize 0
set linesize 100
set feedback off

drop role $2_analyst;
create role $2_analyst not identified;
drop role $2_software;
create role $2_software not identified;

GRANT $2_analyst to $2 with admin option;
GRANT $2_software to $2 with admin option;
GRANT
   CREATE PROCEDURE,
   CREATE SEQUENCE,
   CREATE SESSION,
   CREATE SYNONYM,
   CREATE TABLE,
   CREATE TRIGGER,
   CREATE VIEW
TO $2_software;
GRANT 
   CREATE SESSION,
   CREATE SYNONYM,
   CREATE TABLE
TO $2_analyst;

#
#  Create secure/predefined users
#
DROP USER $2_USER CASCADE;
CREATE USER $2_USER identified by analyst
   default tablespace users
   temporary tablespace temp
   quota 3M on users
   quota unlimited on temp;
GRANT $2_ANALYST to $2_USER;
 
DROP USER $2_PICSPROCESS CASCADE;
CREATE USER $2_PICSPROCESS identified by picsprocess
   default tablespace users
   temporary tablespace temp
   quota 3M on users
   quota unlimited on temp;
GRANT $2_SOFTWARE to $2_PICSPROCESS;
 
DROP USER $2_PDPSPROCESS CASCADE;
CREATE USER $2_PDPSPROCESS identified by pdpsprocess
   default tablespace users
   temporary tablespace temp
   quota 3M on users
   quota unlimited on temp;
GRANT $2_SOFTWARE to $2_PDPSPROCESS;
 

ENDORACLE
echo ""
echo "*******************************"
echo "DPS roles have been created ..."
echo "*******************************"
echo ""
