lunes, 11 de noviembre de 2013

Diferencia de fecha de archivos entre carpetas ( FMB - FMX, RDF - REP, PLL - PLX )

Para aquellos auditores o desarrolladores que quieran verificar si la diferencia de fecha de los fuentes de sus programas oracle forms & Report ( fmb - rdf ) estan dentro de un margen razonable de diferencia con respecto a sus ejecutables, les paso el siguiente script en vbs:
' Script que tiene como objetivo comparar fechas
' entre los archivos fuentes y ejecutables en una 
' estructura de programacion Oracle Form & Report
' Si pasa el limite de 2 minutos, el script envia un
' alerta
' Requerimientos:
' bmail.exe, es un emisor de mail SMTP via linea de comandos
' gratuido 
MinutoLimiteDiferencia = 2
str_CarpetaFuente = "z:\"
str_CarpetaEjecutable = "s:\"
str_NombreFicheroSalida = "c:\sistema\log.txt"
num_cantidad = 0

Set obj_FileSystemObject = CreateObject("Scripting.FileSystemObject")
Set Command = WScript.CreateObject("WScript.Shell") 
Set obj_FicheroSalida = obj_FileSystemObject.CreateTextFile(str_NombreFicheroSalida,True)
Set obj_CarpetaFuente = obj_FileSystemObject.GetFolder(str_CarpetaFuente)
Set obj_CarpetaEjecutable = obj_FileSystemObject.GetFolder(str_CarpetaEjecutable)
Set obj_FicherosFuente = obj_CarpetaFuente.Files
Set obj_FicherosEjecutables = obj_CarpetaEjecutable.Files

obj_FicheroSalida.Write "Fecha Inicio: " & Now() & vbCr
For Each obj_FicheroFuente in obj_FicherosFuente
    FicheroFuente = UCase(obj_FileSystemObject.GetBaseName(obj_FicheroFuente))
    ExtensionFuente = UCase(obj_FileSystemObject.GetExtensionName(obj_FicheroFuente))
 For Each obj_FicheroEjecutable in obj_FicherosEjecutables
  FicheroEjecutable = UCase(obj_FileSystemObject.GetBaseName(obj_FicheroEjecutable))
  ExtensionEjecutable = UCase(obj_FileSystemObject.GetExtensionName(obj_FicheroEjecutable))
  if FicheroFuente = FicheroEjecutable and ((ExtensionFuente="FMB" and ExtensionEjecutable = "FMX") or (ExtensionFuente="RDF" and ExtensionEjecutable = "REP") or (ExtensionFuente="PLL" and ExtensionEjecutable = "PLX")) then
   v_diferencia = abs(DateDiff("n",obj_FicheroEjecutable.DateLastModified,obj_FicheroFuente.DateLastModified))
   if v_diferencia >= MinutoLimiteDiferencia then
    obj_FicheroSalida.Write "Los archivos " & obj_FicheroEjecutable.Name & " - " & obj_FicheroFuente.Name & " difieren en " & ToHuman(v_diferencia) & "." & vbCr
    num_cantidad = num_cantidad + 1
   end if
   Exit for
  end if
 Next
Next
obj_FicheroSalida.Write "Total " & num_cantidad & " archivos con diferencias." & vbCr
obj_FicheroSalida.Write "Fecha Fin: " & Now() & vbCr
obj_FicheroSalida.Close
WScript.sleep 5000
Set obj_FicheroLog = obj_FileSystemObject.GetFile(str_NombreFicheroSalida)
LogSize = obj_FicheroLog.Size
If LogSize > 64 Then
  MsgBox("Total " & num_cantidad & " archivos con diferencias.")
  cmd = "c:\windows\system32\bmail.exe -s mail.dominio.com.py -p 25 -t informatica@dominio.com.py -f alerts@dominio.com.py -h -a 'Diferencia de Fuentes y Ejecutables' -m " & str_NombreFicheroSalida
  Command.Run (cmd)
End If

Function ToHuman(p_minutos)
    CambioUnidadTiempo = 0
 if p_minutos >=60  then
    UnidTiempoHoras = p_minutos/60
    UnidadTiempo = " Horas"
    CambioUnidadTiempo = 1
    ToHuman=Int(UnidTiempoHoras) & UnidadTiempo
 end if
 if UnidTiempoHoras >=24  then
    UnidTiempoDias = UnidTiempoHoras/24
    UnidadTiempo = " Dias"
    CambioUnidadTiempo = 1
    ToHuman=Int(UnidTiempoDias) & UnidadTiempo
 end if
 if UnidTiempoDias >=30  then
    UnidTiempoMeses = UnidTiempoDias/30
    UnidadTiempo = " Meses"
    CambioUnidadTiempo = 1
    ToHuman=Int(UnidTiempoMeses) & UnidadTiempo
 end if
 if UnidTiempoMeses >=12  then
    UnidTiempoAnos = UnidTiempoMeses/12
    UnidadTiempo = " Anos"
    CambioUnidadTiempo = 1
    ToHuman=Int(UnidTiempoAnos) & UnidadTiempo
 end if
 if CambioUnidadTiempo = 0 then
  UnidadTiempo = " Minutos"
  ToHuman=p_minutos & UnidadTiempo
 end if
