🔐 Tutorial: Bloqueo de Usuarios Genéricos por S.O. al Esquema BOTICARIO en BOTICA_DB

🎯 Objetivo

Este tutorial documenta cómo implementar una política de seguridad en Oracle que bloquea conexiones al esquema BOTICARIO basándose en el nombre del usuario del sistema operativo (OS_USER).

Incluye:

• Tablas de control y auditoría

• Trigger de conexión

• Procedimientos administrativos

• Paquete PL/SQL centralizado para gestión

💡 Ideal para entornos donde se requiere seguridad granular y trazabilidad de accesos no autorizados.

---

1️⃣ Preparación del Entorno

👤 Crear el esquema de seguridad

CREATE USER SEGURIDAD IDENTIFIED BY tu_contraseña_segura;
GRANT CONNECT, RESOURCE TO SEGURIDAD;
ALTER USER SEGURIDAD QUOTA UNLIMITED ON USERS;

---

2️⃣ Crear tabla de usuarios bloqueados

CREATE TABLE SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO (
  OS_USERNAME VARCHAR2(30) PRIMARY KEY
)
TABLESPACE USERS;

---

3️⃣ Crear tabla de auditoría de accesos

CREATE TABLE SEGURIDAD.LOG_ACCESOS_BLOQUEADOS (
  OS_USERNAME VARCHAR2(30),
  USERNAME    VARCHAR2(30),
  FECHA       DATE DEFAULT SYSDATE
)
TABLESPACE USERS;

---

4️⃣ Otorgar permisos al trigger

GRANT SELECT ON SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO TO SISTEMA;

---

5️⃣ Crear trigger de LOGON a nivel de base de datos

CREATE OR REPLACE TRIGGER SISTEMA.TRG_BLOQUEO_USUARIOS_BOTICARIO
AFTER LOGON ON DATABASE
DECLARE
  v_osuser   VARCHAR2(30);
  v_username VARCHAR2(30);
  v_count    NUMBER := 0;
BEGIN
  v_osuser   := SYS_CONTEXT('USERENV', 'OS_USER');
  v_username := SYS_CONTEXT('USERENV', 'SESSION_USER');

  IF UPPER(v_username) = 'BOTICARIO' THEN
    BEGIN
      SELECT COUNT(*) INTO v_count
      FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO
      WHERE LOWER(OS_USERNAME) = LOWER(v_osuser);
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;

    IF v_count > 0 THEN
      BEGIN
        INSERT INTO SEGURIDAD.LOG_ACCESOS_BLOQUEADOS (OS_USERNAME, USERNAME)
        VALUES (v_osuser, v_username);
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN NULL;
      END;

      RAISE_APPLICATION_ERROR(-20001, 'Acceso denegado para usuario con esquema BOTICARIO');
    END IF;
  END IF;
END;
/

---

6️⃣ Insertar usuarios del S.O. a bloquear

✅ Inserción directa

INSERT INTO SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO (OS_USERNAME)
VALUES ('DOCTORCHAPATIN');
COMMIT;

🛡️ Inserción condicional

DECLARE
    v_count PLS_INTEGER;
BEGIN
    FOR usr IN (
        SELECT 'DOCTORCHAPATIN' AS username FROM DUAL 
    ) LOOP
        SELECT COUNT(*) INTO v_count
        FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO
        WHERE OS_USERNAME = usr.username;

        IF v_count = 0 THEN
            INSERT INTO SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO (OS_USERNAME)
            VALUES (usr.username);
        END IF;
    END LOOP;
END;
/

---

7️⃣ Eliminar usuarios bloqueados

DELETE FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO
WHERE OS_USERNAME = 'DOCTORCHAPATIN';
COMMIT;

---

8️⃣ Limpiar tabla de auditoría

Opción 1: Borrado con conservación de espacio

DELETE FROM SEGURIDAD.LOG_ACCESOS_BLOQUEADOS;
COMMIT;

Opción 2: Truncado (más rápido)

