Azure SQL数据库的审核/活动日志 [英] Audit/Activity logs of Azure SQL database

问题描述

当某个用户试图访问Azure SQL数据库中他/她没有任何访问权限的任何表时，我是否可以通过该方式触发警报并获得以下错误：

Is there any way by which I can have an alert triggered whenever some user tries to access any table in Azure SQL database for which he/she doesnt have any access and gets the below error:

对象'People'，数据库'POCDB'，架构'HighlyConfidential'拒绝SELECT权限。

The SELECT permission was denied on the object 'People', database 'POCDB', schema 'HighlyConfidential'.

可以捕获这些查询日志吗？

Can these query logs be captured?

推荐答案

嗨Nandan Hegde，



AFAIK可以按照以下步骤进行。

Hi Nandan Hegde,

AFAIK it's possible by following the below steps.

1。创建（或使用已存在的）日志分析工作区，如
所述这里。

2.按照
这里。确保选择日志目标作为Log Analytics并提供日志分析工作区名称。

3.尝试您的要求，即尝试访问Azure SQL数据库的任何用户没有的表任何访问。

4.转到日志分析工作区，路由到日志磁贴并运行如下所示的查询以获取相关日志。请注意，xxxxxxxxxxxxxxxxxxxx应替换为您的错误内容，例如"选择权限被拒绝对象上的
"等。

1. Create (or use already existing) log analytics workspace as explained here.
2. Enable SQL database auditing as explained here. Make sure you select logs destination as Log Analytics and provide your log analytics workspace name.
3. Try your requirement i.e., try to access any table of your Azure SQL database for which a user doesn't have any access.
4. Go to your log analytics workspace, route to Logs tile and run query something like shown below to fetch related logs. Note that xxxxxxxxxxxxxxxxxxxx should be replaced with the content of your error i.e., something like "the select permission was denied on the object", etc.

AzureDiagnostics| where Category == 'SQLSecurityAuditEvents'| where additional_information_s contains "xxxxxxxxxxxxxxxxxxxx" | summarize AggregatedValue= any(additional_information_s) by Computer, bin(TimeGenerated, 30s) 

5.检查上述查询的输出确保在日志中捕获错误。

6.按照此处的说明创建日志分析警报
here



希望这有帮助!!

5. Check the output of the above query to make sure the error is captured in the logs.
6. Create a log analytics alert as explained here here

Hope this helps!!

这篇关于Azure SQL数据库的审核/活动日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！