dbando: dbando script para monitorear cambios realizados a datos sensibles

Buen dia,

El siguiente script en shell fue un requerimiento para tener un monitoreo de los usuarios que realizan cambios a datos sensibles en una tabla critica y enviar correo al momento para tomar acciones en caso de cualquier aclaracion, para esto existe un Trigger que inserta los datos modificados en una tabla de logs de donde se obtienen los datos de los usuarios que han cambiado dicho valor, tambien se utiliza la tabla donde se realizan los cambios y ademas se utiliza una tabla donde se tienen registrados los nombres completos de los usuarios y su nombre de "usuario", por ejemplo el mio seria LTORRES y mi nombre completo Luis Torres.

El script es el siguiente:

#!/bin/ksh

# |----------------------------------------------------------|
# |-- Script que monitorea cambios de CUALQUIERCOSA --|
# |----------------------------------------------------------|

#LETG 14 de Mayo del 2015#

# |-------------------------------|
# |-- Declaracion de variables --|
# |-------------------------------|

#En /opt/oracle/scripts/variable10g.scipts se encuentran las variables de ambiente

while read VARIABLE
do
export $VARIABLE
done < /opt/oracle/scripts/variable10g.scipts
export PATH=$PATH:$ORACLE_HOME/bin:.

#Se genero con anterioridad el password encryptado con OPENSSL

PASS_ENTRADA=$(${OPENSSL} enc ${OPENSSL_ENCRIPTION} -d -in ${DIRECCION_ACTUAL}/.passdecrypt -pass pass:$((cat ${MASTER_PASS})|awk -F":" '{print $2}'))
USER_PASS=$(${OPENSSL} enc ${OPENSSL_ENCRIPTION} -d -in ${DIRECCION_ACTUAL}/.pwd${USUARIO_ORACLE} -pass pass:${PASS_ENTRADA})

# |--- ------------------------------------------------|
# |-- Rutas constantes para desposito de informacion --|
# |----------------------------------------------------|

USR_INFO=/opt/oracle/scripts/allinf.csv
MAIL_INFO=/opt/oracle/scripts/allinf2.csv
TABLE_INFO=/opt/oracle/scripts/allinf3.csv
NOMBRE_USR=/opt/oracle/scripts/allinf6.csv
DATE_INFO=/opt/oracle/scripts/allinf4.out
DATE_INFO2=/opt/oracle/scripts/allinf7.out
DATE_COUNT=/opt/oracle/scripts/allinf5.out
GOLD_LIST='juancho_casitas_@algo.mx'
today_date=$(date +"%d_%m_%Y_%H_%M_%S")
SENDER='Security_Manager@algo.mx'
TMP_MAIL_FILE=/tmp/salida_cambio.out

# |----------------------------------------------------|
# |-- Obtencion de fecha de logueo para comparacion --|
# |----------------------------------------------------|

#El script corre cada 5 minutos por lo cual se obtiene la fecha de cambio del registro
#entre los ultimos cinco minutos y la fecha actual, para evitar que se siga enviando
#el correo cada que corra el cron

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
set feed off;
set pagesize 0;
column machine format a17;
column sid format 999;
ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
spool $DATE_COUNT
SELECT COUNT(FECHA_CAMBIO) FROM DUEÑO.TABLA where FECHA_CAMBIO BETWEEN SYSDATE-.084/24 AND SYSDATE
and rownum=1;

spool off;
EOF

compare=$(cat $DATE_COUNT)

if [ $compare -gt 0 ]

then

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
ALTER SESSION SET NLS_DATE_FORMAT="MM/DD/YYYY";
set feed off;
set pagesize 0;
spool $DATE_INFO
SELECT FECHA_CAMBIO FROM DUEÑO.TABLA where TRUNC (FECHA_CAMBIO) = TRUNC (SYSDATE)
and rownum=1;
spool off;
EOF

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
set feed off;
set pagesize 0;
spool $DATE_INFO2
SELECT FECHA_CAMBIO FROM DUEÑO.TABLA where FECHA_CAMBIO BETWEEN SYSDATE-.084/24 AND SYSDATE;
spool off;
EOF

