Reglas de Negocio – en T-Sql

Uno de los temas es como poner reglas de negocio en SQL Server, estoy probando usar C# y el motor BRMS, pero en los ultimos años he utilizado T-SQL y adición dinámica de SPs a los procesos.

Primer problema: como controlar que el SP que se va a ejecutar es uno autorizado?

Si escribi un SP que hace el cálculo de las comisiones de venta, necesito controlar que el SP a ejecutar sea uno autorizado y no uno que puso otra persona (con acceso a la DB). Voy a usar AdventureWorks para el ejemplo

   1:  create procedure CalcTotalSalesCommision
   2:  as begin
   3:  SELECT sales.SalesOrderHeader.SalesPersonID, sum(Sales.SalesOrderHeader.SubTotal * Sales.SalesPerson.CommissionPct) as TotalComisionVenta
   4:  FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonID
   5:  group by sales.SalesOrderHeader.SalesPersonID 
   6:  having sum(Sales.SalesOrderHeader.SubTotal * Sales.SalesPerson.CommissionPct) > 0.00 
   7:  end

Si ejecutamos la siguiente consulta

select object_id('CalcTotalSalesCommision')

obtenemos el id que usa internamente Sql Server para identificar al SP, en mi maquina obtuve: 1915153868

si cambiamos el create procedure a alter procedure, ejecutamos la modificación y volvemos a obtener el id del SP: 1915153868, para controlar un cambio, debemos obligar a que cambie el ID del SP, para hacerlo debemos eliminarlo y volverlo a crear, pero debemos obligar a que se borre y no se use la opcion alter. Para lograr este comportamiento, creemos un trigger a nivel de base de datos 

   1:  create TRIGGER ddl_trig_alterproc 
   2:  ON database 
   3:  FOR alter_procedure 
   4:  AS 
   5:  raiserror 50000 'no modificar SPs, eliminelo y vuelvalo a crear'
   6:  GO

 luego de crear este trigger, si queremos usar el comando alter procedure, obtenemos este error:

Msg 50000, Level 16, State 1, Procedure ddl_trig_alterproc, Line 6
no modificar SPs, eliminelos y vuelvalo a crear

Ahora, al hacer drop procedure y luego el create procedure, ejecutemos la consulta y obtenemos distintos valores.

Segundo Problema: aumentar SPs dinámicamente

El siguiente paso es almacenar el id en una tabla y verificar que ese id sea el mismo antes de permitir su ejecución, por propósito del artículo voy a dejar fuera la parte de criptografía, eso lo veremos en otro momento.

Creamos una tabla donde guardar el nombre del SP, su ID y el tipo

Tipo puede ser uno de los siguientes valores:

Valor Descripción
1 PRE insert
2 insert
3 POST insert
4 PRE update
5 update
6 POST update
7 PRE delete
8 delete
9 POST delete

Esto significa que todos los SP de tipo 1 (PRE insert) deben ser ejecutados ANTES de realizar el insert en una tabla, asi en un SP

Una tabla REGLA donde vamos a almacenar las reglas que vamos a utilizar

   1:  CREATE TABLE REGLA (
   2:      id int NOT NULL ,
   3:      nombre varchar (255) NULL ,
   4:      tabla varchar (255) NULL ,
   5:      tipo int NULL ,
   6:      campo varchar (255) NULL ,
   7:      orden int NULL ,
   8:      usuario char (1) NULL DEFAULT ('N') CHECK ((usuario = 'N') or (usuario = 'S')),
   9:      PRIMARY KEY 
  10:      (
  11:          id
  12:      )
  13:  )

