1. sql ser2005怎麼開啟用戶操作審計
sql
ser2005怎麼開啟用戶操作審計
:
語法
create
server
audit
[Audit_Name]
to
file
(filepath=N'c:\aa\')
必須是在master資料庫執行
2. 如何自動化完成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指紋也就是程序員編寫新的代碼產生的.
3. 查詢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)
4. SQL 審計是否會影響實例性能
就是多插入一條審計數據
是否開啟是看你是否准備審計數據變化
如果需要，開啟審計功能肯定比你再去寫個軟體來做這個事情要高效和全面
如果只是想開啟來審計誤操作，那可能不適合，不如通過其他網鏡軟體來抓包實現
另外，審計有幾種策略，不必每個數據動作都要審計，關鍵看你的關注需求。
歸檔模式在生產上還是需要的吧。
5. 如何開啟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里看到相關的審核
6. 掌握sql語言對開展審計信息化有何作用
直接針對資料庫而不是針對系統進行操作。這樣審計的時候就能規避系統邏輯直接訪問資料庫查看數據層面上是否有作假。關聯的表是否能對得上，是否有直接對資料庫的刻意刪除記錄信息等了。
7. 掌握sql語言對開展審計信息化有何作用
掌握SQL你可以按照個人需要，快速從原始數據中篩選需要的數據。這還和個人的SQL水平有關
祝好運，望採納
8. 如何開啟和關閉oracle資料庫中的審計功能
在oracle11g中，資料庫的審計功能是默認開啟的（這和oracle10g的不一樣，10g默認是關閉的），
oracle11gr2的官方文檔上寫的是錯的，當上說default是none，而且是審計到db級別的，這樣就會
往aud$表裡記錄統計信息。
1.如果審計不是必須的，可以關掉審計功能；
sql>
show
parameter
audit_trail;
name
type
value
------------------------------------
-----------
------------------------------
audit_trail
string
db
sql>
alter
system
set
audit_trail=none
scope=spfile;
sql>
shut
immediate;
sql>startup
2.刪除已有的審計信息
可以直接truncate表aud$,
truncate
table
sys.aud$;
3.或者將aud$表移到另外一個表空間下，以減少system表空間的壓力和被撐爆的風險。
附：11g中有關audit_trail參數的設置說明：
audit_trail
property
description
parameter
type
string
syntax
audit_trail
=
{
none
|
os
|
db
[,
extended]
|
xml
[,
extended]
}
default
value
none
modifiable
no
basic
no
audit_trail
enables
or
disables
database
auditing.
values:
none
disables
standard
auditing.
this
value
is
the
default
if
the
audit_trail
parameter
was
not
set
in
the
initialization
parameter
file
or
if
you
created
the
database
using
a
method
other
than
database
configuration
assistant.
if
you
created
the
database
using
database
configuration
assistant,
then
the
default
is
db.
os
directs
all
audit
records
to
an
operating
system
file.
oracle
recommends
that
you
use
the
os
setting,
particularly
if
you
are
using
an
ultra-secure
database
configuration.
db
directs
audit
records
to
the
database
audit
trail
(the
sys.aud$
table),
except
for
records
that
are
always
written
to
the
operating
system
audit
trail.
use
this
setting
for
a
general
database
for
manageability.
if
the
database
was
started
in
read-only
mode
with
audit_trail
set
to
db,
then
oracle
database
internally
sets
audit_trail
to
os.
check
the
alert
log
for
details.
db,
extended
performs
all
actions
of
audit_trail=db,
and
also
populates
the
sql
bind
and
sql
text
clob-type
columns
of
the
sys.aud$
table,
when
available.
these
two
columns
are
populated
only
when
this
parameter
is
specified.
if
the
database
was
started
in
read-only
mode
with
audit_trail
set
to
db,
extended,
then
oracle
database
internally
sets
audit_trail
to
os.
check
the
alert
log
for
details.
xml
writes
to
the
operating
system
audit
record
file
in
xml
format.
records
all
elements
of
the
auditrecord
node
except
sql_text
and
sql_bind
to
the
operating
system
xml
audit
file.
xml,
extended
performs
all
actions
of
audit_trail=xml,
and
populates
the
sql
bind
and
sql
text
clob-type
columns
of
the
sys.aud$
table,
wherever
possible.
these
columns
are
populated
only
when
this
parameter
is
specified.
you
can
use
the
sql
audit
statement
to
set
auditing
options
regardless
of
the
setting
of
this
parameter.
9. 掌握sql語言對開展電子數據審計有何作用
審計涵蓋的業務數據一是信息系統架構。信息系統是以B/S為主體結構、由多個管理子系統相結合的分布式網路管理系統.採用的是Oracle資料庫,各子系統具有一定的獨立性.同時.資料庫之間通過一定的約束進行關聯。此次審計主要針對權屬交易子...
關鍵詞
質量審計 / 業務數據 / SQL語句 / Or
10. SQL Server在審計工作中的應用
1、熟悉該單位所使用的財務軟體的資料庫結構，例如：金蝶、用友等
2、本身具備強大的SQL語句功底，可以使用SQL語句對其財務表內的數據進行匯總統計
3、依據財務常識與審計中對各個財務科目的數據進行比對，發現其中問題，然後再追朔其具體帳目的單據
4、不同種類的企業，在其財務流中，均會不同程度存在違法違紀問題，只要在審計過程中仔細認真核對其數據即可，另外，也要對其數據與財務憑證做細致對比
願你成功！