Skip to content

Commit 48138be

Browse files
author
Aruneko
authored
fix(azure): Improve query of CIS v1.3.0 Section 4.2.1 (#10362)
#### Summary I noticed that the current query of CIS v1.3.0 Section 4.2.1 checks the ATP (Advanced Threat Protection) status by SQL databases. But the original document says that the we have to check the status by SQL Servers. In addition, even though I have enabled the ATP settings on my Azure SQL Server, current query always returns `fails`, because the status is managed by not database level but server level. To solve the problem, I have added a new table `azure_sql_server_advanced_threat_protection_settings` to store the status, and use the table to check the policy.
1 parent f28795d commit 48138be

10 files changed

Lines changed: 111 additions & 6 deletions

File tree

‎plugins/source/azure/docs/tables/README.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,7 @@
278278
- [azure_sql_managed_instance_vulnerability_assessments](../../../../../website/tables/azure/azure_sql_managed_instance_vulnerability_assessments.md)
279279
- [azure_sql_servers](../../../../../website/tables/azure/azure_sql_servers.md)
280280
- [azure_sql_server_admins](../../../../../website/tables/azure/azure_sql_server_admins.md)
281+
- [azure_sql_server_advanced_threat_protection_settings](../../../../../website/tables/azure/azure_sql_server_advanced_threat_protection_settings.md)
281282
- [azure_sql_server_blob_auditing_policies](../../../../../website/tables/azure/azure_sql_server_blob_auditing_policies.md)
282283
- [azure_sql_server_databases](../../../../../website/tables/azure/azure_sql_server_databases.md)
283284
- [azure_sql_server_database_blob_auditing_policies](../../../../../website/tables/azure/azure_sql_server_database_blob_auditing_policies.md)

‎plugins/source/azure/policies/queries/sql/atp_on_sql_server_disabled.sql‎

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7,11 +7,8 @@ SELECT
77
s.subscription_id,
88
s.id AS server_id,
99
case
10-
when p.properties->>'state' is distinct from 'Enabled'
10+
when atp.properties->>'state' is distinct from 'Enabled'
1111
then 'fail' else 'pass'
1212
end
1313
FROM azure_sql_servers s
14-
LEFT JOIN azure_sql_server_databases d ON
15-
s._cq_id = d._cq_parent_id
16-
LEFT JOIN azure_sql_server_database_threat_protections p ON
17-
d._cq_id = p._cq_parent_id
14+
JOIN azure_sql_server_advanced_threat_protection_settings atp ON s._cq_id = atp._cq_parent_id

‎plugins/source/azure/resources/services/sql/server_advanced_threat_protection_settings.go‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
package sql
2+
3+
import (
4+
"context"
5+
6+
"github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/sql/armsql"
7+
"github.com/cloudquery/cloudquery/plugins/source/azure/client"
8+
"github.com/cloudquery/plugin-sdk/v2/schema"
9+
"github.com/cloudquery/plugin-sdk/v2/transformers"
10+
)
11+
12+
func serverAdvancedThreatProtections() *schema.Table {
13+
return &schema.Table{
14+
Name: "azure_sql_server_advanced_threat_protection_settings",
15+
Resolver: fetchServerAdvancedThreatProtections,
16+
Description: "https://learn.microsoft.com/en-us/rest/api/sql/2021-11-01/server-advanced-threat-protection-settings/list-by-server?tabs=HTTP#advancedthreatprotectionstate",
17+
Transform: transformers.TransformWithStruct(&armsql.ServerAdvancedThreatProtection{}, transformers.WithPrimaryKeys("ID")),
18+
Columns: schema.ColumnList{client.SubscriptionID},
19+
}
20+
}
21+
22+
func fetchServerAdvancedThreatProtections(ctx context.Context, meta schema.ClientMeta, parent *schema.Resource, res chan<- any) error {
23+
p := parent.Item.(*armsql.Server)
24+
cl := meta.(*client.Client)
25+
svc, err := armsql.NewServerAdvancedThreatProtectionSettingsClient(cl.SubscriptionId, cl.Creds, cl.Options)
26+
if err != nil {
27+
return err
28+
}
29+
group, err := client.ParseResourceGroup(*p.ID)
30+
if err != nil {
31+
return err
32+
}
33+
pager := svc.NewListByServerPager(group, *p.Name, nil)
34+
for pager.More() {
35+
p, err := pager.NextPage(ctx)
36+
if err != nil {
37+
return err
38+
}
39+
res <- p.Value
40+
}
41+
return nil
42+
}

‎plugins/source/azure/resources/services/sql/server_advanced_threat_protection_settings_mock_test.go‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
package sql
2+
3+
import (
4+
"encoding/json"
5+
"net/http"
6+
7+
"github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/sql/armsql"
8+
"github.com/cloudquery/plugin-sdk/v2/faker"
9+
"github.com/gorilla/mux"
10+
)
11+
12+
func createServerAdvancedThreatProtectionSettings(router *mux.Router) error {
13+
var item armsql.ServerAdvancedThreatProtectionSettingsClientListByServerResponse
14+
if err := faker.FakeObject(&item); err != nil {
15+
return err
16+
}
17+
18+
emptyStr := ""
19+
item.NextLink = &emptyStr
20+
21+
router.HandleFunc("/subscriptions/{subscriptionId}/resourceGroups/debug/providers/Microsoft.Sql/servers/test string/advancedThreatProtectionSettings", func(w http.ResponseWriter, r *http.Request) {
22+
b, err := json.Marshal(&item)
23+
if err != nil {
24+
http.Error(w, "unable to marshal request: "+err.Error(), http.StatusBadRequest)
25+
return
26+
}
27+
if _, err := w.Write(b); err != nil {
28+
http.Error(w, "failed to write", http.StatusBadRequest)
29+
return
30+
}
31+
})
32+
return nil
33+
}

‎plugins/source/azure/resources/services/sql/servers.go‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ func Servers() *schema.Table {
2525
serverDatabases(),
2626
virtualNetworkRules(),
2727
serverSecurityAlertPolicies(),
28+
serverAdvancedThreatProtections(),
2829
},
2930
}
3031
}

