IBM Security Guardium

Hi,

I want to log SELECT commands performed by DBAs on a MSSQL server. However, when using the (Public) Select Command rule it also include SQL commands generated by the MSSQL server and I am only interested of the DBA input.

If I use the same Select command rule on MySql it only logs the DBA input and nothing else. 

I have used Action: Ignore S-TAP Session and it had some effect on the machine generated logs but if I do a INSERT it generates a lot of logs since the INSERT command uses SELECT commands to perform the INSERT.

Do you have any suggestion how to solve this issue or point me in the right direction?

My experience is that connecting to an instance with MSSQL programs such as 'sqlcmd.exe' and Management Studio (SSMS) will generate SQL to set up the environment before the first user SQL is sent to the instance.  I don't think there is a way around this behavior.  You may be able to remove the set-up SQL in reports with special conditions to exclude those statements.

------------------------------
Frank Bates
Guardium Administrator
KeyBank N.A.
Brooklyn OH
+1 (216) 471-2740
------------------------------

Original Message:
Sent: Thu October 17, 2019 09:28 AM
From: Herman Engström
Subject: Select Command - MSSQL

Hi,

I want to log SELECT commands performed by DBAs on a MSSQL server. However, when using the (Public) Select Command rule it also include SQL commands generated by the MSSQL server and I am only interested of the DBA input.

If I use the same Select command rule on MySql it only logs the DBA input and nothing else. 

I have used Action: Ignore S-TAP Session and it had some effect on the machine generated logs but if I do a INSERT it generates a lot of logs since the INSERT command uses SELECT commands to perform the INSERT.

Do you have any suggestion how to solve this issue or point me in the right direction?

I assume that you described that built-in account is used by DBA's.
The first question is - why do DBA's use this account? The good DB security policy requirement stress the use of named accounts.
It will be hard to identify what is generated by DB engine itself from other activity. Even you will able do that, still skilled DBA can try to hide from being tracked by generating something similar to standard DB engine activity.
If DBA's have to use built-in account I suggest focus on access vector. The UID chain (on Linux, Unix) will provide information about the full process chain. The DB engine activity will refer to mysql binary and DBA's sessions will come mostly from ssh process. However UID cannot be used in real time monitoring.

You can also consider the non-user related session identification using rule based on number of sql's generated in the defined time. For example - if session generates more than 1000 sql's per second in the first minute the session should be considered as a application one.

------------------------------
Zbigniew Szmigiero
IBM
Warsaw
------------------------------

Original Message:
Sent: Thu October 17, 2019 09:28 AM
From: Herman Engström
Subject: Select Command - MSSQL

Hi,

I want to log SELECT commands performed by DBAs on a MSSQL server. However, when using the (Public) Select Command rule it also include SQL commands generated by the MSSQL server and I am only interested of the DBA input.

If I use the same Select command rule on MySql it only logs the DBA input and nothing else. 

I have used Action: Ignore S-TAP Session and it had some effect on the machine generated logs but if I do a INSERT it generates a lot of logs since the INSERT command uses SELECT commands to perform the INSERT.

Do you have any suggestion how to solve this issue or point me in the right direction?

Ok I will look into your suggestions.

Thanks for the input Frank and Zbigniew.
Original Message:
Sent: Fri October 18, 2019 06:49 AM
From: Zbigniew Szmigiero
Subject: Select Command - MSSQL

I assume that you described that built-in account is used by DBA's.
The first question is - why do DBA's use this account? The good DB security policy requirement stress the use of named accounts.
It will be hard to identify what is generated by DB engine itself from other activity. Even you will able do that, still skilled DBA can try to hide from being tracked by generating something similar to standard DB engine activity.
If DBA's have to use built-in account I suggest focus on access vector. The UID chain (on Linux, Unix) will provide information about the full process chain. The DB engine activity will refer to mysql binary and DBA's sessions will come mostly from ssh process. However UID cannot be used in real time monitoring.

You can also consider the non-user related session identification using rule based on number of sql's generated in the defined time. For example - if session generates more than 1000 sql's per second in the first minute the session should be considered as a application one.

------------------------------
Zbigniew Szmigiero
IBM
Warsaw

Original Message:
Sent: Thu October 17, 2019 09:28 AM
From: Herman Engström
Subject: Select Command - MSSQL

Hi,

I want to log SELECT commands performed by DBAs on a MSSQL server. However, when using the (Public) Select Command rule it also include SQL commands generated by the MSSQL server and I am only interested of the DBA input.

If I use the same Select command rule on MySql it only logs the DBA input and nothing else. 

I have used Action: Ignore S-TAP Session and it had some effect on the machine generated logs but if I do a INSERT it generates a lot of logs since the INSERT command uses SELECT commands to perform the INSERT.

Do you have any suggestion how to solve this issue or point me in the right direction?