Restringir operaciones SQL en Management Studio

Question

Saludo a toda la comunidad.

Tengo una cuestión que hacerles para que me den un tip, de como pudiera controlar lo siguiente:

• Tengo un sistema que su base de datos esta soportado en SQL Server 2017
• y queremos controlar que los clientes a quienes se les instala nuestro sistema, no puedan realizar operaciones SQL desde el management Studio, tales como: UPDATE, y DELETE, para que estas operaciones únicamente sean por medio del sistema
• Obviamente ustedes dirán en primera instancia: simplemente a los usuarios hacerles un GRANT o REVOKE, según sea el caso, sin embargo esto nosotros no lo podemos controlar, ya que el sistema al ser comprado se instalada en la infraestructura del cliente y ellos tienen acceso a la base de datos y fácil pudieran entrar y asignar GRANT o REVOKE.

Entonces lo que buscamos es alguna encriptación o algún control, ya sea para saber si alguien modifico información desde el management studio o bien que la base de datos de plano no permita hacer UPDATE o DELETE desde el management Studio.

• ya que como sabrán, si alguien con conocimientos de SQL intenta hacer ajustes a la información y los hace incorrectamente y daña la integridad, inmediatamente nos hablan y dicen que el sistema esta haciendo los procesos mal.

Aun no tenemos nada en concreto, solo lo comentamos a ver si alguien hizo algo con respecto a esto y que nos de un norte.

de antemano muchas gracias.

Answer 1

Hola, si creas un usuario en la base de datos y asignas el perfil para que pueda realizar solo lo que deseas lo podrías controlar, y el usuario "sa" no seria permitido para la persona que administra la BD, ademas si habilitas el log de auditoria podrias revisar que hizo el usuario que accedio y que operaciones realizo tales como detele o update

Answer 2

hola que tal.

• Recuerda que nosotros no tenemos control sobre los usuarios de la base de datos, eso es administrado por el cliente y el DBA del cliente.
• Además que nuestro sistema se conecta con autenticación de windows, es decir con usuarios de dominio y las restricciones por usuario son gestionadas por el propio cliente.

Lo que yo deseo hacer o tengo la idea de hacer, es digamos usar alguna contraseña o algo que no permita hacer update, insert o delete desde el management studio o como coloquialmente se dice: por base de datos.

• digamos que cuando un usuario ejecute un update, sobre determinada tabla, un trigger valide si la instrucción SQL esta ejecutandose desde el management studio y el mismo trigger revierta la operación y no permita la actualización.
• pero si la instrucción proviene de nuestra aplicación el trigger permita dicha operación.
• ahora bien, el trigger tiene que estar bloqueado o algo así, para que no se permita que lo vayan a modificar o deshabilitar.

por otro lado, eso de habilitar el log de auditorias, como funciona?

de antemano muchas gracias.

saludos.

Answer 3

Si la BD esta instalada en servidor del cliente, la BD le pertenece al cliente.

Si deseas demostrar al cliente que ellos metieron mano y por ello el problema, entonces, usa auditoria como bien dice el colega mas arriba pero seguiras con tu problema " y si detienen la auditoria ?", o podrias adquirir un software que lea el log de transacciones ahi el cliente no podra modificar ese log.

Saludos.

Cristian.

Answer 4

que tal Cristian

Si, es correcta tu apreciación.

Ya revise las auditorias y están muy bien, pero como tu dices sigo teniendo el mismo problema...

Y es que todo radica en que hay compañías terceras que les dan soporte o mantenimiento a nuestro clientes, obviamente son compañías que no son autorizadas por nosotros, pero ellos como conocen la base de datos o se las dan que la conocen, les ejecutan updates, insert y hasta delete's por medio del management studio y si se equivocan en algo, luego el cliente cree que fue el sistema que hizo mal algo.

Es por eso que buscaba algún tip para controlar eso, al menos con las auditorias puedo saber quien fue.

