Este es el Post #15 del Topic: Guía Práctica de PostgreSQL
SUGERENCIA:
Si has utilizado la base de datos dbadmision en ejercicios previos es mejor volver a crearla.Descargar Script: DBAdmision.sql
El Lenguaje de Control de Datos (Data Control Language – DCL) es utilizado para llevar a cabo la administración de priviligios en los objetos de la base de datos el cual ayuda a garantizar la accesibilidad y seguridad dentro de los objetos de la base de datos.
Las clásulas DCL que se usan para el control de accesos en PostgreSQL son:
GRANT
REVOKE
Como de costumbre, usamos la base de datos dbadmision y nos enfocaremos en un caso real de implmentación de un sistema para la Universidad Continental, que de paso es donde estudio xD.
Escenario:
Después de los estudios realizados sobre las necesidades de la oficina de Admisión de la Universidad Continental se ha construido la base de datos DBadmision.
Las oficinas que trabajan con esta base de datos son:
Oficina | Empleado | Inicio de Sesión | Contraseña | Tipo de Usuario |
Admisión | Cecilia Calderón | Ccalderon | Patito | Jefe |
Carmen Peralta | Cperalta | Perrito | Jefe | |
Yovana Vargas | Yvargas | Muchachita | Operador | |
Liz Castro | Lcastro | Michimichi | Operador | |
Marketing | Katty Suarez | Ksuarez | Loquita | Jefe |
Martha Carrion | Mcarrion | Love | Operador |
Se tiene desarrollada una aplicación que acceda a la base de datos con el usuario y contraseña concedidos a cada uno de los usuarios (Autenticación SQL). Con respecto a la aplicación la oficina de Admisión está solicitando que se implemente:
- Un reporte que dada un determinado periodo académico (IDPerAcad] liste: apellidos y nombres, carrera y modalidad de todos los postulantes de ese periodo.
- Un reporte que dada un determinado periodo académico (IDPerAcad) liste: apellidos y nombres, carrera y modalidad de todos los ingresantes de ese periodo.
- Un reporte que dada un determinado periodo académico (IDPerAcad) liste: apellidos y nombres, carrera y modalidad de todos los postulantes que no asistieron al examen de ese periodo.
- Los 10 mejores puestos por periodo (IDPerAcad).
Así mismo los de la oficina de Marketing necesitan que se implemente lo siguiente:
- Un reporte que muestre la cantidad de postulantes por cada periodo (IDPerAcad)
- Un reporte que muestre la cantidad de postulantes por cada periodo (IDPerAcad), este reporte debe de mostrar las columnas: Periodo, varón, mujer y total.
- Un reporte que liste los apellidos y Nombres de todos aquellos que cumplan años, este reporte debe de funcionar en función a la fecha actual.
- Listado de aquellos contactos que nunca ingresaron.
Los usuarios operadores de la oficina de Admisión solo pueden:
- Seleccionar, Actualizar e Insertar Contactos y Postulante.
- De los postulantes solo pueden tener acceso de selección en los campos Puntaje, AsistioExamen e Ingreso.
- Acceso a los requerimientos previamente solicitados.
Los Usuarios jefes de la oficina de Admisión pueden:
- Seleccionar y Eliminar Postulantes.
- Actualizar los campos Puntaje, AsistioExamen e Ingreso de los postulantes.
- Seleccionar Contactos
- Seleccionar, Insertar, Eliminar y Actualizar Carreras, Modalidades y Periodos.
- Acceso a los requerimientos previamente solicitados.
Los usuarios operadores y jefes de Marketing:
- Acceso a los requerimientos recientemente solicitados.
Tenga en cuenta que en cualquier momento el jefe u operadores de las oficinas pueden cambiar, así que las soluciones planteadas deben de ser lo más flexible posible.
DESARROLLO
PARTE 1: CREACIÓN DE ROLES Y USUARIOS DE LOGIN
–CREANDO ROLES
CREATE ROLE JefeADM; CREATE ROLE OpeADM; CREATE ROLE JefeMKT; CREATE ROLE OpeMKT;
–CREANDO LOGINS
CREATE ROLE Ccalderon WITH LOGIN PASSWORD 'Patito' VALID UNTIL '2012-01-01' IN ROLE JefeADM;
CREATE ROLE Cperalta WITH LOGIN PASSWORD 'Perrito' VALID UNTIL '2012-01-01' IN ROLE JefeADM;
CREATE ROLE Yvargas WITH LOGIN PASSWORD 'Muchachita' VALID UNTIL '2012-01-01' IN ROLE OpeADM;
CREATE ROLE Lcastro WITH LOGIN PASSWORD 'Michimichi' VALID UNTIL '2012-01-01' IN ROLE OpeADM;
CREATE ROLE Ksuarez WITH LOGIN PASSWORD 'Loquita' VALID UNTIL '2012-01-01' IN ROLE JefeMKT;
CREATE ROLE Mcarrion WITH LOGIN PASSWORD 'Love' VALID UNTIL '2012-01-01' IN ROLE OpeMKT;
PARTE 2: CREACIÓN DE REQUERIMIENTOS
–CREANDO ESQUEMAS
CREATE SCHEMA REQ_ADM; CREATE SCHEMA REQ_MKT;
–CREANDO FUNCIONES REQUERIDAS
Las siguientes funciones fueron creadas según los requerimientos realizados en el escenario mostrado inicialmente de las oficinas de Admision (ADM) y Marketing (MKT), que serán almacenados en los esquemas anteriormente definidos para organizarlo adecuadamente y también para facilitar la asignación de permisos por esquemas.
Ejecutar: Script de Requerimientos
PARTE 3: ASIGNACIÓN DE DERECHOS
–(A LOS OPERADORES DE ADM)
–(Derecho 1) : Seleccionar, Actualiza e Insertar Contactos y Postulantes
GRANT SELECT ON Persona.Contacto TO OpeADM; GRANT INSERT ON Persona.Contacto TO OpeADM; GRANT UPDATE ON Persona.Contacto TO OpeADM;
GRANT SELECT ON Persona.Postulante TO OpeADM; GRANT INSERT ON Persona.Postulante TO OpeADM; GRANT UPDATE ON Persona.Postulante TO OpeADM;
GRANT USAGE ON SCHEMA Persona TO OpeADM;
–(Derecho 2) : De los postulantes solo debe tener acceso de seleccion sobre Puntaje, AsistioExamen e Ingreso
REVOKE SELECT ON Persona.Postulante FROM OpeADM; --Quitamos permiso de SELECT sobre Postulante GRANT SELECT (AsistioExamen) ON Persona.Postulante TO OpeADM; GRANT SELECT (Ingreso) ON Persona.Postulante TO OpeADM;
–(Derecho 3) : Acceso a los requerimientos previamente solicitados
GRANT USAGE ON SCHEMA REQ_ADM TO OpeADM;
–(A LOS JEFES DE ADM)
–(Derecho 1) : Seleccionar y Eliminar Postulantes.
GRANT SELECT ON Persona.Postulante TO JefeADM; GRANT DELETE ON Persona.Postulante TO JefeADM; GRANT USAGE ON SCHEMA Persona TO JefeADM;
–(Derecho 2) : Actualizar los campos Puntaje, AsistioExamen e Ingreso de Postulante
GRANT UPDATE (Puntaje) ON Persona.Postulante TO JefeADM; GRANT UPDATE (AsistioExamen) ON Persona.Postulante TO JefeADM; GRANT UPDATE (Ingreso) ON Persona.Postulante TO JefeADM;
–(Derecho 3) : Seleccionar Contactos
GRANT SELECT ON Persona.Contacto to JefeADM;
–(Derecho 4) : Seleccionar, Insertar, Eliminar y Actuzalizar Carreras, Modalidades y Periodos
GRANT SELECT,INSERT,DELETE, UPDATE ON Admision.Carrera TO JefeADM; GRANT SELECT,INSERT,DELETE, UPDATE ON Admision.Modalidad TO JefeADM; GRANT SELECT,INSERT,DELETE, UPDATE ON Admision.PerAcad TO JefeADM;
GRANT USAGE ON SCHEMA Admision to JefeADM;
–(Derecho 5) : Acceso a los requerimientos previamente solicitados
GRANT USAGE ON SCHEMA REQ_ADM TO JefeADM;
–(A LOS JEFES Y OPERADORES DE mkt)
GRANT USAGE ON SCHEMA REQ_MKT TO JefeMkt,OpeMKt;
PARTE 4: VERIFICACIÓN DE DERECHOS ASIGNADOS
La siguiente parte es verificar que los permisos hayan sido otorgados correctamente, tambièn es posibleir verificando estos a medida se vayan asignando los privilegios.
–VERIFICANDO USUARIO ACTUAL
SELECT SESSION_USER, CURRENT_USER;
–COMPROBANDO DERECHOS
–(DE LOS OPERADORES DE ADM)
SET SESSION AUTHORIZATION OpeADM;
–(Derecho 1) : Seleccionar, Actualiza e Insertar Contactos y Postulantes
SELECT * FROM Persona.Contacto;
INSERT INTO Persona.Contacto VALUES( ,'Anonymous' ,'Anonymous' ,'Anonymous' ,0 ,00000000 ,'10/11/2000' ,now());
UPDATE Persona.Contacto SET nombres='Anonimo' ,paterno='Anonimo' ,materno='Anonimo' WHERE IDCOntacto='176';
DELETE FROM Persona.Contacto WHERE IDCOntacto='176'; --El rol OpeADM no tiene permiso para eliminar.
–(Derecho 2) De los postulantes solo debe tener acceso de seleccion sobre Puntaje, AsistioExamen e Ingreso
SELECT IDPostulante,IDContacto,IDCarrera,IDModalidad FROM Persona.Postulante;
SELECT Puntaje,AsistioExamen,Ingreso FROM Persona.Postulante;
–(Derecho 3) : Acceso a los requerimientos previamente solicitados
SELECT * FROM REQ_ADM.pa_r1() AS (column1 unknown); SELECT * FROM REQ_ADM.pa_r2() AS (column1 unknown); SELECT * FROM REQ_ADM.pa_r3() AS (column1 unknown); SELECT * FROM REQ_ADM.pa_r4() AS (column1 unknown);
–(DE LOS JEFES DE ADM)
SET SESSION AUTHORIZATION JefeADM;
–(Derecho 1) : Seleccionar y Eliminar Postulantes.
SELECT * FROM Persona.Postulante; DELETE FROM Persona.Postulante WHERE IDContacto='175';–(Derecho 2) : Actualizar los campos Puntaje, AsistioExamen e Ingreso de Postulante
UPDATE Persona.Postulante SET Puntaje=20, AsistioExamen='1', Ingreso='1' WHERE IDContacto='174'; UPDATE Persona.Postulante SET IDModalidad='0';--No tiene persmiso
–(Derecho 3) : Seleccionar Contactos
SELECT * FROM Persona.Contacto;–(Derecho 4) : Seleccionar, Insertar, Eliminar y Actuzalizar Carreras, Modalidades y Periodos
SELECT * FROM Admision.Carrera; SELECT * FROM Admision.Modalidad; SELECT * FROM Admision.PerAcad;
–(Derecho 5) : Acceso a los requerimientos previamente solicitados
SELECT * FROM REQ_ADM.pa_r1() AS (column1 unknown); SELECT * FROM REQ_ADM.pa_r2() AS (column1 unknown); SELECT * FROM REQ_ADM.pa_r3() AS (column1 unknown); SELECT * FROM REQ_ADM.pa_r4() AS (column1 unknown);
–(DE LOS JEFES DE MKT)
SET SESSION AUTHORIZATION OpeMKT;
–(Derecho 1) : Acceso a los requerimientos previamente solicitados
SELECT * FROM REQ_MKT.pa_r1() AS (column1 unknown); SELECT * FROM REQ_MKT.pa_r2() AS (column1 unknown); SELECT * FROM REQ_MKT.pa_r3() AS (column1 unknown); SELECT * FROM REQ_MKT.pa_r4() AS (column1 unknown);
–(DE LOS OPERADORES DE MKT)
SET SESSION AUTHORIZATION JefeMKT;
–(Derecho 1) : Acceso a los requerimientos previamente solicitados
SELECT * FROM REQ_MKT.pa_r1() AS (column1 unknown); SELECT * FROM REQ_MKT.pa_r2() AS (column1 unknown); SELECT * FROM REQ_MKT.pa_r3() AS (column1 unknown); SELECT * FROM REQ_MKT.pa_r4() AS (column1 unknown);
Es importante señalar que las verificaciones se han realizado únicamente por medio de roles de grupo, lo ideal y correcto será realizar las operaciones como usuarios (roles de login) por ejemplo:
SET SESSION AUTHORIZATION OpeADM;
cambiarlo por:
SET SESSION AUTHORIZATION Yvargas;
El resultado deberá ser el mismo ya que Yvargas pertenece al grupo OpeADM.
PARTE 5: REESTABLECIMIENTO Y/O ELIMINACIÒN DE OBJETOS
Si queremos eliminar algunos objetos o incluso la base de datos no debe existir ningún rol asociado a èste
RESET SESSION AUTHORIZATION; --Regresamos al usuario principal por defecto.
SELECT SESSION_USER, CURRENT_USER;
DROP DATABASE dbadmision; --ERROR: database "dbadmision" is being accessed by other users
REVOKE ALL ON SCHEMA REQ_MKT,REQ_ADM,Persona,Admision FROM JefeADM,OpeADM,JefeMKT,OpeMKT;
REVOKE ALL ON TABLE Persona.Contacto ,Persona.Postulante ,Admision.Carrera ,Admision.PerAcad ,Admision.Modalidad FROM JefeADM,OpeADM,JefeMKT,OpeMKT;
DROP USER CCalderon,CPeralta,Yvargas,LCastro,Ksuarez,Mcarrion;
DROP ROLE JefeADM,OpeADM,JefeMKT,OpeMKT;
DROP DATABASE dbadmision;
Para la elminación de la base de datos habrá que confirmar además de que no exista ninguna conexión abierta de la base de datos por algún usuario.
SELECT * FROM pg_stat_activity;
Your article helped me a lot, is there any more related content? Thanks!
Your point of view caught my eye and was very interesting. Thanks. I have a question for you.
Excelente blog. No se puede descargar el archivo .sql
Una excelente y muy apropiada explicación de la funcionalidad de manejo de roles y usuarios, mejor no podia estar, muchas gracias por tu articulo
Lo que faltaría es realizar triggers para saber quien cambio la contraseña, quien creo usuarios, etc.
Saludos.
Es excelente tu trabajo, gracias por un tutorial tan detallado de postgresql
Interesante tu publicación. Lo usaré para uno de mis trabajos grupales que tengo en la universidad. Gracias de antemano 🙂
A sacarle provecho!!
Saludos!!