End Function

martes, 14 de agosto de 2012

Todas las dependencias

Buenos dias, les paso un script con el que pueden obtener la dependencia entre objetos en la db y asi cómo dependen uno de otros. Espero que les sea útil.
DECLARE
v_type        VARCHAR2(30) := 'FUNCTION';
v_object_name VARCHAR2(30);
BEGIN
dbms_output.put_line(' ' || ',' || ' ' || ',,,,,,,,,,' || 'SELECT' || ',' ||
   'INSERT' || ',' || 'UPDATE' || ',' || 'DELETE' || ',' ||
   'EXECUTE');
FOR level0 IN (SELECT obje.owner,
 obje.object_name,
 obje.object_type,
 (CASE
   WHEN obje.object_type IN ('VIEW', 'TABLE') THEN
    'X'
 END) AS vselect,
 decode(obje.object_type, 'TABLE', ' ', ' ') AS vinsert,
 decode(obje.object_type, 'TABLE', ' ', ' ') AS vupdate,
 decode(obje.object_type, 'TABLE', ' ', ' ') AS vdelete,
 (CASE
   WHEN obje.object_type IN
     ('PACKAGE',
   'PACKAGE BODY',
   'PROCEDURE',
   'FUNCTION') THEN
    'X'
 END) AS vexecute
FROM all_objects obje
WHERE obje.object_type = v_type
AND obje.owner = 'SIFICO2') LOOP
dbms_output.put_line(level0.owner || '.' || level0.object_name || ',' ||
  level0.object_type || ',,,,,,,,,,' ||
  level0.vselect || ',' || level0.vinsert || ',' ||
  level0.vupdate || ',' || level0.vdelete || ',' ||
  level0.vexecute);
FOR level1 IN (SELECT depe.referenced_owner,
   depe.referenced_name,
   depe.referenced_type,
   (CASE
  WHEN depe.referenced_type IN ('VIEW', 'TABLE') THEN
   'X'
   END) AS vselect,
   (CASE
  WHEN depe.referenced_type = 'TABLE' AND EXISTS
   (SELECT 1
    FROM dba_source sour
      WHERE sour.owner = depe.owner
     AND sour.name = depe.name
     AND sour.type = depe.type
     AND sour.text LIKE '%INSERT%INTO%'
     AND depe.referenced_name =
      fc_insert_table_usage(sour.text)) THEN
   'X'
   END) AS vinsert,
   (CASE
  WHEN depe.referenced_type = 'TABLE' AND EXISTS
   (SELECT 1
    FROM dba_source sour
      WHERE sour.owner = depe.owner
     AND sour.name = depe.name
     AND sour.type = depe.type
     AND sour.text LIKE '%UPDATE%'
     AND depe.referenced_name =
      fc_update_table_usage(sour.text)) THEN
   'X'
   END) AS vupdate,
   (CASE
  WHEN depe.referenced_type = 'TABLE' AND EXISTS
   (SELECT 1
    FROM dba_source sour
      WHERE sour.owner = depe.owner
     AND sour.name = depe.name
     AND sour.type = depe.type
     AND sour.text LIKE '%DELETE%'
     AND depe.referenced_name =
      fc_delete_table_usage(sour.text)) THEN
   'X'
   END) AS vdelete,
   (CASE
  WHEN depe.referenced_type IN
    ('PACKAGE',
     'PACKAGE BODY',
     'PROCEDURE',
     'FUNCTION') THEN
   'X'
   END) AS vexecute
 FROM all_dependencies depe
WHERE depe.name = level0.object_name
  AND depe.type = level0.object_type
  AND depe.owner NOT IN ('SIFICO', 'PUBLIC')
ORDER BY 1, 2) LOOP
dbms_output.put_line(',' || level1.referenced_owner || '.' ||
    level1.referenced_name || ',' ||
    level1.referenced_type || ',,,,,,,,,' ||
    level1.vselect || ',' || level1.vinsert || ',' ||
    level1.vupdate || ',' || level1.vdelete || ',' ||
    level1.vexecute);
