# 🔐 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

```sql
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

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

---

## 3️⃣ Crear tabla de auditoría de accesos

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

---

## 4️⃣ Otorgar permisos al trigger

```sql
GRANT SELECT ON SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO TO SISTEMA;
```

---

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

```sql
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

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

### 🛡️ Inserción condicional

```sql
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

```sql
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

```sql
DELETE FROM SEGURIDAD.LOG_ACCESOS_BLOQUEADOS;
COMMIT;
```

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

```sql
TRUNCATE TABLE SEGURIDAD.LOG_ACCESOS_BLOQUEADOS;
```

---

## 9️⃣ Consultar registros

### Usuarios bloqueados

```sql
SELECT * FROM SEGURIDAD.USUARIOS_BLOQUEADOS_BOTICARIO ORDER BY OS_USERNAME;
```

### Auditoría de accesos denegados

```sql
SELECT * FROM SEGURIDAD.LOG_ACCESOS_BLOQUEADOS ORDER BY FECHA DESC;
```

---

## 🔁 Rollback completo (Reversión)

```sql
-- 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

```sql
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:

```sql
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](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.

---