✔ MS SQL Server Version: 15.0.2000.5 LOGON Trigger mit globaler Variable @@OPTIONS und @@CONNECTIONS falscher Wert für die Optionen

[tutorial-db]

Hallo liebe Gemeinde,

ich bin ein bisschen verzweifelt und evtl. hat jemand einen Tip was ich falsch mache.

Motivation:
Es soll ein LOGON Trigger geschrieben werden der neben Informationen zu Connects, Host, User u.s.w. auch die voreingestellten Optionen berichten soll.
Das klappt auch alles, bis auf den Wert für die ausgelesene Umgebungsvariable @@OPTIONS.
Diesen Wert brauche ich, um festzustellen, wie der Wert für IMPLICIT_TRANSACTION beim logon gesetzt ist und möchte den Wert dann auflösen.
Da ich Neuling bin im MS SQL Server Umfeld bin, könnte es natürlich sein, dass das gar nicht geht und mein Ansatz falsch ist.

Was habe ich gemacht:

---------------------------------------
-- TEST
---------------------------------------
/* Creates db for storing audit data */
CREATE DATABASE logonaudit
go

/* use the db */
USE logonaudit
go

/* Creates table for logons inside db */
CREATE TABLE logonauditings
(
    A_CONNECTIONS int,
    A_OPTIONS int,
    A_IMPLICIT_TRANSACTION varchar(32)
)
GO

/* creates the trigger */
CREATE or alter TRIGGER logonauditingtrigger
 ON ALL SERVER FOR LOGON
AS
begin
declare
 @A_CONNECTIONS           int,
 @A_OPTIONS               int,
 @A_IMPLICIT_TRANSACTION  varchar(32) = 'Hier steht entw. ON o. OFF'
 
 set @A_OPTIONS = @@OPTIONS;

 /* diese Interpretation geht schief da der INT Wert nicht korrekt scheint */
 if ((@A_OPTIONS & 2) = 0)  
     set @A_IMPLICIT_TRANSACTION = 'Implicit Transactions are OFF'
 ELSE
     set @A_IMPLICIT_TRANSACTION = 'Implicit Transactions are ON';

INSERT INTO logonaudit.dbo.logonauditings
(
A_CONNECTIONS,
A_OPTIONS,
A_IMPLICIT_TRANSACTION
)
values
(
 @@CONNECTIONS,
 @@OPTIONS,
 @A_IMPLICIT_TRANSACTION
)
end
go

COMMIT;


/* manuelle Analyse wenn man die Option umschaltet für implicit_transactions */

-- ON
set implicit_transactions ON;

select  @@OPTIONS --> Ergebnis: 5498

-- OFF
set implicit_transactions OFF;

select  @@OPTIONS --> Ergebnis: 5496

/* Nach dem neuen login in die datenbank audit tabelle abfragen also was wurde während des login geschrieben */
select *
from logonaudit.dbo.logonauditings
;

/* in der audit tabelle steht dann der Wert */

-- A_CONNECTIONS    A_OPTIONS    A_IMPLICIT_TRANSACTION
-- 1975                             22328        Implicit Transactions are ON

/* clean up */
truncate table logonaudit.dbo.logonauditings
;

Also im Wesentlichen geht es um die Frage:
---------------------------------------------------
Warum wird bei manueller Abfrage der Variablen @@OPTIONS ein Wert 5496 oder 5498 angezeigt und wenn das über den Trigger läuft der Wert 22328.
Das scheint das Vier-Fache zu sein, erschließt sich mir aber nicht wirklich.
Bei der Anzahl der Connections ist ein Unterschied von nur 2 oder ähnlich.

Ich würde mich sehr über Tips feuen.

Herzliche Grüße

 

[Zvoni]

Wenn ich es richtig verstanden habe: der LOGON-Trigger feuert, NACHDEM erfolgreich authentifiziert wurde, jedoch BEVOR eine USER-Session erzeugt ist.

Vermutung: @@OPTIONS liefert im Trigger die serverseitigen Default-Werte, da ja noch keine User-Session läuft, in welcher die @@OPTIONS umkonfiguriert werden könnten (per SET oder sp_configure)

 

[tutorial-db]

@Zwoni, vielen Dank für Deine Überlegungen und Einschätzungen.

Dann werde ich eher den Weg über einen INSERT, UPDATE, DELETE Trigger gehen um die eingestellte Option zu tracken. Da sollte ja schon ein Userlogon stattgefunden haben.

Hier ist die Herausforderung, dass der Trigger feuern soll egal an welcher Tabelle in der DB (vom User erstellte DB) DML betrieben wird. Da sich ja die Anzahl der Tabellen der Applikation ändern kann und mir nicht bekannt ist welche Tabelle hinzugekommen ist, die Frage, kann man den Trigger auf DB - Ebene anlegen oder muss das Anlegen eines Triggers für jedes Tabellenobjekt erfolgen?