FOR level2 IN (SELECT depe2.referenced_owner,
  depe2.referenced_name,
  depe2.referenced_type,
  (CASE
    WHEN depe2.referenced_type IN ('VIEW', 'TABLE') THEN
     'X'
  END) AS vselect,
  (CASE
    WHEN depe2.referenced_type = 'TABLE' AND EXISTS
     (SELECT 1
      FROM dba_source sour
     WHERE sour.owner = depe2.owner
       AND sour.name = depe2.name
       AND sour.type = depe2.type
       AND sour.text LIKE '%INSERT%INTO%'
       AND depe2.referenced_name =
        fc_insert_table_usage(sour.text)) THEN
     'X'
  END) AS vinsert,
  (CASE
    WHEN depe2.referenced_type = 'TABLE' AND EXISTS
     (SELECT 1
      FROM dba_source sour
     WHERE sour.owner = depe2.owner
       AND sour.name = depe2.name
       AND sour.type = depe2.type
       AND sour.text LIKE '%UPDATE%'
       AND depe2.referenced_name =
        fc_update_table_usage(sour.text)) THEN
     'X'
  END) AS vupdate,
  (CASE
    WHEN depe2.referenced_type = 'TABLE' AND EXISTS
     (SELECT 1
      FROM dba_source sour
     WHERE sour.owner = depe2.owner
       AND sour.name = depe2.name
       AND sour.type = depe2.type
       AND sour.text LIKE '%DELETE%'
       AND depe2.referenced_name =
        fc_delete_table_usage(sour.text)) THEN
     'X'
  END) AS vdelete,
  (CASE
    WHEN depe2.referenced_type IN
      ('PACKAGE',
    'PACKAGE BODY',
    'PROCEDURE',
    'FUNCTION') THEN
     'X'
  END) AS vexecute
   FROM all_dependencies depe2
  WHERE depe2.name = level1.referenced_name
 AND depe2.owner = level1.referenced_owner
 AND depe2.type = level1.referenced_type
 AND depe2.referenced_owner NOT IN
  ('SIFICO', 'PUBLIC')
  ORDER BY 1, 2) LOOP
dbms_output.put_line(',,' || level2.referenced_owner || '.' ||
   level2.referenced_name || ',' ||
   level2.referenced_type || ',,,,,,,,' ||
   level2.vselect || ',' || level2.vinsert || ',' ||
   level2.vupdate || ',' || level2.vdelete || ',' ||
   level2.vexecute);
FOR level3 IN (SELECT depe3.referenced_owner,
    depe3.referenced_name,
    depe3.referenced_type,
    (CASE
   WHEN depe3.referenced_type IN
     ('VIEW', 'TABLE') THEN
    'X'
    END) AS vselect,
    (CASE
   WHEN depe3.referenced_type = 'TABLE' AND
     EXISTS
    (SELECT 1
     FROM dba_source sour
       WHERE sour.owner = depe3.owner
      AND sour.name = depe3.name
      AND sour.type = depe3.type
      AND sour.text LIKE '%INSERT%INTO%'
      AND depe3.referenced_name =
       fc_insert_table_usage(sour.text)) THEN
    'X'
    END) AS vinsert,
    (CASE
   WHEN depe3.referenced_type = 'TABLE' AND
     EXISTS
    (SELECT 1
     FROM dba_source sour
       WHERE sour.owner = depe3.owner
      AND sour.name = depe3.name
      AND sour.type = depe3.type
      AND sour.text LIKE '%UPDATE%'
      AND depe3.referenced_name =
       fc_update_table_usage(sour.text)) THEN
    'X'
    END) AS vupdate,
    (CASE
   WHEN depe3.referenced_type = 'TABLE' AND
     EXISTS
    (SELECT 1
     FROM dba_source sour
       WHERE sour.owner = depe3.owner
      AND sour.name = depe3.name
      AND sour.type = depe3.type
      AND sour.text LIKE '%DELETE%'
      AND depe3.referenced_name =
       fc_delete_table_usage(sour.text)) THEN
    'X'
    END) AS vdelete,
    (CASE
   WHEN depe3.referenced_type IN
     ('PACKAGE',
      'PACKAGE BODY',
      'PROCEDURE',
      'FUNCTION') THEN
    'X'
    END) AS vexecute
  FROM all_dependencies depe3
 WHERE depe3.name = level2.referenced_name
   AND depe3.owner = level2.referenced_owner
   AND depe3.type = level2.referenced_type
   AND depe3.referenced_owner NOT IN
    ('SIFICO', 'PUBLIC')
 ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,' || level3.referenced_owner || '.' ||
     level3.referenced_name || ',' ||
     level3.referenced_type || ',,,,,,,' ||
     level3.vselect || ',' || level3.vinsert || ',' ||
     level3.vupdate || ',' || level3.vdelete || ',' ||
     level3.vexecute);
