update - trigger sql server w3schools

Construyendo un sistema de auditoría; Interfaz de MS Access en el back-end de SQL Server

Question

Básicamente, estoy construyendo una aplicación para mi empresa y NECESITA crearse con MS Access y debe estar basada en SQL Server.

He elaborado la mayoría de los planes pero estoy teniendo dificultades para encontrar una manera de manejar el sistema de auditoría.

Como solo se usa internamente y ni siquiera podrá tocar el archivo desde fuera del edificio, no estamos usando un sistema de inicio de sesión, ya que el programa solo se utilizará una vez que el usuario ya haya iniciado sesión en nuestra red interna a través de Active Directorio. Sabiendo esto, estamos usando un sistema para detectar automáticamente el nombre del usuario de Active Directory y con sus permisos en una de las tablas de DB, decidiendo qué pueden o no pueden hacer.

Entonces, la tabla de auditoría real tendrá 3 columnas (este diseño puede cambiar, pero para esta pregunta no importa); quién (usuario de Active Directory), cuándo (hora de adición / eliminación / edición), qué (qué se cambió)

Mi pregunta es cómo debería manejar esto. Idealmente, sé que debería utilizar un desencadenador para que no se pueda actualizar la base de datos sin que se haya registrado una auditoría, sin embargo, no sé cómo podría captar al usuario de Active Directory de esa manera. Una alternativa sería codificarla directamente en el origen de Access para que cada vez que algo cambie ejecute una instrucción INSERT. Obviamente, esto tiene fallas porque si algo le sucede a Access o la base de datos es tocada por otra cosa, entonces no registrará la auditoría.

¡Cualquier consejo, ejemplo o artículo que pueda ayudarme sería muy apreciado!

Answer 1

¿Esto funciona para tí?

select user_name(),suser_sname()

Doh! Olvidé escapar de mi código.

Answer 2

Debería ser

select user name(),suser sname()

reemplazar espacios con guiones bajos

Answer 3

Intenté jugar un poco con Access para ver si podía encontrar un camino para ti. Creo que puede especificar una nueva fuente de datos en su tabla SQL y seleccionar Autenticación de Windows NT como su tipo de conexión.

Answer 4

Ok, está trabajando aquí. Estoy viendo mis credenciales de Windows cuando actualizo mis tablas. Entonces, apuesto a que perdimos un paso. Permítanme armar una secuencia de 1, 2, 3 de lo que hice y quizás podamos rastrear dónde se está rompiendo esto para ustedes.

1. Crear una nueva base de datos de MSAccess (vacía)
2. Haga clic en la sección de tablas
3. Seleccionar datos externos
4. Elija la base de datos ODBC
5. Elija Enlace a la fuente de datos creando una tabla vinculada
6. Seleccionar origen de datos de la máquina
7. Elige nuevo ...
8. Datasource del sistema
9. Elija SQL Server de la lista y haga clic en Siguiente, Finalizar.
10. Proporcione a la nueva fuente de datos un nombre y una descripción, y seleccione (local) para el servidor. Haga clic en Siguiente.
11. Elija "Con la autenticación de Windows NT usando la ID de inicio de sesión de red". Haga clic en Siguiente.
12. Marque Cambiar la base de datos predeterminada a, y elija la base de datos. Haga clic en Siguiente. Haga clic en Finalizar.
13. Pruebe la fuente de datos.
14. Elija la tabla con la que está asociado el Trigger y haga clic en Aceptar.
15. Abra la tabla en Access y modifique una de las entradas (el desencadenador no se activa en Insertar, solo actualiza)
16. Seleccione * de su tabla de auditoría

Answer 5

Por supuesto :)

Debería haber una sección en Access llamada "External Data" (estoy ejecutando una nueva versión de Access, por lo que la elección del menú puede ser diferente).

Forma esto debería haber una opción para especificar una conexión ODBC.

Obtengo una opción para Vincular a la fuente de datos creando una tabla vinculada.

Luego creé un datasource de Machine. Seleccioné SqlServer de la lista desplegable. Luego, cuando hago clic en Siguiente, me preguntan cómo quiero autenticarme.

Answer 6

Si especifica SSPI en su cadena de conexión a Sql, creo que se proporcionan sus credenciales de Windows.

Answer 7

necesita conectarse con seguridad integrada, también conocida como conexión confiable, vea ( http://www.connectionstrings.com/?carrier=sqlserver )

Answer 8

CREATE TRIGGER testtrigger1
ON testdatatable
AFTER update
AS
BEGIN
INSERT INTO testtable (datecol,usercol1,usercol2) VALUES (getdate(),user_name(),suser_sname());
END
GO

Answer 9

¿Cuántos usuarios de la aplicación habrá? ¿Existe la posibilidad de usar la autenticación integrada de Windows para la autenticación de SQL?

Actualizado : si puede dar a cada usuario un inicio de sesión de SQL (Windows integrado), entonces puede recoger al usuario que inició sesión utilizando la función SYSTEM_USER.

Answer 10

También tenemos un sistema de base de datos que se usa exclusivamente dentro de la organización y usa inicios de sesión de Window NT. Esta función devuelve el nombre de inicio de sesión de los usuarios actuales:

CREATE FUNCTION dbo.UserName() RETURNS varchar(50)
AS
BEGIN
RETURN (SELECT nt_username FROM master.dbo.sysprocesses WHERE spid = @@SPID)
END

Puedes usar esta función en tus disparadores.

Answer 11

Mi solución sería no permitir que Access modifique los datos con tablas vinculadas.

Solo crearía la interfaz de usuario en Access y crearía una conexión ADO con el servidor utilizando ventanas autenticadas en la cadena de conexión. Compile la aplicación de acceso como dbe para proteger el código VB.

No emitiría una declaración SQL, pero llamaría procedimientos almacenados para realizar los cambios en la base de datos y crearía la entrada del registro de auditoría en una transacción atómica.

La UI (Acceso) no necesita conocer los trabajos internos en el servidor. Todo lo que necesita hacer es solicitar y actualizar / insertar / eliminar utilizando los procedimientos almacenados que crearía para este fin. El servidor debe manejar el trabajo.

Recupere un conjunto de registros con ADO utilizando una vista con la sugerencia NOLOCK implementada en el servidor y guarde en caché estos datos en Access para la visualización local. O recupera un solo registro y bloquea solo esa fila para editar.

Al usar tablas vinculadas, tus usuarios se bloquearán entre sí.

Con las conexiones ADO no tendrá el problema de establecer ODBC en cada cliente.

Crea una tabla para configurar el estado del servidor. Su aplicación lo comprobará antes de cualquier acción. puede usarlo para cerrar el servidor a la aplicación en caso de que necesite realizar cambios o mantenimiento.

El acceso es una gran herramienta. Pero solo debe manejar sus datos locales y no debe interferir con el precioso servidor.