feat: total re-write from Emacs org-mode to Zola markdown

1 files changed, 0 insertions, 278 deletions

diff --git a/blog/2023-09-19-audit-sql-scripts.org b/blog/2023-09-19-audit-sql-scripts.org
deleted file mode 100644
index 3ab4b2a..0000000
--- a/blog/2023-09-19-audit-sql-scripts.org
+++ /dev/null
@@ -1,278 +0,0 @@
-#+title: Useful SQL Scripts for Auditing Logical Access
-#+date:  2023-09-20
-
-** Overview
-:PROPERTIES:
-:CUSTOM_ID: overview
-:END:
-When you have to scope a database into your engagement, you may be
-curious how to best extract the information from the database. While
-there are numerous different methods to extract this type of
-information, I'm going to show an example of how to gather all users and
-privileges from three main database types: Oracle, Microsoft SQL, and
-MySQL.
-
-** Oracle
-:PROPERTIES:
-:CUSTOM_ID: oracle
-:END:
-You can use the following SQL script to see all users and their
-privileges in an Oracle database:
-
-#+begin_src sql
-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);
-#+end_src
-
-This script queries the =dba_sys_privs= and =dba_tab_privs= views to
-retrieve system and table-level privileges respectively. It then
-combines the results using =UNION ALL= to show all users and their
-associated privileges. Please note that this method does not extract
-information from the =dba_role_privs= table - use the method below for
-that data.
-
-Please note that you might need appropriate privileges (e.g., DBA
-privileges) to access these views, and you should exercise caution when
-querying system tables in a production Oracle database.
-
-*** Alternative Oracle Query
-:PROPERTIES:
-:CUSTOM_ID: alternative-oracle-query
-:END:
-You can also extract each table's information separately and perform
-processing outside the database to explore and determine the information
-necessary for the audit:
-
-#+begin_src sql
-SELECT * FROM sys.dba_role_privs;
-SELECT * FROM sys.dba_sys_privs;
-SELECT * FROM sys.dba_tab_privs;
-SELECT * FROM sys.dba_users; 
-#+end_src
-
-** Microsoft SQL
-:PROPERTIES:
-:CUSTOM_ID: microsoft-sql
-:END:
-You can use the following SQL script to see all users and their
-privileges in a Microsoft SQL Server database
-([[https://stackoverflow.com/a/30040784][source]]):
-
-#+begin_src sql
-/*
-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]
-#+end_src
-
-** MySQL
-:PROPERTIES:
-:CUSTOM_ID: mysql
-:END:
-You can use the following SQL script to see all users and their
-privileges in a MySQL database:
-
-#+begin_src shell
-mysql -u root -p
-#+end_src
-
-Find all users and hosts with access to the database:
-
-#+begin_src sql
-SELECT * FROM information_schema.user_privileges;
-#+end_src
-
-This script retrieves user information and their associated
-database-level privileges from the =information_schema.user_privileges=
-table in MySQL. It lists various privileges such as SELECT, INSERT,
-UPDATE, DELETE, CREATE, and more for each user and database combination.
-
-Please note that you may need appropriate privileges (e.g., =SELECT=
-privileges on =information_schema.user_privileges=) to access this
-information in a MySQL database. Additionally, some privileges like
-GRANT OPTION, EXECUTE, EVENT, and TRIGGER may not be relevant for all
-users and databases.
-
-*** Alternative MySQL Query
-:PROPERTIES:
-:CUSTOM_ID: alternative-mysql-query
-:END:
-You can also grab individual sets of data from MySQL if you prefer to
-join them after extraction. I have marked the queries below with
-=SELECT ...= and excluded most =WHERE= clauses for brevity. You should
-determine the relevant privileges in-scope and query for those
-privileges to reduce the length of time to query.
-
-#+begin_src sql
--- 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;
-
--- Password Configuration
-SHOW GLOBAL VARIABLES LIKE 'validate_password%'; 
-SHOW VARIABLES LIKE 'validate_password%';
-#+end_src