FOR level4 IN (SELECT depe4.referenced_owner,
   depe4.referenced_name,
   depe4.referenced_type,
   (CASE
     WHEN depe4.referenced_type IN
       ('VIEW', 'TABLE') THEN
      'X'
   END) AS vselect,
   (CASE
     WHEN depe4.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
      WHERE sour.owner = depe4.owner
        AND sour.name = depe4.name
        AND sour.type = depe4.type
        AND sour.text LIKE '%INSERT%INTO%'
        AND depe4.referenced_name =
         fc_insert_table_usage(sour.text)) THEN
      'X'
   END) AS vinsert,
   (CASE
     WHEN depe4.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
      WHERE sour.owner = depe4.owner
        AND sour.name = depe4.name
        AND sour.type = depe4.type
        AND sour.text LIKE '%UPDATE%'
        AND depe4.referenced_name =
         fc_update_table_usage(sour.text)) THEN
      'X'
   END) AS vupdate,
   (CASE
     WHEN depe4.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
      WHERE sour.owner = depe4.owner
        AND sour.name = depe4.name
        AND sour.type = depe4.type
        AND sour.text LIKE '%DELETE%'
        AND depe4.referenced_name =
         fc_delete_table_usage(sour.text)) THEN
      'X'
   END) AS vdelete,
   (CASE
     WHEN depe4.referenced_type IN
       ('PACKAGE',
     'PACKAGE BODY',
     'PROCEDURE',
     'FUNCTION') THEN
      'X'
   END) AS vexecute
    FROM all_dependencies depe4
   WHERE depe4.name = level3.referenced_name
  AND depe4.owner = level3.referenced_owner
  AND depe4.type = level3.referenced_type
  AND depe4.referenced_owner NOT IN
   ('SIFICO', 'PUBLIC')
   ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,' || level4.referenced_owner || '.' ||
    level4.referenced_name || ',' ||
    level4.referenced_type || ',,,,,,' ||
    level4.vselect || ',' || level4.vinsert || ',' ||
    level4.vupdate || ',' || level4.vdelete || ',' ||
    level4.vexecute);
FOR level5 IN (SELECT depe5.referenced_owner,
     depe5.referenced_name,
     depe5.referenced_type,
     (CASE
    WHEN depe5.referenced_type IN
      ('VIEW', 'TABLE') THEN
     'X'
     END) AS vselect,
     (CASE
    WHEN depe5.referenced_type = 'TABLE' AND
      EXISTS
     (SELECT 1
      FROM dba_source sour
        WHERE sour.owner = depe5.owner
       AND sour.name = depe5.name
       AND sour.type = depe5.type
       AND sour.text LIKE
        '%INSERT%INTO%'
       AND depe5.referenced_name =
        fc_insert_table_usage(sour.text)) THEN
     'X'
     END) AS vinsert,
     (CASE
    WHEN depe5.referenced_type = 'TABLE' AND
      EXISTS
     (SELECT 1
      FROM dba_source sour
        WHERE sour.owner = depe5.owner
       AND sour.name = depe5.name
       AND sour.type = depe5.type
       AND sour.text LIKE '%UPDATE%'
       AND depe5.referenced_name =
        fc_update_table_usage(sour.text)) THEN
     'X'
     END) AS vupdate,
     (CASE
    WHEN depe5.referenced_type = 'TABLE' AND
      EXISTS
     (SELECT 1
      FROM dba_source sour
        WHERE sour.owner = depe5.owner
       AND sour.name = depe5.name
       AND sour.type = depe5.type
       AND sour.text LIKE '%DELETE%'
       AND depe5.referenced_name =
        fc_delete_table_usage(sour.text)) THEN
     'X'
     END) AS vdelete,
     (CASE
    WHEN depe5.referenced_type IN
      ('PACKAGE',
       'PACKAGE BODY',
       'PROCEDURE',
       'FUNCTION') THEN
     'X'
     END) AS vexecute
   FROM all_dependencies depe5
  WHERE depe5.name = level4.referenced_name
    AND depe5.owner = level4.referenced_owner
    AND depe5.type = level4.referenced_type
    AND depe5.referenced_owner NOT IN
     ('SIFICO', 'PUBLIC')
  ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,' || level5.referenced_owner || '.' ||
      level5.referenced_name || ',' ||
      level5.referenced_type || ',,,,,' ||
      level5.vselect || ',' || level5.vinsert || ',' ||
      level5.vupdate || ',' || level5.vdelete || ',' ||
      level5.vexecute);
