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.
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
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.