‎plugins/source/azure/resources/services/sql/servers_mock_test.go‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -51,6 +51,9 @@ func createServers(router *mux.Router) error {
5151
if err := createMockServerSecurityAlertPolicies(router); err != nil {
5252
return err
5353
}
54+
if err := createServerAdvancedThreatProtectionSettings(router); err != nil {
55+
return err
56+
}
5457
return createDatabases(router)
5558
}
5659

‎website/pages/docs/plugins/sources/azure/policies.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -25,9 +25,9 @@ tables:
2525
- azure_security_auto_provisioning_settings
2626
- azure_security_pricings
2727
- azure_sql_server_admins
28+
- azure_sql_server_advanced_threat_protection_settings
2829
- azure_sql_server_blob_auditing_policies
2930
- azure_sql_server_database_blob_auditing_policies
30-
- azure_sql_server_database_threat_protections
3131
- azure_sql_server_databases
3232
- azure_sql_server_encryption_protectors
3333
- azure_sql_server_vulnerability_assessments

‎website/pages/docs/plugins/sources/azure/tables.md‎

Lines changed: 1 addition & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎website/tables/azure/azure_sql_server_advanced_threat_protection_settings.md‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
# Table: azure_sql_server_advanced_threat_protection_settings
2+
3+
This table shows data for Azure SQL Server Advanced Threat Protection Settings.
4+
5+
https://learn.microsoft.com/en-us/rest/api/sql/2021-11-01/server-advanced-threat-protection-settings/list-by-server?tabs=HTTP#advancedthreatprotectionstate
6+
7+
The primary key for this table is **id**.
8+
9+
## Relations
10+
11+
This table depends on [azure_sql_servers](azure_sql_servers).
12+
13+
## Columns
14+
15+
| Name | Type |
16+
| ------------- | ------------- |
17+
|_cq_source_name|String|
18+
|_cq_sync_time|Timestamp|
19+
|_cq_id|UUID|
20+
|_cq_parent_id|UUID|
21+
|subscription_id|String|
22+
|properties|JSON|
23+
|id (PK)|String|
24+
|name|String|
25+
|system_data|JSON|
26+
|type|String|

‎website/tables/azure/azure_sql_servers.md‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@ The primary key for this table is **id**.
1010

1111
The following tables depend on azure_sql_servers:
1212
- [azure_sql_server_admins](azure_sql_server_admins)
13+
- [azure_sql_server_advanced_threat_protection_settings](azure_sql_server_advanced_threat_protection_settings)
1314
- [azure_sql_server_blob_auditing_policies](azure_sql_server_blob_auditing_policies)
1415
- [azure_sql_server_databases](azure_sql_server_databases)
1516
- [azure_sql_server_encryption_protectors](azure_sql_server_encryption_protectors)

0 commit comments

Comments
 (0)