reorganize db dir (#6)

4 files changed, 263 insertions, 0 deletions

diff --git a/databases/sql/admins.sql b/databases/sql/admins.sql
new file mode 100644
index 0000000..278fafc
--- /dev/null
+++ b/databases/sql/admins.sql
@@ -0,0 +1,144 @@
+/*
+Security Audit Report
+1) List all access provisioned to a sql user or windows user/group directly
+2) List all access provisioned to a sql user or windows user/group through a database or application role
+3) List all access provisioned to the public role
+
+Columns Returned:
+UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
+UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
+                  SQL Server user account.
+DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
+                  same as the server user.
+Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
+                  on the user account, otherwise this will be the name of the role that the user is a member of.
+PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
+                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
+                  This value may not be populated for all roles.  Some built in roles have implicit permission
+                  definitions.
+PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
+                  This value may not be populated for all roles.  Some built in roles have implicit permission
+                  definitions.
+ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
+                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
+                  This value may not be populated for all roles.  Some built in roles have implicit permission
+                  definitions.
+ObjectName      : Name of the object that the user/role is assigned permissions on.
+                  This value may not be populated for all roles.  Some built in roles have implicit permission
+                  definitions.
+ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
+                  is only populated if the object is a table, view or a table value function.
+*/
+
+--List all access provisioned to a sql user or windows user/group directly
+SELECT
+    [UserName] = CASE princ.[type]
+                    WHEN 'S' THEN princ.[name]
+                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
+                 END,
+    [UserType] = CASE princ.[type]
+                    WHEN 'S' THEN 'SQL User'
+                    WHEN 'U' THEN 'Windows User'
+                 END,
+    [DatabaseUserName] = princ.[name],
+    [Role] = null,
+    [PermissionType] = perm.[permission_name],
+    [PermissionState] = perm.[state_desc],
+    [ObjectType] = obj.type_desc,--perm.[class_desc],
+    [ObjectName] = OBJECT_NAME(perm.major_id),
+    [ColumnName] = col.[name]
+FROM
+    --database user
+    sys.database_principals princ
+LEFT JOIN
+    --Login accounts
+    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
+LEFT JOIN
+    --Permissions
+    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
+LEFT JOIN
+    --Table columns
+    sys.columns col ON col.[object_id] = perm.major_id
+                    AND col.[column_id] = perm.[minor_id]
+LEFT JOIN
+    sys.objects obj ON perm.[major_id] = obj.[object_id]
+WHERE
+    princ.[type] in ('S','U')
+UNION
+--List all access provisioned to a sql user or windows user/group through a database or application role
+SELECT
+    [UserName] = CASE memberprinc.[type]
+                    WHEN 'S' THEN memberprinc.[name]
+                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
+                 END,
+    [UserType] = CASE memberprinc.[type]
+                    WHEN 'S' THEN 'SQL User'
+                    WHEN 'U' THEN 'Windows User'
+                 END,
+    [DatabaseUserName] = memberprinc.[name],
+    [Role] = roleprinc.[name],
+    [PermissionType] = perm.[permission_name],
+    [PermissionState] = perm.[state_desc],
+    [ObjectType] = obj.type_desc,--perm.[class_desc],
+    [ObjectName] = OBJECT_NAME(perm.major_id),
+    [ColumnName] = col.[name]
+FROM
+    --Role/member associations
+    sys.database_role_members members
+JOIN
+    --Roles
+    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
+JOIN
+    --Role members (database users)
+    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
+LEFT JOIN
+    --Login accounts
+    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
+LEFT JOIN
+    --Permissions
+    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
+LEFT JOIN
+    --Table columns
+    sys.columns col on col.[object_id] = perm.major_id
+                    AND col.[column_id] = perm.[minor_id]
+LEFT JOIN
+    sys.objects obj ON perm.[major_id] = obj.[object_id]
+UNION
+--List all access provisioned to the public role, which everyone gets by default
+SELECT
+    [UserName] = '{All Users}',
+    [UserType] = '{All Users}',
+    [DatabaseUserName] = '{All Users}',
+    [Role] = roleprinc.[name],
+    [PermissionType] = perm.[permission_name],
+    [PermissionState] = perm.[state_desc],
+    [ObjectType] = obj.type_desc,--perm.[class_desc],
+    [ObjectName] = OBJECT_NAME(perm.major_id),
+    [ColumnName] = col.[name]
+FROM
+    --Roles
+    sys.database_principals roleprinc
+LEFT JOIN
+    --Role permissions
+    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
+LEFT JOIN
+    --Table columns
+    sys.columns col on col.[object_id] = perm.major_id
+                    AND col.[column_id] = perm.[minor_id]
+JOIN
+    --All objects
+    sys.objects obj ON obj.[object_id] = perm.[major_id]
+WHERE
+    --Only roles
+    roleprinc.[type] = 'R' AND
+    --Only public role
+    roleprinc.[name] = 'public' AND
+    --Only objects of ours, not the MS objects
+    obj.is_ms_shipped = 0
+ORDER BY
+    princ.[Name],
+    OBJECT_NAME(perm.major_id),
+    col.[name],
+    perm.[permission_name],
+    perm.[state_desc],
+    obj.type_desc--perm.[class_desc]
diff --git a/databases/sql/passwords/data.csv b/databases/sql/passwords/data.csv
new file mode 100644
index 0000000..fc925ea
--- /dev/null
+++ b/databases/sql/passwords/data.csv
@@ -0,0 +1,9 @@
+name,principal_id,sid,type,type_desc,is_disabled,create_date,modify_date,default_database_name,default_language_name,credential_id,is_policy_checked,is_expiration_checked,password_hash,IsMustChange,IsLocked,LockoutTime,PasswordLastSetTime,IsExpired,BadPasswordCount,BadPasswordTime,HistoryLength
+user1,1,,S,SQL_LOGIN,0,2023-01-15 10:35:00,2023-01-15 10:35:00,master,us_english,NULL,1,0,0x01004086CEB6772AE2356381B9B069D4E02C0185D5A06CFA3822,0,0,,2023-01-15 10:35:00,0,0,,5
+user2,267,,S,SQL_LOGIN,0,2023-02-20 20:49:00,2023-02-20 20:49:00,master,us_english,NULL,0,0,0x01003E3A7A6F88A8F548540ECB2043946AC2545120424CCD8782,1,0,,2023-02-20 20:49:00,0,1,2023-02-20 20:50:00,3
+user3,268,,S,SQL_LOGIN,0,2023-03-10 11:20:00,2023-03-10 11:20:00,secondary,us_english,NULL,1,0,0x010042516769FBC191A67840731CB36B41EFDACC97BE8264281F,0,0,,2023-03-10 11:20:00,0,0,,4
+user4,269,,S,SQL_LOGIN,0,2023-04-01 10:40:00,2023-04-01 11:32:00,secondary,us_english,NULL,1,0,0x01005F3B351B26E2DB7C7FD3C7ED02B3FD2EDC09BB2BF13DA3E5,0,1,2023-04-01 11:32:00,2023-04-01 10:40:00,0,3,2023-04-01 11:30:00,2
+user5,270,,S,SQL_LOGIN,0,2023-05-05 12:33:00,2023-05-05 12:33:00,master,us_english,NULL,1,0,0x0100AE15D55972BB3D6C6283921711CD4A208747888BEEFED71B,0,0,,2023-05-05 12:33:00,0,0,,6
+user6,272,,S,SQL_LOGIN,0,2023-06-15 11:46:00,2023-06-15 11:46:00,secondary,us_english,NULL,1,1,0x0100F12FAE790FCE0FF356A0948211AE4052653503E1BBC28FAB,0,0,,2023-06-15 11:46:00,0,0,,7
+user7,279,,S,SQL_LOGIN,0,2023-07-20 12:50:00,2023-07-20 12:50:00,secondary,us_english,NULL,1,1,0x01004856A222264E62219236AB6AC7E5B622F1E53D1CCA2AF9B8,0,0,,2023-07-20 12:50:00,0,0,,8
+user8,284,,S,SQL_LOGIN,0,2023-08-25 13:56:00,2023-08-25 13:56:00,master,us_english,NULL,1,1,0x0100723BEDBE69779CD3087C0E60AD69C33CC7E969F78DA2498A,0,0,,2023-08-25 13:56:00,0,0,,9
\ No newline at end of file
diff --git a/databases/sql/passwords/get_data.sql b/databases/sql/passwords/get_data.sql
new file mode 100644
index 0000000..b5bef36
--- /dev/null
+++ b/databases/sql/passwords/get_data.sql
@@ -0,0 +1,30 @@
+/*
+References:
+1. https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy
+2. https://learn.microsoft.com/en-us/sql/t-sql/functions/loginproperty-transact-sql
+*/
+
+SELECT
+    name,
+    principal_id,
+    sid,
+    type,
+    type_desc,
+    is_disabled,
+    create_date,
+    modify_date,
+    default_database_name,
+    default_language_name,
+    credential_id,
+    is_policy_checked,
+    is_expiration_checked,
+    password_hash,
+    LOGINPROPERTY(name, 'IsMustChange') AS IsMustChange,
+    LOGINPROPERTY(name, 'IsLocked') AS IsLocked,
+    LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime,
+    LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime,
+    LOGINPROPERTY(name, 'IsExpired') AS IsExpired,
+    LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount,
+    LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime,
+    LOGINPROPERTY(name, 'HistoryLength') AS HistoryLength
+FROM sys.sql_logins;
diff --git a/databases/sql/passwords/test.py b/databases/sql/passwords/test.py
new file mode 100644
index 0000000..81c1138
--- /dev/null
+++ b/databases/sql/passwords/test.py
@@ -0,0 +1,80 @@
+"""
+Checks SQL Server user data for compliance with Windows policies.
+"""
+
+# Import packages
+import pandas as pd
+
+# Load the data into a pandas DataFrame
+df_input = pd.read_csv("./data.csv")
+
+
+# Function to apply rules and generate report
+def apply_rules_and_report(df):
+    """
+    Apply defined rules against the input data.
+
+            Parameters:
+                df (pandas.DataFrame): SQL login data
+
+            Returns:
+                report (list): List of dictionaries containing test results
+    """
+    report = []
+    for _, row in df.iterrows():
+        result = {
+            "Name": row["name"],
+            "Type Check": "",
+            "Policy Check": "",
+            "Expiration Check": "",
+            "Reason": "",
+        }
+
+        # Check the type_desc
+        if row["type_desc"] == "SQL_LOGIN":
+            result["Type Check"] = "SQL_LOGIN"
+        elif row["type_desc"] == "WINDOWS_LOGIN":
+            result["Type Check"] = "N/A"
+            result["Reason"] = "Refer to Windows password policy."
+        else:
+            result["Type Check"] = "Manual Review"
+            result["Reason"] = "Reviewer to manually review."
+
+        # Check if password policy is enforced
+        if row["is_policy_checked"] == 1:
+            result["Policy Check"] = "PASS"
+            result["Reason"] += """Password policy is enforced. Reviewer to
+            check the assigned policy."""
+        else:
+            result["Policy Check"] = "FAIL"
+            result["Reason"] += "Password policy is not enforced."
+
+        # Check if password expiration is enforced
+        if row["is_expiration_checked"] == 1:
+            result["Expiration Check"] = "PASS"
+            result["Reason"] += """Password expiration is enforced. Reviewer to
+            check the expiration policy."""
+        else:
+            result["Expiration Check"] = "FAIL"
+            result["Reason"] += "Password expiration is not enforced."
+
+        report.append(result)
+
+    return report
+
+
+# Main function to run the script
+def main():
+    """
+    Apply defined rules against the input data and print the results.
+    """
+    # Apply rules and generate report
+    report = apply_rules_and_report(df_input)
+    report_df = pd.DataFrame(report)
+
+    # Print the report
+    print(report_df)
+
+
+if __name__ == "__main__":
+    main()