asp.net - Pista de auditoría en aplicación web usando servidor sql
sql-server audit-trail (4)
¿Qué tan probable es que se realicen cambios legítimos en la base de datos mediante la ejecución directa de consultas SQL contra la base de datos, ya sea a través del estudio de administración de SQL o de otra manera? Recomendaría asumir que todos los datos en los datos se ingresan a través de su aplicación y tienen la auditoría en su capa BL. Entonces, simplemente puede restringir el acceso a la base de datos a usuarios de confianza. En última instancia, tiene que haber uno o más usuarios con permiso para modificar el esquema de la base de datos, si esos usuarios quisieran pasar por alto la auditoría, simplemente podrían deshabilitar los activadores o falsificar la pista de auditoría. Si alguna vez existen motivos legítimos para ejecutar consultas SQL directas en la base de datos, por ejemplo, importaciones de datos poco frecuentes desde otros sistemas, etc., puede limitar esta actividad a los usuarios de confianza y asegurarse de que sus scripts de importación llenen correctamente la tabla de auditoría. Cualquier cosa que suponga una carga de trabajo excesiva para los administradores de bases de datos o para quienes sean los usuarios de confianza debe estar integrado en la aplicación de todos modos.
Estamos desarrollando una aplicación web utilizando asp.net y un servidor sql. Estamos obligados a hacer un seguimiento de auditoría para la aplicación. Como entiendo esto, una pista de auditoría sería básicamente para todos los Inserts, Updates y Delete en la base de datos, ¿no? Ahora, la forma de abordar esto es que tengo una Tabla de seguimiento de auditoría en la base de datos que se rellena después de cada inserción, actualización o eliminación (Escritura manual de la secuencia de comandos dentro del DAL). Sin embargo, cualquier cambio en la base de datos disparado directamente desde SQL Management studio NO será registrado (por razones obvias: P).
Para atender eso pude crear un disparador y eso se encarga de todo. También hice algunas búsquedas en Google y descubrí que el servidor SQL tiene la capacidad de hacer un seguimiento de auditoría. Sin embargo, el problema con el uso de desencadenadores es que NO obtendré la información del usuario que inició sesión en el sitio web. Obtendré el usuario de SQL, pero no le doy dos vueltas al respecto, estoy preocupado por el usuario del sitio web.
Una solución que descubrí fue: a) Tener un seguimiento de auditoría de mi aplicación web y también tener una configuración de activador. En el informe de auditoría, simplemente muestro un registro de auditoría de la aplicación web y un registro de auditoría del servidor SQL. Problemas obvios con este enfoque: por encima de la cabeza. Escribiendo en dos conjuntos diferentes de tablas en CADA CAMBIO DE DB.
b) Tengo una columna llamada UserId EN CADA TABLA DE DB. Entonces creo un disparador para capturar todos los cambios de base de datos. Paso este ID de usuario en cada tabla que cambio (insertar, actualizar, eliminar) y obtener este ID desde el activador. Retrocesos obvios: columna de ID de usuario innecesaria en cada tabla
Me disculpo por el largo post. Básicamente, necesito un registro de auditoría que registre todos los cambios de db (incluido el hackeo directo a db) pero al mismo tiempo me da la información de inicio de sesión del usuario para esos cambios de db que se realizaron DESDE la aplicación web.
Apreciará cualquier entrada en este sentido.
Muchas gracias
Xeshu
Estoy de acuerdo con los otros dos carteles. La conclusión es que si desea almacenar el nombre de usuario de su aplicación web (es decir, su autenticación personalizada), los activadores NO lo ayudarán a auditar lo que está sucediendo. - Advertencia a menos que pueda utilizar la autenticación integrada
Esto es realmente importante si también desea utilizar las pistas de auditoría para monitorear volúmenes de actividad por usuario, por ejemplo. La solución a esto es realizar todos los DDL a través de procedimientos almacenados y, dentro de esos procedimientos almacenados, agregue su lógica de auditoría (si desea que todo el registro esté escrito en T-SQL). Alternativamente, hágalo desde la aplicación y observe una de las muchas bibliotecas de registro disponibles para ASP.Net, como NLog .
Gracias por sus respuestas a todos. Después de algunas búsquedas en Google, este es el enfoque que creo que sería apropiado: Tabla de auditoría genérica
Tabla de auditoría (Id, TableName, RecordId, (enlace al registro en cuestión) ModifiedBy, ModifiedOn, Type (I, U o D))
Audit_Details_Table (Id, AuditId, FieldName, OldValue, NewValue)
Creo que esto debería hacerlo. ¿Alguna idea?
Suena como si estuvieras en las líneas correctas. Sin embargo, generalmente no tendrá una sola tabla de seguimiento de auditoría, sino una tabla de auditoría para cada tabla. Por lo tanto, para cada modificación de una fila en la Tabla A, se agrega una nueva fila a la TablaA_Auditoría que contiene el nuevo estado en la TablaA, más la fecha y el nombre del usuario.
Normalmente se usa un activador para esto, pero si está almacenando el nombre de usuario de la aplicación web, no sé cómo pasar estos datos a un activador (¿alguien más puede ayudar?) En este caso, podría estar tentado a usar procedimientos Para cada tabla, tenga procedimientos almacenados para insertar, actualizar y eliminar filas. Cada uno de estos procedimientos almacenados llamaría a otro procedimiento almacenado que inserta la fila en la tabla de auditoría. De esta manera, usted pasa fácilmente el nombre de usuario de la aplicación web al procedimiento almacenado que inserta la fila en la tabla de auditoría. Obviamente, el inconveniente es tener que mantener un montón de procedimientos almacenados para cada tabla, lo que puede ser un poco tedioso, ya que debe asegurarse de que todos se ajusten a las tablas (y la capa de acceso a datos de la aplicación) ya que los cambios de esquema son inevitablemente necesarios. .
Tenga en cuenta que no necesita una columna de nombre de usuario en cada tabla, solo en cada tabla de auditoría.
Espero que algo de eso fuera de utilidad.
Aclamaciones
David