AnsweredAssumed Answered

AUDITORIA MySQL - Procedimiento almacenado auditoria

Question asked by venzia on Sep 11, 2009
Latest reply on Mar 23, 2010 by baskeyfield
Quien necesite auditar en algún momento de forma "clara" las acciones que ha realizado un usuario durante un intervalo de tiempo puede crear y utilizar en su servidor MySQL donde este alojado Alfresco el siguiente procedimiento almacenado :
DROP PROCEDURE `PRO_AUDIT_UsuarioIntervalo`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `PRO_AUDIT_UsuarioIntervalo`(IN FechaInicio varchar(10),IN FechaFin varchar(10),IN Usuario varchar(20))
BEGIN

IF(Usuario<>'')
THEN

select f.user_id as Usuario, f.timestamp as FechaAccion, f.node_uuid as IdNodo,
(select a1.arg_2 from alf_audit_fact a1 where a1.return_val like concat(concat('%',f.node_uuid),'%') and a1.audit_source_id=8) as NombreFichero,
(Select method from alf_audit_source where id=f.audit_source_id) as Accion,
f.arg_1 as Detalle1, f.arg_2 as Detalle2,f.arg_3 as Detalle3,f.exception_message as MensajeError
from alf_audit_fact f
where ((f.audit_source_id=6 and f.arg_2 like '%versionLabel%') or
f.audit_source_id=1 or f.audit_source_id=5 or f.audit_source_id=6 or f.audit_source_id=7 or f.audit_source_id=8 or f.audit_source_id=10 or f.audit_source_id=12) and
(date(f.timestamp) between FechaInicio and FechaFin) and f.user_id<>'System' and (f.user_id LIKE Usuario or (f.user_id LIKE 'guest' and f.arg_1 LIKE Usuario));

ELSE

select f.user_id as Usuario, f.timestamp as FechaAccion, f.node_uuid as IdNodo,
(select a1.arg_2 from alf_audit_fact a1 where a1.return_val like concat(concat('%',f.node_uuid),'%') and a1.audit_source_id=8) as NombreFichero,
(Select method from alf_audit_source where id=f.audit_source_id) as Accion,
f.arg_1 as Detalle1, f.arg_2 as Detalle2,f.arg_3 as Detalle3,f.exception_message as MensajeError
from alf_audit_fact f
where ((f.audit_source_id=6 and f.arg_2 like '%versionLabel%') or
f.audit_source_id=1 or f.audit_source_id=5 or f.audit_source_id=6 or f.audit_source_id=7 or f.audit_source_id=8 or f.audit_source_id=10 or f.audit_source_id=12) and
(date(f.timestamp) between FechaInicio and FechaFin) and f.user_id<>'System';

END IF;

END

la llamada al mismo se realizaría de la siguiente forma (desde consola o algun query browser de MySQL) :
CALL
PRO_AUDIT_UsuarioIntervalo('2009-07-15','2009-07-30','nombredeusuario');

DETALLES A TENER EN CUENTA :
-En la claúsula where tendreís que adapatar los códigos de f.audit_source_id=N a vuestro sistema (ya que varía según el orden de ejecución inicial .. y quizás os interese auditar otros eventos).
En mi caso quería auditar los eventos de :
1-authenticate
5-setProperties ( cuando modificamos las propiedades generales )
6-setProperty ( cuando añadimos una version )
7-deleteNode ( cuando eliminamos un documento )
8-create ( cuando creamos un documento o espacio anno )
10-addAspect ( cuando añadimos cualquier aspecto al documento, versionable, metemos area, tag, relext o expedientes )
12-setPermission ( cuando modificamos permisos )
Estos códigos me vienen de la tabla alf_audit_source .. en la que tenía :
"1";"SystemMethodInterceptor";"AuthenticationService";"authenticate"
"2";"SystemMethodInterceptor";"AuthenticationService";"invalidateTicket"
"3";"SystemMethodInterceptor";"AuthenticationService";"clearCurrentSecurityContext"
"4";"SystemMethodInterceptor";"AuthenticationService";"validate"
"5";"SystemMethodInterceptor";"NodeService";"setProperties"
"6";"SystemMethodInterceptor";"NodeService";"setProperty"
"7";"SystemMethodInterceptor";"NodeService";"deleteNode"
"8";"SystemMethodInterceptor";"FileFolderService";"create"
"9";"SystemMethodInterceptor";"ContentService";"getWriter"
"10";"SystemMethodInterceptor";"NodeService";"addAspect"
"11";"SystemMethodInterceptor";"PermissionService";"setInheritParentPermissions"
"12";"SystemMethodInterceptor";"PermissionService";"setPermission"
"13";"SystemMethodInterceptor";"ScriptService";"buildDefaultModel"
"14";"SystemMethodInterceptor";"NodeService";"moveNode"
"15";"SystemMethodInterceptor";"ScriptService";"executeScript"
"16";"SystemMethodInterceptor";"ActionService";"executeAction"
"17";"SystemMethodInterceptor";"FileFolderService";"rename"
"18";"SystemMethodInterceptor";"CheckoutCheckinService";"checkout"
"19";"SystemMethodInterceptor";"PermissionService";"deletePermissions"
"20";"SystemMethodInterceptor";"CheckoutCheckinService";"cancelCheckout"
"21";"SystemMethodInterceptor";"CheckoutCheckinService";"checkin"
-Para mostrar el nombre de fichero cuando capturamos el evento de creación de contenido habrá que adaptar el código de (select a1.arg_2 from alf_audit_fact a1 where a1.return_val like concat(concat('%',f.node_uuid),'%') and a1.audit_source_id=8) as NombreFichero al que tengamos en la tabla alf_audit_source .. en mi caso "8";"SystemMethodInterceptor";"FileFolderService";"create".
*Hay que tener en cuenta que estos elementos irán apareciendo a medida que se vayan ejecutando en el sistema por los usuarios, una vez haya sido activada la auditoría.
-Para activar la auditoría http://wiki.alfresco.com/wiki/Audit_Configuration
-Mas documentación al respecto http://wiki.alfresco.com/wiki/Audit

Espero que sea de utilidad.
Saludos,

Outcomes