Permisos en SQL Dinámico

Permisos en SQL Dinámico

El otro día en un grupo de Facebook un participante hizo una pregunta relacionada a un caso en donde tenia un Procedimiento Almacenado con SQL Dinámico y que al ejecutarlo le daba errores de permisos sobre los objetos que usaba ese procedimiento.

Esto técnicamente se da porque el SQL Dinámico pasa las credenciales del contexto de ejecución del SP el cual es Caller por defecto, para mas información puedes acceder al siguiente enlace

En este post voy a hacer una demostración paso a paso de este caso y cual seria la forma de resolverlo.

Paso 1: Creamos los logins y usuarios en nuestra base de datos

En este primer paso vamos a crear dos (2) logins y usuarios a nuestra base de datos, uno de ellos con permisos restringidos y el otro con mas permisos.

USE AdventureWorks2019 
GO
-- CREAMOS UN NUEVO LOGIN
CREATE LOGIN TRIGGERDB WITH PASSWORD ='PASSW@RD'
GO
CREATE LOGIN TRIGGERDB_FULL WITH PASSWORD ='PASSW@RD'
GO
-- CREAMOS EL USUARIO EN LA BASE DE DATOS
CREATE USER TRIGGERDB 
GO
CREATE USER TRIGGERDB_FULL 
GO

ALTER ROLE [db_owner] ADD MEMBER [TRIGGERDB_FULL]  -- PERMISO DE DBOWNER
GO

Paso 2: Creación de Procedimientos almacenados

En este paso vamos a crear dos procedimientos almacenados , uno de ellos común y el otro usando SQL Dinámico

Luego al login TRIGGERDB le vamos a dar permisos de ejecución sobre los SP pero no así sobre las tablas

CREATE OR ALTER PROC DBO.USP_CUSTOMER AS
 SELECT * FROM SALES.Customer 
GO
-- CREAMOS OTRO STORE QUE CONSUME LA TABLA PERO CON SQLDINAMICO
CREATE OR ALTER PROC DBO.USP_CUSTOMER_DINAMICO AS
 DECLARE @N NVARCHAR(50)
 SET @N = N'SELECT * FROM SALES.Customer'
 EXECUTE SP_EXECUTESQL @N 
GO
-- TRIGGERDB SOLO TIENE PERMISOS A LOS STORES
GRANT EXECUTE ON DBO.USP_CUSTOMER TO TRIGGERDB
GRANT EXECUTE ON DBO.USP_CUSTOMER_DINAMICO TO TRIGGERDB 
GO

Paso 3: Pruebas de funcionalidad

Ahora probaremos los dos procedimientos almacenados usando el login TRIGGERDB y además los permisos

EXECUTE AS LOGIN = 'TRIGGERDB'
SELECT SUSER_SNAME()

Hacemos un Select a la tabla con Triggerdb

SELECT * FROM SALES.Customer 

Ejecutamos el primer procedimiento normal y vemos que funciona porque el login tiene permisos.

Msg 229, Level 14, State 5, Line 78
The SELECT permission was denied on the object ‘Customer’, database ‘AdventureWorks2019’, schema ‘Sales’.

EXEC DBO.USP_CUSTOMER

Ejecutamos el otro SP con SQL Dinámico sobre la misma tabla y falla por mas que tengamos permisos sobre el mismo , al ser SQL Dinámico se pasan las credenciales del Caller (TRIGGERDB) al objeto que usa el SP_executeSQL y como este login no tiene permisos sobre dicha tabla va a dar error


EXEC DBO.USP_CUSTOMER_DINAMICO

Msg 229, Level 14, State 5, Line 78
The SELECT permission was denied on the object ‘Customer’, database ‘AdventureWorks2019’, schema ‘Sales’.

REVERT 

Paso 4: Resolviendo el problema

Para poder resolver estos problemas sin la necesidad de darle permisos al Login TRIGGERDB sobre la tabla al usar SQL Dinámico lo que debemos hacer es cambiar el contexto de ejecución del SP de caller a Owner o Login , yo lo voy a cambiar indicándole un login que si tiene permisos sobre la tabla por mas que luego lo llame otro que no tiene permisos.

ALTER PROC DBO.USP_CUSTOMER_DINAMICO
WITH EXECUTE AS 'TRIGGERDB_FULL' -- CAMBIO DE CONTEXTO A OWNER
AS
DECLARE @N NVARCHAR(50)
SET @N = N'SELECT * FROM SALES.Customer'
EXECUTE SP_EXECUTESQL @N 