FOR level6 IN (SELECT depe6.referenced_owner,
    depe6.referenced_name,
    depe6.referenced_type,
    (CASE
      WHEN depe6.referenced_type IN
        ('VIEW', 'TABLE') THEN
       'X'
    END) AS vselect,
    (CASE
      WHEN depe6.referenced_type = 'TABLE' AND
        EXISTS
       (SELECT 1
        FROM dba_source sour
       WHERE sour.owner = depe6.owner
         AND sour.name = depe6.name
         AND sour.type = depe6.type
         AND sour.text LIKE
          '%INSERT%INTO%'
         AND depe6.referenced_name =
          fc_insert_table_usage(sour.text)) THEN
       'X'
    END) AS vinsert,
    (CASE
      WHEN depe6.referenced_type = 'TABLE' AND
        EXISTS
       (SELECT 1
        FROM dba_source sour
       WHERE sour.owner = depe6.owner
         AND sour.name = depe6.name
         AND sour.type = depe6.type
         AND sour.text LIKE '%UPDATE%'
         AND depe6.referenced_name =
          fc_update_table_usage(sour.text)) THEN
       'X'
    END) AS vupdate,
    (CASE
      WHEN depe6.referenced_type = 'TABLE' AND
        EXISTS
       (SELECT 1
        FROM dba_source sour
       WHERE sour.owner = depe6.owner
         AND sour.name = depe6.name
         AND sour.type = depe6.type
         AND sour.text LIKE '%DELETE%'
         AND depe6.referenced_name =
          fc_delete_table_usage(sour.text)) THEN
       'X'
    END) AS vdelete,
    (CASE
      WHEN depe6.referenced_type IN
        ('PACKAGE',
      'PACKAGE BODY',
      'PROCEDURE',
      'FUNCTION') THEN
       'X'
    END) AS vexecute
     FROM all_dependencies depe6
    WHERE depe6.name = level5.referenced_name
   AND depe6.owner = level5.referenced_owner
   AND depe6.type = level5.referenced_type
   AND depe6.referenced_owner NOT IN
    ('SIFICO', 'PUBLIC')
    ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,,' || level6.referenced_owner || '.' ||
     level6.referenced_name || ',' ||
     level6.referenced_type || ',,,,' ||
     level6.vselect || ',' ||
     level6.vinsert || ',' ||
     level6.vupdate || ',' ||
     level6.vdelete || ',' ||
     level6.vexecute);
FOR level7 IN (SELECT depe7.referenced_owner,
      depe7.referenced_name,
      depe7.referenced_type,
      (CASE
     WHEN depe7.referenced_type IN
       ('VIEW', 'TABLE') THEN
      'X'
      END) AS vselect,
      (CASE
     WHEN depe7.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
         WHERE sour.owner = depe7.owner
        AND sour.name = depe7.name
        AND sour.type = depe7.type
        AND sour.text LIKE
         '%INSERT%INTO%'
        AND depe7.referenced_name =
         fc_insert_table_usage(sour.text)) THEN
      'X'
      END) AS vinsert,
      (CASE
     WHEN depe7.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
         WHERE sour.owner = depe7.owner
        AND sour.name = depe7.name
        AND sour.type = depe7.type
        AND sour.text LIKE '%UPDATE%'
        AND depe7.referenced_name =
         fc_update_table_usage(sour.text)) THEN
      'X'
      END) AS vupdate,
      (CASE
     WHEN depe7.referenced_type = 'TABLE' AND
       EXISTS
      (SELECT 1
       FROM dba_source sour
         WHERE sour.owner = depe7.owner
        AND sour.name = depe7.name
        AND sour.type = depe7.type
        AND sour.text LIKE '%DELETE%'
        AND depe7.referenced_name =
         fc_delete_table_usage(sour.text)) THEN
      'X'
      END) AS vdelete,
      (CASE
     WHEN depe7.referenced_type IN
       ('PACKAGE',
        'PACKAGE BODY',
        'PROCEDURE',
        'FUNCTION') THEN
      'X'
      END) AS vexecute
    FROM all_dependencies depe7
   WHERE depe7.name = level6.referenced_name
     AND depe7.owner = level6.referenced_owner
     AND depe7.type = level6.referenced_type
     AND depe7.referenced_owner NOT IN
      ('SIFICO', 'PUBLIC')
   ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,,,' || level7.referenced_type || ' ' ||
       level7.referenced_owner || '.' ||
       level7.referenced_name || ',,,' ||
       level7.vselect || ',' ||
       level7.vinsert || ',' ||
       level7.vupdate || ',' ||
       level7.vdelete || ',' ||
       level7.vexecute);
