AUDITORIA MySQL - Procedimiento almacenado auditoria

[venzia]

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,

[baskeyfield]

Buenas,

Muchas gracias por la aportación.

Más adelante tendré que hacer una auditoría y esto me será de gran utilidad.

Un saludo.