Seguridad Técnica de
Bases de Datos

-- 1. Función de predicado (Inline TVF)
--    Devuelve 1 (permitido) si el médico en sesión atiende al paciente
CREATE FUNCTION dbo.fn_FiltroMedicoTratante(@MedicoID INT)
  RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN SELECT 1 AS resultado
  WHERE
    -- Médico autenticado en la aplicación (via SESSION_CONTEXT)
    @MedicoID = CAST(SESSION_CONTEXT(N'MedicoID') AS INT)
    -- O usuario con rol administrativo explícito
    OR IS_ROLEMEMBER('rol_jefatura_medica') = 1
    OR IS_ROLEMEMBER('db_owner') = 1;
GO

-- 2. Crear la Security Policy asociando la función a la tabla
CREATE SECURITY POLICY dbo.Policy_PacientesPorMedico
  ADD FILTER PREDICATE  dbo.fn_FiltroMedicoTratante(MedicoID)
    ON dbo.Pacientes,
  ADD BLOCK PREDICATE   dbo.fn_FiltroMedicoTratante(MedicoID)
    ON dbo.Pacientes AFTER INSERT
  WITH (STATE = ON);

-- 3. La aplicación establece el contexto de sesión al autenticar
EXEC sys.sp_set_session_context 'MedicoID', @id_medico_autenticado;

-- Función que retorna el predicado WHERE dinámico
CREATE OR REPLACE FUNCTION fn_vpd_medico(
  p_schema VARCHAR2, p_object VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
  RETURN 'MEDICO_ID = SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'')';
END;
/

-- Asociar la política VPD a la tabla
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'SALUD',
    object_name     => 'PACIENTES',
    policy_name     => 'POL_PACIENTE_MEDICO',
    function_schema => 'SALUD',
    policy_function => 'FN_VPD_MEDICO',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE',
    policy_type     => DBMS_RLS.CONTEXT_SENSITIVE
  );
END;