audit-labs/audit-tools

 1"""
 2Checks SQL Server user data for compliance with Windows policies.
 3"""
 4
 5# Import packages
 6import pandas as pd
 7
 8# Report column labels (defined once to avoid duplicated string literals).
 9TYPE_CHECK = "Type Check"
10POLICY_CHECK = "Policy Check"
11EXPIRATION_CHECK = "Expiration Check"
12
13# Load the data into a pandas DataFrame
14df_input = pd.read_csv("./data.csv")
15
16
17# Function to apply rules and generate report
18def apply_rules_and_report(df):
19    """
20    Apply defined rules against the input data.
21
22            Parameters:
23                df (pandas.DataFrame): SQL login data
24
25            Returns:
26                report (list): List of dictionaries containing test results
27    """
28    report = []
29    for _, row in df.iterrows():
30        result = {
31            "Name": row["name"],
32            TYPE_CHECK: "",
33            POLICY_CHECK: "",
34            EXPIRATION_CHECK: "",
35            "Reason": "",
36        }
37
38        # Check the type_desc
39        if row["type_desc"] == "SQL_LOGIN":
40            result[TYPE_CHECK] = "SQL_LOGIN"
41        elif row["type_desc"] == "WINDOWS_LOGIN":
42            result[TYPE_CHECK] = "N/A"
43            result["Reason"] = "Refer to Windows password policy."
44        else:
45            result[TYPE_CHECK] = "Manual Review"
46            result["Reason"] = "Reviewer to manually review."
47
48        # Check if password policy is enforced
49        if row["is_policy_checked"] == 1:
50            result[POLICY_CHECK] = "PASS"
51            result["Reason"] += """Password policy is enforced. Reviewer to
52            check the assigned policy."""
53        else:
54            result[POLICY_CHECK] = "FAIL"
55            result["Reason"] += "Password policy is not enforced."
56
57        # Check if password expiration is enforced
58        if row["is_expiration_checked"] == 1:
59            result[EXPIRATION_CHECK] = "PASS"
60            result["Reason"] += """Password expiration is enforced. Reviewer to
61            check the expiration policy."""
62        else:
63            result[EXPIRATION_CHECK] = "FAIL"
64            result["Reason"] += "Password expiration is not enforced."
65
66        report.append(result)
67
68    return report
69
70
71# Main function to run the script
72def main():
73    """
74    Apply defined rules against the input data and print the results.
75    """
76    # Apply rules and generate report
77    report = apply_rules_and_report(df_input)
78    report_df = pd.DataFrame(report)
79
80    # Do not truncate output
81    pd.set_option("display.expand_frame_repr", True)
82    pd.set_option("display.width", 1000)
83    pd.set_option("display.max_colwidth", 1000)
84
85    # Print the report
86    print(report_df)
87
88
89if __name__ == "__main__":
90    main()