Ahora solo nos toca volver a probar (correr el código por partes)

EXECUTE AS LOGIN = 'TRIGGERDB'
SELECT SUSER_SNAME()

SELECT * FROM SALES.Customer  -- FALLA PORQUE NO TENEMOS PERMISO

EXEC DBO.USP_CUSTOMER -- FUNCIONA
EXEC DBO.USP_CUSTOMER_DINAMICO -- FUNCIONA

REVERT

Conclusiones

Si usamos SQL Dinámico y nuestro user que llama al SP no tiene permisos sobre los objetos del mismo y no queremos darlos, entonces lo que debemos hacer es cambiar el contexto de ejecución de ese SP, Función o Trigger

Permisos en SQL Dinámico

Cómo copiar logins entre instancias SQL

En nuestro trabajo diario nos toca muchas veces copiar los logins que tenemos en una instancia a la otra.

En este tutorial paso a paso te muestro las distintas metodologías que podes usar para realizar de forma efectiva esta tarea y no tener problemas.

Transfer logins and passwords between instances – SQL Server | Microsoft Docs

dbatools – the community’s sql powershell module

USE [master]
GO
CREATE LOGIN [DEMO1] WITH PASSWORD=N'123', 
DEFAULT_DATABASE=[master], 
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [AdventureWorks2019]
GO
CREATE USER [DEMO1] FOR LOGIN [DEMO1]
GO
USE [AdventureWorks2019]
GO
ALTER ROLE [db_owner] ADD MEMBER [DEMO1]
GO
Permisos en SQL Dinámico

Revisando la seguridad de tu SQL Server

Sin lugar a dudas que la seguridad es muy importante en nuestros entornos de base de datos ya sea on-prem o nube.

Ahora bien, ¿Cómo podríamos saber que tenemos configurado de forma correcta la seguridad en la instancia y bases de datos?

La primer medida es conocer las buenas prácticas de seguridad recomendadas por Microsoft para SQL Server

Pero cómo podemos revisar que nuestros servidores y bases estén de forma correcta?

Bueno para esto se ha incorporado al SSMs (SQL Server Management Studio) unos informes de auditoria que nos ayudaran a revisar toda la configuración e indicarnos las mejoras necesarias.

Este nuevo componente se llama Vulnerability assessment

En el siguiente video tutorial de mi canal de Youtube te voy a mostrar cómo se utiliza esta tremenda herramienta

Cómo encriptar bases de datos MSSQL con TDE

SQL Server desde su versión 2008 tiene la funcionalidad TDE (Transparent Data Encryption) la cual permite encriptar toda la base de datos de forma transparente.

Esta funcionalidad hasta MSSQL 2019 solo estaba disponible en la edición Enterprise pero a partir de 2019 ya se puede usar en la Standard.

En el siguiente video te muestro paso a paso como se configura TDE y funciona como así también algunos tips que seria bueno considerar.

Para poder descargar el material que utilice en el video lo podes hacer desde el siguiente link

Material TDE

Cómo evitar conexiones desde EXCEL , POWER BI u otra aplicación a MSSQL

En muchas ocasiones necesitamos controlar el acceso de nuestros usuarios a nuestro motor de bases de datos SQL Server.

Por ejemplo, que los mismos no puedan hacerlo desde EXCEL, POWER BI, Management Studio o cualquier otra aplicación que no sea la autorizada.

Para poder resolver esto vamos a usar Triggers DDL de SQL Server.

En el siguiente ejemplo te muestro como podemos controlar que un usuario de aplicación solo pueda ser accedido desde nuestro sistema y no así desde otra aplicación.

-- CREAMOS UN TRIGGER DDL LOGON 

CREATE TRIGGER [CONNECTION_LIMIT_TRIGGER]
ON ALL SERVER 
FOR LOGON
AS 
 SET NOCOUNT ON 
-- CON ESTE TRIGGER QUE EL LOGIN DEMOAPP SOLO PUEDA SER USADO DESDE NUESTRA APP
 BEGIN
 IF SUSER_SNAME() = 'DEMOAPP' AND APP_NAME() <> 'MIAPLICACION'
    ROLLBACK;
 END;
 GO 
GO 

SET QUOTED_IDENTIFIER OFF
GO 

ENABLE TRIGGER [CONNECTION_LIMIT_TRIGGER] ON ALL SERVER
GO 

Si deseas ver una explicación completa te paso el siguiente video en el cual comento como hacer esta implementación