From 95bf612c338dec8235e89ca6a1d9e5e8cad3f997 Mon Sep 17 00:00:00 2001 From: Christian Cleberg Date: Tue, 6 May 2025 21:31:46 -0500 Subject: reorganize db dir (#6) --- .../administrators/microsoft-sql/mssql_admins.sql | 144 ---------------- databases/administrators/mongo/README.org | 104 ------------ databases/administrators/mongo/admins.py | 16 -- databases/administrators/mysql/README.org | 108 ------------ databases/administrators/mysql/mysql_admins.sql | 1 - .../administrators/mysql/mysql_admins_alt.sql | 14 -- databases/administrators/oracle/oracle_admins.sql | 15 -- .../administrators/oracle/oracle_admins_alt.sql | 4 - databases/administrators/postgres/README.org | 45 ----- databases/administrators/postgres/admins.sql | 22 --- databases/mongo/README.org | 104 ++++++++++++ databases/mongo/admins.py | 16 ++ databases/mysql/README.org | 183 +++++++++++++++++++++ databases/mysql/mysql_admins.sql | 1 + databases/mysql/mysql_admins_alt.sql | 14 ++ databases/mysql/passwords.sql | 13 ++ databases/oracle/oracle_admins.sql | 15 ++ databases/oracle/oracle_admins_alt.sql | 4 + databases/passwords/mysql/README.org | 76 --------- databases/passwords/mysql/passwords.sql | 13 -- databases/passwords/postgres/README.org | 31 ---- databases/passwords/postgres/passwords.sql | 18 -- databases/passwords/sql/data.csv | 9 - databases/passwords/sql/get_data.sql | 30 ---- databases/passwords/sql/test.py | 80 --------- databases/postgres/README.org | 75 +++++++++ databases/postgres/admins.sql | 22 +++ databases/postgres/passwords.sql | 18 ++ databases/sql/admins.sql | 144 ++++++++++++++++ databases/sql/passwords/data.csv | 9 + databases/sql/passwords/get_data.sql | 30 ++++ databases/sql/passwords/test.py | 80 +++++++++ 32 files changed, 728 insertions(+), 730 deletions(-) delete mode 100644 databases/administrators/microsoft-sql/mssql_admins.sql delete mode 100644 databases/administrators/mongo/README.org delete mode 100644 databases/administrators/mongo/admins.py delete mode 100644 databases/administrators/mysql/README.org delete mode 100644 databases/administrators/mysql/mysql_admins.sql delete mode 100644 databases/administrators/mysql/mysql_admins_alt.sql delete mode 100644 databases/administrators/oracle/oracle_admins.sql delete mode 100644 databases/administrators/oracle/oracle_admins_alt.sql delete mode 100644 databases/administrators/postgres/README.org delete mode 100644 databases/administrators/postgres/admins.sql create mode 100644 databases/mongo/README.org create mode 100644 databases/mongo/admins.py create mode 100644 databases/mysql/README.org create mode 100644 databases/mysql/mysql_admins.sql create mode 100644 databases/mysql/mysql_admins_alt.sql create mode 100644 databases/mysql/passwords.sql create mode 100644 databases/oracle/oracle_admins.sql create mode 100644 databases/oracle/oracle_admins_alt.sql delete mode 100644 databases/passwords/mysql/README.org delete mode 100644 databases/passwords/mysql/passwords.sql delete mode 100644 databases/passwords/postgres/README.org delete mode 100644 databases/passwords/postgres/passwords.sql delete mode 100644 databases/passwords/sql/data.csv delete mode 100644 databases/passwords/sql/get_data.sql delete mode 100644 databases/passwords/sql/test.py create mode 100644 databases/postgres/README.org create mode 100644 databases/postgres/admins.sql create mode 100644 databases/postgres/passwords.sql create mode 100644 databases/sql/admins.sql create mode 100644 databases/sql/passwords/data.csv create mode 100644 databases/sql/passwords/get_data.sql create mode 100644 databases/sql/passwords/test.py (limited to 'databases') diff --git a/databases/administrators/microsoft-sql/mssql_admins.sql b/databases/administrators/microsoft-sql/mssql_admins.sql deleted file mode 100644 index 278fafc..0000000 --- a/databases/administrators/microsoft-sql/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] diff --git a/databases/administrators/mongo/README.org b/databases/administrators/mongo/README.org deleted file mode 100644 index 689d37d..0000000 --- a/databases/administrators/mongo/README.org +++ /dev/null @@ -1,104 +0,0 @@ -#+title: MongoDB Scripts - -* =admins.py= - -Dependency: - -#+begin_src shell -pip install pymongo -#+end_src - -#+begin_src python -python ./admins.py -#+end_src - -Example output: - -#+begin_src json -[ - { - "_id": "admin.admin", - "user": "admin", - "db": "admin", - "roles": [ - { - "role": "userAdminAnyDatabase", - "db": "admin" - }, - { - "role": "readWriteAnyDatabase", - "db": "admin" - }, - { - "role": "dbAdminAnyDatabase", - "db": "admin" - }, - { - "role": "clusterAdmin", - "db": "admin" - } - ], - "credentials": { - "SCRAM-SHA-1": { - "iterationCount": 10000, - "salt": "abc123", - "storedKey": "storedKeyHash", - "serverKey": "serverKeyHash" - }, - "SCRAM-SHA-256": { - "iterationCount": 15000, - "salt": "def456", - "storedKey": "storedKeyHash256", - "serverKey": "serverKeyHash256" - } - } - }, - { - "_id": "test.user1", - "user": "user1", - "db": "test", - "roles": [ - { - "role": "readWrite", - "db": "test" - } - ], - "credentials": { - "SCRAM-SHA-1": { - "iterationCount": 10000, - "salt": "ghi789", - "storedKey": "storedKeyHashUser1", - "serverKey": "serverKeyHashUser1" - } - } - }, - { - "_id": "test.ldapUser", - "user": "ldapUser", - "db": "test", - "roles": [ - { - "role": "read", - "db": "test" - } - ], - "userSource": "ldap" - }, - { - "_id": "admin.x509User", - "user": "x509User", - "db": "$external", - "roles": [ - { - "role": "readWrite", - "db": "admin" - } - ], - "credentials": { - "MONGODB-X509": { - "subject": "CN=x509User,OU=OrgUnit,O=Org,L=City,ST=State,C=Country" - } - } - } -] -#+end_src diff --git a/databases/administrators/mongo/admins.py b/databases/administrators/mongo/admins.py deleted file mode 100644 index e844cbc..0000000 --- a/databases/administrators/mongo/admins.py +++ /dev/null @@ -1,16 +0,0 @@ -from pymongo import MongoClient - -# Connect to the MongoDB server -client = MongoClient("mongodb://localhost:27017/") - -# Select the 'admin' database -db = client.admin - -# Query the 'system.users' collection -users = db.system.users.find( - {}, {"user": 1, "db": 1, "roles": 1, "credentials": 1, "userSource": 1} -) - -# Print the results in a pretty format -for user in users: - print(user) diff --git a/databases/administrators/mysql/README.org b/databases/administrators/mysql/README.org deleted file mode 100644 index 82ae540..0000000 --- a/databases/administrators/mysql/README.org +++ /dev/null @@ -1,108 +0,0 @@ -#+title: MySQL Admins - -* =mysql_admins.sql= - -#+begin_src sql -SELECT * FROM information_schema.user_privileges; -#+end_src - -#+begin_src -MySQL [(none)]> SELECT * FROM information_schema.user_privileges; -+--------------------------------+---------------+---------------------------------+--------------+ -| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | -+--------------------------------+---------------+---------------------------------+--------------+ -| 'mysql.infoschema'@'localhost' | def | SELECT | NO | -| 'mysql.infoschema'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO | -| 'mysql.infoschema'@'localhost' | def | FIREWALL_EXEMPT | NO | -| 'mysql.infoschema'@'localhost' | def | SYSTEM_USER | NO | -| 'mysql.session'@'localhost' | def | SHUTDOWN | NO | -| 'mysql.session'@'localhost' | def | SUPER | NO | -| 'mysql.session'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO | -| 'mysql.session'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | NO | -| 'mysql.session'@'localhost' | def | BACKUP_ADMIN | NO | -| 'mysql.session'@'localhost' | def | CLONE_ADMIN | NO | -| 'mysql.session'@'localhost' | def | CONNECTION_ADMIN | NO | -| 'mysql.session'@'localhost' | def | FIREWALL_EXEMPT | NO | -| 'mysql.session'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | NO | -| 'mysql.session'@'localhost' | def | SESSION_VARIABLES_ADMIN | NO | -| 'mysql.session'@'localhost' | def | SYSTEM_USER | NO | -| 'mysql.session'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | NO | -| 'mysql.sys'@'localhost' | def | USAGE | NO | -| 'mysql.sys'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO | -| 'mysql.sys'@'localhost' | def | FIREWALL_EXEMPT | NO | -| 'mysql.sys'@'localhost' | def | SYSTEM_USER | NO | -| 'root'@'localhost' | def | SELECT | YES | -| 'root'@'localhost' | def | INSERT | YES | -| 'root'@'localhost' | def | UPDATE | YES | -| 'root'@'localhost' | def | DELETE | YES | -| 'root'@'localhost' | def | CREATE | YES | -| 'root'@'localhost' | def | DROP | YES | -| 'root'@'localhost' | def | RELOAD | YES | -| 'root'@'localhost' | def | SHUTDOWN | YES | -| 'root'@'localhost' | def | PROCESS | YES | -| 'root'@'localhost' | def | FILE | YES | -| 'root'@'localhost' | def | REFERENCES | YES | -| 'root'@'localhost' | def | INDEX | YES | -| 'root'@'localhost' | def | ALTER | YES | -| 'root'@'localhost' | def | SHOW DATABASES | YES | -| 'root'@'localhost' | def | SUPER | YES | -| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES | -| 'root'@'localhost' | def | LOCK TABLES | YES | -| 'root'@'localhost' | def | EXECUTE | YES | -| 'root'@'localhost' | def | REPLICATION SLAVE | YES | -| 'root'@'localhost' | def | REPLICATION CLIENT | YES | -| 'root'@'localhost' | def | CREATE VIEW | YES | -| 'root'@'localhost' | def | SHOW VIEW | YES | -| 'root'@'localhost' | def | CREATE ROUTINE | YES | -| 'root'@'localhost' | def | ALTER ROUTINE | YES | -| 'root'@'localhost' | def | CREATE USER | YES | -| 'root'@'localhost' | def | EVENT | YES | -| 'root'@'localhost' | def | TRIGGER | YES | -| 'root'@'localhost' | def | CREATE TABLESPACE | YES | -| 'root'@'localhost' | def | CREATE ROLE | YES | -| 'root'@'localhost' | def | DROP ROLE | YES | -| 'root'@'localhost' | def | ALLOW_NONEXISTENT_DEFINER | YES | -| 'root'@'localhost' | def | APPLICATION_PASSWORD_ADMIN | YES | -| 'root'@'localhost' | def | AUDIT_ABORT_EXEMPT | YES | -| 'root'@'localhost' | def | AUDIT_ADMIN | YES | -| 'root'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | YES | -| 'root'@'localhost' | def | BACKUP_ADMIN | YES | -| 'root'@'localhost' | def | BINLOG_ADMIN | YES | -| 'root'@'localhost' | def | BINLOG_ENCRYPTION_ADMIN | YES | -| 'root'@'localhost' | def | CLONE_ADMIN | YES | -| 'root'@'localhost' | def | CONNECTION_ADMIN | YES | -| 'root'@'localhost' | def | CREATE_SPATIAL_REFERENCE_SYSTEM | YES | -| 'root'@'localhost' | def | ENCRYPTION_KEY_ADMIN | YES | -| 'root'@'localhost' | def | FIREWALL_EXEMPT | YES | -| 'root'@'localhost' | def | FLUSH_OPTIMIZER_COSTS | YES | -| 'root'@'localhost' | def | FLUSH_PRIVILEGES | YES | -| 'root'@'localhost' | def | FLUSH_STATUS | YES | -| 'root'@'localhost' | def | FLUSH_TABLES | YES | -| 'root'@'localhost' | def | FLUSH_USER_RESOURCES | YES | -| 'root'@'localhost' | def | GROUP_REPLICATION_ADMIN | YES | -| 'root'@'localhost' | def | GROUP_REPLICATION_STREAM | YES | -| 'root'@'localhost' | def | INNODB_REDO_LOG_ARCHIVE | YES | -| 'root'@'localhost' | def | INNODB_REDO_LOG_ENABLE | YES | -| 'root'@'localhost' | def | OPTIMIZE_LOCAL_TABLE | YES | -| 'root'@'localhost' | def | PASSWORDLESS_USER_ADMIN | YES | -| 'root'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | YES | -| 'root'@'localhost' | def | REPLICATION_APPLIER | YES | -| 'root'@'localhost' | def | REPLICATION_SLAVE_ADMIN | YES | -| 'root'@'localhost' | def | RESOURCE_GROUP_ADMIN | YES | -| 'root'@'localhost' | def | RESOURCE_GROUP_USER | YES | -| 'root'@'localhost' | def | ROLE_ADMIN | YES | -| 'root'@'localhost' | def | SENSITIVE_VARIABLES_OBSERVER | YES | -| 'root'@'localhost' | def | SERVICE_CONNECTION_ADMIN | YES | -| 'root'@'localhost' | def | SESSION_VARIABLES_ADMIN | YES | -| 'root'@'localhost' | def | SET_ANY_DEFINER | YES | -| 'root'@'localhost' | def | SHOW_ROUTINE | YES | -| 'root'@'localhost' | def | SYSTEM_USER | YES | -| 'root'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | YES | -| 'root'@'localhost' | def | TABLE_ENCRYPTION_ADMIN | YES | -| 'root'@'localhost' | def | TELEMETRY_LOG_ADMIN | YES | -| 'root'@'localhost' | def | TRANSACTION_GTID_TAG | YES | -| 'root'@'localhost' | def | XA_RECOVER_ADMIN | YES | -| 'cmc'@'%' | def | USAGE | NO | -+--------------------------------+---------------+---------------------------------+--------------+ -92 rows in set (0.001 sec) -#+end_src diff --git a/databases/administrators/mysql/mysql_admins.sql b/databases/administrators/mysql/mysql_admins.sql deleted file mode 100644 index 9115ec5..0000000 --- a/databases/administrators/mysql/mysql_admins.sql +++ /dev/null @@ -1 +0,0 @@ -SELECT * FROM information_schema.user_privileges; diff --git a/databases/administrators/mysql/mysql_admins_alt.sql b/databases/administrators/mysql/mysql_admins_alt.sql deleted file mode 100644 index 9552ee2..0000000 --- a/databases/administrators/mysql/mysql_admins_alt.sql +++ /dev/null @@ -1,14 +0,0 @@ --- Global Permissions -SELECT ... FROM mysql.user; - --- Database Permissions -SELECT ... FROM mysql.db -WHERE db = @db_name; - --- Table Permissions -SELECT ... FROM mysql.tables -WHERE db = @db_name; - --- Column Permissions -SELECT ... FROM mysql.columns_priv -WHERE db = @db_name; diff --git a/databases/administrators/oracle/oracle_admins.sql b/databases/administrators/oracle/oracle_admins.sql deleted file mode 100644 index bac5934..0000000 --- a/databases/administrators/oracle/oracle_admins.sql +++ /dev/null @@ -1,15 +0,0 @@ -SELECT - grantee AS "User", - privilege AS "Privilege" -FROM - dba_sys_privs -WHERE - grantee IN (SELECT DISTINCT grantee FROM dba_sys_privs) -UNION ALL -SELECT - grantee AS "User", - privilege AS "Privilege" -FROM - dba_tab_privs -WHERE - grantee IN (SELECT DISTINCT grantee FROM dba_tab_privs); diff --git a/databases/administrators/oracle/oracle_admins_alt.sql b/databases/administrators/oracle/oracle_admins_alt.sql deleted file mode 100644 index 4486829..0000000 --- a/databases/administrators/oracle/oracle_admins_alt.sql +++ /dev/null @@ -1,4 +0,0 @@ -SELECT ** FROM sys.dba_role_privs; -SELECT ** FROM sys.dba_sys_privs; -SELECT ** FROM sys.dba_tab_privs; -SELECT ** FROM sys.dba_users; diff --git a/databases/administrators/postgres/README.org b/databases/administrators/postgres/README.org deleted file mode 100644 index fe361de..0000000 --- a/databases/administrators/postgres/README.org +++ /dev/null @@ -1,45 +0,0 @@ -#+title: Postgres Admins - -* =admins.sql= - -#+begin_src sql -SELECT - r.rolname AS role_name, - r.rolsuper AS is_superuser, - r.rolinherit AS inherits_privileges, - r.rolcreaterole AS can_create_roles, - r.rolcreatedb AS can_create_db, - r.rolcanlogin AS can_login, - r.rolreplication AS can_replication, - r.rolconnlimit AS connection_limit, - r.rolvaliduntil AS valid_until, - ARRAY( - SELECT b.rolname - FROM pg_auth_members m - JOIN pg_roles b ON (m.roleid = b.oid) - WHERE m.member = r.oid - ) AS member_of -FROM pg_roles r; -#+end_src - -#+begin_src -| role_name | is_superuser | inherits_privileges | can_create_roles | can_create_db | can_login | can_replication | connection_limit | valid_until | member_of | -|-----------------------------+--------------+---------------------+------------------+---------------+-----------+-----------------+------------------+------------------------+--------------------------------------------------------------| -| cmc | true | true | true | true | true | true | -1 | | {} | -| pg_database_owner | false | true | false | false | false | false | -1 | | {} | -| pg_read_all_data | false | true | false | false | false | false | -1 | | {} | -| pg_write_all_data | false | true | false | false | false | false | -1 | | {} | -| pg_monitor | false | true | false | false | false | false | -1 | | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} | -| pg_read_all_settings | false | true | false | false | false | false | -1 | | {} | -| pg_read_all_stats | false | true | false | false | false | false | -1 | | {} | -| pg_stat_scan_tables | false | true | false | false | false | false | -1 | | {} | -| pg_read_server_files | false | true | false | false | false | false | -1 | | {} | -| pg_write_server_files | false | true | false | false | false | false | -1 | | {} | -| pg_execute_server_program | false | true | false | false | false | false | -1 | | {} | -| pg_signal_backend | false | true | false | false | false | false | -1 | | {} | -| pg_checkpoint | false | true | false | false | false | false | -1 | | {} | -| pg_maintain | false | true | false | false | false | false | -1 | | {} | -| pg_use_reserved_connections | false | true | false | false | false | false | -1 | | {} | -| pg_create_subscription | false | true | false | false | false | false | -1 | | {} | -| testuser | false | true | false | false | true | false | -1 | 2025-12-31 00:00:00-06 | {} | -#+end_src diff --git a/databases/administrators/postgres/admins.sql b/databases/administrators/postgres/admins.sql deleted file mode 100644 index 6f9d320..0000000 --- a/databases/administrators/postgres/admins.sql +++ /dev/null @@ -1,22 +0,0 @@ --- References: --- : https://www.postgresql.org/docs/current/user-manag.html --- : https://www.postgresql.org/docs/current/view-pg-roles.html --- : https://www.postgresql.org/docs/current/catalog-pg-auth-members.html - -SELECT - r.rolname AS role_name, - r.rolsuper AS is_superuser, - r.rolinherit AS inherits_privileges, - r.rolcreaterole AS can_create_roles, - r.rolcreatedb AS can_create_db, - r.rolcanlogin AS can_login, - r.rolreplication AS can_replication, - r.rolconnlimit AS connection_limit, - r.rolvaliduntil AS valid_until, - ARRAY( - SELECT b.rolname - FROM pg_auth_members m - JOIN pg_roles b ON (m.roleid = b.oid) - WHERE m.member = r.oid - ) AS member_of -FROM pg_roles r; \ No newline at end of file diff --git a/databases/mongo/README.org b/databases/mongo/README.org new file mode 100644 index 0000000..689d37d --- /dev/null +++ b/databases/mongo/README.org @@ -0,0 +1,104 @@ +#+title: MongoDB Scripts + +* =admins.py= + +Dependency: + +#+begin_src shell +pip install pymongo +#+end_src + +#+begin_src python +python ./admins.py +#+end_src + +Example output: + +#+begin_src json +[ + { + "_id": "admin.admin", + "user": "admin", + "db": "admin", + "roles": [ + { + "role": "userAdminAnyDatabase", + "db": "admin" + }, + { + "role": "readWriteAnyDatabase", + "db": "admin" + }, + { + "role": "dbAdminAnyDatabase", + "db": "admin" + }, + { + "role": "clusterAdmin", + "db": "admin" + } + ], + "credentials": { + "SCRAM-SHA-1": { + "iterationCount": 10000, + "salt": "abc123", + "storedKey": "storedKeyHash", + "serverKey": "serverKeyHash" + }, + "SCRAM-SHA-256": { + "iterationCount": 15000, + "salt": "def456", + "storedKey": "storedKeyHash256", + "serverKey": "serverKeyHash256" + } + } + }, + { + "_id": "test.user1", + "user": "user1", + "db": "test", + "roles": [ + { + "role": "readWrite", + "db": "test" + } + ], + "credentials": { + "SCRAM-SHA-1": { + "iterationCount": 10000, + "salt": "ghi789", + "storedKey": "storedKeyHashUser1", + "serverKey": "serverKeyHashUser1" + } + } + }, + { + "_id": "test.ldapUser", + "user": "ldapUser", + "db": "test", + "roles": [ + { + "role": "read", + "db": "test" + } + ], + "userSource": "ldap" + }, + { + "_id": "admin.x509User", + "user": "x509User", + "db": "$external", + "roles": [ + { + "role": "readWrite", + "db": "admin" + } + ], + "credentials": { + "MONGODB-X509": { + "subject": "CN=x509User,OU=OrgUnit,O=Org,L=City,ST=State,C=Country" + } + } + } +] +#+end_src diff --git a/databases/mongo/admins.py b/databases/mongo/admins.py new file mode 100644 index 0000000..e844cbc --- /dev/null +++ b/databases/mongo/admins.py @@ -0,0 +1,16 @@ +from pymongo import MongoClient + +# Connect to the MongoDB server +client = MongoClient("mongodb://localhost:27017/") + +# Select the 'admin' database +db = client.admin + +# Query the 'system.users' collection +users = db.system.users.find( + {}, {"user": 1, "db": 1, "roles": 1, "credentials": 1, "userSource": 1} +) + +# Print the results in a pretty format +for user in users: + print(user) diff --git a/databases/mysql/README.org b/databases/mysql/README.org new file mode 100644 index 0000000..ce7c438 --- /dev/null +++ b/databases/mysql/README.org @@ -0,0 +1,183 @@ +#+title: MySQL + +* =mysql_admins.sql= + +#+begin_src sql +SELECT * FROM information_schema.user_privileges; +#+end_src + +#+begin_src +MySQL [(none)]> SELECT * FROM information_schema.user_privileges; ++--------------------------------+---------------+---------------------------------+--------------+ +| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | ++--------------------------------+---------------+---------------------------------+--------------+ +| 'mysql.infoschema'@'localhost' | def | SELECT | NO | +| 'mysql.infoschema'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO | +| 'mysql.infoschema'@'localhost' | def | FIREWALL_EXEMPT | NO | +| 'mysql.infoschema'@'localhost' | def | SYSTEM_USER | NO | +| 'mysql.session'@'localhost' | def | SHUTDOWN | NO | +| 'mysql.session'@'localhost' | def | SUPER | NO | +| 'mysql.session'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO | +| 'mysql.session'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | NO | +| 'mysql.session'@'localhost' | def | BACKUP_ADMIN | NO | +| 'mysql.session'@'localhost' | def | CLONE_ADMIN | NO | +| 'mysql.session'@'localhost' | def | CONNECTION_ADMIN | NO | +| 'mysql.session'@'localhost' | def | FIREWALL_EXEMPT | NO | +| 'mysql.session'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | NO | +| 'mysql.session'@'localhost' | def | SESSION_VARIABLES_ADMIN | NO | +| 'mysql.session'@'localhost' | def | SYSTEM_USER | NO | +| 'mysql.session'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | NO | +| 'mysql.sys'@'localhost' | def | USAGE | NO | +| 'mysql.sys'@'localhost' | def | AUDIT_ABORT_EXEMPT | NO | +| 'mysql.sys'@'localhost' | def | FIREWALL_EXEMPT | NO | +| 'mysql.sys'@'localhost' | def | SYSTEM_USER | NO | +| 'root'@'localhost' | def | SELECT | YES | +| 'root'@'localhost' | def | INSERT | YES | +| 'root'@'localhost' | def | UPDATE | YES | +| 'root'@'localhost' | def | DELETE | YES | +| 'root'@'localhost' | def | CREATE | YES | +| 'root'@'localhost' | def | DROP | YES | +| 'root'@'localhost' | def | RELOAD | YES | +| 'root'@'localhost' | def | SHUTDOWN | YES | +| 'root'@'localhost' | def | PROCESS | YES | +| 'root'@'localhost' | def | FILE | YES | +| 'root'@'localhost' | def | REFERENCES | YES | +| 'root'@'localhost' | def | INDEX | YES | +| 'root'@'localhost' | def | ALTER | YES | +| 'root'@'localhost' | def | SHOW DATABASES | YES | +| 'root'@'localhost' | def | SUPER | YES | +| 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES | +| 'root'@'localhost' | def | LOCK TABLES | YES | +| 'root'@'localhost' | def | EXECUTE | YES | +| 'root'@'localhost' | def | REPLICATION SLAVE | YES | +| 'root'@'localhost' | def | REPLICATION CLIENT | YES | +| 'root'@'localhost' | def | CREATE VIEW | YES | +| 'root'@'localhost' | def | SHOW VIEW | YES | +| 'root'@'localhost' | def | CREATE ROUTINE | YES | +| 'root'@'localhost' | def | ALTER ROUTINE | YES | +| 'root'@'localhost' | def | CREATE USER | YES | +| 'root'@'localhost' | def | EVENT | YES | +| 'root'@'localhost' | def | TRIGGER | YES | +| 'root'@'localhost' | def | CREATE TABLESPACE | YES | +| 'root'@'localhost' | def | CREATE ROLE | YES | +| 'root'@'localhost' | def | DROP ROLE | YES | +| 'root'@'localhost' | def | ALLOW_NONEXISTENT_DEFINER | YES | +| 'root'@'localhost' | def | APPLICATION_PASSWORD_ADMIN | YES | +| 'root'@'localhost' | def | AUDIT_ABORT_EXEMPT | YES | +| 'root'@'localhost' | def | AUDIT_ADMIN | YES | +| 'root'@'localhost' | def | AUTHENTICATION_POLICY_ADMIN | YES | +| 'root'@'localhost' | def | BACKUP_ADMIN | YES | +| 'root'@'localhost' | def | BINLOG_ADMIN | YES | +| 'root'@'localhost' | def | BINLOG_ENCRYPTION_ADMIN | YES | +| 'root'@'localhost' | def | CLONE_ADMIN | YES | +| 'root'@'localhost' | def | CONNECTION_ADMIN | YES | +| 'root'@'localhost' | def | CREATE_SPATIAL_REFERENCE_SYSTEM | YES | +| 'root'@'localhost' | def | ENCRYPTION_KEY_ADMIN | YES | +| 'root'@'localhost' | def | FIREWALL_EXEMPT | YES | +| 'root'@'localhost' | def | FLUSH_OPTIMIZER_COSTS | YES | +| 'root'@'localhost' | def | FLUSH_PRIVILEGES | YES | +| 'root'@'localhost' | def | FLUSH_STATUS | YES | +| 'root'@'localhost' | def | FLUSH_TABLES | YES | +| 'root'@'localhost' | def | FLUSH_USER_RESOURCES | YES | +| 'root'@'localhost' | def | GROUP_REPLICATION_ADMIN | YES | +| 'root'@'localhost' | def | GROUP_REPLICATION_STREAM | YES | +| 'root'@'localhost' | def | INNODB_REDO_LOG_ARCHIVE | YES | +| 'root'@'localhost' | def | INNODB_REDO_LOG_ENABLE | YES | +| 'root'@'localhost' | def | OPTIMIZE_LOCAL_TABLE | YES | +| 'root'@'localhost' | def | PASSWORDLESS_USER_ADMIN | YES | +| 'root'@'localhost' | def | PERSIST_RO_VARIABLES_ADMIN | YES | +| 'root'@'localhost' | def | REPLICATION_APPLIER | YES | +| 'root'@'localhost' | def | REPLICATION_SLAVE_ADMIN | YES | +| 'root'@'localhost' | def | RESOURCE_GROUP_ADMIN | YES | +| 'root'@'localhost' | def | RESOURCE_GROUP_USER | YES | +| 'root'@'localhost' | def | ROLE_ADMIN | YES | +| 'root'@'localhost' | def | SENSITIVE_VARIABLES_OBSERVER | YES | +| 'root'@'localhost' | def | SERVICE_CONNECTION_ADMIN | YES | +| 'root'@'localhost' | def | SESSION_VARIABLES_ADMIN | YES | +| 'root'@'localhost' | def | SET_ANY_DEFINER | YES | +| 'root'@'localhost' | def | SHOW_ROUTINE | YES | +| 'root'@'localhost' | def | SYSTEM_USER | YES | +| 'root'@'localhost' | def | SYSTEM_VARIABLES_ADMIN | YES | +| 'root'@'localhost' | def | TABLE_ENCRYPTION_ADMIN | YES | +| 'root'@'localhost' | def | TELEMETRY_LOG_ADMIN | YES | +| 'root'@'localhost' | def | TRANSACTION_GTID_TAG | YES | +| 'root'@'localhost' | def | XA_RECOVER_ADMIN | YES | +| 'cmc'@'%' | def | USAGE | NO | ++--------------------------------+---------------+---------------------------------+--------------+ +92 rows in set (0.001 sec) +#+end_src + +* =passwords.sql= + +#+begin_src sql +SELECT user, host, plugin FROM mysql.user; +#+end_src + +#+begin_src +mysql> SELECT user, host, plugin FROM mysql.user; ++------------------+-----------+-----------------------+ +| user | host | plugin | ++------------------+-----------+-----------------------+ +| cmc | % | caching_sha2_password | +| mysql.infoschema | localhost | caching_sha2_password | +| mysql.session | localhost | caching_sha2_password | +| mysql.sys | localhost | caching_sha2_password | +| root | localhost | caching_sha2_password | ++------------------+-----------+-----------------------+ +5 rows in set (0.001 sec) +#+end_src + +#+begin_src sql +SHOW GLOBAL VARIABLES LIKE 'validate_password%'; +SHOW VARIABLES LIKE 'validate_password%'; +#+end_src + +#+begin_src +mysql> SHOW GLOBAL VARIABLES LIKE 'validate_password%'; ++-------------------------------------------------+--------+ +| Variable_name | Value | ++-------------------------------------------------+--------+ +| validate_password.changed_characters_percentage | 0 | +| validate_password.check_user_name | ON | +| validate_password.dictionary_file | | +| validate_password.length | 8 | +| validate_password.mixed_case_count | 1 | +| validate_password.number_count | 1 | +| validate_password.policy | MEDIUM | +| validate_password.special_char_count | 1 | ++-------------------------------------------------+--------+ +8 rows in set (0.004 sec) + +mysql> SHOW VARIABLES LIKE 'validate_password%'; ++-------------------------------------------------+--------+ +| Variable_name | Value | ++-------------------------------------------------+--------+ +| validate_password.changed_characters_percentage | 0 | +| validate_password.check_user_name | ON | +| validate_password.dictionary_file | | +| validate_password.length | 8 | +| validate_password.mixed_case_count | 1 | +| validate_password.number_count | 1 | +| validate_password.policy | MEDIUM | +| validate_password.special_char_count | 1 | ++-------------------------------------------------+--------+ +8 rows in set (0.004 sec) +#+end_src + +#+begin_src sql +SELECT * FROM mysql.user +#+end_src + +#+begin_src +MySQL [(none)]> SELECT * FROM mysql.user; ++-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ +| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes | ++-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ +| % | cmc | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | N | 2025-04-25 16:28:52 | NULL | N | N | N | NULL | NULL | NULL | NULL | +| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-04-25 15:51:53 | NULL | Y | N | N | NULL | NULL | NULL | NULL | +| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-04-25 15:51:53 | NULL | Y | N | N | NULL | NULL | NULL | NULL | +| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-04-25 15:51:53 | NULL | Y | N | N | NULL | NULL | NULL | NULL | +| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | N | 2025-04-25 15:51:53 | NULL | N | Y | Y | NULL | NULL | NULL | NULL | ++-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ +5 rows in set (0.005 sec) +#+end_src diff --git a/databases/mysql/mysql_admins.sql b/databases/mysql/mysql_admins.sql new file mode 100644 index 0000000..9115ec5 --- /dev/null +++ b/databases/mysql/mysql_admins.sql @@ -0,0 +1 @@ +SELECT * FROM information_schema.user_privileges; diff --git a/databases/mysql/mysql_admins_alt.sql b/databases/mysql/mysql_admins_alt.sql new file mode 100644 index 0000000..9552ee2 --- /dev/null +++ b/databases/mysql/mysql_admins_alt.sql @@ -0,0 +1,14 @@ +-- Global Permissions +SELECT ... FROM mysql.user; + +-- Database Permissions +SELECT ... FROM mysql.db +WHERE db = @db_name; + +-- Table Permissions +SELECT ... FROM mysql.tables +WHERE db = @db_name; + +-- Column Permissions +SELECT ... FROM mysql.columns_priv +WHERE db = @db_name; diff --git a/databases/mysql/passwords.sql b/databases/mysql/passwords.sql new file mode 100644 index 0000000..1a5bf81 --- /dev/null +++ b/databases/mysql/passwords.sql @@ -0,0 +1,13 @@ +-- NOTE: Please review the server's "my.cnf" file for default values; +-- OR: run the "SHOW [GLOBAL | SESSION] VARIABLES" command(s) on the database. + +-- Authentication methods only +SELECT user, host, plugin FROM mysql.user; + +-- Default password configuration only +SHOW GLOBAL VARIABLES LIKE 'validate_password%'; +SHOW VARIABLES LIKE 'validate_password%'; + +-- Authentication methods and MySQL password configurations +-- Reference: https://mariadb.com/kb/en/mysql-user-table/ +SELECT * FROM mysql.user diff --git a/databases/oracle/oracle_admins.sql b/databases/oracle/oracle_admins.sql new file mode 100644 index 0000000..bac5934 --- /dev/null +++ b/databases/oracle/oracle_admins.sql @@ -0,0 +1,15 @@ +SELECT + grantee AS "User", + privilege AS "Privilege" +FROM + dba_sys_privs +WHERE + grantee IN (SELECT DISTINCT grantee FROM dba_sys_privs) +UNION ALL +SELECT + grantee AS "User", + privilege AS "Privilege" +FROM + dba_tab_privs +WHERE + grantee IN (SELECT DISTINCT grantee FROM dba_tab_privs); diff --git a/databases/oracle/oracle_admins_alt.sql b/databases/oracle/oracle_admins_alt.sql new file mode 100644 index 0000000..4486829 --- /dev/null +++ b/databases/oracle/oracle_admins_alt.sql @@ -0,0 +1,4 @@ +SELECT ** FROM sys.dba_role_privs; +SELECT ** FROM sys.dba_sys_privs; +SELECT ** FROM sys.dba_tab_privs; +SELECT ** FROM sys.dba_users; diff --git a/databases/passwords/mysql/README.org b/databases/passwords/mysql/README.org deleted file mode 100644 index b843bd1..0000000 --- a/databases/passwords/mysql/README.org +++ /dev/null @@ -1,76 +0,0 @@ -#+title: MySQL Passwords - -* =mysql_admins.sql= - -#+begin_src sql -SELECT user, host, plugin FROM mysql.user; -#+end_src - -#+begin_src -mysql> SELECT user, host, plugin FROM mysql.user; -+------------------+-----------+-----------------------+ -| user | host | plugin | -+------------------+-----------+-----------------------+ -| cmc | % | caching_sha2_password | -| mysql.infoschema | localhost | caching_sha2_password | -| mysql.session | localhost | caching_sha2_password | -| mysql.sys | localhost | caching_sha2_password | -| root | localhost | caching_sha2_password | -+------------------+-----------+-----------------------+ -5 rows in set (0.001 sec) -#+end_src - -#+begin_src sql -SHOW GLOBAL VARIABLES LIKE 'validate_password%'; -SHOW VARIABLES LIKE 'validate_password%'; -#+end_src - -#+begin_src -mysql> SHOW GLOBAL VARIABLES LIKE 'validate_password%'; -+-------------------------------------------------+--------+ -| Variable_name | Value | -+-------------------------------------------------+--------+ -| validate_password.changed_characters_percentage | 0 | -| validate_password.check_user_name | ON | -| validate_password.dictionary_file | | -| validate_password.length | 8 | -| validate_password.mixed_case_count | 1 | -| validate_password.number_count | 1 | -| validate_password.policy | MEDIUM | -| validate_password.special_char_count | 1 | -+-------------------------------------------------+--------+ -8 rows in set (0.004 sec) - -mysql> SHOW VARIABLES LIKE 'validate_password%'; -+-------------------------------------------------+--------+ -| Variable_name | Value | -+-------------------------------------------------+--------+ -| validate_password.changed_characters_percentage | 0 | -| validate_password.check_user_name | ON | -| validate_password.dictionary_file | | -| validate_password.length | 8 | -| validate_password.mixed_case_count | 1 | -| validate_password.number_count | 1 | -| validate_password.policy | MEDIUM | -| validate_password.special_char_count | 1 | -+-------------------------------------------------+--------+ -8 rows in set (0.004 sec) -#+end_src - -#+begin_src sql -SELECT * FROM mysql.user -#+end_src - -#+begin_src -MySQL [(none)]> SELECT * FROM mysql.user; -+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ -| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes | -+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ -| % | cmc | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | N | 2025-04-25 16:28:52 | NULL | N | N | N | NULL | NULL | NULL | NULL | -| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-04-25 15:51:53 | NULL | Y | N | N | NULL | NULL | NULL | NULL | -| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-04-25 15:51:53 | NULL | Y | N | N | NULL | NULL | NULL | NULL | -| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2025-04-25 15:51:53 | NULL | Y | N | N | NULL | NULL | NULL | NULL | -| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | N | 2025-04-25 15:51:53 | NULL | N | Y | Y | NULL | NULL | NULL | NULL | -+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+ -5 rows in set (0.005 sec) -#+end_src diff --git a/databases/passwords/mysql/passwords.sql b/databases/passwords/mysql/passwords.sql deleted file mode 100644 index 1a5bf81..0000000 --- a/databases/passwords/mysql/passwords.sql +++ /dev/null @@ -1,13 +0,0 @@ --- NOTE: Please review the server's "my.cnf" file for default values; --- OR: run the "SHOW [GLOBAL | SESSION] VARIABLES" command(s) on the database. - --- Authentication methods only -SELECT user, host, plugin FROM mysql.user; - --- Default password configuration only -SHOW GLOBAL VARIABLES LIKE 'validate_password%'; -SHOW VARIABLES LIKE 'validate_password%'; - --- Authentication methods and MySQL password configurations --- Reference: https://mariadb.com/kb/en/mysql-user-table/ -SELECT * FROM mysql.user diff --git a/databases/passwords/postgres/README.org b/databases/passwords/postgres/README.org deleted file mode 100644 index 694aa4e..0000000 --- a/databases/passwords/postgres/README.org +++ /dev/null @@ -1,31 +0,0 @@ -#+title: Postgres Passwords - -* =passwords.sql= - -#+begin_src sql -SELECT * -FROM pg_settings -WHERE name LIKE 'password_%'; -#+end_src - -#+begin_src -| name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart | -|---------------------+---------------+------+-------------------------------------------------+-------------------------------------------------+------------+---------+---------+---------+---------+---------+---------------------+---------------+---------------+------------+------------+-----------------| -| password_encryption | scram-sha-256 | | Connections and Authentication / Authentication | Chooses the algorithm for encrypting passwords. | | user | enum | default | | | {md5,scram-sha-256} | scram-sha-256 | scram-sha-256 | | | false | -#+end_src - -#+begin_src sql -SELECT - usename AS user_name, - passwd AS password, - valuntil AS valid_until, - useconfig AS user_config -FROM pg_shadow; -#+end_src - -#+begin_src -| user_name | password | valid_until | user_config | -|-----------+---------------------------------------------------------------------------------------------------------------------------------------+------------------------+-------------| -| cmc | | | | -| testuser | SCRAM-SHA-256$4096:+NSpEU+8afhJ4BUTkzdKeg==$FGIRcTWr89b42qkLUl4Ntfp4RUpoc3GIpLHqJl/fWZE=:o1UM8YiEj5SLV5l/geMuqXMRi6onWazryn/l+LXYMxU= | 2025-12-31 00:00:00-06 | | -#+end_src diff --git a/databases/passwords/postgres/passwords.sql b/databases/passwords/postgres/passwords.sql deleted file mode 100644 index cb81cd6..0000000 --- a/databases/passwords/postgres/passwords.sql +++ /dev/null @@ -1,18 +0,0 @@ --- References: --- : https://www.postgresql.org/docs/current/view-pg-shadow.html --- : https://www.postgresql.org/docs/current/auth-password.html --- : https://www.postgresql.org/docs/current/auth-password.html#AUTH-PASSWORD-ENCRYPTION --- : https://www.postgresql.org/docs/current/runtime-config.html - --- Defined password configuration -SELECT * -FROM pg_settings -WHERE name LIKE 'password_%'; - --- Users and their password configurations -SELECT - usename AS user_name, - passwd AS password, - valuntil AS valid_until, - useconfig AS user_config -FROM pg_shadow; \ No newline at end of file diff --git a/databases/passwords/sql/data.csv b/databases/passwords/sql/data.csv deleted file mode 100644 index fc925ea..0000000 --- a/databases/passwords/sql/data.csv +++ /dev/null @@ -1,9 +0,0 @@ -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/passwords/sql/get_data.sql b/databases/passwords/sql/get_data.sql deleted file mode 100644 index b5bef36..0000000 --- a/databases/passwords/sql/get_data.sql +++ /dev/null @@ -1,30 +0,0 @@ -/* -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/passwords/sql/test.py b/databases/passwords/sql/test.py deleted file mode 100644 index 81c1138..0000000 --- a/databases/passwords/sql/test.py +++ /dev/null @@ -1,80 +0,0 @@ -""" -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() diff --git a/databases/postgres/README.org b/databases/postgres/README.org new file mode 100644 index 0000000..e7cd062 --- /dev/null +++ b/databases/postgres/README.org @@ -0,0 +1,75 @@ +#+title: Postgres + +* =passwords.sql= + +#+begin_src sql +SELECT * +FROM pg_settings +WHERE name LIKE 'password_%'; +#+end_src + +#+begin_src +| name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart | +|---------------------+---------------+------+-------------------------------------------------+-------------------------------------------------+------------+---------+---------+---------+---------+---------+---------------------+---------------+---------------+------------+------------+-----------------| +| password_encryption | scram-sha-256 | | Connections and Authentication / Authentication | Chooses the algorithm for encrypting passwords. | | user | enum | default | | | {md5,scram-sha-256} | scram-sha-256 | scram-sha-256 | | | false | +#+end_src + +#+begin_src sql +SELECT + usename AS user_name, + passwd AS password, + valuntil AS valid_until, + useconfig AS user_config +FROM pg_shadow; +#+end_src + +#+begin_src +| user_name | password | valid_until | user_config | +|-----------+---------------------------------------------------------------------------------------------------------------------------------------+------------------------+-------------| +| cmc | | | | +| testuser | SCRAM-SHA-256$4096:+NSpEU+8afhJ4BUTkzdKeg==$FGIRcTWr89b42qkLUl4Ntfp4RUpoc3GIpLHqJl/fWZE=:o1UM8YiEj5SLV5l/geMuqXMRi6onWazryn/l+LXYMxU= | 2025-12-31 00:00:00-06 | | +#+end_src + +* =admins.sql= + +#+begin_src sql +SELECT + r.rolname AS role_name, + r.rolsuper AS is_superuser, + r.rolinherit AS inherits_privileges, + r.rolcreaterole AS can_create_roles, + r.rolcreatedb AS can_create_db, + r.rolcanlogin AS can_login, + r.rolreplication AS can_replication, + r.rolconnlimit AS connection_limit, + r.rolvaliduntil AS valid_until, + ARRAY( + SELECT b.rolname + FROM pg_auth_members m + JOIN pg_roles b ON (m.roleid = b.oid) + WHERE m.member = r.oid + ) AS member_of +FROM pg_roles r; +#+end_src + +#+begin_src +| role_name | is_superuser | inherits_privileges | can_create_roles | can_create_db | can_login | can_replication | connection_limit | valid_until | member_of | +|-----------------------------+--------------+---------------------+------------------+---------------+-----------+-----------------+------------------+------------------------+--------------------------------------------------------------| +| cmc | true | true | true | true | true | true | -1 | | {} | +| pg_database_owner | false | true | false | false | false | false | -1 | | {} | +| pg_read_all_data | false | true | false | false | false | false | -1 | | {} | +| pg_write_all_data | false | true | false | false | false | false | -1 | | {} | +| pg_monitor | false | true | false | false | false | false | -1 | | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables} | +| pg_read_all_settings | false | true | false | false | false | false | -1 | | {} | +| pg_read_all_stats | false | true | false | false | false | false | -1 | | {} | +| pg_stat_scan_tables | false | true | false | false | false | false | -1 | | {} | +| pg_read_server_files | false | true | false | false | false | false | -1 | | {} | +| pg_write_server_files | false | true | false | false | false | false | -1 | | {} | +| pg_execute_server_program | false | true | false | false | false | false | -1 | | {} | +| pg_signal_backend | false | true | false | false | false | false | -1 | | {} | +| pg_checkpoint | false | true | false | false | false | false | -1 | | {} | +| pg_maintain | false | true | false | false | false | false | -1 | | {} | +| pg_use_reserved_connections | false | true | false | false | false | false | -1 | | {} | +| pg_create_subscription | false | true | false | false | false | false | -1 | | {} | +| testuser | false | true | false | false | true | false | -1 | 2025-12-31 00:00:00-06 | {} | +#+end_src diff --git a/databases/postgres/admins.sql b/databases/postgres/admins.sql new file mode 100644 index 0000000..6f9d320 --- /dev/null +++ b/databases/postgres/admins.sql @@ -0,0 +1,22 @@ +-- References: +-- : https://www.postgresql.org/docs/current/user-manag.html +-- : https://www.postgresql.org/docs/current/view-pg-roles.html +-- : https://www.postgresql.org/docs/current/catalog-pg-auth-members.html + +SELECT + r.rolname AS role_name, + r.rolsuper AS is_superuser, + r.rolinherit AS inherits_privileges, + r.rolcreaterole AS can_create_roles, + r.rolcreatedb AS can_create_db, + r.rolcanlogin AS can_login, + r.rolreplication AS can_replication, + r.rolconnlimit AS connection_limit, + r.rolvaliduntil AS valid_until, + ARRAY( + SELECT b.rolname + FROM pg_auth_members m + JOIN pg_roles b ON (m.roleid = b.oid) + WHERE m.member = r.oid + ) AS member_of +FROM pg_roles r; \ No newline at end of file diff --git a/databases/postgres/passwords.sql b/databases/postgres/passwords.sql new file mode 100644 index 0000000..cb81cd6 --- /dev/null +++ b/databases/postgres/passwords.sql @@ -0,0 +1,18 @@ +-- References: +-- : https://www.postgresql.org/docs/current/view-pg-shadow.html +-- : https://www.postgresql.org/docs/current/auth-password.html +-- : https://www.postgresql.org/docs/current/auth-password.html#AUTH-PASSWORD-ENCRYPTION +-- : https://www.postgresql.org/docs/current/runtime-config.html + +-- Defined password configuration +SELECT * +FROM pg_settings +WHERE name LIKE 'password_%'; + +-- Users and their password configurations +SELECT + usename AS user_name, + passwd AS password, + valuntil AS valid_until, + useconfig AS user_config +FROM pg_shadow; \ No newline at end of file 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() -- cgit v1.2.3-70-g09d2