Hi,
General exclusion INSERT and DELETE and even DROP operations is very restrict policy. DB Tools very often creates temporary objects for gathering auxiliary information.
I suggested before the correct solution - create blocking policy per
sensitive objects instead general one or add separate rules base of SQL pattern to avoid blocking some standard SQL's generated by tools.
Be aware that second approach will lead to policy with hundreds of rules and it must be reviewed every time when you tool is upgraded.
------------------------------
Zbigniew (Zibi) Szmigiero
IBM
Międzyrzecz
------------------------------
Original Message:
Sent: Tue November 24, 2020 07:02 AM
From: PHANENDRA RAO CHAVANA
Subject: MS-SQL Commands Exclusion During Login
Hi Zibi,
I have created a policy for MSSQL server to block some commands like INSERT,DELETE,DROP etc.
It blocks me without logging in to database server through SQL server management studio since some commands are running in background.
I want to exclude the queries that runs in background. how can I exclude it in blocking policy?
Thanks,
Panendar Rao.C
------------------------------
PHANENDRA RAO CHAVANA
Original Message:
Sent: Tue November 24, 2020 03:35 AM
From: Zbigniew (Zibi) Szmigiero
Subject: MS-SQL Commands Exclusion During Login
Hi,
I assume that you tried to create whitelisting policy - only defined list of SQL instructions are allowed.
I think that this approach will fail because it is almost impossible to identify all possible SQL generated in the background by tools like MSSQL management studio.
Additionally sp_executesql opens access to execute any SQL command on your system.
I rather suggest based on user identitfy create policy which block DML or DDL on sensitive tables than try to tune your policy this way.
It is not clear what would you like to achieve using group? Groups can be used to identify commands. In you case you must look for pattern where you provide REGEXP to identify particular SQL body.
------------------------------
Zbigniew (Zibi) Szmigiero
IBM
Międzyrzecz
Original Message:
Sent: Mon November 23, 2020 09:36 PM
From: PHANENDRA RAO CHAVANA
Subject: MS-SQL Commands Exclusion During Login
Hi all,
I want to exclude some commands running in background while I login MS-SQL server management studio.
Some commands are blocking automatically while i operate the things in MS-SQL database.
I created a group and tried to exclude some commands but they are not working properly.
Could any suggest how to put in group builder?
Queries running in background:
DECLARE @edition sysname;
SET LOCK_TIMEOUT 10000
SELECT case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType',
Commands added in groupbuilder for exclusion
DECLARE @edition%
SET LOCK_TIMEOUT%
SELECT case%
set quoted_identifier%
sp_executesql%]
Thanks,
Panendar Rao.C
------------------------------
PHANENDRA RAO CHAVANA
------------------------------