# |-----------------------------------------------------|
# |-- Generacion de archivo maestro de comparacion --|
# |-----------------------------------------------------|

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
set feed off;
set pagesize 0;
set lines 300;
spool $USR_INFO
SELECT DISTINCT USUARIO ||','||F_ACTUAL||','||PROGRAMA||','||FECHA_CAMBIO||','||USU_MODIF||','||COD_PROV||','||COD_DEPTO||','||
COD_MUNIC||','||COD_LOCAL||','||NOM_LOCAL||','||AREA_EJEC||','||USR_NUMBER1||','||USR_NUMBER2||','||
USR_NUMBER3||','||USR_VARCHAR1||','||USR_VARCHAR2||','||USR_VARCHAR3||','||USR_DATETIME1||','||USR_DATETIME2
FROM (
SELECT DISTINCT USUARIO, F_ACTUAL, PROGRAMA, FECHA_CAMBIO, USU_MODIF, COD_PROV, COD_DEPTO, COD_MUNIC, COD_LOCAL,
NOM_LOCAL, AREA_EJEC, USR_NUMBER1,USR_NUMBER2, USR_NUMBER3, USR_VARCHAR1, USR_VARCHAR2,
USR_VARCHAR3, USR_DATETIME1, USR_DATETIME2 FROM DUEÑO.TABLA where FECHA_CAMBIO BETWEEN SYSDATE-.084/24 AND SYSDATE and rownum=1);
spool off;
EOF

CODIGO=$(cat ${USR_INFO}|awk -F"," '{printf "%s\n", $9}')
USUARIO_MAIL=$(cat ${USR_INFO}|awk -F"," '{printf "%s\n", $5}')

# |-----------------------------------------------------|
# |-- Obtencion de datos de usuario para denunciarlo --|
# |-----------------------------------------------------|

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
set pagesize 0;
set lines 300;
spool $MAIL_INFO
SELECT DISTINCT USUARIO ||','||F_ACTUAL||','||PROGRAMA||','||FECHA_CAMBIO||','||USU_MODIF||','||COD_PROV||','||COD_DEPTO||','||COD_MUNIC||','||COD_LOCAL||','||NOM_LOCAL||
','||AREA_EJEC||','||USR_NUMBER1||','||USR_NUMBER2||','||USR_NUMBER3||','||USR_VARCHAR1||','||USR_VARCHAR2||','||USR_VARCHAR3||','||USR_DATETIME1||','||USR_DATETIME2
FROM (
SELECT DISTINCT USUARIO, F_ACTUAL, PROGRAMA, FECHA_CAMBIO, USU_MODIF, COD_PROV, COD_DEPTO, COD_MUNIC, COD_LOCAL, NOM_LOCAL, AREA_EJEC, USR_NUMBER1, USR_NUMBER2, USR_NUMBE
R3, USR_VARCHAR1, USR_VARCHAR2, USR_VARCHAR3, USR_DATETIME1, USR_DATETIME2 FROM DUEÑO.TABLA where FECHA_CAMBIO BETWEEN SYSDATE-.084/24 AND SYSDATE and row
num=1 order by 4 desc);
spool off;
EOF

USUARIO=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $1}')
F_ACTUAL=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $2}')
#PROGRAMA=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $3}')
FECHA_CAMBIO=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $4}')
USU_MODIF=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $5}')
COD_PROV=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $6}')
COD_DEPTO=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $7}')
COD_MUNIC=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $8}')
COD_LOCAL=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $9}')
NOM_LOCAL=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $10}')
AREA_EJEC=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $11}')
USR_NUMBER1=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $12}')
USR_NUMBER2=$(cat ${MAIL_INFO}|awk -F"," '{printf "%s\n", $13}')

# |-----------------------------------------------------|
# |-- Obtencion de datos de tabla para comparacion --|
# |-----------------------------------------------------|

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
ALTER SESSION SET NLS_DATE_FORMAT='dd-mm-yyyy hh24:mi:ss';
set feed off;
set pagesize 0;
set lines 300;
spool $TABLE_INFO
SELECT DISTINCT USUARIO ||','||F_ACTUAL||','||PROGRAMA||','||COD_PROV||','||COD_DEPTO||','||COD_MUNIC||','||COD_LOCAL||','||NOM_LOCAL||','||AREA_EJEC||','||USR_NUMBE
R1||','||USR_NUMBER2||','||USR_NUMBER3||','||USR_VARCHAR1||','||USR_VARCHAR2||','||USR_VARCHAR3||','||USR_DATETIME1||','||USR_DATETIME2 FROM (
SELECT USUARIO, F_ACTUAL, PROGRAMA, COD_PROV, COD_DEPTO, COD_MUNIC, COD_LOCAL, NOM_LOCAL, AREA_EJEC,USR_NUMBER1, USR_NUMBER2, USR_NUMBER3, USR_VARCHAR1, USR_VARCHAR2
, USR_VARCHAR3,USR_DATETIME1, USR_DATETIME2 FROM DUEÑO.TABLA2 where COD_LOCAL=$CODIGO);
spool off;
EOF

