conversion from Zola to Weblorg

1 files changed, 268 insertions, 0 deletions

diff --git a/content/blog/2023-09-19-audit-sql-scripts.org b/content/blog/2023-09-19-audit-sql-scripts.org
new file mode 100644
index 0000000..1e2da28
--- /dev/null
+++ b/content/blog/2023-09-19-audit-sql-scripts.org
@@ -0,0 +1,268 @@
+#+date: <2023-09-19>
+#+title: Useful SQL Scripts for Auditing Logical Access
+#+description: 
+
+
+* Overview
+
+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
+
+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
+
+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
+
+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
+
+You can use the following SQL script to see all users and their
+privileges in a MySQL database:
+
+#+begin_src sh
+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
+
+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