Access Control List 12c compatible

#!/bin/sh

######################################################################################
# Objective: Grant Access Control List for executing package UTL_SMTP and UTL_INADDR #
# Parameters required (For Oralce Login):                                            #
#     DB User Name    : $1                                                           #
#     DB User Passwd  : $2                                                           #
#     DB TNS Name     : $3                                                           #
######################################################################################

LOGDIR="$HOME/spool"

echo "Enter DB User Name having DBA Privilege"
read DBUsrName

echo "Enter $DBUsrName Password"
read DBUsrPasswd

DBUsrTNS="";
echo "Enter $DBUsrName DB TNS entry"
read DBUsrTNS

echo "Enter HotScan DB User Name"
read HSUsrName

while [ 1 ]
do
echo "Enter The MAILHOST IP Address. Hit Enter if Mail feature is not required"
read MailHost
if [ "x$MailHost" = "x" ]; then
MailHost="0";
break
else
if [ `echo $MailHost | awk -F. '{print NF}'` -ne 4 ]; then
echo "Give proper IP address of Mail Host"
else
break
fi
fi
done

echo "Processing request. Please wait.........."
dateTime=`date +"%Y%m%d%H%M%S"`
fileName=ACL_${HSUsrName}_${dateTime}.sql
fileLog=ACL_${HSUsrName}_${dateTime}.log

rm -rf $fileName $LOGDIR/$fileLog 2>&1 >/dev/null

cat >> $fileName << EOD

WHENEVER SQLERROR EXIT FAILURE
SET TERMOUT ON
SET DEFINE OFF
SET SERVEROUTPUT ON SIZE 1000000
SPOOL $LOGDIR/$fileLog

DECLARE
v_schema    VARCHAR2(100):=UPPER('$HSUsrName');
v_mailhost  VARCHAR2(20):='$MailHost'; --Mail server IP address
v_cnt       NUMBER:=0;
i           NUMBER:=0;
TYPE        t_principal IS TABLE OF DBA_HOST_ACES.PRINCIPAL%TYPE INDEX BY BINARY_INTEGER;
a_principal t_principal;
TYPE        t_privilege IS TABLE OF DBA_HOST_ACES.PRIVILEGE%TYPE INDEX BY BINARY_INTEGER;
a_privilege t_privilege;
TYPE        t_host IS TABLE OF DBA_HOST_ACES.HOST%TYPE INDEX BY BINARY_INTEGER;
a_host      t_host;
BEGIN
SELECT COUNT(*) 
INTO v_cnt
FROM V\$VERSION
WHERE BANNER LIKE 'Oracle Database 12c Enterprise Edition Release 12.%';

IF v_cnt>0 THEN
SELECT COUNT(*)
INTO v_cnt
FROM DBA_NETWORK_ACL_PRIVILEGES
WHERE ACL LIKE '%simple_acl.xml%';

IF v_cnt > 0 THEN
DBMS_OUTPUT.PUT_LINE('One or more schemas already granted with 11g ACLs. Re-granting 12c ACE privileges!');

FOR rec_acl IN (
SELECT PRINCIPAL,PRIVILEGE,HOST
FROM DBA_HOST_ACES
ORDER BY ACE_ORDER
)
LOOP
a_principal(i) := rec_acl.PRINCIPAL;
a_privilege(i) := rec_acl.PRIVILEGE;
a_host(i)      := rec_acl.HOST;
i := i + 1;
END LOOP;

FOR i IN a_principal.FIRST .. a_principal.LAST
LOOP
BEGIN
DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE(
host             => a_host(i),     
remove_empty_acl => TRUE,
ace              => xs\$ace_type(
privilege_list => xs\$name_list(a_privilege(i)),
principal_name => a_principal(i),
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege removed from '||a_principal(i)||' successfully!');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to remove ACL privilege to '||a_principal(i)||' with '||SQLERRM);
END;
END LOOP;

FOR i IN a_principal.FIRST .. a_principal.LAST
LOOP
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => a_host(i),
ace  => xs\$ace_type(
privilege_list => xs\$name_list(a_privilege(i)),
principal_name => a_principal(i),
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege granted to '||a_principal(i)||' successfully!');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to append ACL privilege to '||a_principal(i)||' with '||SQLERRM);
END;
END LOOP;
END IF;

SELECT COUNT(*)
INTO v_cnt
FROM DBA_HOST_ACES
WHERE PRINCIPAL=v_schema;

IF v_cnt<>0 THEN
DBMS_OUTPUT.PUT_LINE('ACL privilege already granted to user '||v_schema);
ELSE
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace  => xs\$ace_type(
privilege_list => xs\$name_list('RESOLVE'),
principal_name => v_schema,
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege granted to user '||v_schema||' successfully!');
END IF;

IF LENGTH(TRIM(v_mailhost))>0 AND TRIM(v_mailhost)<>'0' THEN
SELECT COUNT(*)
INTO v_cnt
FROM DBA_HOST_ACES
WHERE PRINCIPAL=v_schema
AND HOST=v_mailhost;

IF v_cnt<>0 THEN
DBMS_OUTPUT.PUT_LINE('Required ACL privilege already granted to user '||v_schema||' on mail host '||v_mailhost); 
ELSE
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host       => v_mailhost,
lower_port => 25,
upper_port => 25,
ace        => xs\$ace_type(
privilege_list => xs\$name_list('CONNECT'),
principal_name => v_schema,
principal_type => xs_acl.ptype_db
)
);
DBMS_OUTPUT.PUT_LINE('ACL privilege granted to user '||v_schema||' successfully on mail host '||v_mailhost);
END IF;
END IF;

DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);
ELSE
DBMS_OUTPUT.PUT_LINE('Not executed on 12c database; Please execute script only on Oracle Release 12c!');
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to append ACL privilege to '||v_schema||' with '||SQLERRM);
END;
/
SPOOL OFF;
EXIT;

EOD

if [ -z "$DBUsrTNS" ]; then
string="$DBUsrName/$DBUsrPasswd"
else
string="$DBUsrName/$DBUsrPasswd@$DBUsrTNS "
fi

dba_flag=$(sqlplus -S $string << EOF
VARIABLE DBA_FLAG VARCHAR2;
SET FEEDBACK OFF HEADING OFF
DECLARE
v_cnt NUMBER;
BEGIN
:DBA_FLAG := 'N';
SELECT COUNT(*)
INTO v_cnt
FROM USER_ROLE_PRIVS
WHERE GRANTED_ROLE='DBA';
IF v_cnt > 0 THEN
:DBA_FLAG:='Y';
END IF;
END;
/
PRINT :DBA_FLAG
EOF
)

dba_flag="`echo ${dba_flag}|sed '/./,$!d'`"; export dba_flag

if [ "$dba_flag" = "N" ]; then
echo
echo "DB user is not DBA; Please re-execute script by providing DBA user credential"
echo
exit
fi

sqlplus -s $string @$fileName 2>&1 >>$LOGDIR/$fileLog
status=$?

echo "Processing Completed.........."

if [ $status -eq 0 ]; then
mv $fileName $LOGDIR/
else
grep -i Error $LOGDIR/$fileLog 2>&1 >/dev/null
if [ $? -eq 0 ] ; then
mv $fileName $LOGDIR/
echo "Error Occurred!!!"
fi
fi