# |-----------------------------------------------------|
# |-- Obtencion de datos de usuario para cotejar --|
# |-----------------------------------------------------|

sqlplus -s ${USUARIO_ORACLE}/${USER_PASS}@$ORACLE_SID<<EOF
set feed off;
set pagesize 0;
set lines 300;
spool $NOMBRE_USR
SELECT USUARIO ||','||F_ACTUAL||','||PROGRAMA||','||NOM_USR||','||DESC_USR||','||COD_UNICOM||','||COD_CENTEC||','||MAX_NUM_SESSIONES||','||PAN_COD_ALMACE
FROM (
SELECT USUARIO,F_ACTUAL,PROGRAMA,NOM_USR,DESC_USR,COD_UNICOM,COD_CENTEC,MAX_NUM_SESSIONES,PAN_COD_ALMACE
FROM OPENS.USUARIOS
WHERE NOM_USR='$USUARIO_MAIL');
spool off;
EOF

NOM_USR=$(cat ${NOMBRE_USR}|awk -F"," '{printf "%s\n", $5}')
USUARIO_TABLE=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $1}')
FECHA_ACTUAL=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $2}')
#PROGRAMA_TABLE=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $3}')
CODIGO_PROVINCIA=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $4}')
CODIGO_DEPARTAMENTO=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $5}')
CODIGO_MUNICIPIO=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $6}')
CODIGO_SENSIBLE=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $7}')
DATO_SENSIBLE=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $8}')
AREA_EJECUCION=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $9}')
CODIGO_POSTAL=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $10}')
ESTRATO=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $11}')
USR_NUMBER3=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $12}')
USR_VARCHAR1=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $13}')
USR_VARCHAR2=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $14}')
USR_VARCHAR3=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $15}')
USR_DATETIME1=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $16}')
USR_DATETIME2=$(cat ${TABLE_INFO}|awk -F"," '{printf "%s\n", $17}')

FECHA=$(cat ${DATE_INFO2})

if [ "$NOM_LOCAL" = "$DATO_SENSIBLE" ];

then

echo "Nada que hacer";

else

cat /dev/null>$TMP_MAIL_FILE

echo "MIME-Version: 1.0
From: $SENDER
To: $GOLD_LIST
Subject: Importante, un usuario ha cambiado el valor de un campo
Content-Type: text/html

<html>
<body>
<table>
<tr>
<td colspan=4><center>Reporte de cambios en datos sensibles</center></td></tr>
</tr>
<tr>
<td>Campo modificado </td>
<td>NOMBRE_CAMPO </td>
</tr>
<tr>
<td>Usuario que realizo el cambio </td>
<td>$USUARIO_MAIL - $NOM_USR </td>
</tr>
<tr>
<td>Dato Anterior </td>
<td>$NOM_LOCAL </td>
</tr>
<tr>
<td>Dato Nuevo </td>
<td>$DATO_SENSIBLE </td>
</tr>
<tr>
<td>Codigo del dato sensible </td>
<td>$CODIGO_SENSIBLE </td>
</tr>
<tr>
<td>Fecha de modificacion </td>
<td>$FECHA </td>
</tr>

</table>
 

Reporte de cambios realizados a datos sensibles, para notificar y tomar acciones en caso de ser requerida cualquier aclaracion. Equipo de seguridad NOMBRE_EMPRESA

Security_Manager@algo.com
 
 
 

 * Este es un email auto generado. Favor de no responderlo. *
 
 

Este correo electronico es confidencial y/o puede contener informacion privilegiada.
Si usted no es su destinatario o no es alguna persona autorizada por este para recibir sus correos electronicos,
NO debera usted utilizar, copiar, revelar, o tomar ninguna accion basada en este correo electronico o cualquier otra informacion incluida en el, favor de borrar e
ste correo electronico totalmente y sus anexos.

Nota: Los acentos y caracteres especiales fueron omitidos para su correcta lectura en cualquier medio electronico.

This e-mail is confidential and/or may contain privileged information.
If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any ot
her information herein, delete this e-mail and its attachments.


 

</body>

</html>

" >> $TMP_MAIL_FILE

cat $TMP_MAIL_FILE| /usr/sbin/sendmail -t

fi

rm /tmp/salida_cambio.out
rm $USR_INFO
rm $DATE_INFO
rm $DATE_INFO2
rm $TABLE_INFO
rm $MAIL_INFO
rm $NOMBRE_USR
fi
rm $DATE_COUNT

Saludos!!

dbando

viernes, 5 de junio de 2015

dbando script para monitorear cambios realizados a datos sensibles

No hay comentarios:

Publicar un comentario