FOR level8 IN (SELECT depe8.referenced_owner,
     depe8.referenced_name,
     depe8.referenced_type,
     (CASE
       WHEN depe8.referenced_type IN
         ('VIEW', 'TABLE') THEN
        'X'
     END) AS vselect,
     (CASE
       WHEN depe8.referenced_type =
         'TABLE' AND EXISTS
        (SELECT 1
         FROM dba_source sour
        WHERE sour.owner =
           depe8.owner
          AND sour.name = depe8.name
          AND sour.type = depe8.type
          AND sour.text LIKE
           '%INSERT%INTO%'
          AND depe8.referenced_name =
           fc_insert_table_usage(sour.text)) THEN
        'X'
     END) AS vinsert,
     (CASE
       WHEN depe8.referenced_type =
         'TABLE' AND EXISTS
        (SELECT 1
         FROM dba_source sour
        WHERE sour.owner =
           depe8.owner
          AND sour.name = depe8.name
          AND sour.type = depe8.type
          AND sour.text LIKE
           '%UPDATE%'
          AND depe8.referenced_name =
           fc_update_table_usage(sour.text)) THEN
        'X'
     END) AS vupdate,
     (CASE
       WHEN depe8.referenced_type =
         'TABLE' AND EXISTS
        (SELECT 1
         FROM dba_source sour
        WHERE sour.owner =
           depe8.owner
          AND sour.name = depe8.name
          AND sour.type = depe8.type
          AND sour.text LIKE
           '%DELETE%'
          AND depe8.referenced_name =
           fc_delete_table_usage(sour.text)) THEN
        'X'
     END) AS vdelete,
     (CASE
       WHEN depe8.referenced_type IN
         ('PACKAGE',
       'PACKAGE BODY',
       'PROCEDURE',
       'FUNCTION') THEN
        'X'
     END) AS vexecute
      FROM all_dependencies depe8
     WHERE depe8.name = level7.referenced_name
    AND depe8.owner =
     level7.referenced_owner
    AND depe8.type = level7.referenced_type
    AND depe8.referenced_owner NOT IN
     ('SIFICO', 'PUBLIC')
     ORDER BY 1, 2) LOOP
dbms_output.put_line(',,,,,,,,' ||
      level8.referenced_owner || '.' ||
      level8.referenced_name || ',' ||
      level8.referenced_type || ',,' ||
      level8.vselect || ',' ||
      level8.vinsert || ',' ||
      level8.vupdate || ',' ||
      level8.vdelete || ',' ||
      level8.vexecute);
FOR level9 IN (SELECT depe9.referenced_owner,
       depe9.referenced_name,
       depe9.referenced_type,
       (CASE
      WHEN depe9.referenced_type IN
        ('VIEW', 'TABLE') THEN
       'X'
       END) AS vselect,
       (CASE
      WHEN depe9.referenced_type =
        'TABLE' AND EXISTS
       (SELECT 1
        FROM dba_source sour
          WHERE sour.owner =
          depe9.owner
         AND sour.name =
          depe9.name
         AND sour.type =
          depe9.type
         AND sour.text LIKE
          '%INSERT%INTO%'
         AND depe9.referenced_name =
          fc_insert_table_usage(sour.text)) THEN
       'X'
       END) AS vinsert,
       (CASE
      WHEN depe9.referenced_type =
        'TABLE' AND EXISTS
       (SELECT 1
        FROM dba_source sour
          WHERE sour.owner =
          depe9.owner
         AND sour.name =
          depe9.name
         AND sour.type =
          depe9.type
         AND sour.text LIKE
          '%UPDATE%'
         AND depe9.referenced_name =
          fc_update_table_usage(sour.text)) THEN
       'X'
       END) AS vupdate,
       (CASE
      WHEN depe9.referenced_type =
        'TABLE' AND EXISTS
       (SELECT 1
        FROM dba_source sour
          WHERE sour.owner =
          depe9.owner
         AND sour.name =
          depe9.name
         AND sour.type =
          depe9.type
         AND sour.text LIKE
          '%DELETE%'
         AND depe9.referenced_name =
          fc_delete_table_usage(sour.text)) THEN
       'X'
       END) AS vdelete,
       (CASE
      WHEN depe9.referenced_type IN
        ('PACKAGE',
         'PACKAGE BODY',
         'PROCEDURE',
         'FUNCTION') THEN
       'X'
       END) AS vexecute
     FROM all_dependencies depe9
    WHERE depe9.name =
       level8.referenced_name
      AND depe9.owner =
       level8.referenced_owner
      AND depe9.type =
       level8.referenced_type
      AND depe9.referenced_owner NOT IN
       ('SIFICO', 'PUBLIC')
    ORDER BY 1, 2) LOOP
  dbms_output.put_line(',,,,,,,,' ||
        level9.referenced_owner || '.' ||
        level9.referenced_name || ',' ||
        level9.referenced_type || ',' ||
        level9.vselect || ',' ||
        level9.vinsert || ',' ||
        level9.vupdate || ',' ||
        level9.vdelete || ',' ||
        level9.vexecute);
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END;

