aboutsummaryrefslogtreecommitdiff
path: root/databases/administrators
diff options
context:
space:
mode:
Diffstat (limited to 'databases/administrators')
-rw-r--r--databases/administrators/microsoft-sql/mssql_admins.sql144
-rw-r--r--databases/administrators/mongo/README.org104
-rw-r--r--databases/administrators/mongo/admins.py16
-rw-r--r--databases/administrators/mysql/README.org108
-rw-r--r--databases/administrators/mysql/mysql_admins.sql1
-rw-r--r--databases/administrators/mysql/mysql_admins_alt.sql14
-rw-r--r--databases/administrators/oracle/oracle_admins.sql15
-rw-r--r--databases/administrators/oracle/oracle_admins_alt.sql4
-rw-r--r--databases/administrators/postgres/README.org45
-rw-r--r--databases/administrators/postgres/admins.sql22
10 files changed, 0 insertions, 473 deletions
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
generated by cgit v1.2.3-70-g09d2 (git 2.46.0) at 2025-09-15 06:19:41 +0000