MySQL & Postgres Enhancements (#5)

* remove mysql login script and add password script

* move excess mysql password query to new script

* add db admin folders

* add postgres

* add mongo admins script

* Commit from GitHub Actions (Ruff)

* update tests for mysql and postgres

* update tests for mysql and postgres

---------

Co-authored-by: github-actions <41898282+github-actions[bot]@users.noreply.github.com>

1 files changed, 0 insertions, 144 deletions

diff --git a/databases/administrators/mssql_admins.sql b/databases/administrators/mssql_admins.sql
deleted file mode 100644
index 278fafc..0000000
--- a/databases/administrators/mssql_admins.sql
+++ /dev/null
@@ -1,144 +0,0 @@
-/*
-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]