unidad7_controldedatos
TRANSCRIPT
-
7/23/2019 Unidad7_ControldeDatos
1/15
Control de DatosBase de Datos I
Juan Jos Ramrez Lama
www.juaramir.com
Universidad del Bo-Bo
Basada en las clases de Monica Caniupan
Contenido
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 2 / 58
Lenguaje de Control de Datos
Contenido
1 Lenguaje de Control de DatosGRANT
REVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 3 / 58
Lenguaje de Control de Datos
Esquemas (SCHEME)
Los esquemas son importantes para agrupar objetos segn espe-
cificaciones, y al mantener organizado la base de datos permite un
mejor desempeo al momento de la administracin.
Si no se le asigna un esquema al objeto, postgres lo asignar im-
plicitamente al esquema public.
Para definir que un objeto pertenezca a un esquema se pone el
nombre del esquema seguido por un punto . y el nombre del ob-
jeto, que puede ser una tabla, una secuencia, etc.
Ejemplo
CREATE SCHEMA Administrador;
CREATE TABLE Administrador . Usuarios(nick v a r c h a r( 1 5 ) PRIMARY KEY,
nombre v a r c h a r( 2 0 ) ,apellido v a r c h a r( 2 0 ) ,password v a r c h a r( 5 0 ) NOT NULL )
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 4 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.juaramir.com/mailto:[email protected] -
7/23/2019 Unidad7_ControldeDatos
2/15
Lenguaje de Control de Datos
Sentencias DCL
El Lenguaje de Control de Datos (Data Control Language DCL) es
utilizado para llevar a cabo la administracin de privilegios en los
objetos de la base de datos el cual ayuda a garantizar la accesibi-
lidad y seguridad dentro de los objetos de la base de datos.Una aplicacin de BD debe cumplir tres objetivos de seguridad:
Secreto: La informacin no se debe dar a conocer a usuarios noautorizaos.Integridad: Solo se debe permitir modificar los datos a los usuariosautorizados.
Disponibilidad: No se debe impedir el acceso a los usuarios auto-rizados.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 5 / 58
Lenguaje de Control de Datos
Control de Acceso
Una poltica de seguridad describe las medidas que se deben
aplicar.
Un mecanismo de seguridad nos permite implantar una polticade seguridad.
El SGBD ofrece dos mecanismos de control de acceso:
Control discrecional de acceso basado en privilegios.Controlobligatorio de acceso.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 6 / 58
Lenguaje de Control de Datos
Privilegios
Se otorgan privilegios a los usuarios de tablas y vistas.
El creador de la tabla/vista obtiene automticamente todos los pri-vilegios sobre la tabla/vista.
El SGBD mantiene un registro de quienes tienen privilegios sobre
objetos (y de quienes los pierden) y se asegura que solo los usua-
rios que tienen privilegios operen sobre los datos.
Las clusulas DCL que se usan para el control de accesos discre-cional son:
GRANT: concede a los usuarios privilegios sobre tablas y vistas.REVOKE: retira un privilegio de un objeto de la BD.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 7 / 58
Lenguaje de Control de Datos GRANT
Contenido
1 Lenguaje de Control de DatosGRANT
REVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 8 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
3/15
Lenguaje de Control de Datos GRANT
GRANT
GRANT privilegio ON objeto TO usuario [ WITH GRANT
OPTION]
privilegio: SELECT, INSERT, DELETE, . . .
objeto: es una tabla o vista.
[WITH GRANT OPTION]: el usuario puede transmitir a otro usua-rio los privilegios recibidos.
Solo los propietarios del esquema pueden ejecutar instruccionesCREATE TABLE,ALTER TABLEy DROP
Tal derecho no se puede conceder ni revocar.
Los usuarios que crean vistas tienen los mismos privilegios que
tengan las tablas (vistas) empleadas para definir las vistas.
Los privilegios se asignan a los identificadores de autorizaciones,
que pueden denotar a un solo usuario o a un grupo de usuarios.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 9 / 58
Lenguaje de Control de Datos GRANT
Privilegios GRANT
SELECT: tener acceso a todas las columnas de la tabla objetivo
(incluyendo aquellas aadidas vaALTER TABLE).
INSERT (nombre-columna): Puede insertar tuplas con valores no
nulos en la tabla sealada como objeto.INSERT: puede insertar tuplas en todas las columnas de la tabla
sealada como objeto (lo mismo para UPDATE).
DELETE: derecho a eliminar tuplas.
REFERENCES (nombre-columna): el derecho a definir claves ex-
ternas (de otras tablas) que hagan referencia a la columna especi-
ficada de la tabla objetivo.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 10 / 58
Lenguaje de Control de Datos GRANT
Ejemplo de GRANT
El usuario Juan crea las tablas Barco, Reservas y Marineros.
Algunas ordenes que puede ejecutar Juan:
GRANT INSERT , DELETE ON Reservas TO Horacio
WITH GRANT OPCION
GRANT SELECT ON Reservas TO Miguel
GRANT SELECT ON Marineros TO Miguel WITH GRANT
OPTION
GRANT DELETE ON Barcos TO Maria WITH GRANT
OPTION
GRANT UPDATE ( categoria) ON Marineros TO Mario
GRANT REFERENCES ( idb) ON Barcos TO Humberto
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 11 / 58
Lenguaje de Control de Datos GRANT
Ejemplo de GRANT
Miguel tiene el siguiente privilegio:
GRANT SELECT ON Marineros TO Miguel WITH GRANTOPCION
Miguel crea la vista:
CREATE VIEW MarinerosJovenes( idm , edad ,
categoria) AS SELECT M . idm , M . edad , M .
categoria
FROM Marineros M
WHERE M . edad < 18
Miguiel puede otorgar privilegios sobre la vista.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 12 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
4/15
Lenguaje de Control de Datos REVOKE
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 13 / 58
Lenguaje de Control de Datos REVOKE
REVOQUE
REVOKE [GRANT OPTION FOR] privilegios ON objeto
FROM usuario { RESTRICT | CASCADE}
Esta orden se usa para revocar privilegios o solo la opcin GRANT
de los privilegios
Cuando se ejecutaCASCADEes efecto es la retirada de todos los
privilegios indicados a todos los usuarios que recibieron el privile-
gio vaGRANTpor el mismo usuario que ahora ejecuta REVOKE
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 14 / 58
Lenguaje de Control de Datos REVOKE
Ejemplo de REVOKE
Juan crea Marineros.
Consideremos las siguientes ordenes:
GRANT SELECT ON Marineros TO Arturo WITH GRANT
OPTION ( e j e c u t a d a p o r J ua n )
GRANT SELECT ON Marineros TO Borja WITH GRANT
OPTION ( e j e cu t a da p o r A r t u r o )
REVOKE SELECT ON Marineros FROM Arturo CASCADE (
e j e cu t a d a p o r J ua n )
Consecuencias:
Arturo pierde el privilegio de SELECT sobre marineros.Borja, que recibio ese privilegio de Arturo, y solo de el, tambien lo
pierde.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 15 / 58
Lenguaje de Control de Datos REVOKE
Ejemplo de REVOKE
Consideremos las siguientes ordenes:
GRANT SELECT ON Marineros TO Arturo WITH GRANT
OPTION ( e j e c u t a d a p o r J ua n )
GRANT SELECT ON Marineros TO Borja WITH GRANT
OPTION ( e j e c u t a d a p o r J ua n )
GRANT SELECT ON Marineros TO Borja WITH GRANT
OPTION ( e j e cu t a d a p o r A r t u r o )
REVOKE SELECT ON Marineros FROM Arturo CASCADE (
e j e cu t a d a p o r J ua n )
Ahora pierde Borja sus privilegios?
Borja conserva el privilegio porque tambin lo haba recibido de
Juan.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 16 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
5/15
Lenguaje de Control de Datos REVOKE
Ejemplo de REVOKE
Supongamos queArturorecibi el siguiente privilegio:
GRANT SELECT ON Marineros TO Arturo WITH GRANT
OPTION ( e j e c u t a d a p o r J ua n )
La siguiente sentencia revoca solo el GRANT OPTION del privile-
gio:
REVOKE GRANT OPTION FOR SELECT ON Marineros FROM
Arturo CASCADE ( e j e c u t a d a p o r J ua n )
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 17 / 58
Control Obligatorio de Acceso
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 18 / 58
Control Obligatorio de Acceso
Control Obligatorio de Acceso
Los mecanismos de control discrecional de acceso son efectivos
pero tienen algunos puntos dbiles.
Son vulnerables a los esquemas tipo caballo de Troya, en los que
usuarios no autorizados con malas intenciones pueden engaar a
usuarios autorizados que entreguen datos delicados.
Los mecanismos de control obligatorio de acceso estn pen-
sados para abordar estos puntos dbiles.
El modelo ms popular se denominaBell-LaPadula.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 19 / 58
Control Obligatorio de Acceso
Modelo Bell-LaPadula
Este modelo se describe en trminos de:
Objetos: tablas, vistas, filas, columnas.
Sujetos: usuarios, cuentas, programas.
Clase de seguridad: Mximo Secreto (MS), Secreto (S), Confi-dencial (C), No Clasificado (N), donde:
MS> S> C> N(datos ms delicados que otros).
Autorizaciones: cada objeto tiene una clase de seguridad y a ca-
da sujeto se le concede autorizacin para una clase de seguridad.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 20 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
6/15
Control Obligatorio de Acceso
Restricciones sobre Operaciones
El modelo impone 2 restricciones a todas las operaciones de lectura y
escritura:Propiedad de Seguridad Simple: el sujetoS puede leer el objetoOsi:
clase(S) clase(O)Ejemplo: Los usuarios con autorizacin MS pueden leer tablas con
autorizacin C, pero un usuario con autorizacin C no puede leerobjetos con clasificacin MS.
Propiedad *: Se permite que el sujeto S escriba el objeto O si:clase(S) clase(O)Ejemplo: Los usuarios con autorizacin S solo pueden escribir ob-jetos con clasificacin S o MS (MS> S> C> N).Esta restriccin impide que un sujeto escriba un objeto que se en-cuentre en una clasificacin de seguridad menor que el nivel delsujeto.La violacin de esta regla permitira que la informacin fluyese des-de clasificaciones ms altas hacia las ms bajas, lo cual viola unode los principios bsicos de la seguridad multinivel.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 21 / 58
Control Obligatorio de Acceso
Ejemplo Control Obligatorio de Acceso
El alumno Diego (usuario) quiere modificar la tabla de notas del
Profesor Juan.
La tabla notas se puede clasificar S y Juan con autorizacin S para
la tabla.
Diego tiene autorizacinC (C < S)y solo puede crear objetos declasificacin C o inferior.
Se corre un programa de aplicacin con el nombre de Juan, el
programa dice que tiene clase S y copia las notas en una tabla
Copiaque creo Diego con clase C.
El programa no cumplir su objetivo ya que no se cumple:
clase(Aplicacion) clase(Copia)
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 22 / 58
Seguridad de Aplicaciones en Internet
Contenido
1 Lenguaje de Control de DatosGRANT
REVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 23 / 58
Seguridad de Aplicaciones en Internet
Seguridad de Aplicaciones en Internet
Los mtodos de seguridad ms conocidos son:
Cifrado.
Servidores de Certificacin.
Firmas Digitales.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 24 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
7/15
Seguridad de Aplicaciones en Internet Cifrado
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 25 / 58
Seguridad de Aplicaciones en Internet Cifrado
Cifrado
Se aplica un algoritmo de cifrado a los datos mediante una Clave
de Cifradoespecificada por el usuario o ABD.
El resultado del algoritmo es la versin cifrada de los datos.
Hay unAlgoritmo de descifradode los datos que toma los datosy una clave de descifrado y devuelve los datos originales.
Cifrar(datos originales, clave cifrado) = datos cifradoDescifrar(datos cifrados, clave de descifrado) = datos origina-lesSin la clave para descifrar no es posible recuperar los datos origina-les.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 26 / 58
Seguridad de Aplicaciones en Internet Cifrado
Cifrado
Cifrado Simtrico:Clave cifrado = Clave descifradoTodos los usuarios autorizados conocen la clave.
Cifrado de clave pblica: Cada usuario autorizado tiene dos cla-ves:
Clave de cifrado pblica: conocida por todos los usuarios.Clave de descifrado privada: conocida solamente por un usuario.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 27 / 58
Seguridad de Aplicaciones en Internet Servidores de Certificacin
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 28 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
8/15
Seguridad de Aplicaciones en Internet Servidores de Certificacin
Servidores de Certificacin
El protocolo SSL (URL con https)
Ejemplo: Samuel enva un pedido a amazon.com cifrado con la
clave pblica de Amazon.
Samuel debe averiguar la clave pblica de Amazon.
Varias compaas actan de autoridades de certificacin, ej. Veri-
sign.Amazon genera la clave pblica Ca y la enva a Verisign.
Verisign emite un certificado para Amazon:
{Verisign, Amazon, https://www.amazon.com,Ca}
Este certificado se cifra empleando la clave privada de Verisign
que es conocida por los navegadores.
Samuel entonces hace su pedido usando este cifrado.
A continuacin, el navegador genera una clave de sesin aleatoria,
la cifra empleando la clave pblica de Amazon y la enva al servidor
de Amazon.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 29 / 58
Seguridad de Aplicaciones en Internet Firmas Digitales
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 30 / 58
Seguridad de Aplicaciones en Internet Firmas Digitales
Firmas Digitales
Mtodo que permite verificar remitentes.
Ejemplo: Blanca cifra un mensaje para Emilio empleando su cla-
ve privada y luego cifra el resultado utilizando la clave pblica de
Emilio.
Emilio recibe el mensaje, lo descifra usando su clave privada, y
luego descifra el resultado usando la clave pblica de Blanca.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 31 / 58
Caso Practico
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 32 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
9/15
Caso Practico
Escenario
Usamos la base de datos dbadmisiony nos enfocaremos en un
caso ficticio de implementacin de un sistema para la Universidad.
Las oficinas que trabajan con esta base de datos son:
Oficina Empleado Login Contrasea Tipo de
Usuario
Admisin
Cecilia Caldern Ccalderon Patito Jefe
Carmen Peralta Cperalta Perr ito Jefe
Yovana Vargas Yvargas Muchachita Operador
Liz Castro Lcastro Michimichi Operador
Marketing Katty Suarez Ksuarez Loquita Jefe
Mar tha Carrion Mcarrion Love Operador
Se tiene desarrollada una aplicacin que accede a la base de da-
tos con el usuario y contrasea concedidos a cada uno de los
usuarios (Autenticacin SQL).
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 33 / 58
Caso Practico
Escenario
Con respecto a la aplicacin la oficina de Admisin est solicitando que
se implemente:
Un reporte que dado un determinado periodo acadmico ( IDPerAcad)liste: apellidos y nombres, carrera y modalidad de todos los postulantes
de ese periodo.Un reporte que dada un determinado periodo acadmico ( IDPerAcad)liste: apellidos y nombres, carrera y modalidad de todos los ingresantes
de ese periodo.
Un reporte que dada un determinado periodo acadmico ( IDPerAcad)liste: apellidos y nombres, carrera y modalidad de todos los postulantesque no asistieron al examen de ese periodo.
Los 10 mejores puestos por periodo (IDPerAcad).
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 34 / 58
Caso Practico
Escenario
As mismo los de la oficina de Marketing necesitan que se implemente
lo siguiente:
Un reporte que muestre la cantidad de postulantes por cada perio-do (IDPerAcad)
Un reporte que muestre la cantidad de postulantes por cada pe-
riodo (IDPerAcad), este reporte debe de mostrar las columnas:
Periodo, varn, mujer y total.
Un reporte que liste los apellidos y Nombres de todos aquellos que
cumplan aos, este reporte debe de funcionar en funcin a la fecha
actual.
Listado de aquellos contactos que nunca ingresaron.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 35 / 58
Caso Practico
Escenario
Los usuarios operadores de la oficina de Admisin solo pueden:
Seleccionar, Actualizar e Insertar Contactos y Postulante.
De los postulantes solo pueden tener acceso de seleccin en los
campos Puntaje, AsistioExamen e Ingreso.
Acceso a los requerimientos previamente solicitados.
Los usuarios operadores y jefes de Marketing:
Acceso a los requerimientos recientemente solicitados.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 36 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
10/15
Caso Practico
Escenario
Los Usuarios jefes de la oficina de Admisin 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.
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 ms flexible posible.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 37 / 58
Caso Practico Desarrollo
Contenido
1 Lenguaje de Control de DatosGRANTREVOKE
2 Control Obligatorio de Acceso
3 Seguridad de Aplicaciones en InternetCifradoServidores de CertificacinFirmas Digitales
4 Caso PracticoDesarrollo
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 38 / 58
Caso Practico Desarrollo
Creacin de Roles
CREATE ROLE JefeADM;
CREATE ROLE OpeADM;
CREATE ROLE JefeMKT;
CREATE ROLE OpeMKT;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 39 / 58
Caso Practico Desarrollo
Creacin de Logins
CREATE ROLE Ccalderon
WITH LOGIN PASSWORD ' P a t i t o '
VALID UNTIL '20200101 '
IN ROLE JefeADM;
CREATE ROLE Cperalta
WITH LOGIN PASSWORD ' P e r r i t o '
VALID UNTIL '20200101 '
IN ROLE JefeADM;
CREATE ROLE Yvargas
WITH LOGIN PASSWORD ' M uc hac hit a '
VALID UNTIL '20200101 '
IN ROLE OpeADM;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 40 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
11/15
Caso Practico Desarrollo
Creacin de Logins
CREATE ROLE Lcastro
WITH LOGIN PASSWORD ' M i c h i m i c h i '
VALID UNTIL '20200101 '
IN ROLE OpeADM;
CREATE ROLE Ksuarez
WITH LOGIN PASSWORD ' Loqui t a '
VALID UNTIL '20200101 '
IN ROLE JefeMKT;
CREATE ROLE Mcarrion
WITH LOGIN PASSWORD ' Love '
VALID UNTIL '20200101 '
IN ROLE OpeMKT;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 41 / 58
Caso Practico Desarrollo
Creando Esquemas
CREATE SCHEMA REQ_ADM;CREATE SCHEMA REQ_MKT;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 42 / 58
Caso Practico Desarrollo
A los Operadores de ADMSeleccionar, 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;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 43 / 58
Caso Practico Desarrollo
A los Operadores de ADM
De los postulantes solo debe tener acceso de seleccin sobre
Puntaje, AsistioExamen e Ingreso
REVOKE SELECT ON Persona. Postulante FROM OpeADM;Quitamos permiso de SELECT sobre Pos tul ant e
GRANT SELECT ( AsistioExamen) ON Persona. Postulante
TO OpeADM;
GRANT SELECT ( Ingreso) ON Persona. Postulante TO
OpeADM;
GRANT SELECT ( Puntaje) ON Persona. Postulante TO
OpeADM;
Acceso a los requerimientos previamente solicitados
GRANT USAGE ON SCHEMA REQ_ADM TO OpeADM;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 44 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
12/15
-
7/23/2019 Unidad7_ControldeDatos
13/15
Caso Practico Desarrollo
Comprobando Derechos de los Operadores de ADM
Seleccionar, Actualiza e Insertar Contactos y Postulantes
SELECT * FROM Persona. Contacto;
INSERT INTO Persona. Contacto VALUES( , 'Anonymous ' , '
Anonymous ' , 'Anonymous ' , 0, 00000000,' 10/11/2014 ' ,
now () ) ;
UPDATE Persona. Contacto SET
nombres='Anonimo ' , paterno='Anonimo ' , materno='
Anonimo '
WHERE IDCOntacto=' 176 ';
DELETE FROM Persona. Contacto WHERE IDCOntacto=' 176
' ;
E l r o l OpeADM no t i e n e p e rm i so p a ra e l i m i n a r .
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 49 / 58
Caso Practico Desarrollo
Comprobando Derechos de los Operadores de ADM
Para los postulantes solo debe tener acceso de seleccin sobre
Puntaje, AsistioExamen e Ingreso
SELECT IDPostulante, IDContacto, IDCarrera,
IDModalidad
FROM Persona. Postulante;
SELECT Puntaje, AsistioExamen, Ingreso FROM Persona.
Postulante;
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 ) ;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 50 / 58
Caso Practico Desarrollo
Comprobando Derechos de los Jefes de ADM
SET SESSION AUTHORIZATION JefeADM;
Seleccionar y Eliminar Postulantes
SELECT * FROM Persona. Postulante;
DELETE FROM Persona. Postulante WHERE IDContacto='
175 ';
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
t i e n e p e r sm i so
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 51 / 58
Caso Practico Desarrollo
Comprobando Derechos de los Jefes de ADM
Seleccionar Contactos
SELECT * FROM Persona. Contacto;
SELECT * FROM Persona.Contacto; Seleccionar, Insertar, Eliminar yActuzalizar Carreras, Modalidades y Periodos
SELECT * FROM Admision. Carrera;
SELECT * FROM Admision. Modalidad;
SELECT * FROM Admision. PerAcad;
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 ) ;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 52 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
14/15
Caso Practico Desarrollo
Comprobando Derechos de Jefes de MKT
SET SESSION AUTHORIZATION OpeMKT;
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 ) ;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 53 / 58
Caso Practico Desarrollo
Comprobando Derechos de los Operadores de MKT
SET SESSION AUTHORIZATION JefeMKT;
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 ) ;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 54 / 58
Caso Practico Desarrollo
Es importante sealar que las verificaciones se han realizado nica-
mente 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 Yvargaspertenece al grupo
OpeADM.
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 55 / 58
Caso Practico Desarrollo
Reestrablecer y/o eliminar los objetos
Si queremos eliminar algunos objetos o incluso la base de datos no
debe existir ningn rol asociado a este
RESET SESSION AUTHORIZATION;
Regresamos a l u s u a r i o p r i n c i p a l p o r d e f e c to .
SELECT SESSION_USER, CURRENT_USER;
DROP DATABASE dbadmision;
ERROR : dat abas e " dbadm is ion" is being ac c es s ed
by o t h er u se rs
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 56 / 58
http://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdfhttp://www.twitter.com/juaramir.pdf -
7/23/2019 Unidad7_ControldeDatos
15/15
Caso Practico Desarrollo
Reestrablecer y/o eliminar los objetos
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;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 57 / 58
Caso Practico Desarrollo
Reestrablecer y/o eliminar los objetos
Para la eliminacin de la base de datos habr que confirmar adems de
que no exista ninguna conexin abierta de la base de datos por algnusuario.
SELECT * FROM pg_stat_activity;
Juan Jos Ramrez Lama (UBB) Control de Datos 2015-1 - @juaramir 58 / 58