/
Saludos.

sábado, 26 de mayo de 2012

SQL Parser

Para aquellos que necesiten un sql parser.
Con este codigo se pueden extraer los privilegios y los objetos.


 Les paso uno hecho en SL.
Ejemplo: sql_examples.sql
select * from tabla1;
select *
  from tabla2
 where columna1=1;
 select columna1,columna2
    ,columna3
  from tabla3;
select columna1, (select columna1 from tabla4) as columna2
  from tabla5 tab
 where exists (select 1 from tabla6 tab6 where tab.columna1 = tab6.columna3);

update tabla7 set columna1 = 1000;
delete from tabla8;
insert into tabla9 (columna1) values (1);
insert into tabla10 values (1,2,3);
-----------------------------
------ sql_parser.sl   -----
-----------------------------
var
 linea       ="";
 cant_lineas    = 0;
 nro_caracteres = 0;
 v_caracter     = "";
 v_palabra     = "";
 cant_palabra_reservada = 0;
 line_indice    = 0;
const
 ARCH_ENTRADA  = "sql_examples.sql";
 ARCH_SALIDA  = "object.txt";
inicio
 si ( not set_stdin (ARCH_ENTRADA) ) {
  imprimir ("\nNo se pudo abrir el archivo "+ ARCH_ENTRADA);
  terminar ("\nEl programa no puede continuar.");
 }
 set_ifs ("\n");
 si ( not set_stdout (ARCH_SALIDA) ) {
  terminar ("\nNo se pudo abrir el archivo "+ ARCH_SALIDA);
 }
 leer (linea);
 mientras ( not eof() ) {
  linea=upper(linea);
  nro_caracteres=strlen(linea);
  desde line_indice= 1 hasta nro_caracteres {
   v_caracter = substr(linea,line_indice,1);
   si ( v_palabra=='(SELECT') { v_palabra = 'SELECT';}
   si ( es_delimitador_sr(v_caracter) ) {
    si ( es_privilegio_sr(v_palabra) ) {
   v_palabra = substr(lpad(' ', (cant_palabra_reservada - 1) * 3, '- ') + v_palabra, 1, 60);
     imprimir("\n",v_palabra);
    }
 si ( cant_palabra_reservada > 0 ) {
   v_palabra = substr(lpad(' ', (cant_palabra_reservada - 1) * 3, '- ') + v_palabra, 1, 60);
     imprimir("\n",v_palabra);
     dec(cant_palabra_reservada);
    }
 si ( es_palabra_clave_sr(v_palabra) ) {
     inc(cant_palabra_reservada);
 }
    v_palabra = "";
   sino
    v_palabra = v_palabra+v_caracter;
   }
  }
  leer(linea);
 }
 set_stdout("");
fin;
subrutina es_privilegio_sr(p_palabra : cadena)  retorna logico
var
 es_privilegio  : logico;
 privilegios  : vector [4] cadena;
 priv_indice  : numerico;
inicio
 privilegios = {'SELECT','INSERT','UPDATE','DELETE'};
 desde priv_indice= 1 hasta alen(privilegios) {
  si (p_palabra == privilegios[priv_indice]) {
   es_privilegio = SI;
   priv_indice = alen(privilegios);
  sino
   es_privilegio = NO;
  }
 }
 retorna ( es_privilegio );
fin;
subrutina es_delimitador_sr(p_caracter : cadena)  retorna logico
var
 es_delimitador : logico;
 delimitadores  : vector [5] cadena;
 deli_indice  : numerico;
inicio
 delimitadores = {';','/',')',',',' '};
 desde deli_indice= 1 hasta alen(delimitadores) {
  si ( p_caracter == delimitadores[deli_indice] ) {
   es_delimitador = SI;
   deli_indice=alen(delimitadores);
  sino
   es_delimitador = NO;
  }
 }
 retorna ( es_delimitador );
fin;
subrutina es_palabra_clave_sr(p_clave : cadena)  retorna logico
var
 es_palabra_clave  : logico;
 palabra_clave   : vector [3] cadena;
 plcl_indice   : numerico;
inicio
 palabra_clave = {'UPDATE','FROM','INTO'};
 desde plcl_indice= 1 hasta alen(palabra_clave) {
  si (p_clave == palabra_clave[plcl_indice]) {
   es_palabra_clave = SI;
   plcl_indice = alen(palabra_clave);
  sino
   es_palabra_clave = NO;
  }
 }
 retorna ( es_palabra_clave );
