#!/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 #
# MAILHOST : $4 (For Email Notification) #
######################################################################################
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
if [ -z "$DBUsrTNS" ]; then
string="$DBUsrName/$DBUsrPasswd"
else
string="$DBUsrName/$DBUsrPasswd@$DBUsrTNS "
fi
dba_flag=`sqlplus -s $string << EOF
SET FEEDBACK OFF HEADING OFF
SELECT DECODE(COUNT(*),'0','N','Y') FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE='DBA';
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
elif [ "$dba_flag" != "Y" ]; then
echo
echo "Invalid DB user credential; Please re-execute script by providing valid DBA user credential"
echo
exit
fi
echo "Processing request. Please wait.........."
dateTime=`date +"%Y%m%d%H%M%S"`
fileName=ACL_${HSUsrName}_${dateTime}.sql
fileName11g=ACL11g_${HSUsrName}_${dateTime}.sql
fileName12c=ACL12c_${HSUsrName}_${dateTime}.sql
fileLog=ACL_${HSUsrName}_${dateTime}.log
mkdir -p "$LOGDIR" 2>&1 >/dev/null
rm -rf $fileName $fileName11g $fileName12c $LOGDIR/$fileLog 2>&1 >/dev/null
cat >> $fileName11g << 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_acl VARCHAR2(20):='simple_acl.xml';
v_acl_comment VARCHAR2(100);
i_cnt NUMBER:=0;
i_acl_present NUMBER:=0;
BEGIN
v_acl_comment := 'ACL for user '||v_schema;
SELECT NVL(SUM(INSTR(ACL,'/'||v_acl)),0)
INTO i_acl_present
FROM DBA_NETWORK_ACLS;
IF i_acl_present=0 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Creating ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(v_acl,v_acl_comment,v_schema,TRUE,'resolve');
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS created..');
DBMS_OUTPUT.PUT_LINE('Assigning ACL ADDRESS..');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(v_acl,'*');
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS assigned..');
EXCEPTION WHEN OTHERS THEN
i_cnt:=1;
DBMS_OUTPUT.PUT_LINE('ACL'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
ELSE
DBMS_OUTPUT.PUT_LINE('ACL'||v_acl||' Already Present');
i_cnt:=1;
END IF;
IF i_cnt=1 THEN
BEGIN
dbms_output.put_line('Adding ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.add_privilege(v_acl,v_schema,TRUE,'resolve',null,null,null);
dbms_output.put_line('ACL ADDRESS Added..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Add'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;
IF LENGTH(TRIM(v_mailhost))>0 AND TRIM(v_mailhost)<>'0' THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Adding ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.add_privilege(v_acl,v_schema,TRUE,'connect',null,null,null);
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS Added..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Mail Add'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('ACL assigning Mail Server '||v_mailhost);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(v_acl,v_mailhost,25);
DBMS_OUTPUT.PUT_LINE('ACL assigned..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Mail Add'||v_acl||' Assigned for Mail Server '||v_mailhost);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error creating ACL:');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
COMMIT;
SPOOL OFF;
EXIT;
EOD
cat >> $fileName12c << 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
i_cnt NUMBER:=0;
i NUMBER:=0;
i_port NUMBER;
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 i_cnt
FROM DBA_NETWORK_ACL_PRIVILEGES
WHERE ACL NOT LIKE 'NETWORK_ACL%'
AND LENGTH(ACL)<>44;
IF i_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
A.ACL,
B.HOST,
B.LOWER_PORT,
B.UPPER_PORT,
B.PRINCIPAL,
B.PRIVILEGE
FROM DBA_NETWORK_ACL_PRIVILEGES A,DBA_HOST_ACES B
WHERE A.PRINCIPAL=B.PRINCIPAL
AND UPPER(A.PRIVILEGE)=UPPER(B.PRIVILEGE)
AND (ACL NOT LIKE 'NETWORK_ACL%'
AND LENGTH(ACL)<>44)
ORDER BY B.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
SELECT DECODE(a_host(i),'*',NULL,25) INTO i_port FROM DUAL;
DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE(
host => a_host(i),
lower_port => i_port,
upper_port => i_port,
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
SELECT DECODE(a_host(i),'*',NULL,25) INTO i_port FROM DUAL;
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => a_host(i),
lower_port => i_port,
upper_port => i_port,
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 i_cnt
FROM DBA_HOST_ACES
WHERE PRIVILEGE='RESOLVE'
AND PRINCIPAL=v_schema;
IF i_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 i_cnt
FROM DBA_HOST_ACES
WHERE PRINCIPAL=v_schema
AND HOST=v_mailhost;
IF i_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);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to append ACL privilege to '||v_schema||' with '||SQLERRM);
END;
/
SPOOL OFF;
EXIT;
EOD
vTable="V\$VERSION"; export vTable
version_flag=`sqlplus -S $string << EOF
SET FEEDBACK OFF HEADING OFF
SELECT DECODE(INSTR(BANNER,'11g'),0,'12c','11g') FROM $vTable WHERE BANNER LIKE 'Oracle Database%Edition Release%';
EOF
`
version_flag="`echo ${version_flag}|sed '/./,$!d'`"; export version_flag
if [ "$version_flag" = "11g" ]; then
mv $fileName11g $fileName 2>&1 >/dev/null
rm $fileName12c 2>&1 >/dev/null
elif [ "$version_flag" = "12c" ]; then
mv $fileName12c $fileName 2>&1 >/dev/null
rm $fileName11g 2>&1 >/dev/null
fi
echo "Oracle Version is $version_flag"
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
######################################################################################
# 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 #
# MAILHOST : $4 (For Email Notification) #
######################################################################################
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
if [ -z "$DBUsrTNS" ]; then
string="$DBUsrName/$DBUsrPasswd"
else
string="$DBUsrName/$DBUsrPasswd@$DBUsrTNS "
fi
dba_flag=`sqlplus -s $string << EOF
SET FEEDBACK OFF HEADING OFF
SELECT DECODE(COUNT(*),'0','N','Y') FROM USER_ROLE_PRIVS WHERE GRANTED_ROLE='DBA';
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
elif [ "$dba_flag" != "Y" ]; then
echo
echo "Invalid DB user credential; Please re-execute script by providing valid DBA user credential"
echo
exit
fi
echo "Processing request. Please wait.........."
dateTime=`date +"%Y%m%d%H%M%S"`
fileName=ACL_${HSUsrName}_${dateTime}.sql
fileName11g=ACL11g_${HSUsrName}_${dateTime}.sql
fileName12c=ACL12c_${HSUsrName}_${dateTime}.sql
fileLog=ACL_${HSUsrName}_${dateTime}.log
mkdir -p "$LOGDIR" 2>&1 >/dev/null
rm -rf $fileName $fileName11g $fileName12c $LOGDIR/$fileLog 2>&1 >/dev/null
cat >> $fileName11g << 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_acl VARCHAR2(20):='simple_acl.xml';
v_acl_comment VARCHAR2(100);
i_cnt NUMBER:=0;
i_acl_present NUMBER:=0;
BEGIN
v_acl_comment := 'ACL for user '||v_schema;
SELECT NVL(SUM(INSTR(ACL,'/'||v_acl)),0)
INTO i_acl_present
FROM DBA_NETWORK_ACLS;
IF i_acl_present=0 THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Creating ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(v_acl,v_acl_comment,v_schema,TRUE,'resolve');
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS created..');
DBMS_OUTPUT.PUT_LINE('Assigning ACL ADDRESS..');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(v_acl,'*');
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS assigned..');
EXCEPTION WHEN OTHERS THEN
i_cnt:=1;
DBMS_OUTPUT.PUT_LINE('ACL'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
ELSE
DBMS_OUTPUT.PUT_LINE('ACL'||v_acl||' Already Present');
i_cnt:=1;
END IF;
IF i_cnt=1 THEN
BEGIN
dbms_output.put_line('Adding ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.add_privilege(v_acl,v_schema,TRUE,'resolve',null,null,null);
dbms_output.put_line('ACL ADDRESS Added..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Add'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;
IF LENGTH(TRIM(v_mailhost))>0 AND TRIM(v_mailhost)<>'0' THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Adding ACL ADDRESS '||v_acl);
DBMS_NETWORK_ACL_ADMIN.add_privilege(v_acl,v_schema,TRUE,'connect',null,null,null);
DBMS_OUTPUT.PUT_LINE('ACL ADDRESS Added..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Mail Add'||v_acl||' Present');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('ACL assigning Mail Server '||v_mailhost);
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(v_acl,v_mailhost,25);
DBMS_OUTPUT.PUT_LINE('ACL assigned..');
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ACL Mail Add'||v_acl||' Assigned for Mail Server '||v_mailhost);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error creating ACL:');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
COMMIT;
SPOOL OFF;
EXIT;
EOD
cat >> $fileName12c << 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
i_cnt NUMBER:=0;
i NUMBER:=0;
i_port NUMBER;
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 i_cnt
FROM DBA_NETWORK_ACL_PRIVILEGES
WHERE ACL NOT LIKE 'NETWORK_ACL%'
AND LENGTH(ACL)<>44;
IF i_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
A.ACL,
B.HOST,
B.LOWER_PORT,
B.UPPER_PORT,
B.PRINCIPAL,
B.PRIVILEGE
FROM DBA_NETWORK_ACL_PRIVILEGES A,DBA_HOST_ACES B
WHERE A.PRINCIPAL=B.PRINCIPAL
AND UPPER(A.PRIVILEGE)=UPPER(B.PRIVILEGE)
AND (ACL NOT LIKE 'NETWORK_ACL%'
AND LENGTH(ACL)<>44)
ORDER BY B.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
SELECT DECODE(a_host(i),'*',NULL,25) INTO i_port FROM DUAL;
DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE(
host => a_host(i),
lower_port => i_port,
upper_port => i_port,
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
SELECT DECODE(a_host(i),'*',NULL,25) INTO i_port FROM DUAL;
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => a_host(i),
lower_port => i_port,
upper_port => i_port,
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 i_cnt
FROM DBA_HOST_ACES
WHERE PRIVILEGE='RESOLVE'
AND PRINCIPAL=v_schema;
IF i_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 i_cnt
FROM DBA_HOST_ACES
WHERE PRINCIPAL=v_schema
AND HOST=v_mailhost;
IF i_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);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unable to append ACL privilege to '||v_schema||' with '||SQLERRM);
END;
/
SPOOL OFF;
EXIT;
EOD
vTable="V\$VERSION"; export vTable
version_flag=`sqlplus -S $string << EOF
SET FEEDBACK OFF HEADING OFF
SELECT DECODE(INSTR(BANNER,'11g'),0,'12c','11g') FROM $vTable WHERE BANNER LIKE 'Oracle Database%Edition Release%';
EOF
`
version_flag="`echo ${version_flag}|sed '/./,$!d'`"; export version_flag
if [ "$version_flag" = "11g" ]; then
mv $fileName11g $fileName 2>&1 >/dev/null
rm $fileName12c 2>&1 >/dev/null
elif [ "$version_flag" = "12c" ]; then
mv $fileName12c $fileName 2>&1 >/dev/null
rm $fileName11g 2>&1 >/dev/null
fi
echo "Oracle Version is $version_flag"
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
No comments:
Post a Comment