auditoría de datos, funciones y disparadores

[cacr]

Saludos

Deseo implementar el control interno de registro de datos y seguridad de acceso al sistema. El sistema estará montado con php.

Estoy pensando en un tabla única, que registre los cambios, algo así:

id -> identificador único de cada tabla, sobre el cual se hace la transacción
op -> operación realizada
fecha -> momento de ocurrencia
nb_tabla -> tabla de ocurrencia
user -> usuario que ejecutó la transacción

Me guío por el ejemplo de la documentación:
http://www.postgresql.org/docs/curre...l-trigger.html

Ahora bien, resulta que, este se ejecuta con una función y un disparador, pero el usuario que puedo recuperar con pg, es el que se utiliza para conectarse a la BD... ¿y si quiero que sea el que se conecta al sistema?

He pensado en par de alternativas,
1) establecer un criterio, según los permisos de los usuarios existentes en la BD, y generar ciertos perfiles y agregarlos al pg_hba.conf, de manera que el usuario se conecte, verifique quién es, y dependiendo de quien sea, desconectar, cambiar de usuario para establecer la conexión, y volver a conectar... no parece muy clara la idea...
2) (obviamente, no sé si se pueda) pasarle, desde PHP, a la función en pg o al disparador, algún parámetro... ¿se puede? ¿cómo?

cómo verán, el problema plantea dos asuntos, 1) seguridad en el acceso al sistema, y 2) auditoría del sistema...

Todas las ideas, lecturas, comentarios, serán siempre bien recibidos

[Pip]

Hola cacr,

no estoy seguro de poder ayudarte demasiado. Primero decirte que yo tengo que hacer algo similar a lo que vas a hacer tú y que para ello no usaré php (es decir, uso php en la web que voy a hacer pero no para la auditoría). Para la auditoría usaré exclusivamente TRIGGERs, al menos según lo veo ahora. Creo que es lo mejor porque si usas php y alguien se conecta a la base de datos desde el exterior, no quedaría reflejado con php, si bien eso no debería suceder...

El principal problema que tienen los TRIGGERS es no poder crearlos para SELECT (que sería la manera de registrar los usuarios que se conectan, al menos eso creo), en este hilo hay un apaño que no sé si funcionará: http://www.forosdelweb.com/showthrea...hlight=trigger

Espero que te ayude en algo.

[cacr]

Hola Pip

Te cuento...

Hice una tabla que me guarda:
1.- op -> [insert, update, delete] -> TG_OP
2.- fecha -> timestamp -> now()
3.- tabla -> tabla sobre la cual se esta realizando la operacion -> TG_RELNAME
4. IP -> ip de donde se realiza la conexión -> inet_client_addr()
5.- usr -> usuario que se conectó -> session_user

Resulta, que inet_client_addr sólo funciona si la app es cliente servidor, pues si está basada en web, el usuario que se conecta siempre es el mismo, a menos que fumemos algo más, lo cual también se puede...

session_user, le pasa lo mismo, quien es el que se conecta? pues, ya sabemos...

Solución, utilizar funciones que tomen la IP y el nombre de usuario (session) del lado del lenguaje (PHP, por ejemplo) y "meterlas" a la tabla de auditoría, mieńmtras los demás datos se toman desde un trigger...

¿Cómo se le pasan variables a una función en PL (por ejemplo), para que se inyecten al trigger? si hay respuesta, entonces, vamos por buen camino...

Pendientes

[Pip]

Ante todo decirte que trataré de ayudarte con toda mi buena fe pero no sé estoy demasiado capacitado para hacerlo.

Veamos, no sé si estoy entendiéndolo bien, quieres meter una variable como parte del código de una función de PLsql, decirte que yo no sé prácticamente nada de PLsql, salvo el poco que he visto para los triggers (no sé si habrá más) pero, en principio, si lo que quieres es modificar un texto añadiéndole una variable en una zona en concreto, lo único que te hace falta es concatenar la parte en la que tiene que ir la variable con el texto que está a su alrededor. Quizás no estoy entendiendo si el problema es ese u otro.

[cacr]

Hola...

El asunto es, tengo una única tabla auditoria, en la que pretendo se guarden todas las transacciones que se realicen en cualquiera de las otras tablas de la BD, de manera que:

1.- op -> [insert, update, delete]
2.- fecha -> timestamp -> now()
3.- tabla -> en la que se hizo la transacción
4. IP -> desde dónde se hizo la transacción
5.- usr -> quién hizo la transacción

Creo, lo ideal sería que que el _llenado_ de ésta tabla se hiciera desde el mismo postgres, de manera que podría utilizar para:
1.- op -> TG_OP
2.- tabla -> TG_RELNAME

que son valores que se pueden obtener cuando la función se activa desde un trigger, pero...

4.- IP y 5.- usr , al ser un sistema basado en web, no puedo utilizar ninguna de las funciones:
inet_client_addr() ni user_session()

entonces, me veo con las siguientes opciones (si hay más, bienvenidas sean):

1) disparar la función sin la posibilidad de guardar quién y desde dónde se hizo la transacción, lo cual no parece muy lógico, pues es de las cosas más importantes, y
2) ejecutar la función desde php, sin el trigger, y llenar el nombre de la tabla y el tipo de transacción a punta de php... (no estaría aprovechando los recursos de pg)

Ideas?

[cacr]

Pip, te cuento que cómo lo resolví... y para otros lectores

1.- cree una función audit:

Código:

function audit() {
// query de auditoria
  $audit = "create temp table audit_tmp (usuario varchar(30), ip inet);";
  $audit .= " insert into audit_tmp values ('".$_SESSION['usr']."', '".$_SERVER['REMOTE_ADDR']."');";
echo $audit."<br>"; /*imprimir la query*/
  $this->Query_ID = pg_query($audit); /* vale decir que estoy usando una pequeña clase */
}

2.- llamo la función audit(), justo antes de hacer el insert, update o delete...

3.- creo una función que será llamada dsde un trigger, y que devuelve, obviamente, un trigger:

Código:

BEGIN
    IF (TG_OP = 'DELETE') THEN
            INSERT INTO ssc.auditor (id, user_session, op, nb_tabla, ip) VALUES (OLD.id,(SELECT usuario FROM audit_tmp),TG_OP,TG_RELNAME,(SELECT ip FROM audit_tmp));
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO ssc.auditor (id, user_session, op, nb_tabla, ip) VALUES (NEW.id,(SELECT usuario FROM audit_tmp),TG_OP,TG_RELNAME,(SELECT ip FROM audit_tmp));
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO ssc.auditor (id, user_session, op, nb_tabla, ip) VALUES (NEW.id,(SELECT usuario FROM audit_tmp),TG_OP,TG_RELNAME,(SELECT ip FROM audit_tmp));
            RETURN NEW;
        END IF;
  END

Me imagino, que hay forma de mejorar esto, pero así lo he resuleto, cuando sepa como mejorarlo, seguro que lo cambio...

Pendientes