--禁用C2 審核跟蹤和只限成功的登錄
EXEC sys.sp_configure N'c2 audit mode', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSsqlServer\MSSQLServer', N'AuditLevel', REG_DWORD, 1
GO
SQLSERVER2008新增的審核功能
在sqlserver2008新增了審核功能，可以對伺服器級別和資料庫級別的操作進行審核/審計，事實上，事件通知、更改跟蹤、變更數據捕獲（CDC）
都不是用來做審計的，只是某些人亂用這些功能，也正因為亂用這些功能導致踩坑
事件通知：性能跟蹤
更改跟蹤：用Sync Services來構建偶爾連接的系統
變更數據捕獲（CDC）：數據倉庫的ETL 中的數據抽取（背後使用logreader）
而審核是SQLSERVER專門針對資料庫安全的進行的審核，記住，他是專門的！
② 如何通過SQL語句設置資料庫登錄審核的狀態
剛好上次講三層架構.有現成的例子
以一個驗證登陸為例子
這里是界面層一般叫UIL
protected void Button1_Click(object sender, EventArgs e)
{
List<User> Users = BAL.GetUserInfo(txtUserName.Text,txtPassword.Text);
if(Users.Length > 0)
{
Response.Write("登陸成功");
}
else
{
Response.Write("登陸失敗");
}
}
以下是邏輯層代碼,業務邏輯層一般叫BLL
public static List<User> GetUserInfo(string user,string password)
{
string newPassword = GetMD5Hash(password); //這里對密碼進行加密處理,資料庫中存放的是經過MD5加密後的密,業務邏輯層一般都是處理復雜的邏輯.例如加密邏輯
List<User> Users = DAL.GetUserInfo(user,newPassword);
return Users;
}
以下是數據訪問層代碼,數據訪問層一般叫DAL
public static List<User> GetUserInfo(string user,string password)
{
List<User> Users = new List<User>();
string sql = "select * from User where Password = '"+password+"' and User = '"+user+"'"; //寫where子句的時候把Password放前面.因為Password經過加密,所以可以防止SQL注入攻擊
SqlDataAdapter da = new SqlDataAdapter(sql,"這里是資料庫連接字元串");
DataSet ds = new DataSet();
da.Fill(ds);
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
User user = new User(ds.Tables[0].Rows[i]["ID"].ToString(),ds.Tables[0].Rows[i]["User"].ToString(),ds.Tables[0].Rows[i]["Password"].ToString());
Users.Add(user);
}
return Users;
}
還會有一個Model層.叫做模板層.是數據表結構的印射.Model層是共用層,其他三層都要用到.
比如資料庫中有張表User,裡面有3個欄位ID,User,Password
那麼在模板層中應該有一個類,資料庫中User表的一行對應一個User對象,一張表對應User對象的集合.
public class User
{
string ID;
string User;
string Password;
//重載構造函數
User(string id,string user,string password)
{
this.ID=id;
this.User=user;
this.Password=password;
}
}
③ 如何將伺服器審核事件寫入安全日誌
其他審核位置也受支持，但是更易被篡改。
將 SQL Server 伺服器審核寫入 Windows 安全日誌有兩個關鍵要求：
必須配置審核對象訪問設置以捕獲事件。可根據您的操作系統而採用最佳的配置方法。
在 Windows Vista 和 Windows Server 2008 中，使用審核策略工具 (
auditpol.exe
)。審核策略程序公開了
審核對象訪問
類別中的多種子策略設置。若要允許 SQL Server 審核對象訪問，請配置
應用程序生成的設置。
對於 Windows 的早期版本，審核策略工具不可用。請改用安全策略管理單元 (secpol.msc
)。如果可用，優先採用審核策略，因為可以配置更精細的設置。
SQL Server 服務正在其下運行的帳戶必須擁有
生成安全審核
許可權才能寫入 Windows 安全日誌。默認情況下，LOCAL SERVICE 和 NETWORK SERVICE 帳戶擁有此許可權。如果 SQL Server 正在其中一個帳戶下運行，則不需要此步驟。
將 Windows 審核策略配置為寫入 Windows 安全日誌時，可能會影響 SQL Server 審核，此時若該審核策略配置不正確，就可能導致事件丟失。通常，將 Windows 安全日誌設置為覆蓋較舊的事件。這樣可保留最新的事件。但如果 Windows 安全日誌未設置為覆蓋較舊的事件，則當安全日誌已滿時，系統將發出 Windows 事件 1104（日誌已滿）。此時：
不再記錄其他安全事件
SQL Server 將無法檢測系統是否能夠在安全日誌中記錄事件，從而導致可能丟失審核事件
Box 管理員修復安全日誌後，日誌記錄行為將恢復正常。
SQL Server 計算機的管理員應了解安全日誌的本地設置可能會被域策略覆蓋。在這種情況下，域策略可能覆蓋子類別設置 (
auditpol /get /subcategory:"application generated"
)。這可能會影響 SQL Server 在無法檢測 SQL Server 嘗試審核的事件是否將不被記錄的情況下記錄事件的能力。
④ SQL Server中安全審核級別
審核就是記日誌
無表示不記日誌
成功表示成功的登錄記日誌
失敗表示失敗的登錄記日誌
全部就是所有的登錄操作都記日誌
WIN2000的話在管理工具->事件查看器中看
⑤ 如何開啟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語句，請教。
是ASP嗎？改成這樣子或者合並成一條命令用；隔開
sql="update sjzc set sjmoney=sjmoney+"&price&" where sjUserName='"&sjUserName&"'"
Conn.execute sql
sql="update OrderList set sjsh=1 where sjUserName='"&sjUserName&"'"
Conn.execute sql
sql="update [user] set sjmoney=hymoney-"&price&" where sjUserName='"&sjUserName&"'"
Conn.execute 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")
⑧ 如何自動化完成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指紋也就是程序員編寫新的代碼產生的.
⑨ sql server audit 什麼版本開始
「資料庫審核規范」對象也屬於 SQL Server 審核。針對每個審核，可以為每個 SQL Server 資料庫創建一個資料庫審核規范。
資料庫審核規范可收集由擴展事件功能引發的資料庫級審核操作。可以向資料庫審核規范添加審核操作組或審核事件。「審核事件」是可以由 SQL Server 引擎審核的原子操作。「審核操作組」是預定義的操作組。它們都位於 SQL Server資料庫作用域。這些操作將發送到審核，審核將它們記錄到目標中。