fin;
subrutina lpad(p_palabra:cadena; p_cantidad :numerico; p_caracter_rellenar: cadena) retorna cadena
var
 indice      : numerico;
 v_cant_caracteres  : numerico;
inicio
 v_cant_caracteres = strlen(p_palabra);
 desde indice = 1 hasta p_cantidad - v_cant_caracteres {
  p_palabra = p_caracter_rellenar+p_palabra;
 }
 retorna p_palabra;
fin;

-----------------------------
------ object.txt   --------
-----------------------------

 SELECT
 TABLA1
 SELECT
 TABLA2
 SELECT
 TABLA3
 SELECT
 SELECT
 TABLA4
 TABLA5
 SELECT
 TABLA6
 UPDATE
 DELETE
 TABLA8
 INSERT
 TABLA9
 INSERT
 TABLA10

Ojala que les sirva.

sábado, 14 de abril de 2012

Vulnerabilidad en SAMBA

Documento sin título

Ejecución de código remoto en Samba (una vulnerabilidad "como las de antes")

----------------------------------------------------------------------------

Se ha anunciado una grave vulnerabilidad en Samba que podría permitir la ejecución remota de código como usuario root a través de una conexión anónima (sin autenticar). Samba es un software libre que permite compartir ficheros e impresoras entre cliente y servidor con diferentes sistemas operativos, tales como DOS, Microsoft Windows, OS/2, Linux, o MacOS, mediante el protocolo SMB/CIFS. Samba es ampliamente utilizado tanto entre particulares como por parte de empresas, y se encuentra en prácticamente la totalidad de las distribuciones Linux, y en un gran número de dispositivos con GNU/Linux en su interior.

La vulnerabilidad está causada por un error en el generador de código utilizado para empaquetar y desempaquetar las llamadas de procedimiento remoto (RPC) a través de la red. Este error se debe a la falta de comprobación de concordancia entre la variable que contiene la longitud de un array y la variable usada para reservar memoria para dicho array.

Un atacante remoto no autenticado podría explotar esta vulnerabilidad a través de una llamada RPC especialmente manipulada para lograr ejecutar código arbitrario en el servidor como usuario root. En la práctica, significa que con solo lanzar un comando contra un servidor Samba vulnerable en la red, se tendría total control sobre la máquina afectada. El error ha sido descubierto por el investigador Brian Gorenc y una persona anónima de Zero Day Initiative (ZDI) quienes, además, han demostrado su explotabilidad mediante una prueba de concepto. Se ha asignado el identificador CVE-2012-1182 a esta vulnerabilidad. Su gravedad es máxima, y recuerda a vulnerabilidades de otro tiempo, donde resultaban más comunes fallos de este tipo en una red mucho más insegura.

El fallo se encuentra en el código desde la versión 3.0.25 de Samba, de mayo de 2007. Lo que significa que lleva cinco años en el código fuente del programa. Aunque las distribuciones ya están publicando nuevos paquetes para actualizar Samba y corregir el fallo, se puede mitigar el problema para aquellos dispositivos o servidores que no puedan actualizar de inmediato. Es posible utilizar la opción "host allow" en el fichero de configuración smb.conf para restringir los clientes que pueden acceder, o hacerlo a través de un cortafuegos adicional. Para solucionar la vulnerabilidad, Samba ha lanzado las versiones 3.4.16, 3.5.14, y 3.6.4, así como parches para otras versiones más antiguas.

Se pueden descargar desde la página oficial.

Más información: "root" credential remote code execution.

http://www.samba.org/samba/security/CVE-2012-1182

Samba Security Releases

http://www.samba.org/samba/history/security.html

martes, 3 de abril de 2012

En este episodio piloto de Hak.5 ponemos a prueba para ver si la velocidad a la que se conduce afecta a su capacidad para recoger los puntos de acceso. También le mostrará cómo controlar las luces desde un teléfono celular habilitado para Internet, matar a distancia los procesos de Windows, modificar el  gamecube para permitir la reproducción de DVD, y el encargo de la pintura de su caja del PC. Además de una ayuda sobre instalación de Windows XP que todos deben saber.

Y proximamente los subtitulos de este y los demas capitulos!!

miércoles, 28 de marzo de 2012

Presentacion

¿Cómo están todos?
En este blog podrán encontrar todo sobre seguridad informática, pen testing, hacking ético, etc.
Y como soy fanático de Hak5, y se que muchos desean una version en espanol. Esta realizando los subtitulos de cada capítulo, para que todos puedan disfrutarlo.

Saludos.