Ya hablamos de como mantener bien nuestros logins, ahora lo que falta es controlar que todo funcione como queremos. La auditoria es una de las partes mas importantes a la hora de administrar la seguridad.
Hay varias cosas que podemos auditar pero principalmente quisiera hacer foco en dos:
1) Auditoria SQL
2) Logon triggers
En la auditoria podemos definir los eventos que queremos que se controlen. Esto en la versión 2008 se ha echo muy sencillo, con solo un par de clicks podemos hacerlo. Si en cambio nuestra base es SQL 2005 deberemos trabajar un poco mas y habilitar el service broker (ENABLE_BROKER) y tenemos varios eventos que esta bueno monitorear y loguear en una tabla:
AUDIT_LOGIN_CHANGE_PROPERTY_EVENT
AUDIT_LOGIN_FAILED
AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT
AUDIT_ADDLOGIN_EVENT
Estos eventos nos permitiran monitorear si se han creado nuevos logins o se le asignaron permisos a los ya existentes. Seguro se estarán preguntando "y no voy a auditar si se borraron loggins?" el evento cuando se borra un login es "AUDIT_ADDLOGIN_EVENT" pero en el xml del evento el subclass es 2 en lugar de 1.
Lo otro que podemos hacer interesante es definir logon tringgers y aquí podemos dejar volar nuestra imaginación. Entre las ideas que se pueden implementar estaría:
- Definir que un login pueda conectarse solamente desde un host en particular
- Definir que un login pueda tener N ocurrencias en el día o simultaneas
- Guardar en una tabla los horarios de login del usuario
- No permitir que dos usuarios se conecten de forma simultanea
y esas son solo algunas ideas, cada uno puede adaptarlas según las necesidades de su empresa
viernes, 13 de agosto de 2010
miércoles, 4 de agosto de 2010
Todo sobre logins (parte 1)
Uno de los temas mas importantes en las bases de datos es la seguridad, y obviamente ésto viene de la mano de los logins.
En este articulo intentaré transmitir lo que para mi son las buenas prácticas al respecto, tanto sea para administradores principiantes como no.
Seguridad integrada
Para empezar recordemos que en SQL contamos con dos tipos de autentificación: integrada con windows o manejada por SQL. Aquí ya tenemos una de las primeras buenas prácticas, cuando sea posible es bueno definir los logins para utilizar seguridad integrada, esto permite que nuestra seguridad sea manejada de forma aislada. Nuestro segundo paso para asegurar la base sería que nuestros logins no sean usuarios de windows, sino usuarios de red, de forma deseable manejados desde AD. De esta forma si queremos denegar el acceso a un usuario de todas nuestras instancias, podemos hacerlo simplemente denegando el acceso desde AD. El tercer y último paso en esta linea de pensamiento es no definir al usuario de AD como login de nuestro SQL, sino definir un grupo de AD y en el SQL definir como login a dicho grupo. La administración de dar o quitar permisos ahora pasa simplemente a ser agregar o quitar una persona al grupo de AD.
Este modelo es muy seguro y tiene varios puntos fuertes:
1) Puedo dar o quitar permisos a un usuario en varias instancias SQL de forma inmediata.
2) Si otra persona debe tener los mismos permisos, no requiere ningún esfuerzo. Nota: es común que varias personas cumplan el mismo rol y por ende tengan los mismos permisos.
3) Es facil garantizar que todos los usuarios cumplan los mismos requisitos de seguridad de contraseñas (caducidad, complejidad, etc).
Este a mi ver es el mejor que se puede plantear, pero como siempre hay detalles a tener en cuenta. Tal es el caso si tengo varias instancias SQL y un grupo con permisos sobre diferentes bases en estos. Supongamos que ingresa una nueva persona que debe tener todos los mismos permisos SALVO que no debe poder acceder a una tabla que el resto si. Una opción poco práctica sería definir un nuevo grupo y replicar todos los permisos, pero es dificil de mantener visto que si agrego un nuevo permiso debo recordar agregarlo en ambos grupos. Otra opción, a mi ver mucho mas práctica, sería definir en mi base no solo al grupo sino también al login particular, y a éste denegarle el permiso a dicha tabla. De esta forma heredará los permisos del grupo pero le será denegado el acceso a la tabla.
Seguridad SQL
El esquema visto anteriormente es el ideal, pero es un echo que no siempre puedo adaptarme a éste modelo y a veces no hay mas remedio que utilizar seguridad SQL. En estos casos es una buena práctica que los logins deban cumplir con cierta complejidad de contraseña, así como también definir la caducidad. Recuerden tener esto en cuenta porque son dos clicks al dar de alta y nos dejará dormir mucho mas tranquilos. Pero es una tarea humana realizar esta alta en general, y como tal puede fallar. Por eso es importante corroborar que fehacientemente los logins cumplan con nuestros criterios de seguridad establecidos. En SQL 2008 podemos utilizar policys (http://msdn.microsoft.com/en-us/library/bb510667.aspx) que nos permiten hacer dos cosas en este contexto:
- Tener un listado periodico de todos los logins que no cumplan con determinados criterios
- Prohibir la creación de un login si no cumple los criterios
Esto es muy comodo porque nos evita los errores humanos, aunque en algunos contextos puede ser demasiado restrictivo.
Certificados
Por último una buena forma de tener acotados los permisos que les damos a los usuarios es ponerles una fecha de caducidad si sabemos que la tarea que van a realizar es solamente por un tiempo acotado. En estos casos podemos definir un certificado en la master que tenga un expiration date y asociar el login a dicho certificado. De ésta forma una vez vencido el certificado el usuario no podrá loguearse al sistema.
Saludos!
En este articulo intentaré transmitir lo que para mi son las buenas prácticas al respecto, tanto sea para administradores principiantes como no.
Seguridad integrada
Para empezar recordemos que en SQL contamos con dos tipos de autentificación: integrada con windows o manejada por SQL. Aquí ya tenemos una de las primeras buenas prácticas, cuando sea posible es bueno definir los logins para utilizar seguridad integrada, esto permite que nuestra seguridad sea manejada de forma aislada. Nuestro segundo paso para asegurar la base sería que nuestros logins no sean usuarios de windows, sino usuarios de red, de forma deseable manejados desde AD. De esta forma si queremos denegar el acceso a un usuario de todas nuestras instancias, podemos hacerlo simplemente denegando el acceso desde AD. El tercer y último paso en esta linea de pensamiento es no definir al usuario de AD como login de nuestro SQL, sino definir un grupo de AD y en el SQL definir como login a dicho grupo. La administración de dar o quitar permisos ahora pasa simplemente a ser agregar o quitar una persona al grupo de AD.
Este modelo es muy seguro y tiene varios puntos fuertes:
1) Puedo dar o quitar permisos a un usuario en varias instancias SQL de forma inmediata.
2) Si otra persona debe tener los mismos permisos, no requiere ningún esfuerzo. Nota: es común que varias personas cumplan el mismo rol y por ende tengan los mismos permisos.
3) Es facil garantizar que todos los usuarios cumplan los mismos requisitos de seguridad de contraseñas (caducidad, complejidad, etc).
Este a mi ver es el mejor que se puede plantear, pero como siempre hay detalles a tener en cuenta. Tal es el caso si tengo varias instancias SQL y un grupo con permisos sobre diferentes bases en estos. Supongamos que ingresa una nueva persona que debe tener todos los mismos permisos SALVO que no debe poder acceder a una tabla que el resto si. Una opción poco práctica sería definir un nuevo grupo y replicar todos los permisos, pero es dificil de mantener visto que si agrego un nuevo permiso debo recordar agregarlo en ambos grupos. Otra opción, a mi ver mucho mas práctica, sería definir en mi base no solo al grupo sino también al login particular, y a éste denegarle el permiso a dicha tabla. De esta forma heredará los permisos del grupo pero le será denegado el acceso a la tabla.
Seguridad SQL
El esquema visto anteriormente es el ideal, pero es un echo que no siempre puedo adaptarme a éste modelo y a veces no hay mas remedio que utilizar seguridad SQL. En estos casos es una buena práctica que los logins deban cumplir con cierta complejidad de contraseña, así como también definir la caducidad. Recuerden tener esto en cuenta porque son dos clicks al dar de alta y nos dejará dormir mucho mas tranquilos. Pero es una tarea humana realizar esta alta en general, y como tal puede fallar. Por eso es importante corroborar que fehacientemente los logins cumplan con nuestros criterios de seguridad establecidos. En SQL 2008 podemos utilizar policys (http://msdn.microsoft.com/en-us/library/bb510667.aspx) que nos permiten hacer dos cosas en este contexto:
- Tener un listado periodico de todos los logins que no cumplan con determinados criterios
- Prohibir la creación de un login si no cumple los criterios
Esto es muy comodo porque nos evita los errores humanos, aunque en algunos contextos puede ser demasiado restrictivo.
Certificados
Por último una buena forma de tener acotados los permisos que les damos a los usuarios es ponerles una fecha de caducidad si sabemos que la tarea que van a realizar es solamente por un tiempo acotado. En estos casos podemos definir un certificado en la master que tenga un expiration date y asociar el login a dicho certificado. De ésta forma una vez vencido el certificado el usuario no podrá loguearse al sistema.
Saludos!
Etiquetas:
certificado,
certificate,
logins,
policys,
seguridad,
SQL 2008
lunes, 26 de julio de 2010
Jobs Matusalén (Procesos colgados)
Un tema interesante que se me presentó hace unos meses fue encontrar un job que llevaba corriendo una semana y como ningún usuario reclamó nada pasó desapercibido. Ahí fue que decidí tomar una acción preventiva para estos casos. Cuando configuramos nuestras tareas de mantenimiento o control lo mas normal es que utilicemos jobs. Los jobs si ejecutan de forma correcta podemos configurar que nos mande un mail, o lo que es mas habitual, podemos configurar que nos envíe mail cuando falla (a nosotros o cualquier operador del SQL). El problema es que pasa cuando el proceso queda colgado, por lo tanto no recibimos ni mail de ok, ni mensaje de error y es muy probable que no nos demos cuenta.
Con la intención de alertar esta situación generé un job que se ejecute cada 15 minutos y controle todos los jobs que se encuentran vivos hace cuanto tiempo lo están. La forma de implementarlo fue hacer un procedimiento que para cada job que se encuentra corriendo hace mucho genere una excepción, y en caso contrario no haga nada, de esta forma puedo en mi job de control programar la alerta de mail y que la reciban todos los administradores.
Para obtener los jobs vivos utilicé "xp_sqlagent_enum_jobs", que es medio tramposa así que tengan en cuenta que deben pasarle como primer parámetro 0 y el segundo parametro no interesa pero debe ser algún texto (no importa cual). Esto se debe a que el primer parámetro hace que se ignore el segundo pero igual lo exije.
El resultado de esa consulta nos retorna jobs_id así que deberemos cruzarla con "msdb.dbo.sysjobs" y "msdb.dbo.sysjobactivity" para transoformala en algo humanamente entendible.
La pregunta interesante es la siguiente: cuando consideramos que un job está tardando? La mejor solución asumí que es basarnos en el historial. Por suerte contamos con la tabla msdb.dbo.sysjobhistory. Un error común podría ser querer promediar el tiempo que ahí se encuentra y generar una alerta cada vez que el job tarda mas que ese promedio. ERROR. ¿por que es esto incorrecto? porque si un job a veces tarda 58min y a veces 1hs 2min, no suena razonable que pasada una hora alerte que ocurrió un error, sino la mitad de las veces recibiría el mail, y bien sabemos que un mail que llega seguido termina siendo ignorado con las posibles consecuencías que esto trae. Por eso es necesario basarnos en la matemática y averiguar el desvio estandar de nuestra muestra, y calcular cual es el tiempo esperado tal que un 90% de los casos alcanza a terminar, asumiendo que el tiempo esperado suele ser de la forma valor esperado + error, teniendo el error una distrinución Gaussiana.
En la impementación me pareció bueno meter esto en otra tabla y actualizarla períodicamente con otro job que se encargue de calcular los tiempos (una vez por semana por ejemplo) y el job de control consulte estos tiempos. Una de las partes mas molestas de implementar esto es que la job_history no tiene un formato muy amigable para los tiempos, les recomiendo que lo calculen con una función y la tengan a mano porque sirve para muchas cosas y es muy molesta de rehacer.
Espero haber sido claro, si alguién desea el código puede enviarme un mensaje privado.
Con la intención de alertar esta situación generé un job que se ejecute cada 15 minutos y controle todos los jobs que se encuentran vivos hace cuanto tiempo lo están. La forma de implementarlo fue hacer un procedimiento que para cada job que se encuentra corriendo hace mucho genere una excepción, y en caso contrario no haga nada, de esta forma puedo en mi job de control programar la alerta de mail y que la reciban todos los administradores.
Para obtener los jobs vivos utilicé "xp_sqlagent_enum_jobs", que es medio tramposa así que tengan en cuenta que deben pasarle como primer parámetro 0 y el segundo parametro no interesa pero debe ser algún texto (no importa cual). Esto se debe a que el primer parámetro hace que se ignore el segundo pero igual lo exije.
El resultado de esa consulta nos retorna jobs_id así que deberemos cruzarla con "msdb.dbo.sysjobs" y "msdb.dbo.sysjobactivity" para transoformala en algo humanamente entendible.
La pregunta interesante es la siguiente: cuando consideramos que un job está tardando? La mejor solución asumí que es basarnos en el historial. Por suerte contamos con la tabla msdb.dbo.sysjobhistory. Un error común podría ser querer promediar el tiempo que ahí se encuentra y generar una alerta cada vez que el job tarda mas que ese promedio. ERROR. ¿por que es esto incorrecto? porque si un job a veces tarda 58min y a veces 1hs 2min, no suena razonable que pasada una hora alerte que ocurrió un error, sino la mitad de las veces recibiría el mail, y bien sabemos que un mail que llega seguido termina siendo ignorado con las posibles consecuencías que esto trae. Por eso es necesario basarnos en la matemática y averiguar el desvio estandar de nuestra muestra, y calcular cual es el tiempo esperado tal que un 90% de los casos alcanza a terminar, asumiendo que el tiempo esperado suele ser de la forma valor esperado + error, teniendo el error una distrinución Gaussiana.
En la impementación me pareció bueno meter esto en otra tabla y actualizarla períodicamente con otro job que se encargue de calcular los tiempos (una vez por semana por ejemplo) y el job de control consulte estos tiempos. Una de las partes mas molestas de implementar esto es que la job_history no tiene un formato muy amigable para los tiempos, les recomiendo que lo calculen con una función y la tengan a mano porque sirve para muchas cosas y es muy molesta de rehacer.
Espero haber sido claro, si alguién desea el código puede enviarme un mensaje privado.
Etiquetas:
colgado,
jobs,
SQL,
SQL 2008,
sysjobhistory,
xp_sqlagent_enum_jobs
jueves, 22 de julio de 2010
Auditoria de Active Directory con SQL 2008
Hace poco se me presentó un problema que luego de buscarlo por varias páginas sin resultados buenos decidí implementar una solución desde cero. El equipo de seguridad informática necesitaba un producto para llevar una auditoría de cambios de AD y controlar que usuarios han sido modificados o se les han asignado nuevos permisos.
El primero intento (y el que abunda en la red) de llevar esto a SQL 2008 tiene sus problemas. Si la empresa está compuesta por pocos usuarios no hay inconveniente, se hace un open query de SQL a AD utilizando LDAP y se controla de a un usuario por vez si cambio algo. El problema es que el SQL no permite traer TODOS los usuarios si estos son muchos, así que hay que buscar otra solución.
La respuesta que plantee consiste de combinar varias tecnologías. Para poder hacer la auditoría primero cargaremos todos los datos de AD en un conjunto de tablas intermedias en SQL. La forma de hacer esto será mediante Power Shell. El Power Shell puede conectarse sin problemas al AD y cada resultado obtenido en el iterador lo volcaremos en la tabla SQL. Hay que tener cuidado con la normalización de las tablas visto que algunos de los campos de AD son multivaluados y debemos guardarlos como tablas independientes (el ejemplo mas importante es Member o MemberOf).
Una vez cargados los datos en las tablas intermedia SQL lo volcamos en la tabla final. Este paso intermedio lo hacemos para poder saber que cosas han cambiado sin tener que volcar una gran lógica en el Power Shell. En lugar de hacer complejas consultas actualizaremos la tabla final (Ej. T_AD_USERS) con la tabla intermedia (Ej. T_AD_USERS_TEMP). La forma mas rápida podría ser hacer un truncate y volcar todo el contenido, pero cambiaremos solamente lo que sea realmente necesario y ya veremos por que. El SQL 2008 nos brinda para esto el comando MERGE (http://technet.microsoft.com/en-us/library/bb510625.aspx) que nos permite de forma rápida incorporar los registros faltantes, modificar los existentes y borrar los dados de baja.

El primero intento (y el que abunda en la red) de llevar esto a SQL 2008 tiene sus problemas. Si la empresa está compuesta por pocos usuarios no hay inconveniente, se hace un open query de SQL a AD utilizando LDAP y se controla de a un usuario por vez si cambio algo. El problema es que el SQL no permite traer TODOS los usuarios si estos son muchos, así que hay que buscar otra solución.
La respuesta que plantee consiste de combinar varias tecnologías. Para poder hacer la auditoría primero cargaremos todos los datos de AD en un conjunto de tablas intermedias en SQL. La forma de hacer esto será mediante Power Shell. El Power Shell puede conectarse sin problemas al AD y cada resultado obtenido en el iterador lo volcaremos en la tabla SQL. Hay que tener cuidado con la normalización de las tablas visto que algunos de los campos de AD son multivaluados y debemos guardarlos como tablas independientes (el ejemplo mas importante es Member o MemberOf).
Una vez cargados los datos en las tablas intermedia SQL lo volcamos en la tabla final. Este paso intermedio lo hacemos para poder saber que cosas han cambiado sin tener que volcar una gran lógica en el Power Shell. En lugar de hacer complejas consultas actualizaremos la tabla final (Ej. T_AD_USERS) con la tabla intermedia (Ej. T_AD_USERS_TEMP). La forma mas rápida podría ser hacer un truncate y volcar todo el contenido, pero cambiaremos solamente lo que sea realmente necesario y ya veremos por que. El SQL 2008 nos brinda para esto el comando MERGE (http://technet.microsoft.com/en-us/library/bb510625.aspx) que nos permite de forma rápida incorporar los registros faltantes, modificar los existentes y borrar los dados de baja.
Con esto ya tenemos una tabla con la información de AD actualizada cuando querramos (podría ser un job que ejecute cada una hora). Lo que nos falta es llevar la auditoría en si misma, y para eso activaremos el CDC (change data capture) sobre la tabla en cuestión. Como nos encargamos de no borrar todo cada vez sino solamente actualizar según los cambios, el CDC llevará un registro de todos los usuarios que hayan sido actualizados. La única salvedad es que nuestra auditoría así como está planteada mostrará las altas, las bajas, y por cada vez que se ejecute el job todos los campos figurarán como actualizado, así que debemos hacer dos MERGE, uno de altas y bajas (WHEN NOT MATCHED BY TARGET y WHEN NOT MATCHED BY SOURCE) y otro para actualizar que contenga en el join que coincida la PK y alguno de los campos haya cambiado.
Con esto ya tendrémos una tabla de auditoría de AD (cdc.dbo_T_AD_USERS_CT), ahora lo único que nos falta es darle a la gente de seguridad informática una interfaz amigable para acceder a ésta. Recordemos que el CDC trabaja con marcas de agua y esto no es muy amigable para la gente de otras áreas, así que lo que recomiendo como mínimo es hacer un stored procedure que tome como parametros dos DATETIME (desde, hasta) y retorne todas las entradas del CDC que se encuentren en dicho período pero cambiando la marca de agua por su fecha correspondiente y mostrar esto desde reporting services u otra interfaz amigable.
Si alguien quiere el código de todo esto implementado no dude en ponerse en contacto conmigo.
Saludos!
Si alguien quiere el código de todo esto implementado no dude en ponerse en contacto conmigo.
Saludos!
Etiquetas:
Active Directory,
AD,
Auditoria,
CDC,
ChangeDataCapture,
LDAP,
MERGE,
PowerShell,
SQL 2008
Suscribirse a:
Entradas (Atom)