Encriptar procedimientos almacenados SQL Server

Procedimientos almacenados encriptados SQL Server

En este artículo se muestran los procedimientos almacenados de una tabla de Categorías de platos en un restaurante. Es importante que la definición de los Store procedures no se puedan visualizar ya que al tener el nombre de una de las tablas de la base de datos se puede llegar a cualquiera de las tablas de la misma con el peligro de llegar a la tabla con los usuarios y lograr acceder a los sistemas.

Asegurable que se pueden encriptar

Elementos de la base de datos que se pueden encriptar, para esto se utiliza la cláusula with encryption
Vistas
Funciones definidas por el usuario
Procedimientos almacenados
Triggers

Importante:

Cree procedimientos almacenados y asignar el permiso para ejecutarlo al usuario de base de datos relacionado con el inicio de sesión asignado al usuario que utilizará el sistema.

Para mayor información sobre seguridad ver
Inicios de sesión
Usuarios de base de datos
Asignar permisos Grant

Base de datos

Para este artículo se va a crear la base de datos es CocinArte.

Create database CocinArte
go
use CocinArte
go

Tabla Categorías

Create table Categorias
(
CategoriasCodigo nchar(5),
CategoriasDescripcion nvarchar(50) not null,
CategoriasFechaRegistro Date,
CategoriasEstado nchar(1),
constraint CategoriasPK primary key (CategoriasCodigo),
constraint CategoriasDescripcionUQ Unique (CategoriasDescripcion)
)
go

Insertar las categorías de los platos.

set dateformat dmy
insert into Categorias
(CategoriasCodigo, CategoriasDescripcion,
CategoriasFechaRegistro, CategoriasEstado)
Values
(‘00001′,’Entradas’,’10/02/2019′,’A’),
(‘00002′,’Arroces’,’21/11/2018′,’A’),
(‘00003′,’Ensaladas’,’16/11/2017′,’A’),
(‘00004′,’Carnes’,’23/07/2020′,’A’),
(‘00005′,’Parrillas’,’01/10/2016′,’A’),
(‘00006′,’Bebidas’,’06/08/2010′,’A’),
(‘00007′,’Pescados y Mariscos’,’14/07/2013′,’A’)
go

El campo estado servirá para eliminar un registro únicamente cambiando el estado, el valor por defecto es «A» de Activo, al eliminar el registro cambia a «E» de eliminado.

Listado

Procedimiento para listar las Categorías no eliminadas
Este primer SP no se va a encriptar.

Create Or Alter procedure spCategoriasListadoSinBorrados
As
Select [CategoriasCodigo], [CategoriasDescripcion],
[CategoriasFechaRegistro], [CategoriasEstado]
from Categorias As C
where C.CategoriasEstado = ‘A’
go

Se puede visualizar la definición del procedimiento usando el SP del sistema sp_helptext
sp_helptext spCategoriasListadoSinBorrados
go
La imagen muestra el resultado mostrando la definición del SP

Modificar el SP y encriptarlo, para ello se utiliza la opción with encryption

Create Or Alter procedure spCategoriasListadoSinBorrados
with encryption
As
Select [CategoriasCodigo], [CategoriasDescripcion],
[CategoriasFechaRegistro], [CategoriasEstado]
from Categorias As C
where C.CategoriasEstado = ‘A’
go

Al intentar visualizar la definición del SP
sp_helptext spCategoriasListadoSinBorrados
go
Resultado: The text for object ‘spCategoriasListadoSinBorrados’ is encrypted.

Procedimiento para listar Categorías eliminadas

Create Or Alter procedure spCategoriasListadoBorrados
with encryption
As
Select [CategoriasCodigo], [CategoriasDescripcion],
[CategoriasFechaRegistro], [CategoriasEstado]
from Categorias As C
where C.CategoriasEstado = ‘E’
go

Procedimiento para insertar una Categoría

Create Or Alter procedure spCategoriasInsertarNueva
(
@CategoriasCodigo nchar(5),
@CategoriasDescripcion nvarchar(50),
@CategoriasFechaRegistro Date,
@CategoriasEstado nchar(1)
)
with encryption
As
insert into Categorias
Values
(@CategoriasCodigo, @CategoriasDescripcion,
@CategoriasFechaRegistro, @CategoriasEstado )
go

Procedimiento para actualizar una Categoría

Create Or Alter procedure spCategoriasActualiza
(
@CategoriasCodigo nchar(5),
@CategoriasDescripcion nvarchar(50),
@CategoriasFechaRegistro Date
)
with encryption
As
Update Categorias
set
CategoriasDescripcion = @CategoriasDescripcion,
CategoriasFechaRegistro = @CategoriasFechaRegistro
where CategoriasCodigo = @CategoriasCodigo
go

Procedimiento para eliminar una Categoría

Create Or Alter procedure spCategoriasElimina
(
@CategoriasCodigo nchar(5)
)
with encryption
As
Update Categorias
set
CategoriasEstado = ‘E’
where CategoriasCodigo = @CategoriasCodigo
go

Procedimiento para recupera una Categoría

Create Or Alter procedure spCategoriasRecupera
(
@CategoriasCodigo nchar(5)
)
with encryption
As
Update Categorias
set
CategoriasEstado = ‘A’
where CategoriasCodigo = @CategoriasCodigo
go