PostgreSQL: Sentencias DCL

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;