de ante mano muchas gracias por tu comentario, si llego a encontrar algo, os lo compartiré.

saludos.

Answer 5

Hola nuevamente.

Si haces un trigger a nivel de servidor, puedes controlar el login y con que app se conecta y hacer rollback

Si haces trigger a nivel de BD puedes controlar los cambios de los objetos y la app

Si haces trigger a nivel de tabla puedes controlar las instrucciones y la app

Pero son varias preguntas, tales como, y tu come entraras si necesitas hacer alguna mantención ?

No debes negarle el acceso al cliente, mal que mal ellos son lso dueños.

Con que usuario se conecta la empresa externa ? es ademas sysadmin ?

Consulta EVENDATA() tiene muchos datos de los que andas buscando, como por ejemplo, usuario, app,sentencia,objetos, etc

Pero insisto ten ojo de como lo vas a usar, no te vayas a quedar fuera tu tambien y/o dejar fuera al cliente.

Seria mas sano que hagas trigger de tus tablas con los cambios realizados por todas las aplicaciones que no sean la tuya ver APP_NAME, pero insisto siendo sysadmin puedes perfectamente borrar esa historia.

Puedes leer el log de transacciones y verificar las instrucciones por ejemplo con APEX SQL u otros.

Saludos.

Cristian

Answer 6

Buen día, me sumo a la conversación dado que en mi empresa tuvimos una problemática similar y una alternativa es auditar las sentencias DML provenientes de un determinado grupo de usuarios. Pero si no es posible identificar a éstos, una buena alternativa es un Tg a nivel de server que audite las conexiones de todo aquel que se conecte desde Management Studio.. dándo la posibilidad tmb de restringir dicho ingreso.

Te dejo un query que te podría servir

CREATE TRIGGER [TR_LOGON_APP]
ON ALL SERVER 
FOR LOGON
AS
BEGIN

	DECLARE @program_name NVARCHAR(128)
	DECLARE @host_name NVARCHAR(128)
	DECLARE @login_time NVARCHAR(128)

	SET @login_time = GETDATE()
	SET @host_name = HOST_NAME()
	SET @program_name = APP_NAME()
			
	IF  ( ORIGINAL_LOGIN() LIKE ('%UsuarioEjemplo%') AND @program_name LIKE '%SQL%Management%Studio%' )
	BEGIN
		BEGIN TRY
			RAISERROR('Conexión no permitida', 16, 1);
		END TRY

		BEGIN CATCH
			ROLLBACK;

			INSERT INTO [master].[dbo].[loginAuditTable] (dloginTime, vhostname, users, comment)
			VALUES(@login_time, @host_name, ORIGINAL_LOGIN(), ERROR_MESSAGE())
		END CATCH			
	END
END;

---

Francisco Ingaramo | Microsoft Certified Professional | https://dbownerblog.wordpress.com | Votar y marcar respuestas es agradecer.

Answer 7

Hola a todos:

Me parece que obvias algún pequeño detalle en esta conversación. Una cosa es que a un dba, le realices un log de algo y no se entere, porque no se puede estar a todo y entonces pase desapercibido, y como no molesta pues bueno, pues aunque se entere un poco.....

Pero realmente si quiere entrar, va a entrar, eliminar todos los triggers y hacer lo que quiera.

Por lo que he entendido, hablaís de una/as compañías que se dedican a dar soporte de vuestro producto a clientes. No es un cliente que  toca algo, sino una compañía de soporte. Por tanto vive de esto, por tanto va a hacerlo.

Por cierto, Management studio no es la única aplicación para poder conectarse y realizar sentencias sql en una base de datos de Microsoft, aun siendo un poco más preciso no creo que sea ni tan siquiera la única que se puede realizar sentencias de un modo visual y gratis... https://cloudblogs.microsoft.com/sqlserver/2018/08/30/the-august-release-of-sql-operations-studio-is-now-available/