TRUNCATE TABLE SEGURIDAD.LOG_ACCESOS_BLOQUEADOS;

---

9️⃣ Consultar registros

Usuarios bloqueados

SELECT * FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO ORDER BY OS_USERNAME;

Auditoría de accesos denegados

SELECT * FROM SEGURIDAD.LOG_ACCESOS_BLOQUEADOS ORDER BY FECHA DESC;

---

🔁 Rollback completo (Reversión)

-- Eliminar trigger
DROP TRIGGER SISTEMA.TRG_BLOQUEO_USUARIOS_BOTICARIO;

-- Eliminar tablas
DROP TABLE SEGURIDAD.LOG_ACCESOS_BLOQUEADOS PURGE;
DROP TABLE SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO PURGE;

-- Eliminar usuario
DROP USER SEGURIDAD CASCADE;

---

🎁 BONUS: Paquete PL/SQL para Gestión de Usuarios

CREATE OR REPLACE PACKAGE SEGURIDAD.PKG_GESTION_BLOQUEOS AS
  PROCEDURE AGREGAR_USUARIO(p_os_user VARCHAR2);
  PROCEDURE ELIMINAR_USUARIO(p_os_user VARCHAR2);
  FUNCTION ESTA_BLOQUEADO(p_os_user VARCHAR2) RETURN BOOLEAN;
END;
/

CREATE OR REPLACE PACKAGE BODY SEGURIDAD.PKG_GESTION_BLOQUEOS AS

  PROCEDURE AGREGAR_USUARIO(p_os_user VARCHAR2) IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count
    FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO
    WHERE OS_USERNAME = UPPER(p_os_user);

    IF v_count = 0 THEN
      INSERT INTO SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO (OS_USERNAME)
      VALUES (UPPER(p_os_user));
      COMMIT;
    END IF;
  END;

  PROCEDURE ELIMINAR_USUARIO(p_os_user VARCHAR2) IS
  BEGIN
    DELETE FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO
    WHERE OS_USERNAME = UPPER(p_os_user);
    COMMIT;
  END;

  FUNCTION ESTA_BLOQUEADO(p_os_user VARCHAR2) RETURN BOOLEAN IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count
    FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO
    WHERE OS_USERNAME = UPPER(p_os_user);
    RETURN v_count > 0;
  END;

END;
/

✅ Con este paquete puedes hacer llamadas como:

EXEC SEGURIDAD.PKG_GESTION_BLOQUEOS.AGREGAR_USUARIO('USUARIOX');

---

⚠️ Notas Finales

• El trigger impide el acceso a cualquier sesión que intente conectarse como BOTICARIO si el OS_USER está registrado como bloqueado.

• Todo intento denegado es auditable, quedando trazado en LOG_ACCESOS_BLOQUEADOS.

• La solución es reversible y extensible: puedes integrarla con alertas, jobs automáticos, etc.

---

🧠 Conclusión

Este procedimiento permite a administradores Oracle mantener control granular, auditable y automatizable sobre accesos al esquema BOTICARIO. Una medida esencial en entornos donde la trazabilidad y el cumplimiento de políticas de seguridad son clave.

---

📡 ¿Te gustó esta receta de monitoreo técnico?
Explora más artículos, diagnósticos y herramientas en https://laboticadeldba.com, el espacio donde los colegas DBAs encuentran materia alquímica para hacer oro con sus entornos Oracle y resguardar su seguridad.

🧪 Comparte este artículo con tus colegas y no dejes tu infraestructura sin receta.
En La Botica del DBA te ofrecemos los mejores elixires para el rendimiento, la seguridad y la gobernabilidad de tus datos, preparados con experiencia, precisión y pasión por la tecnología.

💊 Porque todo buen sistema merece una buena dosis de monitoreo inteligente, automatización efectiva y control preventivo.

🔍 Desde un trigger bien afinado hasta un tuning quirúrgico, aquí destilamos soluciones reales para retos reales.
Síguenos, experimenta y comparte — y que nunca falte botica en tu administración de bases de datos diaria.

---