‘壹’ 如何自动化完成sql审核
sql审核主要完成两方面的目的.
1、避免性能太差的sql进入生产系统,导致整体性能降低
2、检查开发设计的索引是否合理,是否需要添加索引
第一点是SQL审核最核心的地方,避免乱七八糟的sql影响线上性能,甚至导致线上系统崩溃.
第二点是属于建模的范畴,要解决建模的最好办法是DBA参与项目前期审核,由DBA建模,如果DBA人力资源不足,那么就定期由DBA对开发人员进行培训.然后发现建模太烂的就扣KPI.
现在很多公司都是人肉来完成SQL审核的,人肉审核对dba的要求较高,需要懂一些代码,另外是费时费力,毕竟一般公司几十个开发,对应一个DBA，而且DBA还要干很多其他的事情.
如何将DBA从人肉SQL审核中解放出来呢?
思路其实很简单:
1、获取程序要执行的SQL
2、对要执行的SQL做分析,可以加各种分析条件来判断这个SQL是否可以自动审核通过,未通过审核的需要人工处理.
3、配合后期的慢查询日志分析系统完成长期的监控.
开源的解决方案主要有淘宝丹臣sqlautoreview系统.可以在github上搜索到.
但是这个系统主要是基于java sqlmapfile.xml解决自动创建索引的问题,对源数据有要求,并且是通过解析SQL结构来假设SQL的执行计划,不是特别准确,并且不能够很好的区分新sql还是老sql.
所以产生了一个新的方案:
1、为所有的执行过的sql产生一个figerprint
2、基于慢查询提供的数据,加上explain 提供的数据来判断这个sql的性能是否可接受,或者可优化.
3、自动审核通过性能可接受的部分,给DBA展示性能较差的sql,然后进行优化.
方案的优点在于:
基于用户真正执行的SQL,并且可以观察SQL执行频率.
基于MySQL真正的执行计划和执行结果,分析更准确.
每个SQL都有一个fingerprint,只需要增量处理新加的SQL,效率和性能提高.
基于Box anemometer二次开发,让慢查询和sql审核同平台,增加工具集成性,提高用户体验(DBA和开发人员)。
方案实施:
既然咱是DBA，肯定会有更DBA的思维方式.基于现有软件二次开发完成,减少开发成本,整合管理平台.
基于Box anemometer.安装Box anemometer
Box anemometer是一款B/S架构,图形化的MySQL慢查询分析工具.功能强大易用,设计简单直接.anemometer是基于pt-query-digest的二次封装得来.
核心处理流程:
mysql node–>计划任务通过pt-query-digest收集慢查询信息–>结果写入到数据库中–>anemometer按条件去展示慢查询的结果,并且提供了图形化和趋势分布图等功能.
所以anemometer已经帮我们完成了数据收集,包括每个sql的fingerprint信息,以及相关的信息,我们在测试环境,基于anemometer,将long_query_time设置为0,就可以收集到所以的SQL及相关信息.
在我们收集到所有SQL以后,我们就要来分析这个SQL是否可以自动审核通过.这里开始我们就要定制了.
定制内容如下:
一、
设置一个单独的datasources,可以命名为audit_sql.
这个datasources里面只放置开发环境或者测试环境的慢查询(你要做sql审核基于哪个环境),将此环境的long_query_time设置为0,接收所有的sql查询.
二、修改anemometer
ALTER TABLE `global_query_review` ADD audit_status VARCHAR(255) NOT
NULL DEFAULT ‘refuse’ comment ‘sql审计的状态 refuse未通过 pass审核通过’;
修改PHP代码.
在report模块的where条件中增加一个Ait Status的选项框,可以过滤audit_status的状态
在show_query模块中增加一个Audit Status的选项框,可以人工设置audit_status的状态
三、增加两个额外的脚本,准实时的分析audit_status为refuse的sql,如果sql的满足自动审核通过的条件,那么就设置audit_status为pass,表示自动审核通过.
自动审核未通过的sql,由DBA人工在anemometer上检索和处理.
这里就涉及到一个自动审核通过的算法:
算法分两种.
第一种是准实时,也就是可以几分钟或者一个小时运行一次,主要是根据每个sql的执行效率判断是否pass.
对应的脚本名字叫做:audit_sql.py
第二种是一天一次,弱化执行效率判断,增加一天执行的频率判断.
对应的脚本名字叫做:audit_sql_day.py
各家根据自己的实际情况调整或者优化这两个脚本.
至此,你已经可以让99%以上的代码自动审核通过了,审核不通过的代码你可以让开发自己来tracking也可以主动推给开发.
对于才搭建的环境,可能会有一些乱七八糟的sql,不过使用一段时间稳定以后,异常的sql指纹都有了,那么每天产生的sql指纹就比较少了,而这部分SQL指纹也就是程序员编写新的代码产生的.
‘贰’ 如何利用SQL2000数据库中的表进行审计跟踪，进入如下界面，如图
什么意思？
‘叁’ 为什么要数据库审计
安全现状分析
随着计算机和网络技术发展，信息系统的应用越来越广泛。数据库做为信息技术的核心和基础，承载着越来越多的关键业务系统，渐渐成为商业和公共安全中最具有战略性的资产，数据库的安全稳定运行也直接决定着业务系统能否正常使用。近年来，数据泄漏事件频发，涉及各行各业，据Verizon数据泄漏调查报告显示，2013年泄漏事件1347起，2014年泄漏事件1367起，2015年泄漏事件2122起，2016年泄漏事件2260起，2018年泄漏事件2216起等，事件数据量居高不下。数据库系统是一个复杂而又关键的系统，数据库存在各种管理和技术上的风险，对企业来说，数据库安全存在的众多安全隐患：
1、内部人员面临的安全隐患
随着企业信息化进程不断深入，企业的业务系统变得日益复杂，由内部员工违规操作导致的安全问题变得日益突出起来。防火墙、防病毒等常规的安全产品可以解决部分安全问题，但对于内部人员在数据库服务器上直接进行的违规操作却无能为力。
2、第三方维护人员的威胁
企业在发展的过程中，越来越多的会将非核心业务外包给设备商或者其他专业代维公司。如何有效地管控第三方维护人员的操作行为，并进行严格审计是企业面临的一个关键问题。
3、违规行为无法控制的风险
管理人员总是试图定义各种操作条例，来规范内部员工的访问行为，但始终会造成恶性后果，且无法定责，没有更好的方式来限制员工的合规操作。而事后追查，只能是亡羊补牢。
4、系统日志不能发现全部安全隐患
我们经常从各种系统日志里面去发现是否有入侵后留下来的“蛛丝马迹”来判断是否发生过安全事件。但是，系统往往是在经历了大量的操作和变化后，才逐渐变得不安全。另外的情况是，用户通过登录业务服务器来访问数据库等核心资产，单纯的分析业务系统或者数据库系统的日志，都无法对整个访问过程是否存在风险进行判断。
5、系统崩溃带来审计结果的丢失
一般来说，数据库系统都会存储操作日志，也能开启审计模块对访问进行审计，但是一旦有意外发生导致系统的崩溃，这些审计日志也随之消失，管理人员无法得知系统到底发生了什么。
安全需求分析
1、数据库全面和精确审计需求
在数据库中，存在各种各样的访问行为，来自B/S、C/S终端等，数据库审计需要能够进行全面的审计；大部分业务操作语句相似度较高，需要精确审计操作行为，识别真正的操作人、操作工具、终端等。企业中不同业务可能会使用不同数据库，导致数据结构和关注点不一致，需要统一规则。
2、事前预警、事后追责溯源需求
数据库审计是为保证企业数据安全的手段之一，需要从大量的的访问行为中分析提出威胁事件进行实时事前预警。若事件发生，需要通过数据库访问行为等信息定位到真实的责任人、实际操作等。
3、海量访问行为的快速审计需求
企业中数据库面临海量的访问操作，相对于后台操作管理，业务访问流量更加巨大，数据库审计需要能够支撑在大数据中进行分析和审计，并进行提供实时分析结果，为告警提供支撑。
4、遵循法规，提供报表需求
数据库审计需要遵循法律法规，符合合规需求，并需要更具法规提供分析报表，简化审计管理工作。
因此针对以上分析，美创科技推出新版数据库审计
‘肆’ 如何开启sqlserver2008数据库审计功能
SQLSERVER2008新增的审核功能
在sqlserver2008新增了审核功能，可以对服务器级别和数据库级别的操作进行审核/审计，事实上，事件通知、更改跟踪、变更数据捕获（CDC）
都不是用来做审计的，只是某些人乱用这些功能，也正因为乱用这些功能导致踩坑
事件通知：性能跟踪
更改跟踪：用Sync Services来构建偶尔连接的系统
变更数据捕获（CDC）：数据仓库的ETL 中的数据抽取（背后使用logreader）
而审核是SQLSERVER专门针对数据库安全的进行的审核，记住，他是专门的！
我们看一下审核的使用方法
审核对象
步骤一：创建审核对象，审核对象是跟保存路径关联的，所以如果你需要把审核操作日志保存到不同的路径就需要创建不同的审核对象
我们把审核操作日志保存在文件系统里，在创建之前我们还要在相关路径先创建好保存的文件夹，我们在D盘先创建sqlaudits文件夹，然后执行下面语句
--创建审核对象之前需要切换到master数据库
USE [master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(FILEPATH='D:\sqlaudits') --这里指定文件夹不能指定文件，生成文件都会保存在这个文件夹
GO
实际上，我们在创建审核对象的同时可以指定审核选项，下面是相关脚本
把日志放在磁盘的好处是可以使用新增的TVF:sys.[fn_get_audit_file] 来过滤和排序审核数据，如果把审核数据保存在Windows 事件日志里查询起来非常麻烦
USE [master]
GO
CREATE SERVER AUDIT MyFileAudit TO FILE(
FILEPATH='D:\sqlaudits',
MAXSIZE=4GB,
MAX_ROLLOVER_FILES=6)
WITH (
ON_FAILURE=CONTINUE,
QUEUE_DELAY=1000);
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
MAXSIZE：指明每个审核日志文件的最大大小是4GB
MAX_ROLLOVER_FILES：指明滚动文件数目，类似于SQL ERRORLOG，达到多少个文件之后删除前面的历史文件，这里是6个文件
ON_FAILURE：指明当审核数据发生错误时的操作，这里是继续进行审核，如果指定shutdown，那么将会shutdown整个实例
queue_delay：指明审核数据写入的延迟时间，这里是1秒，最小值也是1秒，如果指定0表示是实时写入，当然性能也有一些影响
STATE：指明启动审核功能，STATE这个选项不能跟其他选项共用，所以只能单独一句
在修改审核选项的时候，需要先禁用审核，再开启审核
ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
ALTER SERVER AUDIT MyFileAudit WITH(QUEUE_DELAY =1000)
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
审核规范
在SQLSERVER审核里面有审核规范的概念，一个审核对象只能绑定一个审核规范，而一个审核规范可以绑定到多个审核对象
我们来看一下脚本
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
CREATE SERVER AUDIT MyAppAudit TO APPLICATION_LOG
GO
ALTER SERVER AUDIT MyAppAudit WITH(STATE =ON)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
GO
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyAppAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
我们创建一个服务器级别的审核规范CaptureLoginsToFile，然后再创建多一个审核对象MyAppAudit ，这个审核对象会把审核日志保存到Windows事件日志的应用程序日志里
我们禁用审核规范CaptureLoginsToFile，修改审核规范CaptureLoginsToFile属于审核对象MyAppAudit ，修改成功
而如果要把多个审核规范绑定到同一个审核对象则会报错
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileA
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFileB
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
--消息 33230，级别 16，状态 1，第 86 行
--审核 'MyFileAudit' 的审核规范已经存在。
这里要说一下 ：审核对象和审核规范的修改 ，无论是审核对象还是审核规范，在修改他们的相关参数之前，他必须要先禁用，后修改，再启用
--禁用审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =OFF)
--禁用服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
GO
--禁用数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=OFF)
GO
--相关修改选项操作
--启用审核对象
ALTER SERVER AUDIT MyFileAudit WITH(STATE =ON)
--启用服务器级审核规范
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)
GO
--启用数据库级审核规范
ALTER DATABASE AUDIT SPECIFICATION CaptureDBLoginsToFile WITH (STATE=ON)
GO
审核服务器级别事件
审核服务级别事件，我们一般用得最多的就是审核登录失败的事件，下面的脚本就是审核登录成功事件和登录失败事件
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (failed_login_group),
ADD (successful_login_group)
WITH (STATE=ON)
GO
修改审核规范
--跟审核对象一样，更改审核规范时必须将其禁用
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =OFF)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
ADD (login_change_password_gourp),
DROP (successful_login_group)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE =ON)
GO
审核操作组
每个审核操作组对应一种操作，在SQLSERVER2008里一共有35个操作组，包括备份和还原操作，数据库所有权的更改，从服务器和数据库角色中添加或删除登录用户
添加审核操作组的只需在审核规范里使用ADD，下面语句添加了登录用户修改密码操作的操作组
ADD (login_change_password_gourp)
这里说一下服务器审核的内部实际上使用的是SQL2008新增的扩展事件里面的其中一个package：SecAudit package，当然他内部也是使用扩展事件来收集服务器信息
审核数据库级别事件
数据库审核规范存在于他们的数据库中，不能审核tempdb中的数据库操作
CREATE DATABASE AUDIT SPECIFICATION和ALTER DATABASE AUDIT SPECIFICATION
工作方式跟服务器审核规范一样
在SQLSERVER2008里一共有15个数据库级别的操作组
7个数据库级别的审核操作是：select ,insert,update,delete,execute,receive,references
相关脚本如下：
--创建审核对象
USE [master]
GO
CREATE SERVER AUDIT MyDBFileAudit TO FILE(FILEPATH='D:\sqldbaudits')
GO
ALTER SERVER AUDIT MyDBFileAudit WITH (STATE=ON)
GO
--创建数据库级别审核规范
USE [sss]
GO
CREATE DATABASE AUDIT SPECIFICATION CaptureDBActionToEventLog
FOR SERVER AUDIT MyDBFileAudit
ADD (database_object_change_group),
ADD (SELECT ,INSERT,UPDATE,DELETE ON schema::dbo BY PUBLIC)
WITH (STATE =ON)
我们先在D盘创建sqldbaudits文件夹
第一个操作组对数据库中所有对象的DDL语句create，alter，drop等进行记录
第二个语句监视由任何public用户（也就是所有用户）对dbo架构的任何对象所做的DML操作
创建完毕之后可以在SSMS里看到相关的审核
‘伍’ SQL 审计是否会影响实例性能
就是多插入一条审计数据
是否开启是看你是否准备审计数据变化
如果需要，开启审计功能肯定比你再去写个软件来做这个事情要高效和全面
如果只是想开启来审计误操作，那可能不适合，不如通过其他网镜软件来抓包实现
另外，审计有几种策略，不必每个数据动作都要审计，关键看你的关注需求。
归档模式在生产上还是需要的吧。
‘陆’ 查询SQL审计功能是否开启
mysql服务器自身没有提供审计功能,但是我们可以使用init-connect+binlog的方法进行mysql的操作审计。由于mysqlbinlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connection_id但是却没有记录connection_id对应的详细用户信息。在后期审计进行行为追踪时，根据binlog记录的行为及对应的connection-id结合之前连接日志记录进行分析，得出最后的结论。1.设置init-connect1.1创建用于存放连接日志的数据库和表createdatabaseaccesslog;CREATETABLEaccesslog.accesslog(`id`int(11)primarykeyauto_increment,`time`timestamp,`localname`varchar(30),`matchname`varchar(30))1.2创建用户权限可用现成的root用户用于信息的读取grantselectonaccesslog.*toroot;如果存在具有to*.*权限的用户需要进行限制。这里还需要注意用户必须对accesslog表具有insert权限grantselectonaccesslog.*touser@’%’;1.3设置init-connect在[mysqld]下添加以下设置：init-connect=’insertintoaccesslog.accesslog(id,time,localname,matchname)values(connection_id(),now(),user(),current_user());’------注意user()和current_user()的区别log-bin=xxx这里必须开启binlog1.4重启数据库生效shell>/etc/init.d/mysqlrestart2.记录追踪2.1thread_id确认可以用以下语句定位语句执行人Tencent:~#mysqlbinlog--start-datetime='2011-01-2616:00:00'--stop-datetime='2011-01-2617:00:00'/var/lib/mysql/mysql-bin.000010|grep-B5'wsj'COMMIT/*!*/;#at767#11012616:16:43serverid1end_log_pos872Querythread_id=19exec_time=0error_code=0usetest/*!*/;SETTIMESTAMP=1296029803/*!*/;createtablewsj(idintunsignednotnull)--BEGIN/*!*/;#at940#11012616:16:57serverid1end_log_pos1033Querythread_id=19exec_time=0error_code=0SETTIMESTAMP=1296029817/*!*/;insertintowsj(id)values(1)--BEGIN/*!*/;#at1128#11012616:16:58serverid1end_log_pos1221Querythread_id=19exec_time=0error_code=0SETTIMESTAMP=1296029818/*!*/;insertintowsj(id)values(2)2.2用户确认thread_id确认以后，找到元兇就只是一条sql语句的问题了。mysql>select*fromaccesslogwhereid=19;+----+---------------------+---------------------+-----------+|id|time|localname|matchname|+----+---------------------+---------------------+-----------+|19|2011-01-2616:15:54|[email protected]|test@%|+----+---------------------+---------------------+-----------+1rowinset(0.00sec)
‘柒’ SQL审计是否可以提高数据库的安全性能
可以
‘捌’ sql server 2005 如何查看审计信息
查看与常规 SQL Server 活动相关的日志
在对象资源管理器中，依次展开“管理”和“SQL Server 日志”，再双击“当前 <日期/时间>”，将显示 SQL Server、“SQL 代理”和“Windows 事件”日志。
查看与作业相关的日志
在对象资源管理器中，展开“SQL Server 代理”，右键单击“作业”，再单击“查看历史记录”，此时将显示“作业历史记录”和“SQL 代理”日志。
查看与维护计划相关的日志
在对象资源管理器中，展开“管理”，右键单击“维护计划”，再单击“查看历史记录”，此时将显示“维护计划”、“作业历史记录”和“SQL 代理”日志。
查看与审核集合相关的日志
在对象资源管理器中，依次展开“安全”和“审核”，右键单击一个审核，然后单击“查看审核日志”。
查询SQL错误日志扩展存储过程xp_readerrorlog
xp_readerrorlog 一共有7个参数：
1. 存档编号
2. 日志类型(1为SQL Server日志,2为SQL Agent日志)
3. 查询包含的字符串
4. 查询包含的字符串
5. LogDate开始时间
6. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc")
7. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc")
‘玖’ oracle 11g开启了自身审计，dba_audit_trail中的SQL_TEXT列为null
看下ACTION_NAME，像LOGON之类的操作没有SQL语句，SQL_TEXT就是空
‘拾’ SQL Server在审计工作中的应用
1、熟悉该单位所使用的财务软件的数据库结构，例如：金蝶、用友等
2、本身具备强大的SQL语句功底，可以使用SQL语句对其财务表内的数据进行汇总统计
3、依据财务常识与审计中对各个财务科目的数据进行比对，发现其中问题，然后再追朔其具体帐目的单据
4、不同种类的企业，在其财务流中，均会不同程度存在违法违纪问题，只要在审计过程中仔细认真核对其数据即可，另外，也要对其数据与财务凭证做细致对比
愿你成功！