BD2 · S8 · Usuarios, roles y privilegios¶
SGE-JD · JD Refrigeración S.A.C. · Equipo CherryTree · Yoel Wagner Chambi Sirena · 10/11/2026
Trabajo autónomo de la sesión 8: rediseñar el modelo de seguridad de la base de SGE-JD con mínimo
privilegio y comprobar que la aplicación sigue funcionando. Hoy el backend se conecta como JDREFRIGERACION,
el dueño de las tablas: puede borrar, alterar o eliminar cualquier cosa del esquema. El rediseño lo pasa al
usuario técnico JD_APP (creado en S1), que trabaja solo con roles.
Se hizo en la base de práctica, restaurada a la copia limpia de desarrollo con restaurar_practica.ps1 (237 ventas, más los objetos de S1 a S3).
| Orden | Script | Qué hace |
|---|---|---|
| 1 | S08_01_auditoria.sql (-Dba) |
Privilegios de sistema, roles, privilegios de objeto y cuota, desde el diccionario de datos |
| 2 | S08_02_roles.sql (-Dba -ClaveApp) |
Tres roles por función, JD_APP reasignado, JD_REPORTES de solo lectura y el dueño recortado |
| 3 | S08_03_limites.sql (-Usuario JD_APP y -Usuario JD_REPORTES) |
Accesos denegados a propósito |
| 4 | S08_pruebas_backend.ps1 | Las 217 pruebas del backend, como dueño y como JD_APP |
| 5 | S08_04_rol_pruebas.sql / S08_05_quitar_rol_pruebas.sql | El DELETE que solo piden las pruebas: se da y se quita |
1. Auditoría: el punto de partida¶
USUARIO PRIVILEGIOS DE SISTEMA DIRECTOS ROLES OBJETO
JDREFRIGERACION CREATE PROCEDURE, SEQUENCE, SESSION, TABLE, TRIGGER, VIEW DB_DEVELOPER_ROLE dueño de 31 tablas, 3 triggers, 2 funciones, 2 procedimientos
JD_APP CREATE SESSION — EXECUTE sobre SP_REGISTRAR_VENTA y FN_TOTAL_VENTA (S1)
DB_DEVELOPER_ROLE → RESOURCE → SODA_APP (roles anidados)
DB_DEVELOPER_ROLE: CREATE ASSERTION, CUBE, CUBE BUILD PROCESS, CUBE DIMENSION, DIMENSION, DOMAIN, JOB,
MINING MODEL, MLE, SESSION · DEBUG CONNECT SESSION · EXECUTE DYNAMIC MLE · FORCE TRANSACTION · ON COMMIT REFRESH
El problema no es JD_APP, que está casi vacío: es que el backend no usa JD_APP. Se conecta como el
dueño, que puede hacer DROP TABLE ventas, y que además recibió de la imagen de Docker un rol con 14 privilegios
que el proyecto nunca pidió (CREATE JOB, FORCE TRANSACTION, DEBUG CONNECT SESSION…).
2. Lo que la aplicación necesita de verdad¶
Inventario hecho sobre el código del backend (src/main), no sobre suposiciones:
| Hecho medido | Cómo se midió |
|---|---|
Solo 5 servicios borran filas: CategoriaService, ProductoService, ClienteService, SedeClienteService, ProveedorService |
grep de .delete(, deleteById, deleteBy… y @Modifying |
Ningún DELETE FROM ni SQL nativo en la aplicación |
grep -rn "DELETE FROM\|nativeQuery" en src/main: vacío |
Ninguna colección pierde líneas (no hay .clear() ni remove sobre detalles), así que orphanRemoval nunca borra |
grep de .clear() y removeIf |
Las líneas (DETALLE_*, ITEMS_PROFORMA, MATERIALES_ORDEN) y los registros (HISTORIAL_CAMBIOS, ENVIOS_SUNAT, INTENTOS_CORREO, AJUSTES_INVENTARIO) se insertan y no se modifican |
Revisión de sus servicios |
VENTA_AUDITORIA y LOG_ERRORES las escriben un trigger (S2) y un procedimiento (S3) con los derechos del dueño |
Se ejecutan como JDREFRIGERACION: la aplicación no necesita privilegios sobre ellas |
| Grupo de tablas | Otorgado hoy (como dueño) | Necesario | Exceso |
|---|---|---|---|
5 maestras (CATEGORIAS, PRODUCTOS, CLIENTES, SEDES_CLIENTE, PROVEEDORES) |
Todo | SELECT, INSERT, UPDATE, DELETE | DDL (ALTER, DROP, TRUNCATE) |
15 documentos que cambian de estado (VENTAS, COMPROBANTES, COBROS…) |
Todo | SELECT, INSERT, UPDATE | DELETE y DDL: se anulan, no se borran |
| 9 líneas y registros | Todo | SELECT, INSERT | UPDATE, DELETE y DDL |
VENTA_AUDITORIA, LOG_ERRORES |
Todo | Nada | Todo: la aplicación podría borrar su propio rastro |
| Sistema | 20 privilegios | CREATE SESSION |
19 |
3. Roles por función¶
CREATE ROLE rol_sgejd_maestros; -- S, I, U, D sobre las 5 tablas maestras
CREATE ROLE rol_sgejd_operacion; -- S, I, U sobre 15 documentos; S, I sobre 9 líneas y registros;
-- EXECUTE sobre SP_REGISTRAR_VENTA y FN_TOTAL_VENTA (antes directo a JD_APP)
CREATE ROLE rol_sgejd_consulta; -- solo SELECT sobre 18 tablas de negocio (sin XML, CDR ni correos)
GRANT rol_sgejd_maestros, rol_sgejd_operacion TO jd_app; -- primero los roles…
REVOKE EXECUTE ON jdrefrigeracion.sp_registrar_venta FROM jd_app; -- …después se quita lo directo
REVOKE EXECUTE ON jdrefrigeracion.fn_total_venta FROM jd_app;
CREATE USER jd_reportes …; GRANT CREATE SESSION, rol_sgejd_consulta TO jd_reportes;
REVOKE db_developer_role FROM jdrefrigeracion; -- el dueño se queda con sus 6 CREATE y su cuota
Los roles siguen las funciones del negocio de ADS S6:
- maestros: catálogo, clientes y proveedores.
- operación: vender, facturar, cobrar, comprar y atender.
- consulta: los reportes que Lisandro pueda querer sacar en Excel sin tocar nada.
El orden importa: primero se dan los roles y después se revoca lo directo, para que JD_APP nunca quede sin
acceso entre un paso y otro.
4. Accesos denegados a propósito¶
| Intento | JD_APP |
JD_REPORTES |
|---|---|---|
SELECT COUNT(*) FROM ventas |
237 ✓ | 237 ✓ |
DELETE FROM ventas WHERE id = -1 |
ORA-41900: missing DELETE privilege on "JDREFRIGERACION"."VENTAS" | ORA-41900 |
DELETE FROM detalle_venta WHERE id = -1 |
ORA-41900 | ORA-41900 |
UPDATE productos SET stock_actual = stock_actual WHERE id = -1 |
0 filas ✓ (catálogo) | ORA-41900: missing UPDATE privilege |
SELECT … FROM venta_auditoria / log_errores |
ORA-00942 (no la ve) | ORA-00942 |
CREATE TABLE prueba_s8 (id NUMBER) |
ORA-01031: insufficient privileges | ORA-01031 |
ALTER TRIGGER trg_venta_auditoria DISABLE |
ORA-01031 | ORA-01031 |
fn_total_venta(…) (motor PL/SQL de S1) |
738.68 ✓ (por el rol) | ORA-00904: invalid identifier (ni la ve) |
El intento de desactivar el trigger de auditoría es el más importante. Con el modelo anterior, quien tenía la conexión del backend podía apagar la auditoría de S2, borrar ventas y volver a encenderla sin dejar rastro. Ahora no puede hacer ninguna de las tres cosas.
5. Estado final, verificado en el diccionario¶
USUARIO ROLES (DEFAULT_ROLE = YES) PRIVILEGIOS DE SISTEMA PRIVILEGIOS DE OBJETO DIRECTOS
JD_APP ROL_SGEJD_MAESTROS, ROL_SGEJD_OPERACION CREATE SESSION 0
JD_REPORTES ROL_SGEJD_CONSULTA CREATE SESSION 0
JDREFRIGERACION — 6 (CREATE …) (dueño de su esquema)
ROL PRIVILEGIOS DE OBJETO
ROL_SGEJD_CONSULTA 18 (SELECT sobre 18 tablas)
ROL_SGEJD_MAESTROS 20 (4 acciones × 5 tablas)
ROL_SGEJD_OPERACION 65 (3 × 15 documentos + 2 × 9 líneas y registros + 2 EXECUTE)
ROL_SGEJD_PRUEBAS 8 (DELETE × 8 tablas; existe, pero ya no está otorgado a nadie)
Consultado con DBA_ROLE_PRIVS, DBA_SYS_PRIVS y DBA_TAB_PRIVS después de S08_05
(bd2/salidas/S08_01_despues.txt).
6. ¿La aplicación sigue funcionando? Las 217 pruebas del backend¶
Se corrieron las pruebas del backend (JUnit, incluidas las de integración con Oracle: ventas, anulación,
comprobantes, reemplazos, correo, cobros, mantenimientos y el flujo completo de punta a punta) contra la base de
práctica, con SUNAT simulado y el correo apagado. El backend se conectó como JD_APP, con
hibernate.default_schema = JDREFRIGERACION y cada conexión iniciada con
ALTER SESSION SET CURRENT_SCHEMA = JDREFRIGERACION.
| Corrida | Usuario | Pruebas | Fallan | Causa |
|---|---|---|---|---|
| Línea base | JDREFRIGERACION (dueño) |
217 | 2 | Trigger de S2 (ORA-20022, hallazgo 4) |
| Mínimo privilegio | JD_APP con sus 2 roles |
217 | 20 | Las mismas 2, más 18 con ORA-41900 en DELETE FROM intentos_correo (17) y DELETE FROM historial_cambios (1) |
| Con el rol temporal de pruebas | JD_APP + ROL_SGEJD_PRUEBAS |
217 | 2 | Idéntico a la línea base |
Las 18 fallas de la segunda corrida no son de la aplicación: son de la limpieza que hacen las propias pruebas
con JdbcTemplate. La aplicación no tiene ningún DELETE FROM, y las pruebas sí, en 8 tablas: COMPROBANTES,
DETALLE_VENTA, ENVIOS_CORREO, ENVIOS_SUNAT, HISTORIAL_CAMBIOS, INTENTOS_CORREO, PRODUCTOS y VENTAS.
Con esos DELETE en un rol aparte (ROL_SGEJD_PRUEBAS), el resultado es idéntico al del dueño. Ese rol se revocó
al terminar (S08_05): existe solo para correr pruebas en la base de práctica, nunca en desarrollo ni en
producción.
7. Matriz de accesos¶
| Usuario | Rol | Maestras (5) | Documentos (15) | Líneas y registros (9) | Auditoría y errores | PL/SQL de S1 | DDL |
|---|---|---|---|---|---|---|---|
JD_APP |
ROL_SGEJD_MAESTROS |
S, I, U, D | — | — | — | — | — |
JD_APP |
ROL_SGEJD_OPERACION |
— | S, I, U | S, I | — | EXECUTE | — |
JD_REPORTES |
ROL_SGEJD_CONSULTA |
S (4 de 5: sin sedes) | S (sin envíos de correo) | S (líneas) | — | — | — |
JD_APP (solo pruebas) |
ROL_SGEJD_PRUEBAS |
D en PRODUCTOS |
D en VENTAS, COMPROBANTES, ENVIOS_CORREO |
D en 4 | — | — | — |
JDREFRIGERACION |
— (dueño) | todo | todo | todo | todo | todo | CREATE TABLE, VIEW, SEQUENCE, PROCEDURE, TRIGGER |
Evidencia de cada celda: sección 4 (ORA-41900, ORA-01031, ORA-00942) y sección 6 (217 pruebas).
8. Integración con ADS y LP2¶
| BD2 | ADS | LP2 |
|---|---|---|
| Roles por función | Funciones del negocio (S6); seguridad de mínimo privilegio (atributo 2, S1) | Sin cambios de código |
JD_APP en lugar del dueño |
Contenedor Oracle (C2, S2) | Para producción: DB_USERNAME=jd_app, spring.jpa.properties.hibernate.default_schema y spring.datasource.hikari.connection-init-sql (en application-prod.properties, no por variables de entorno: hallazgo 3) |
JD_REPORTES |
Stakeholder Lisandro (S1): reportes sin riesgo | — |
| La auditoría no la ve la aplicación | Auditabilidad (S1) | El rastro de S2 no puede borrarlo quien tiene la conexión del backend |
Pendiente para la integración final:
- Aplicar
S08_02en desarrollo y producción. - Cambiar el usuario del backend.
- Mantener
ddl-auto=updatesolo en desarrollo, conectado como dueño, porqueJD_APPno puede alterar tablas. - Corregir las dos pruebas del hallazgo 4.
9. Error o hallazgo¶
Hallazgo 1: Oracle 23.26 ya no responde ORA-01031 por un privilegio de objeto. La guía espera ORA-01031 al borrar sin permiso. Oracle respondió:
DELETE FROM jdrefrigeracion.ventas WHERE id = -1
ORA-41900: missing DELETE privilege on "JDREFRIGERACION"."VENTAS"
Cuando el usuario ve la tabla (tiene SELECT) pero le falta la acción, la versión 23.26 dice exactamente qué
falta y dónde. ORA-01031 quedó para los privilegios de sistema (CREATE TABLE, ALTER TRIGGER). Y si ni
siquiera la ve, sigue siendo ORA-00942. Es la segunda vez que la versión real contradice la guía; la primera
fue ORA-00054 en S3.
Hallazgo 2: el dueño tenía un rol, no tres. En S7, SESSION_ROLES mostró DB_DEVELOPER_ROLE, RESOURCE y
SODA_APP. DBA_ROLE_PRIVS mostró que solo el primero está otorgado. Los otros vienen anidados
(DB_DEVELOPER_ROLE → RESOURCE → SODA_APP), y SESSION_ROLES lista todos los activos, incluidos los
heredados. La primera versión del script revocaba los tres por separado, y dos de esos REVOKE habrían fallado.
Basta con revocar el de arriba.
Hallazgo 3: la configuración no llegaba, y el error decía otra cosa. La primera corrida como JD_APP
falló entera con Schema validation: missing table [ajustes_inventario], y parecía un privilegio faltante. No lo
era: la variable SPRING_JPA_PROPERTIES_HIBERNATE_DEFAULT_SCHEMA nunca llegó a Hibernate. Spring convierte cada
_ en ., y la propiedad quedó como hibernate.default.schema. Con CONNECTION_INIT_SQL pasó lo mismo:
connection.init.sql, que ni siquiera se pudo asignar (BindException). Se resolvió pasando las dos con
SPRING_APPLICATION_JSON, que conserva el nombre exacto. Para producción irán en application-prod.properties.
En total hicieron falta cuatro corridas. Las tres primeras fallaron por configuración, y por eso la lección es
leer el Caused by más profundo antes de concluir que falta un privilegio.
Hallazgo 4: el trigger de S2 atrapó a dos pruebas del backend. Ya en la línea base, como dueño,
ComprobanteReemplazoIntegracionTest y VentaAnuladaSinNotaIntegracionTest fallaron así:
UPDATE ventas SET estado = 'ANULADA', fecha_anulacion = SYSDATE WHERE id = ?
ORA-20022: Para anular la venta 1104 indique el motivo (al menos 5 caracteres).
Esas pruebas simulan una venta anulada "a mano" con SQL directo, sin motivo ni usuario. Es justo lo que el trigger
de S2 se diseñó para impedir, porque cualquier script podría hacerlo. El trigger hizo su trabajo. Lo que hay que
corregir son las pruebas: agregarles motivo_anulacion y anulado_por a su UPDATE.
Hallazgo 5: el único DELETE de más lo pedían las pruebas. Con mínimo privilegio, todo lo que dejó de funcionar fue la limpieza de las pruebas (sección 6). La aplicación nunca necesitó borrar documentos, líneas, registros ni auditoría. Ese privilegio se le habría dado al usuario de producción "para que pasen las pruebas" si no se hubiera separado en un rol propio.
10. Reflexión¶
¿Por qué conceder un privilegio "por si acaso" es un riesgo aunque la aplicación nunca lo use, y cómo cambia ese riesgo con un rol por función?
Un privilegio que la aplicación no usa no le sirve al sistema, pero sí le sirve a quien consiga usar su conexión:
un error en una consulta, un script mal apuntado o una contraseña filtrada. En SGE-JD, el backend conectado como
dueño podía desactivar el trigger de auditoría, borrar ventas y volver a encenderlo. Ninguna línea de código lo
hacía, pero la base lo permitía. Con roles por función, el privilegio queda atado a una tarea con nombre. Se ve en
el diccionario quién lo tiene y por qué, y se quita de una vez a todos los que lo heredan. El caso de las pruebas lo
mostró: el DELETE de limpieza existe, pero vive en ROL_SGEJD_PRUEBAS, que solo se activa para correr pruebas
en la base de práctica, y no en el usuario que atiende a Lisandro y a Lucy todos los días.