Como podemos tener el control para las modificaciones de los procedimientos almacenados en oracle?
Para poder crear o modificar procedimientos de base de datos, es necesario tener el privilegio create or replace procedure y si trabajamos en un ambiente en que estos procedimientos estan centralizados en un esquema por ejemplo OWNER_OBJ y tenemos varios desarrolladores trabajando en el esquema OWNER_OBJ, se necesita que los usuarios desarrolladores tengan el privilegio create or replace any procedure para que puedan crear o modficar los procedimientos de base de datos en el esquema OWNER_OBJ.
Como controlar que mas de un desarrollador trabaje sobre el mismo procedimiento?
Esto mas de una vez ha llevado a trabajar doble y perder tiempo en recuperar código ya desarrollado. Me he topado que muchos de los programadores (me incluyo) dejan los editores pl-sql, toad o sqlplus abierto por mucho tiempo y realizan cambios sobre esa versión no considerando que ya pudo ser modificada por otro usuario.
A nivel de oracle no existe un permiso que permita otorgar el privilegio
de moficación (DDL) sobre un procedimiento en específico del OWNER_OBJ.
A continución un ejemplo de como poder controlar los procedimiento de base de datos:
Tenemos el usuario owner_obj, quien es el dueño de los objetos y tenemos a los usuarios desa_1 y desa_2 quienes son los desarrolladores que trabaran sobre los objetos de owner_obj.
--Creamos la funcion f_prueba para nuestro ejemplo
SQL> connect owner_obj/owner_obj
Conectado.
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.
SQL> select text from user_source where name='F_PRUEBA' order by line;
TEXT
--------------------------------------------------------------------------------
function f_prueba return number as
--Creacion OWNER_OBJ
begin
return 1;
end;
--Ahora el usuario desa_1 modificara la función f_prueba
SQL> connect desa_1/desa_1
SQL> create or replace function owner_obj.f_prueba return number as
2 --Creacion OWNER_OBJ
3 --modificado por desa_1
4 begin
5 return 1+2;
6 end;
7 /
Función creada.
SQL> select text from all_source where owner='OWNER_OBJ' and name='F_PRUEBA' order by line;
TEXT
--------------------------------------------------------------------------------
function f_prueba return number as
--Creacion OWNER_OBJ
--modificado por desa_1
begin
return 1+2;
end;
6 filas seleccionadas.
Pero al mismo tiempo el usuario desa_2 tambien realiza cambios sobre la función y compilando despues de que lo hace el usuario desa_1
SQL> connect desa_2/desa_2
Conectado.
SQL> create or replace function owner_obj.f_prueba return number as
2 --Creacion OWNER_OBJ
3 --modificado por desa_2
4 begin
5 return 1*3;
6 end;
7 /
Función creada.
SQL> select text from all_source where owner='OWNER_OBJ' and name='F_PRUEBA' order by line;
TEXT
--------------------------------------------------------------------------------
function f_prueba return number as
--Creacion OWNER_OBJ
--modificado por desa_2
begin
return 1*3;
end;
6 filas seleccionadas.
Esto causa que los cambios realizados por el usuario desa_1 se pierdan.
Ya que no existe un permiso que otorgue el privilegio de modificar un procedimiento específico a continución un ejemplo de como hacerlo.
--Se crea una tabla que almacenará el usuario con el procedimiento que podrá modificar.
SQL> connect owner_obj/owner_obj
Conectado.
SQL> CREATE TABLE PERMISOS_PROGRAMAS
2 (
3 ID_PERMISO_PROGRAMA NUMBER NOT NULL,
4 ID_USUARIO VARCHAR2(30 BYTE) NOT NULL,
5 TIPO_PROGRAMA VARCHAR2(1 BYTE) NOT NULL,
6 PROGRAMA VARCHAR2(30 BYTE) NOT NULL,
7 FECHA_PERMISO DATE NOT NULL
8 );
Tabla creada.
SQL> comment on column permisos_programas.id_permiso_programa is 'Id del permiso';
Comentario creado.
SQL>
SQL> comment on column permisos_programas.id_usuario is 'usuario de base de datos';
Comentario creado.
SQL>
SQL> comment on column permisos_programas.tipo_programa is 'F: funcion, P: Procedimiento y Q: Paquete';
Comentario creado.
SQL>
SQL> comment on column permisos_programas.programa is 'Nombre del programa a controlar';
Comentario creado.
Se crea un trigger de de base de datos que se dispara al tratar de realizar un alter, drop o replace. Este es el que controlará los cambios
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 Objetivo : control de alteraciones a los procedimientos de OWNER_OBJ
8 --
9 ora_sysevent : Tipo de operacion realizada
10 ora_dict_obj_owner : Propietario de los objetos afectados
11 ora_dict_obj_name : Nombre del objeto que interviene en la transaccion DDL
12 ora_dict_obj_type : Tipo de objeto
13 ******************************************************************************/
14 --Variables
15 ln_existe number(5);
16 lb_tipo_objeto user_objects.object_type%type;
17 lcl_script clob:=null; --almacena el script antes de la modificacion del objeto
18 begin
19 -- Solo se controla el acceso a los siguientes objetos
20 if ora_dict_obj_type in ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'VIEW', 'TRIGGER')
21 and ora_sysevent in ('CREATE','DROP')
22 and sys_context('USERENV','CURRENT_SCHEMA') in ( 'OWNER_OBJ') and
23 user not in ('SYS','SYSTEM','SYSMAN','SYSAUX','OWNER_OBJ') then
24 select count(*)
25 into ln_existe
26 from permisos_programas
27 where id_usuario = user and
28 programa = ora_dict_obj_name and
29 tipo_programa = decode(ora_dict_obj_type,
30 'FUNCTION',
31 'F',
32 'PACKAGE',
33 'Q',
34 'PACKAGE BODY',
35 'Q',
36 'PROCEDURE',
37 'P',
38 'VIEW',
39 'V',
40 'TRIGGER',
41 'T');
42 if ln_existe = 0 then
43 raise_application_error(-20000,
44 'Ud no tiene privilegio de ' || ora_sysevent ||
45 ' sobrel el ' || ora_dict_obj_type || ' ' ||
46 ora_dict_obj_name || chr(13) ||
47 'Debe solicitar permiso a este objeto al administrador de la base de datos.');
48 end if;
49 end if;
50 exception
51 when others then
52 raise;
53 end;
54 /
Disparador creado.
--Insertamos en la tabla de permisos el usuario y procedimiento a quien queremos dar el permiso. En este caso es al usuario desa_1 que daremos permiso a la función f_prueba.
SQL> insert into permisos_programas (
2 id_permiso_programa, id_usuario, tipo_programa,
3 programa, fecha_permiso)
4 values (1,
5 'DESA_1',
6 'F',
7 'F_PRUEBA',
8 sysdate );
1 fila creada.
SQL> commit;
Confirmación terminada.
-- Ahora el usuario desa_1 trabajará sobre el la función f_prueba.
SQL> connect desa_1/desa_1
Conectado.
SQL> create or replace function owner_obj.f_prueba return number as
2 --Creacion OWNER_OBJ
3 --modificado por desa_2
4 --modificado por desa_1
5 begin
6 return 1*5;
7 end;
8 /
Función creada.
Pero al mismo momento el desalmado usuario desa_2 va a tratar de modificar la función despues de que el pobre usuario desa_1 a trabajo esforzadamente sobre la función f_prueba.
SQL> connect desa_2/desa_2
Conectado.
SQL> create or replace function owner_obj.f_prueba return number as
2 --Creacion OWNER_OBJ
3 --modificado por desa_2
4 --modificado por desa_2
5 begin
6 return 1*4;
7 end;
8 /
create or replace function owner_obj.f_prueba return number as
*
ERROR en línea 1:
ORA-00604: se ha producido un error a nivel 1 de SQL recursivo
ORA-20000: Ud no tiene privilegio de CREATE sobrel el FUNCTION F_PRUEBA
Debe
solicitar permiso a este objeto al administrador de la base de datos.
ORA-06512: en línea 49
Pero ohh, sorpresa le salió un mensaje al usuario desa_2 de que no puede realizar modificaciones a la función f_prueba por que no tiene permisos. El esta obligado a pedir permiso al administrador y el administrador deberá revisar que usuario tiene solicitado el procedimiento, función o paquete antes de darle el permiso para que no se vaya a perder código.
Ahora lo que queda es realizarle una interfaz gráfica (Como yo la hice) para que sea mas dinámico administrar los permisos.
Espero que les sea util este código
No hay comentarios:
Publicar un comentario