Danke für einen Tip

F.

 

[Zvoni]

Da es MSSQL-Server ist:
MS-SQL-Server implementiert INFORMATION_SCHEMA, und dort steht z.Bsp. welche Tabellen in welcher DB existieren.
Dort wird auch ein neuer "Eintrag" vorgenommen, wenn eine neue Tabelle erzeugt wird.......
(Hinweis, Hinweis, Hinweis.....)

Idee:
Eigene Audit-DB/Tabelle
Trigger auf INFORMATION_SCHEMA bzw. besser gesagt auf "sys.objects" (INFORMATION_SCHEMA ist eine View).
Wenn User eine neue Tabelle anlegt sollte der Trigger feuern, und du kannst in deiner Audit-DB/Tabelle den Eintrag vornehmen "Zvoni hat in Datenbank dbo Tabelle test am soundsovielten angelegt"
Dann musst du dynamisch die DML-trigger auf diese neue Tabelle anlegen. Müsste mit ner SP gehen, welcher du den neuen tabellenNamen als Parameter übergibst, und welche dann in der SP den CREATE TRIGGER auslöst. Die SP wird als letztes aus deinem "Tabelle angelegt"-Trigger abgefeuert

Jetzt nur mal so als Ansatz

 

[tutorial-db]

Dankeschön, so würde ich es machen.

Grüße F.

 

[Zvoni]

Hat funktioniert?

 

[tutorial-db]

Hallo,

der Folgender Test hat funktioniert.

Beobachtung: die OPTIONEN kommen immer noch mit einem anderen INT Wert zurück. Evtl. noch eine Idee woran das liegen kann? Wahrsch. sind TRIGGER auf System-Ebene "verdrahtet" und nicht auf CONNECTION Ebene?

Für meinen Test hat das aber erstmal geklappt und wäre ein Ansatz.

Weitere Überlegung:
wenn die OPTION nicht wie erwartet eingestellt ist, würde ich den INSERT abbrechen wollen und einen Logeintrag schreiben, das muss ich aber noch verstehen wie man sowas macht, oder habt Ihr einen TIP?

/*

TEST zum tracken der OPTIONS - EInstellungen

*/

/* create db */
create database triggertest;

/* current session uses db */
use triggertest;

/* table for insert to trigger the trigger */
CREATE TABLE Employee
( 
    EmployeeID int NOT NULL,
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL,
    HireDate date,   
);

/* table for the option logger results of trigger event */

CREATE TABLE optionlog (
    logID INT IDENTITY(1,1) NOT NULL -- An identity column will automatically generate and populate a numeric
                                     -- column value each time a new row is inserted into a table. The identity column
                                     -- uses the current seed value along with an increment value to generate a new identity
                                     -- value for each row inserted.
   , option_implicit_transaction nvarchar(50)
   , option_value int
   );
GO

/* manuelles setzen der OPTION */

set implicit_transactions off;

/* Trigger creation */
go
CREATE or alter TRIGGER trgoptionlog
ON Employee
FOR INSERT
AS
--SET NOCOUNT ON;
    begin
      DECLARE
      @o                       INT,
      @A_IMPLICIT_TRANSACTION  varchar(32) = 'Hier steht entw. ON o. OFF'
      set @o = @@OPTIONS;
       if (@@OPTIONS & 2) = 0
                       set  @A_IMPLICIT_TRANSACTION = 'Implicit Transactions are OFF'
        ELSE
                        set  @A_IMPLICIT_TRANSACTION = 'Implicit Transactions are ON'   
  
      end
  insert into optionlog(option_value, option_implicit_transaction) values (@@OPTIONS, @A_IMPLICIT_TRANSACTION);
GO

INSERT INTO Employee
VALUES
(101, 'IMPLICIT IS ','OFF',GETDATE()),
(112, 'IMPLICIT IS ','OFF',GETDATE());
GO

commit;

SELECT *
FROM optionlog
GO

/* close mssql management studio: OPTION IS SET TO ON in settings NOT SET OPTION MANUEL!! */

use triggertest
go

INSERT INTO Employee
VALUES
(112, 'IMPLICIT IS ','ON',GETDATE());
GO

SELECT *
FROM optionlog
GO



/* clean up */
commit;
DROP DATABASE triggertest
go

Grüße

F.

 

[Zvoni]

Frag im Trigger mal current_user(), session_user() und system_user() ab und schreib sie mal wohin, wo du danach mal ablesen kannst, unter welchem kontext der trigger ausgeführt wird.

u.u. musst du den user-kontext wechseln (EXECUTE AS)

 

[Zvoni]

Hmm....scheint tatsächlich so zu sein:
Get session of current user on trigger

Könnte sogar dann mit deiner ursprünglichen Variante mit dem Logon-Trigger funktionieren.
Mach mal nen Logon-Trigger, und schreib die Werte für die drei XXX_USER irgendwohin.....