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 ---- 10 files changed, 473 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 (limited to 'databases/administrators') 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 -- cgit v1.2.3-70-g09d2