miércoles, 1 de abril de 2009

Cómo crear una vista parametrizada

En Oracle, podemos crear vistas que retornen resultados dependientes de parámetros previamente seteados. La forma de lograr esto es usando un feature llamado Application Contexts.

¿Qué es un contexto de aplicación?
El contexto de aplicación es simplemente un espacio en memoria que nos permite almacenar valores para luego utilizarlos en SQL o PL/SQL, como cualquier otra variable definida en el entorno. De forma transparente, mis objetos pueden ser 'manipulados' externamente sin necesidad que mis aplicaciones o procesos batch se enteren.
El contexto puede ser definido tanto localmente (privado para cada sesión) como globalmente, compartiendo sus valores para todas las sesiones de la instancia.
Para poder alterar los valores del contexto, debemos crear un paquete especialmente autorizado para ese fin. Esto es un requerimiento por razones de seguridad.
También necesitamos tener el permiso especial de sistema CREATE ANY CONTEXT.

Ejemplo (con sqlplus)

Vamos a ver un sencillo ejemplo de cómo implementar una vista parametrizada con contextos de aplicación, usando el popular usuario SCOTT. El parámetro para la vista en este caso será el número de departamento.

Paso 1: Crear el contexto de aplicación
CREATE CONTEXT app_ctx_scott USING pk_scott_app_context
/
Paso 2: Crear el paquete para manipular el contexto
CREATE OR REPLACE PACKAGE pk_scott_app_context AS
-- El contexto tendra un unico valor deptno
PROCEDURE set_dept (p_deptno IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pk_scott_app_context AS
PROCEDURE set_dept (p_deptno IN NUMBER) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('app_ctx_scott', 'deptno', p_deptno);
END;
END;
/
Paso 3: Setear el parámetro de contexto deptno para el departamento de ventas
BEGIN
pk_scott_app_context.set_dept(30);
END;
/
Paso 4: Crear la vista parametrizada
CREATE VIEW empleados AS
SELECT e.empno, e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno = sys_context('app_ctx_scott','deptno');
Paso 5: Obtener los resultados consultando la vista
SELECT * FROM empleados;

EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7844 TURNER SALESMAN SALES
7900 JAMES CLERK SALES

6 rows selected.
Actualmente la vista retorna los empleados del departamento de ventas, ya que así está definida la variable en el contexto. Ahora cambiaré el valor del parámetro para que la vista retorne resultados únicamente del departamento contable:
BEGIN
pk_scott_app_context.set_dept(10);
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM empleados;


EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7782 CLARK MANAGER ACCOUNTING
7839 KING PRESIDENT ACCOUNTING
7934 MILLER CLERK ACCOUNTING

3 rows selected.

De la misma forma podemos aplicar esta técnica a procedimientos y funciones, pudiendo manipular valores utilizados internamente o inclusive introduciendo fragmentos de código como sql dinámico.

16 comentarios:

Carlos dijo...

No entiendo por qué hace falta un contexto para utilizar una propiedad de un paquete en una vista.
¿Esto de los contextos tiene alguna otra utilidad?

lfer dijo...

Desde luego, los contextos son de vital importancia para poder implementar Fine-Grained Access Control para restringir el acceso a datos a nivel de filas (agregando dinámicamente filtros a las consultas que realiza un usuario), impidiendo que éste acceda a registros no autorizados dentro de un objeto. Basicamente defines políticas de seguridad (código que será incorporado a cada 'WHERE' de SQL). Luego esas políticas se asociarán a los usuarios, y Oracle automáticamente reescribirá los SELECTs que ejecute un usuario, para incorporar estos filtros. Para ese filtrado dinámico, Oracle hace uso del contexto definido.

Este es una funcionalidad muy interesante para seguridad de acceso a datos. En algún momento mostraré algun ejemplo práctico, igualmente la documentación al respecto en OTN es fácil de seguir.

Carlos dijo...

Salvo que se me escape algo, si es necesario invocar explícitamente al setter no es nada que no se pueda hacer ya con un paquete convencional.
Supongo que cuando postees el ejemplo lo veré más claro.

En cualquier caso, gracias por compartirlo. Tienes un blog muy instructivo.

lfer dijo...

Gracias por los comentarios.

Las fine-grain policies no solamente permiten agregar este filtrado a vistas sino tambien a tablas. Con esto no afectamos bases de datos dependientes de otras aplicaciones, ya que no habrá necesidad de alterar ningún objeto. En ese sentido es 'transparente'. Además puedes idear lógica compleja con PL/SQL para armar el filtro dinámico.

Saludos

Lebrijo dijo...

Me ha venido perfecto para parametrizar una vista, y tirar de ella tanto de una aplicación Java como desde Reports Builder.

Gracias¡¡

Anónimo dijo...

(comentarios movidos desde otro articulo, aqui esta mas relacionado)

Es sobre el articulo de CREAR UNA VISTA PARAMETRIZADA
porfavor necesito ayuda estoy creando una vista segun los pasos que explicaste pero al ejecutar el procedure para setear el parametro del contexto me sale este error :

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 58
ORA-06512: at "DBCT_CMC.PK_CUENTA_VENTA", line 9
ORA-06512: at line 1

el usuario con que ejecute tiene permiso de ejecucion de cualquier procedimiento y el de creacion de cualquier contexto.
Gracias.

lfer dijo...

Hola, con gusto puedo ayudar.

Puedes colocar todo el codigo que usaste para llegar al error?
Desde la creacion del contexto, paquete hasta la ejecucion del procedimiento.

Es importante para saber que ocurre.

Saludos

Anónimo dijo...

OK, gracias por contestar, el codigo que use lo especifico abajo; el caso es que herede una aplicacion en la que la tabla de cuentas no tiene unidad de negocio y ahora cambiaron el plan de cuentas y se diferenciaron dichas cuentas entonces la solucion q se me ocurrio fue reemplazar la tabla con una vista dinamica; la duda que tenia era por ejem. si entran dos usuarios a la aplicaion cada uno de diferente unidad de negocio (SETID) cada sesion vera la data que le corresponde o quedara el valor del contexto del ultimo ingreso esto porque la aplicacion internamente usa un solo usuario de base de datos.

CREATE CONTEXT app_cntxt_setid USING PK_SETID_CUENTA

CREATE OR REPLACE PACKAGE DBCT_CMC.PK_SETID_CUENTA IS
PROCEDURE set_setid (p_setid IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY DBCT_CMC.PK_SETID_CUENTA iS
PROCEDURE set_setid (p_setid IN VARCHAR2) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('app_cntxt_setid', 'setid', p_setid) ;
END;
END;
/

En esta me sale el error:
BEGIN
DBCT_CMC.PK_CUENTA_VENTA.SP_SET_CONTEXTO('CMC');
END;
incluso probe asi:
EXEC DBCT_CMC.PK_CUENTA_VENTA.SP_SET_CONTEXTO('CMC');

La vista la cree asi:
CREATE VIEW V_CUENTA_VENTA AS
SELECT
ID_CUENTA,
ID_MON,
CUE_ID_CUENTA,
CUE2_ID_CUENTA,
CUE3_ID_CUENTA,
CUE4_ID_CUENTA,
CUE5_ID_CUENTA,
CUE6_ID_CUENTA,
TX_CONCEP
FROM CUENTA_VENTA_SETID
WHERE SETID = sys_context('app_cntxt_setid','setid')

el usuario dueño del esquema en el que cree los objetos es DBCT_CMC.

Muchas gracias por tu ayuda.

lfer dijo...

Hola,
Unicamente puedes alterar el contexto usando los procedimientos y funciones del paquete que has autenticado para ese fin.

Creaste el contexto autorizando al paquete PK_SETID_CUENTA, el cual definiste un unico procedure, set_setid. Es ese el unico procedure que puedes ejecutar para alterar tu contexto.

Por tanto la ejecución de DBCT_CMC.PK_CUENTA_VENTA.SP_SET_CONTEXTO no es válida. No corresponde a la definicion del contexto que has definido.

Espero que sea de ayuda.

Saludos.

Anónimo dijo...

Gracias me hice bolas con los nombres de los objetos; ya pude crear la vista y funciona bien.

Muchas gracias y muy bueno tu aporte.

Anónimo dijo...

Hola disculpa, me gustaria saber si puedo aplicar lo del contexto con mas de un parametro de entrada

lfer dijo...

Hola,

Naturalmente, la funcion set es como cualquier otra. Dentro de la misma seteas dos veces el contexto una vez para cada parametro. Es eso lo que quieres?

Saludos

Anónimo dijo...

Hola, Muy buen blog.

Tienes idea de como puedo modificar el valor del contexto desde .net?

Edwin_Mejia dijo...

estoy confundido con la manipulacion del paramtro creastes un paquete y no estoy familiarizado con ellos pero bueno yo deseo usar paramtros en la vista pero al llamar la vista te devuelve los resultado segun lo que dejastes en el procedure por lo tanto a eso le llamo como un dato quemado me gustaria hacerlo de forma dinamica view (paramtro) y que me devuelva le resultado segun el parametro que le envio pero en el entorno que me explicas siento que estoy amarrado a lo que ya esta definido en el procedure no se si me doy a explicar

Anónimo dijo...

Muy buen aporte, pero tengo un inconvniente, al parecer con la versión en la que trabajo (Oracle 10G), no funciona, probablemente sea un bug y se necesite un requisito adicional como un parchado a la base, en otros foros he econtrado que a alguien le dejó de funcionar un procedimiento similar al que explicás, venia de una versión 9i y ahora con la 10g no le funciona.

Anónimo dijo...

Isaias

En un análisis por utilizar este método me pregunto quisiera que alguien me conteste cual es la ventaja en usar una vista con parámetros que hay que actualizar o usar una vista normal que sus parámetros vengan de una tabla y hacerle update a la tabla de parámetros antes de ejecutar la vista?