Personalmente opino, que si utilizan compañías de terceros para darles soporte sobre vuestro producto, sois un equipo, lo quieras o no, por tanto tenéis que trabajar como un equipo. Si se conectan a vuestra base de datos, para hacer cosas, es porque seguro que no les queda mucho más remedio, que andar trasteando, aunque alguna vez se pasen de largo.

Quizá entonces la mejor posibilidad es no censurar y buscar un sistema de rearmado de desastres....

Pero tan sólo es una opinión.

Answer 8

Gracias Cristian.

Efectivamente la base de datos es del cliente, sin embargo hay estructuras e información muy delicadas para la operación del sistema y sobre porque es información legal, oficial que no puede sufrir cambios, porque de ser así una auditoria les podría costar millones de dolares, estamos hablando de información que reportas a la autoridad inclusive el cierre de toda la empresa, es por eso que necesitamos evitar que la muevan, editen o borren.

ya logre hacer algo, aunque sabemos que cualquier DBA puede fácilmente burlar cualquier cosa que se presente, pero al menos a los usuarios principiantes se las complicamos mas.

ahorita que estaba por publicar los puntos que indicaste de trigger fueron practicamente lo que hice y se logro el objetivo hasta cierto nivel, por eso tambien voy a marcate como respuesta.

gracias.

Answer 9

Francisco gracias por tu aporte.

como le comente Cristian en la respuesta arriba, tanto tu aporte como el de Cristian fue practicamente la solución que implemente y se las comente de forma resumida:

• Trigger encriptado a nivel tabla, haciendo un rollback si la operación update, insert o delete proviene de una app diferente a la nuestra, usando el

if APP_NAME() != 'Nombre Aplicación'
begin
Rollback transation
print 'No se puede modificar por...'
end

• Posterior un trigger encriptado a nivel base de datos para auditar cambios al trigger nivel tabla, en dicho trigger se almacena información siempre y cuando algún usuario haya borrado o deshabilitado o habilitado el trigger a nivel tabla (es el insert que el buen francisco comparte arriba), esto con la intensión de bloquear el sistema y no permitir usarlo si existe un registro de la auditoria de dicho trigger y así evitar que el sistema vaya a fallar hasta corregir o revisar lo que hayan modificado.

bueno por el momento es suficiente para lo que queríamos lograr, básicamente fue poner todas las trabas posibles para evitar que en determinadas tablas hagan operaciones por fuera.

Ya que como verán un usuario con conocimientos suficientes fácil puede brincar estas trabas.

gracias a todos, si alguien en tiene una traba mas fuerte que podamos agregarla comentenla se los agradecería mucho.

Answer 10

Hola amigo.

Si tienes razón en lo que comentas un DBA facil burlara esas trabas.

aunque en nuestro caso y gracias a Dios un buen DBA si requiriera hacer un update a información tan valiosa, lo mas probable es que nos contacte primeramente a nosotros para indicar la problemática del porque quiere hacer un update y que nosotros como proveedores de la herramienta lo orientemos.

Ya si de plano realiza el update si validarlo, caerá bajo su responsabilidad y nosotros como proveedores y con las auditorias de base de datos nos respaldamos que no fue un mal procesamiento de nuestro de sistema, sino que fue por fuera.

Además nuestro sistema lo único que puede permitir por fuera son consultas a la información, pero que sea modificada por otro es un riesgo muy muy grande.

Pero es bueno tu aporte.

saludos.

Answer 11

La manera correcta es un contrato donde en los lineamientos estipules que cualquier modificación no autorizada al sistema los deja sin garantia o a un costo extra, las empresas le tienen mas miedo a eso que cualquier trigger que puedas implementar.

---

Blog: www.sqlservertoolbox.blogspot.com.mx

Answer 12

100% de acuerdo con Enrique AA ATOS

Microsoft Dynamics CRM, estipula en su contrato que si se hace CUALQUIER MODIFICACION a su bases de datos, se pierde el CERTIFICADO y por ende todo apoyo en cualquier evento.

---

IIslas Master Consultant SQL Server