En mi blog Control Procedimientos Oracle, se explicó la forma de controlar el acceso a los procedimientos almacenados oracle de tal manera que no vayan haber mas de un desarrollador trabajando sobre el mismo y que no se pierda código.
Ahora en este blog se tratará sobre el mismo tema de control de procedimientos, pero ahora es como auditamos y respaldamos nuestros objetos de esquema de base de datos.
Cual es el procedimiento apropiado para aplicar un cambió a un ubjeto de esquema de base de datos?
Como ver como estaba antes de que se aplique el cambio, o saber quien lo modificó? ante todas esas interrogantes en el mercado existen ese tipo de herramientas pero son algo costosas y digamos que para una pequeña o mediana empresa esta lejos del presuesto por lo que debemos ingeniarnos.
A continuación un ejemplo de como logre auditar y respaldar los objetos de esquema de base de datos de manera automática antes de la modificacón.
Creamos una tabla que servirá de auditoría y respaldo de los objetos.
SQL> connect owner_obj/owner_obj@desarrollo_200
Conectado.
SQL> create table respaldos_objetos
2 (
3 fecha date,
4 evento varchar2(30 byte),
5 propietario varchar2(30 byte),
6 tipo_objeto varchar2(64 byte),
7 nombre_objeto varchar2(60 byte),
8 usuario varchar2(30 byte),
9 maquina varchar2(100 byte),
10 terminal varchar2(20 byte),
11 programa varchar2(64 byte),
12 modulo varchar2(60 byte),
13 cliente varchar2(64 byte),
14 objeto clob
15 );
Tabla creada.
SQL>
SQL> comment on table respaldos_objetos is 'Auditoria y respaldos de objetos de un esquema de base de datos';
Comentario creado.
SQL> comment on column respaldos_objetos.fecha is 'Fecha en que se realiza el cambio';
Comentario creado.
SQL> comment on column respaldos_objetos.evento is 'si es un create, drop o replace';
Comentario creado.
SQL> comment on column respaldos_objetos.propietario is 'propietario del objeto';
Comentario creado.
SQL> comment on column respaldos_objetos.tipo_objeto is 'si es una tabla, vista, procedimiento, funcion, paquete, secuencia, etc';
Comentario creado.
SQL> comment on column respaldos_objetos.nombre_objeto is 'nombre del objeto';
Comentario creado.
SQL> comment on column respaldos_objetos.maquina is 'maquina en donde se realizo el cambio';
Comentario creado.
SQL> comment on column respaldos_objetos.terminal is 'terminal en donde se realizo el cambio';
Comentario creado.
SQL> comment on column respaldos_objetos.programa is 'programa en donde se realizo el cambio';
Comentario creado.
SQL> comment on column respaldos_objetos.modulo is 'modulo en donde se realizo el cambio';
Comentario creado.
SQL> comment on column respaldos_objetos.cliente is 'cliente en donde se realizo cambio';
Comentario creado.
SQL> comment on column respaldos_objetos.objeto is 'ddl del objeto antes del cambio';
Comentario creado.
Creamos un trigger de base de datos que se dispare antes de un create, drop o replace
SQL> CREATE OR REPLACE TRIGGER CTRL_PROCEDIMIENTOS
2 BEFORE ALTER OR CREATE OR DROP
3 ON DATABASE
4 DECLARE
5 /******************************************************************************
6 Realizado por Endara Manosalvas Victor
7
8 Objetivo : control de alteraciones a los procedimientos de OWNER_OBJ
9 --
10 ora_sysevent : Tipo de operacion realizada
11 ora_dict_obj_owner : Propietario de los objetos afectados
12 ora_dict_obj_name : Nombre del objeto que interviene en la transaccion DDL
13 ora_dict_obj_type : Tipo de objeto
14 ******************************************************************************/
15 --Variables
16 Ln_Existe NUMBER(5);
17 lb_tipo_objeto user_objects.object_type%type;
18 lcl_script clob:=null; --almacena el script antes de la modificacion del objeto
19 BEGIN
20 --Respaldos a procedimientos de bd
21 if Ora_Dict_Obj_Type IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'VIEW', 'TRIGGER')
22 AND Ora_Sysevent IN ('CREATE','DROP')
23 then
24 for c_usr in (select USERNAME, TERMINAL, PROGRAM, MODULE, MACHINE, CLIENT_INFO from v$session where Sid = Sys_Context('USERENV','SID'))loop
25 if ora_dict_obj_type='PACKAGE BODY' then
26 lb_tipo_objeto:='PACKAGE';
27 else
28 lb_tipo_objeto:=Ora_Dict_Obj_Type;
29 end if;
30 --Con la ayuda de la utilidades del DBMS_METADATA obtendremos el DDL del objeto
31 begin
32 lcl_script:=DBMS_METADATA.GET_DDL(lb_tipo_objeto, ora_dict_obj_name,ora_dict_obj_owner);
33 exception when others then
34 lcl_script:=sqlerrm;
35 end;
36 insert into respaldos_objetos(FECHA, EVENTO, PROPIETARIO, TIPO_OBJETO, NOMBRE_OBJETO, USUARIO, MAQUINA, TERMINAL, PROGRAMA, MODULO, CLIENTE, OBJETO)
37 values(sysdate, ora_sysevent, ora_dict_obj_owner, ora_dict_obj_type, ora_dict_obj_name, c_usr.USERNAME, c_usr.MACHINE, c_usr.TERMINAL, c_usr.PROGRAM, c_usr.MODULE, c_usr.CLIENT_INFO, lcl_script);
38 end loop;
39 end if;
40 EXCEPTION
41 WHEN OTHERS THEN
42 RAISE;
43 END;
44 /
Disparador creado.
Para esta prueba crearemos la funcion f_prueba con el usuario OWNER_OBJ.
SQL> create or replace function f_prueba return number as
2 --Creacion OWNER_OBJ
3 begin
4 return 1;
5 end;
6 /
Función creada.
Hacemos un select a la tabla RESPALDOS_OBJETOS y vemos el evento CREATE realizado por OWNER_OBJ.
SQL> select fecha,usuario,maquina,propietario,evento,nombre_objeto from respaldos_objetos;
FECHA USUARIO MAQUINA PROPIETARIO EVENTO NOMBRE_OBJETO
-------- --------- -------- ----------- ------- -------------
11/04/11 OWNER_OBJ VENDARA OWNER_OBJ CREATE F_PRUEBA
Luego el usuario DESA_1 realizará cambios saobre la función f_prueba de OWNER_OBJ.
SQL> connect desa_1/desa_1@desarrollo_200
Conectado.
SQL> create or replace function owner_obj.f_prueba return number as
2 --Creacion OWNER_OBJ
3 --Modificado DESA_1
4 begin
5 return 1+5;
6 end;
7 /
Función creada.
SQL> connect owner_obj/owner_obj@desarrollo_200
Conectado.SQL> select fecha,usuario,maquina,propietario,evento,nombre_objeto from respaldos_objetos;
FECHA USUARIO MAQUINA PROPIETARIO EVENTO NOMBRE_OBJETO
-------- --------- -------- ----------- ------- --------------
11/04/11 OWNER_OBJ VENDARA OWNER_OBJ CREATE F_PRUEBA
11/04/11 DESA_1 VENDARA OWNER_OBJ CREATE F_PRUEBA
Hacemos un select a la tabla RESPALDOS_OBJETOS filtrando solo lo que realizo el usuario DESA_1 y vemos el objeto que respaldo antes de aplicar el cambio.
SQL> select fecha,usuario,maquina,propietario,evento,nombre_objeto,objeto from respaldos_objetos where usuario='DESA_1';
FECHA USUARIO MAQUINA PROPIETARIO EVENTO NOMBRE_OBJETO OBJETO
-------- -------- -------- ----------- ------- ------------- ------------------------------------------------------------------
11/04/11 DESA_1 VENDARA OWNER_OBJ CREATE F_PRUEBA CREATE OR REPLACE FUNCTION "OWNER_OBJ"."F_PRUEBA" return number as
--Creacion OWNER_OBJ
begin
return 1;
end;
Borramos la función f_prueba con el usuario DESA_1 y revisamos el log en RESPALDOS_OBJETOS.
SQL> connect desa_1/desa_1@desarrollo_200
Conectado.
SQL> drop function owner_obj.f_prueba;
Función borrada.
Revisamos el log para ver las transacciones realizadas.
SQL> connect owner_obj/owner_obj@desarrollo_200
Conectado.
SQL> select fecha,usuario,maquina,propietario,evento,nombre_objeto from respaldos_objetos;
FECHA USUARIO MAQUINA PROPIETARIO EVENTO NOMBRE_OBJETO
-------- --------- -------- ----------- ------ -------------
11/04/11 OWNER_OBJ VENDARA OWNER_OBJ CREATE F_PRUEBA
11/04/11 DESA_1 VENDARA OWNER_OBJ CREATE F_PRUEBA
11/04/11 DESA_1 VENDARA OWNER_OBJ DROP F_PRUEBA
Revisamos el script almacenado antes de ser borrada por DESA_1.
SQL> select objeto from respaldos_objetos where usuario='DESA_1' and evento='DROP';
OBJETO
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "OWNER_OBJ"."F_PRUEBA" return number as
--Creacion OWNER_OBJ
--Modificado DESA_1
begin
return 1+5;
end;
Esto lo he combinado con el trigger de control de procedimientos y me ha servido de mucha ayuda al momento de recuperar una versión anterior, ver como estaba antes de la modificación y dar con el responsable del cambio. Con esto ta no me dicen los desarrolladores el tipico de que "yo no fui!!" por que estan registrados en la auditoría.
Espero les sirva de mucho este pequeño aporte
Hacemos un select a la tabla RESPALDOS_OBJETOS.
Esta es una excelente ayuda Victor, fijate que solamente me da un problema que no me retorna el DDL, lo que me da es el siguiente error:
ResponderEliminarORA-31603: object "F_PRUEBA" of type FUNCTION not found in schema "IT"
Aunque si ejecuto la consulta desde toad si me funciona.
select DBMS_METADATA.GET_DDL('FUNCTION', 'F_PRUEBA', 'IT') FROM DUAL;
De acuerdo a tu experiencia qué crees que puede ser.
Saludos.
Gabriel.
Hola Gabriel, el problema es con el paquete DBMS_METADATA.GET_DDL. Al tratar de capturar la DDL de un esquema diferente al dueño del TRIGGER CTRL_PROCEDIMIENTO el paquete DBMS_METADATA falla (Revisado en el Metalink Calling Dbms_metadata.Get_ddl From Stored Procedure Results In Ora-31603 [ID 463483.1]).
EliminarLo resolvi de la siguiente manera:
1 Crear un procedimiento con el usuario SYS
CREATE OR REPLACE procedure SYS.P$CTRL_OBTENER_DML( pivTipoObj in varchar2,
pivOjeto in varchar2,
pivPropietario in varchar2,
pioScript in out nocopy clob ) is
/*
Desarrollado por : Endara Manosalvas
Fecha : 07/12/2011
Objetivo : Obtenel el DLL de los objetos de los esquemas de datos,para el trigger
CONTROL.CTRL_AUDITAR_CAMBIOS.
Se realiza en el SYS por que no funciona en otros esquemas por sugerencia del metalink :
Calling Dbms_metadata.Get_ddl From Stored Procedure Results In Ora-31603 [ID 463483.1]
*/
begin
pioScript:=DBMS_METADATA.GET_DDL (pivTipoObj,
pivOjeto,
pivPropietario);
EXCEPTION
WHEN OTHERS THEN
pioScript := SQLERRM;
end;
/
2 Dar permiso de ejecucion SYS.P$CTRL_OBTENER_DML al usuario dueño del trigger CTRL_PROCEDIENTO
3 Luego de eso reemplazar la linea del procedimiento CTRL_PROCEDIENTO
lcl_script:=DBMS_METADATA.GET_DDL(lb_tipo_objeto, ora_dict_obj_name,ora_dict_obj_owner);
Por
sys.P$CTRL_OBTENER_DML( lb_tipo_objeto,
ora_dict_obj_name,
ora_dict_obj_owner,
lcl_script);
Espero te sirva, cualquier novedad me avisas