#!/bin/sh
#*******************************************************************************
#             PACOR II SOFTWARE
#       Property of the U.S. Government
#            NASA/GSFC/Code 560
#*******************************************************************************
#PURPOSE: Grants table access to DPS DB Oracle roles so that
#         the tables in the database account can be accessed.
#
#INVOCATION METHOD: ins_grantroles ownerid/ownerpasswd logfilename
#
#
#DEVELOPMENT HISTORY:
#
#       Author          Change-ID  Release    Date    Description of Change
#       ------          ---------  -------    ----    ---------------------
#    P. Matthews                   B3R2     07/07/94  Initial release
#    P. Matthews                   B3R2     08/01/94  Added access to 
#                                                       sequence numbers
#    M.Rauschenberger              B4R2.1   08/25/94  Changed parameters
#    P. Matthews                   B4R2.1   11/16/94  Added condition on table name
#    P. Matthews                   B4R2.1   01/13/95  Modified condition on table
#                                                       name to exclude gen tables
#   I. Horowitz         n/a       ACE R1.0  10/19/95  Corrected argument list 
#                                                      description
#   I. Horowitz         ACE0615	   R2.0	    07/16/96  Corrected roles access privileges;
#							updated to not access QAWS tables.
#   R. Wiechert         ISTP       R1.2     04/21/98  Relocated temporary files to /tmp
#
#
#NOTES: This script is run by each Oracle Database account owner.
#*********************************************************************************
if test $# -ne 2
then
   echo '*******************************************************************'
   echo 'Error, you must enter an Oracle DB Owner/Password and log file name '
   echo '*******************************************************************'
   exit
fi
echo ""
echo "**************************************************"
echo "Creating role grants so users can share tables ..."
echo "**************************************************"
echo ""

tmp_roles_file=/tmp/oracle_roles_$$.sql
rm -f $tmp_roles_file

owneracct=`echo $1 | awk -F/ '{ print $1 }'`

sqlplus -s /nolog <<ENDORACLE >> /dev/null
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
spool $tmp_roles_file

SELECT 'grant select on '|| RTRIM(TABLE_NAME)||' to '|| UPPER(user)||'_ANALYST;' 
from USER_TABLES 
where tablespace_name != 'QAWS_DATA'
and   (table_name like 'DB_%'
and   table_name != 'DB_PRODUCTHOST'
and   table_name != 'DB_IDFSETUP'
and   table_name != 'DB_ORACLELOGIN'
and   table_name != 'DB_QORACLELOGIN')
or    table_name like 'PDP_%'
or    table_name like 'PSC_%';

SELECT 'grant select, insert, update, delete on '|| RTRIM(TABLE_NAME)||' to '||
UPPER(user) || '_SOFTWARE;' 
from USER_TABLES 
where (table_name like 'DB_%'
or    table_name like 'PDP_%'
or    table_name like 'PSC_%')
and   tablespace_name = 'DYNAMIC_DATA';
 
SELECT 'grant select on '|| RTRIM(TABLE_NAME)||' to '|| UPPER(user) || '_SOFTWARE;' 
from USER_TABLES 
where (table_name like 'DB_%'
and   table_name != 'DB_PRODUCTHOST'
and   table_name != 'DB_IDFSETUP'
and   table_name != 'DB_ORACLELOGIN')
or    table_name like 'PDP_%'
and   tablespace_name = 'STATIC_DATA';
 
SELECT 'grant select on '|| RTRIM(SEQUENCE_NAME)||' to '|| UPPER(user)||'_ANALYST;' 
from USER_SEQUENCES;

SELECT 'grant select, alter on '|| RTRIM(SEQUENCE_NAME)||' to '|| UPPER(user) || '_SOFTWARE;' 
from USER_SEQUENCES;
 
GRANT UPDATE (LASTHEARTBEATTIME) on db_resourcecatalog to ${owneracct}_SOFTWARE;
GRANT UPDATE (LASTHEARTBEATTIME) on db_resourcecatalog to ${owneracct}_ANALYST;
GRANT UPDATE (RESOURCESTATUS) on db_resourcecatalog to ${owneracct}_SOFTWARE;
GRANT UPDATE (RESOURCESTATUS) on db_resourcecatalog to ${owneracct}_ANALYST;
GRANT SELECT on db_ProductHost to ${owneracct}_PDPSPROCESS;
GRANT SELECT on db_IDFSetup to ${owneracct}_PICSPROCESS;
GRANT UPDATE on db_AppDestDefaults to ${owneracct}_PICSPROCESS;
GRANT UPDATE on db_ResourceConstituentDefaults to ${owneracct}_PICSPROCESS;

spool off
set term on
set echo off
ENDORACLE

sqlplus /nolog <<ENDORACLE >> $2
connect $1
start $tmp_roles_file
quit
ENDORACLE

rm -f $tmp_roles_file
echo ""
echo "************************************************"
echo "Role grants to DPS tables has been completed ..."
echo "************************************************"
echo ""
