Como administrador de bases de datos, es crucial asegurarse de que se prevengan y auditen los cambios no deseados en las tablas de SQL Server. En este artículo, discutiremos un patrón de diseño que utiliza un esquema de auditoría, una tabla de auditoría central y disparadores de tabla para lograr este objetivo.

Asegurando los datos de auditoría

Uno de los componentes clave de este patrón de diseño es el uso de esquemas de base de datos definidos por el usuario. Estos esquemas nos permiten agrupar objetos relacionados y asignar permisos al grupo. Para crear un esquema de auditoría, podemos usar el siguiente código:

-- Eliminar el esquema existente 
DROP SCHEMA IF EXISTS [audit] 
GO 

-- Agregar el esquema para fines de auditoría 
CREATE SCHEMA [audit] AUTHORIZATION [dbo] 
GO 

-- Mostrar los esquemas de la base de datos 
SELECT * FROM sys.schemas 
WHERE principal_id < 16384 
GO 

Este código crea un nuevo esquema llamado “audit” y muestra todos los esquemas definidos por el usuario en la base de datos.

Concediendo acceso mediante roles de base de datos

Otro aspecto importante de este patrón de diseño es el uso de roles de base de datos definidos por el usuario. Los roles cumplen el mismo propósito que los grupos de Active Directory y se pueden utilizar para asignar permisos a varios usuarios. Para crear un rol de base de datos, podemos usar el siguiente código:

-- Eliminar el rol existente. 
DROP ROLE IF EXISTS [hippa_role] 
GO 

-- Crear el rol de base de datos 
CREATE ROLE [hippa_role] AUTHORIZATION [dbo] 
GO 

Una vez creado el rol, podemos otorgar permisos al rol y agregar usuarios al rol utilizando el siguiente código:

-- Aplicar permisos al esquema 
GRANT INSERT ON SCHEMA::[active] TO [hippa_role] 
GRANT UPDATE ON SCHEMA::[active] TO [hippa_role] 
GRANT DELETE ON SCHEMA::[active] TO [hippa_role] 
GRANT SELECT ON SCHEMA::[active] TO [hippa_role] 
GO 

-- Agregar usuario al rol 
EXEC sp_addrolemember N'hippa_role', N'hippa_user' 
GO 

Este código otorga al rol “hippa_role” los permisos necesarios en el esquema “active” y agrega al usuario “hippa_user” al rol.

Creando una tabla de auditoría central

Para registrar todas las acciones de los usuarios en un grupo de tablas, es necesario crear una tabla de auditoría central. Esta tabla almacenará información como la fecha del cambio, el tipo de cambio, el autor del cambio, el nombre del esquema, el nombre del objeto y el registro(s) cambiado(s) como un documento XML. Podemos crear la tabla de auditoría central utilizando el siguiente código:

-- Eliminar la tabla existente 
DROP TABLE IF EXISTS [audit].[log_table_changes] 
GO 

-- Agregar la tabla 
CREATE TABLE [audit].[log_table_changes] 
( 
  [chg_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL, 
  [chg_date] [datetime] NOT NULL, 
  [chg_type] [varchar](20) NOT NULL, 
  [chg_by] [nvarchar](256) NOT NULL, 
  [app_name] [nvarchar](128) NOT NULL, 
  [host_name] [nvarchar](128) NOT NULL, 
  [schema_name] [sysname] NOT NULL, 
  [object_name] [sysname] NOT NULL, 
  [xml_recset] [xml] NULL, 
  CONSTRAINT [pk_ltc_chg_id] PRIMARY KEY CLUSTERED ([chg_id] ASC) 
); 
GO 

-- Agregar valores predeterminados para la información clave 
ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_CHG_DATE] DEFAULT (getdate()) FOR [chg_date]; 

ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_CHG_TYPE] DEFAULT ('') FOR [chg_type]; 

ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_CHG_BY] DEFAULT (coalesce(suser_sname(),'?')) FOR [chg_by]; 

ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_APP_NAME] DEFAULT (coalesce(app_name(),'?')) FOR [app_name]; 

ALTER TABLE [audit].[log_table_changes] 
  ADD CONSTRAINT [DF_LTC_HOST_NAME] DEFAULT (coalesce(host_name(),'?')) FOR [host_name]; 
GO 

Este código crea la tabla “log_table_changes” en el esquema “audit” y agrega restricciones predeterminadas para capturar información clave como la fecha del cambio, el tipo de cambio, el autor del cambio, el nombre de la aplicación y el nombre del host.

Gestionando los disparadores de tabla

Para automatizar el proceso de creación de disparadores en múltiples tablas, podemos utilizar un procedimiento almacenado llamado “manage_table_triggers”. Este procedimiento almacenado toma tres parámetros: el nombre del esquema de destino, la acción del comando (ya sea “create” o “drop”) y una bandera de verbosidad. El procedimiento almacenado compone y ejecuta dinámicamente las declaraciones Transact-SQL necesarias para crear o eliminar disparadores en el esquema especificado. El código de este procedimiento almacenado es bastante extenso y se puede encontrar en el artículo de ejemplo.

Prevención de acciones no deseadas

Además de auditar los cambios en las tablas, también podemos prevenir acciones no deseadas utilizando disparadores de tabla. Estos disparadores se pueden utilizar para deshacer la transacción ofensiva y mostrar un mensaje de error. Por ejemplo, para prevenir cambios en la tabla “doctor_info”, podemos crear un disparador utilizando el siguiente código:

-- Eliminar el disparador si existe 
IF OBJECT_ID('[active].[trg_sd_doctor_info]') IS NOT NULL 
  DROP TRIGGER [active].[trg_sd_doctor_info] 
GO 

-- Agregar disparador para prevenir cambios en los datos 
CREATE TRIGGER [active].[trg_sd_doctor_info] ON [active].[doctor_info] 
FOR INSERT, UPDATE, DELETE AS 
BEGIN 

  -- Detectar inserciones 
  IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('¡No se permiten inserciones en la tabla [active].[doctor_info]!', 15, 1); 
        RETURN; 
    END 

  -- Detectar eliminaciones 
  IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('¡No se permiten eliminaciones en la tabla [active].[doctor_info]!', 15, 1); 
        RETURN; 
    END 

  -- Detectar actualizaciones 
  IF EXISTS (select * from inserted) AND EXISTS (select * from deleted) 
    BEGIN 
        ROLLBACK TRANSACTION; 
        RAISERROR ('¡No se permiten actualizaciones en la tabla [active].[doctor_info]!', 15, 1); 
        RETURN; 
    END 

END; 
GO 

Este código crea un disparador en la tabla “doctor_info” que previene inserciones, actualizaciones y eliminaciones. Si se intenta alguna de estas acciones, el disparador deshará la transacción y mostrará un mensaje de error.

Conclusión

Al implementar este patrón de diseño, podemos prevenir de manera efectiva los cambios no deseados en las tablas de SQL Server y auditar todas las acciones de los usuarios en un grupo de tablas. El uso de esquemas de base de datos definidos por el usuario, roles de base de datos, tablas de auditoría central y disparadores de tabla proporciona una solución integral para garantizar la integridad de los datos y el cumplimiento de los requisitos normativos. Además, la capacidad de revertir acciones no deseadas utilizando los datos de registro XML capturados agrega una capa adicional de control y responsabilidad.