Vistas
Una vez que se dispone de los datos precisos en una base de
datos, conviene recordar que el superadministrador (postgres) es el
único usuario que puede acceder a la base de datos. Normalmente se
crearán otros usuarios con necesidades de visualización de datos
restringidas a los que podremos permitir operaciones de selección,
inserción, modificación y borrado.
En primer lugar se deben crear vistas de los datos que limiten
determinados campos.
Ejercicio 1:
Crear una base de datos llamada 'empleado' que contenga una única tabla
llamada 'temple' con los siguientes campos:
- codigo:
serial PK
- nombre:
character varying (50)
- sueldo:
double precision
- fechnac:
date
- categoria:
char. Si el campo contiene 'J' se supone que es un jefe y si contiene
una 'E' es um empleado
Introducir en esta tabla algunos registros (por ejemplo 5
empleados y 2 jefes)
Como
puede que no nos interese que un usuario "normal" vea todos los campos
ni todos los registros, creamos una vista que contenga los datos que si
están "permitidos"
Ejercicio
2: Crear una vista llamada 'vista1' que visualice nombre y sueldo de
los empleados
CREATE OR REPLACE VIEW vista1
AS
SELECT temple.nombre, temple.sueldo
FROM temple
WHERE temple.categoria = 'E'::bpchar;
ALTER TABLE vista1 OWNER TO postgres;
Si
alguien realiza una consulta sobre esta vista (SELECT * FROM vista1)
creerá que la base de datos tiene una tabla llamada vista1 que sólo
contiene 2 campos y 5 registros
Usuarios (roles de login)
Un
usuario es una identificación frente a la base de datos. Tiene un
nombre de usuario y una contraseña. Para crearlo con pgAdmin, basta
marcar el objeto roles de login y con el menu contextual, crear un
nuevo usuario
Ejercicio
3: Crear un usuario llamado 'pepito' con contraseña 'pepito'
Verificar
que pepito puede acceder a la base de datos pero no puede ver los datos
de ninguna tabla, ni vista. Tampoco puede asignarse permisos. Es un
usuario "pelado"
Grupos (roles de grupos)
Grupo es un objeto al que se
le conceden permisos sobre las vistas y que tiene como miembros a
distintos usuarios.
- Un usuario puede pertenecer a varios grupos.
- Un grupo puede tener varios usuarios
- Un grupo puede tener permisos sobre varias vistas
- Una vista puede ser manejada por varios grupos, cada uno
con distintos permisos
Ejercicio
4: Crear un grupo llamado 'grupo' con contraseña 'pepito'
Ejercicio
5: Asignar al usuario 'pepito' al grupo 'grupo'
Menu contextual sobre el usuario 'pepito', pestaña 'membresia
de role', asignar el grupo 'grupo'
Ejercicio
6: Permitir al grupo 'grupo' que realice operaciones de SELECT
en la 'vista1'
vista1 -
Propiedades - Privilegios - Seleccionar el rol grupo - Marcar la
casilla SELECT
Verificar
que ahora el usuario 'pepito' puede ver y hacer consultas SQL de SELECT
con la vista1 (tal como SELECT * FROM vista1 WHERE sueldo>1500)
Reglas (rules)
El sistema de reglas de Postgres
Postgres utiliza un poderoso
sistema de reglas para la especificación de vistas y actualizaciones de
vistas ambiguas. La implementación del sistema de reglas es
una técnica llamada reescritura de la consulta. El sistema de reescritura es un módulo que existe entre la etapa del traductor y el planificador/optimizador.
El
sistema de reescritura de la consulta es un módulo entre la etapa de
traducción y el planificador/optimizador. Procesa el árbol devuelto por
la etapa de traducción (que representa una consulta de usuario) y si
existe una regla que deba ser aplicada a la consulta reescribe el árbol de una forma alternativa.
Los
pasos realizados por el sistema de reescritura de la consulta cada vez
que aparece una consulta de usuario son los siguientes:
- Toma la consulta dada por la parte de acción de la regla.
- Adapta
la lista-objetivo para recoger el número y orden de los atributos dados
en la consulta del usuario (old para los datos ya existentes en la
base de datos, new para los nuevos datos)
- Añade la
cualificación dada en la cláusula WHERE de la consulta del usuario a la
cualificación de la consulta dada en la parte de la acción de la regla.
En
el punto anterior se concedió el permiso de SELECT sobre la 'vista1' al
grupo 'grupo' (o lo que es lo mismo, al usuario 'pepito'). Si
hubiéramos concedido el permiso de INSERT, DELETE y/o UPDATE nos
habríamos encontrado con que nos deniega la posibilidad de realizar
esas operaciones por no tener una regla (da el
ERROR:
no se puede eliminar de una vista. Necesita un regla
incondicional ON DELETE DO INSTEAD)
Para poder realizar una
operación de mantenimiento en una vista, es preciso crear una regla
que soporte la operación en cuestión. Una regla es la operación real
realizada sobre las tablas que "filtra" a la operación realizada sobre
la vista. Es una "traducción" de la orden del usuario a la
operación real realizada por el superusuario sobre las tablas de la
base de datos.
Por defecto SOLO existe la regla _RETURN que
permite la operación SELECT. Verificar que la definicion coincide con
la sintaxis de creación de la vista correspondiente
Si queremos que el usuario 'pepito' relice operaciones de
mantenimiemto sobre la 'vista1':
- Sobre la 'vista1' concedemos privilegios a 'grupo' para
realizar las operaciones de INSERT, DELETE y UPDATE .
- NOTA:
Como para hacer inserciones es preciso acceder a la secuencia que marca
el campo código, cocederemos el privilegio de USAGE al grupo 'grupo'
para la secuencia temple_codigo_sec
- Creamos una nueva regla sobre 'vista1' dándole un nombre y
marcando la casilla de la operación que controla
- Marcamos la casilla 'Do Instead' para que la regla esté
activa
- Definimos
la operación real llevada a cabo sobre la tabla pertinente. Los campos
tecleados por el usuario que hagan referencia a valores escritos en las
tablas (órdenes DELETE y UPDATE) se anteceden con el prefijo old,
los campos insertados por el usuario (órdenes INSERT y UPDATE) se
anteceden con el prefijo new
Ver las figuras de la definición de una regla de borrado, una de
inserción y una de modificación:
Notas
sobre las definiciones de las operaciones de los ejemplos:
- Las
inserciones 'pasan' del sueldo que se le ponga al nuevo
empleado,
puesto que siempre le pondrá un sueldo de 1000. Los nuevos empleados
habrásn nacido el día de San Fermin de 1995 y siempre tendrán la
categoría de Empleado
- Las modificaciones 'pasan' del cambio de nombre. Sólo
actualizan el sueldo
- Los borrados se realizan sin problemas
Ejercicio 7: Práctica de gestión de un hospital
Implementar la base de datos 'hospital' que dispone de 2 tablas para gestionar las historias clínicas de sus pacientes:
- Tabla: paciente
- codigo. Ejemplo: '19930721PML'
- nombre. Ejemplo: 'Perez Martinez, Luis'
- fecha_nacimiento. Ejemplo: 1993-07-21
- Tabla: historial
- cod. Ejemplo: 456
- paciente. Ejemplo: '19930721PML'
- fecha. Ejemplo: '2013-04-17'
- diagnostico. Ejemplo: 'amigdalitis'
- tratamiento. Ejemplo: 'Receta de antibiotico y gargaras'
- importe. Ejemplo: 25
Para gestionar las historias, creamos los siguientes grupos:
- grupo: medicos.
- Sobre la tabla paciente
- Pueden seleccionar todos los datos
- Sobre la tabla historial
- Pueden seleccionar todos los datos excepto los importes
- Pueden
dar de alta nuevos registros en el historial. Tomará la fecha de hoy
como fecha del nuevo registro. El importe se pondrá a 0
- No pueden modificar ni dar de baja registros de historial
- grupo: contables
- No puede hacer nada con la tabla paciente
- Sobre la tabla historial
- Puede ver los campos cod, tratamiento e importe de aquellos registros cuyo precio sea 0
- Puede modificar el precio. Observad que en cuanto pone un precio a un registro, deja de verlo
Crear un usuario de cada grupo de los anteriores y verificar que la confidencialidad de los historiales está garantizada
Volver a pgAdminIII