Y en los SPs vamos a poner el siguiente código (en este ejemplo es para DELETE)

   1:  select @ttabla = '<TABLA>'
   2:  select @ttipo = 7 -- PRE DELETE
   3:   
   4:  declare cr_d_<TABLA> cursor for
   5:  select nombre from regla
   6:      where tabla = @ttabla and 
   7:      tipo = @ttipo 
   8:  order by usuario,orden
   9:   
  10:  -- Reglas PRE DELETE
  11:  open cr_d_<TABLA> 
  12:  fetch next from cr_d_<TABLA> into @spnombre
  13:  while @@fetch_status = 0
  14:  begin
  15:      exec @errno = @spnombre
  16:          <F_EXEC>
  17:      if @errno <> 0
  18:      begin
  19:          close  cr_d_<TABLA>
  20:          deallocate  cr_d_<TABLA>
  21:          rollback transaction td_<TABLA>
  22:          goto fin
  23:      end
  24:      fetch next from cr_d_<TABLA> into @spnombre
  25:  end
  26:  close  cr_d_<TABLA>
  27:   
  28:  -- Ejecucion de la Accion
  29:  <ACCION>
  30:  if @@error <> 0
  31:  begin
  32:      raiserror 56001 'Error al eliminar de  "<TABLA>"'
  33:      close  cr_d_<TABLA>
  34:      deallocate  cr_d_<TABLA>
  35:      rollback transaction td_<TABLA>
  36:      goto fin
  37:  end
  38:   
  39:  -- Reglas POST DELETE
  40:  select @ttipo = 9 -- POST DELETE
  41:  open cr_d_<TABLA>
  42:  fetch next from cr_d_<TABLA> into @spnombre
  43:  while @@fetch_status = 0
  44:  begin
  45:      exec @errno = @spnombre 
  46:          <F_EXEC>
  47:      if @errno <> 0
  48:      begin
  49:          close  cr_d_<TABLA>
  50:          deallocate  cr_d_<TABLA>
  51:          rollback transaction td_<TABLA>
  52:          goto fin
  53:      end
  54:      fetch next from cr_d_<TABLA> into @spnombre
  55:  end
  56:  close  cr_d_<TABLA>
  57:  deallocate  cr_d_<TABLA>

En la línea 29 debe estar el código que realmente necesitamos para eliminar el registro que nos interesa.

Lo importante es que seleccionamos los SPs que nos interesan ejecutar ANTES y DESPUES de la acción requerida pero de una forma declarativa, podemos aumentar o cambiar la lista de procedimientos que deben ser utilizados. Este código puede generado usando CodeSmith o T4, tengo que actualizar las templetas y el código para usar las nuevas opciones que nos provee SQL Server 2008 R2.

Todos los campos que son argumentos del SP principal deben ser argumentos de los SPs que se usaran como regla, la templeta simplemente usa todos los campos que tiene la tabla.

Para el ERP vamos a modificar este código para tener reglas a nivel de Instancia, Vertical, Generales y por grupo de usuarios o un usuario en especial.

En el siguiente artículo veremos el problema 3 – como hacer que un usuario NO pueda ingresar, modificar o eliminar registros sin usar las reglas de negocio.

2 thoughts on “Reglas de Negocio – en T-Sql

  1. En T-Sql no es muy recomendable utilizar cursores se recomienda mas el uso de tablas temporales. He seguido todos tus publicaciones del ERP, me parece super interesante tu esfuerzo.
    Te adjuto algo de codigo para que te des una idea, no es codigo perfecto ni mucho menos, es solo para que te orientes.

    SELECT id = IDENTITY( INT,1,1 ), *
    INTO #Tmp
    FROM ( SELECT codcomunidad ,
    descripcion
    FROM dbo.Comunidad
    ) MisDatos

    DECLARE @ptr AS INT = 1
    DECLARE @numreg AS INT = 0
    DECLARE @Nombre AS VARCHAR(50)
    SET @numreg = ( SELECT COUNT(*)
    FROM #Tmp
    )

    WHILE @ptr <= @numreg BEGIN SELECT @Nombre = descripcion FROM #Tmp WHERE id = @ptr ; SELECT @Nombre SET @ptr = @ptr + 1 ; END DROP TABLE #tmp

  2. Una pregunta ¿Todo esto es para que un usuario “avispado” no pueda insertar, actualizar o eliminar de tablas que intervienen en la lógica de negocio de la aplicación? Si asumieramos que el usuario no va a ser mal-intencionado, ¿Seguirías soportando las reglas de negocio a nivel de T-SQL o lo pasarías a código C#? Lo digo porque según entiendo yo la lógica de negocio, moverla toda a T-SQL me parece que al final siempre te quedes corto y hechas de menos tu código compilado… aunque claro, ya puestos se pueden hacer llamadas a ensamblados también desde T-SQL…
    Un saludo y sigo tus posts sobre el tema.

Leave a Reply

Your email address will not be published. Required fields are marked *