"Serial" Database audit products common defects (3) Multiple statements cannot be effectively segmented

In the last period, we introduce the problems of the long SQL statements of database audit products, this period, An Huaqin and the effective segmentation of multi-statement database Audit products common defects. Multiple statements are a specific case on SQL Server. In other database management systems, there are clear segmentation identifiers between statements, and there can be no explicit delimiters between statements in SQL serve. Here is an example:

Use OpenCms SET NOCOUNT on select * Fromopencms.opencms.CMS_LOG where 1=1 or ' a ' = ' B ' SELECT * Fromopencms.opencms.CMS_HIS Tory_projects where 1=1

There is no explicit delimited identifier between these statements, such as ; they actually represent four statements:

Use OpenCms

SET NOCOUNT on

SELECT * from Opencms.opencms.CMS_LOGwhere 1=1 or ' a ' = ' B '

SELECT * Fromopencms.opencms.CMS_HISTORY_PROJECTS where 1=1

SQL Server organizes these statements in a database communication package without splitting them, and for some less specialized database audit products, these statements are audited as a single statement. The efficient implementation of multi-statement segmentation requires very specialized SQL parsing techniques. Some simple methods, such as SELECT, use, set such as the keyword to split the sentence, a little more complex situation is not good to deal with, the following example, cannot be processed in this simple way:

Select * from t1 where t1.col1 = 1 Unionselect * from t2 where t2.col1 = 1 select* from t2 where t2.col1 = 1

Even with slightly more sophisticated techniques, such as regular expressions, it is difficult to cut accurately, and non-specialized database audit products have this flaw. The failure to accurately cut multi-statement defects, in different products, the performance of different, the resulting audit problems are different, but can be summed up as follows:

(1) in Audit records, the SQL operation type of each statement can not be accurately recorded, resulting in some high-risk operations can not be effectively identified or alarm, such as drop, truncate these statements.

(2) in Audit records, the database object of each SQL statement can not be accurately recorded, resulting in access to sensitive objects is not effectively recognized or alarm.

(3) In the audit record, can not accurately record each statement whether the success of the execution; For example, the first statement in multiple statements executed successfully, the subsequent statement execution failed, often the overall record as a result, often recorded results are successful.

(4) In the audit record, can not accurately feedback the impact of each statement caused by the number of rows, and thus can not trigger the security policy based on the impact of the row; often recorded is the first statement of the impact of the row, the rest of the statement affected rows are ignored.

The following results are An Huaqin and professional database audit product execution results, the product accurately realize the multi-statement cutting, while accurately audit the execution of each statement success or not and affect the line:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6E/AC/wKioL1WCjISgKSGRAAHQRw1vLeA991.jpg "title=" 001t9c8mzy6st35uur52b.jpg "alt=" Wkiol1wcjisgksgraahqrw1vlea991.jpg "/>

Above results, according to the order of the statement sent inverted display, while auditing each statement of the operands, SQL statements, execution success or not, affecting the number of rows.

Anwarking's database audit experts have prepared use cases to help readers verify that the database audit product is capable of completing multi-statement cuts, accurately auditing the Access object, the return result, and the impact line for each statement:

Use case one (a very basic multi-statement example)

INSERT Into#am_dbfileio (collection_time,total_io_bytes) VALUES (@CURRENT_COLLECTION_TIME, @CURRENT_TOTAL_IO_MB)

Insert #t1 (object_id, object_type, rank)

Select t.relative_id, T.relative_type, 1 from #t1 T wheret.relative_id No in (select t2.object_id from #t1 T2 where not T2.object_idis null)

Use case two (a slightly more complex point, a cursor-related multi-statement example):

DECLARE tables_cursor cursorforselect name from sysobjects WHERE type = ' U ' OPEN tables_cursordeclare @tablename Sysnamefet CH NEXT from Tables_cursor to @tablenameWHILE (@ @FETCH_STATUS <>-1) beginexec (' DROP TABLE ' + @tablename) FETCH NE XT from Tables_cursor to @tablenameENDPRINT ' all user-defined tables has been dropped from the database. Deallocate tables_cursor;

Use case three: (a more complex example of a multi-statement)

SET NOCOUNT on;--Check Agent statuscreate TABLE #TempAgentStatus (agentstatus nvarchar ()) IF (Is_srvrolemember (' SysAdmin ') > 0) INSERT #TempAgentStatus (agentstatus) EXEC master: Xp_servicecontrol n ' querystate ', n ' SQLServerAgent '; Elseinsert #TempAgentStatus (Agentstatus)--Sometimes this check doesn ' t work-there is complains on the Wwwselect ' Runn ing ' from master.dbo.sysprocesses WHERE program_name liken ' sqlagent%generic%refresher% ' DECLARE @AgentRunning int; SET @AgentRunning = 0SELECT @AgentRunning =1 from #TempAgentStatus where CharIndex (N ' running ', Lower (agentstatus)) > 0D ROP TABLE #TempAgentStatus-End Check Agent statusdeclare @can_see_all_running_jobs int; SET @can_see_all_running_jobs = ISNULL (is_srvrolemember (N ' sysadmin '), 0);D eclare @current_execution_status int;d eclare @job_owner sysname; SET @job_owner = SUSER_SNAME (); CREATE TABLE #xp_results (job_id uniqueidentifier not null,last_run_date int not null,last_run_time int. not Null,next_run_ Date int not null,next_run_timeint not null,next_run_schedule_id int not Null,requested_to_run int. not NULL,--Boolrequest_source int not null,request_s ource_id sysname COLLATE database_default null,running int not NULL,--Boolcurrent_step int. not Null,current_retry_attemp t int not null,job_state int is not NULL), IF @AgentRunning = 1BEGININSERT into #xp_resultsEXECUTE master.dbo.xp_sqlagent_enum _jobs @can_see_all_running_jobs, @job_owner; Endselect DistinctCount (1) FROMmsdb.dbo.sysjobs_view obj left OUTER JOIN msdb.dbo.sysjobservers js onjs.job_id = obj.job_ Idleft OUTER JOIN msdb.dbo.sysjobhistory jh on (jh.job_id = obj.job_id) and (jh.step_id = 0) and (jh.instance_id = (SELECT M Ax (ijh.instance_id) from msdb.dbo.sysjobhistory ijhwhere ijh.job_id = obj.job_id)) left OUTER JOIN #xp_results res on Js.jo b_id = Res.job_iddrop TABLE #xp_results;

This article is from the Database security blog, so be sure to keep this source http://schina.blog.51cto.com/9734953/1663254

"Serial" Database audit products common defects (3) Multiple statements cannot be effectively segmented