none
Logování přístupu ke konkrétní databázi SQL serveru RRS feed

  • Dotaz

  • Mám od klienta požadavek logovat přístupy k jedné konkrétní DB na MS SQL Serveru 2017. Klientovi by stacilo logovat pouze úspěšné přihlášení.

    Jdu na to pomocí SQL server auditu. Nicméně nevím, jak auditovat přístup ke konkrétní DB. Pomocí Audit Action Type SUCCESSFUL_LOGIN_GROUP se logují události přihlášení k SQL serveru bez rozlišení, ke které DB se přistupuje.

    Pomocí SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP jsem schopen logovat přístup jen ke Contained databázi, což požadovaná DB není.

    Napadl mě workaround pomocí auditování příkazu SELECT nad konkrétní DB, tady je ovšem problém, že audit pochopitelně loguje obrovské množství dat.

    Nenapadá vás nějaké inteligentnější řešení?

    Díky


    BB

    čtvrtek 8. dubna 2021 11:53

Odpovědi

  • asi bych zacal vypisem (a naslednym insertem do logu):

    SELECT { fn NOW() },DB_NAME(dbid) ,loginame FROM master.dbo.sysprocesses WHERE spid > 50 and dbid=tvojeid

    a popremyslel, na jakej trigger to navesit - FOR LOGON asi bude too early

    MP



    čtvrtek 8. dubna 2021 20:13
    Moderátor
  • Tak mám konečně finální řešení. Je to řešeno pomocí následujícího logon triggeru:

    CREATE   TRIGGER [LogonTrigger_for_Audit] on ALL SERVER FOR LOGON
    AS
    BEGIN
    INSERT INTO Master.dbo.Logons (Login_Time, Database_Name, Database_ID, Host_Name, Program_Name, Login_Name)
    SELECT login_time, DB_NAME(dbid), dbid, hostname, program_name, loginame FROM master.dbo.sysprocesses WHERE spid > 50 and (dbid = 5 or dbid = 10)
    and login_time between DateAdd(second, -10, SYSDATETIME()) and DateAdd(second, 10, SYSDATETIME())
    END;

    Logon trigger se spouští vždy v kontextu uživatele, který událost logon vyvolal.

    Původní záměr byl informace zapisovat do dedikované databáze, to se ale nepodařilo. Nebyl jsem schopen roli "public" přidělit právo na INSERT.

    Nicméně jsem pak našel varování, že používat logon triggery se zápisem do jiné než Master DB je minimálně nerozum, tak jsem to udělal tak, že tbl pro uložení záznamů o logování je v databázi Master.

    A jedno upozornění pro všechny, kteří by pojali pokušení si s logon triggery hrát: selže-li z jakéhokoliv důvodu vykonání logon triggeru, selže i vlastní přihlášení. Takže dobře otestovat ve vývojovém prostředí, aby jste si nezablokovali přístup k SQL serveru.


    BB

    úterý 27. dubna 2021 13:34

Všechny reakce

  • asi bych zacal vypisem (a naslednym insertem do logu):

    SELECT { fn NOW() },DB_NAME(dbid) ,loginame FROM master.dbo.sysprocesses WHERE spid > 50 and dbid=tvojeid

    a popremyslel, na jakej trigger to navesit - FOR LOGON asi bude too early

    MP



    čtvrtek 8. dubna 2021 20:13
    Moderátor
  • To by mohlo být ono.

    Děkuji


    BB

    pátek 9. dubna 2021 10:56
  • Prosim podel se o finalni reseni (if any)

    MP

    pátek 9. dubna 2021 11:53
    Moderátor
  • JJ, napíšu.

    Prozatím finální řešení není, jen jsem testnul uvedený select a dává, co je třeba. Až odladím ten trigger a export to log, napíšu.


    BB

    pondělí 12. dubna 2021 7:35
  • Tak mám konečně finální řešení. Je to řešeno pomocí následujícího logon triggeru:

    CREATE   TRIGGER [LogonTrigger_for_Audit] on ALL SERVER FOR LOGON
    AS
    BEGIN
    INSERT INTO Master.dbo.Logons (Login_Time, Database_Name, Database_ID, Host_Name, Program_Name, Login_Name)
    SELECT login_time, DB_NAME(dbid), dbid, hostname, program_name, loginame FROM master.dbo.sysprocesses WHERE spid > 50 and (dbid = 5 or dbid = 10)
    and login_time between DateAdd(second, -10, SYSDATETIME()) and DateAdd(second, 10, SYSDATETIME())
    END;

    Logon trigger se spouští vždy v kontextu uživatele, který událost logon vyvolal.

    Původní záměr byl informace zapisovat do dedikované databáze, to se ale nepodařilo. Nebyl jsem schopen roli "public" přidělit právo na INSERT.

    Nicméně jsem pak našel varování, že používat logon triggery se zápisem do jiné než Master DB je minimálně nerozum, tak jsem to udělal tak, že tbl pro uložení záznamů o logování je v databázi Master.

    A jedno upozornění pro všechny, kteří by pojali pokušení si s logon triggery hrát: selže-li z jakéhokoliv důvodu vykonání logon triggeru, selže i vlastní přihlášení. Takže dobře otestovat ve vývojovém prostředí, aby jste si nezablokovali přístup k SQL serveru.


    BB

    úterý 27. dubna 2021 13:34
  • dik za resume!

    MP

    úterý 27. dubna 2021 19:14
    Moderátor