cmc/cleberg.net

  1#+date:        [2023-09-19 Tue 00:00:00]
  2#+title:       SQL Scripts for Auditing User Privileges
  3#+description: SQL queries for auditing user access in Oracle, SQL Server, and MySQL.
  4#+slug:        audit-sql-scripts
  5#+filetags:    :audit:
  6
  7* Overview
  8
  9When you have to scope a database into your engagement, you may be
 10curious how to best extract the information from the database. While
 11there are numerous different methods to extract this type of
 12information, I'm going to show an example of how to gather all users and
 13privileges from three main database types: Oracle, Microsoft SQL, and
 14MySQL.
 15
 16* Oracle
 17
 18You can use the following SQL script to see all users and their
 19privileges in an Oracle database:
 20
 21#+begin_src sql
 22SELECT
 23    grantee AS "User",
 24    privilege AS "Privilege"
 25FROM
 26    dba_sys_privs
 27WHERE
 28    grantee IN (SELECT DISTINCT grantee FROM dba_sys_privs)
 29UNION ALL
 30SELECT
 31    grantee AS "User",
 32    privilege AS "Privilege"
 33FROM
 34    dba_tab_privs
 35WHERE
 36    grantee IN (SELECT DISTINCT grantee FROM dba_tab_privs);
 37#+end_src
 38
 39This script queries the =dba_sys_privs= and =dba_tab_privs= views to
 40retrieve system and table-level privileges respectively. It then
 41combines the results using =UNION ALL= to show all users and their
 42associated privileges. Please note that this method does not extract
 43information from the =dba_role_privs= table - use the method below for
 44that data.
 45
 46Please note that you might need appropriate privileges (e.g., DBA
 47privileges) to access these views, and you should exercise caution when
 48querying system tables in a production Oracle database.
 49
 50** Alternative Oracle Query
 51
 52You can also extract each table's information separately and perform
 53processing outside the database to explore and determine the information
 54necessary for the audit:
 55
 56#+begin_src sql
 57SELECT ** FROM sys.dba_role_privs;
 58SELECT ** FROM sys.dba_sys_privs;
 59SELECT ** FROM sys.dba_tab_privs;
 60SELECT ** FROM sys.dba_users;
 61#+end_src
 62
 63* Microsoft SQL
 64
 65You can use the following SQL script to see all users and their
 66privileges in a Microsoft SQL Server database
 67([[https://stackoverflow.com/a/30040784][source]]):
 68
 69#+begin_src sql
 70/*
 71Security Audit Report
 721) List all access provisioned to a sql user or windows user/group directly
 732) List all access provisioned to a sql user or windows user/group through a database or application role
 743) List all access provisioned to the public role
 75
 76Columns Returned:
 77UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
 78UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the
 79                  SQL Server user account.
 80DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
 81                  same as the server user.
 82Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
 83                  on the user account, otherwise this will be the name of the role that the user is a member of.
 84PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
 85                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
 86                  This value may not be populated for all roles.  Some built in roles have implicit permission
 87                  definitions.
 88PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
 89                  This value may not be populated for all roles.  Some built in roles have implicit permission
 90                  definitions.
 91ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
 92                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
 93                  This value may not be populated for all roles.  Some built in roles have implicit permission
 94                  definitions.
 95ObjectName      : Name of the object that the user/role is assigned permissions on.
 96                  This value may not be populated for all roles.  Some built in roles have implicit permission
 97                  definitions.
 98ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
 99                  is only populated if the object is a table, view or a table value function.
100,*/
101
102--List all access provisioned to a sql user or windows user/group directly
103SELECT
104    [UserName] = CASE princ.[type]
105                    WHEN 'S' THEN princ.[name]
106                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
107                 END,
108    [UserType] = CASE princ.[type]
109                    WHEN 'S' THEN 'SQL User'
110                    WHEN 'U' THEN 'Windows User'
111                 END,
112    [DatabaseUserName] = princ.[name],
113    [Role] = null,
114    [PermissionType] = perm.[permission_name],
115    [PermissionState] = perm.[state_desc],
116    [ObjectType] = obj.type_desc,--perm.[class_desc],
117    [ObjectName] = OBJECT_NAME(perm.major_id),
118    [ColumnName] = col.[name]
119FROM
120    --database user
121    sys.database_principals princ
122LEFT JOIN
123    --Login accounts
124    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
125LEFT JOIN
126    --Permissions
127    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
128LEFT JOIN
129    --Table columns
130    sys.columns col ON col.[object_id] = perm.major_id
131                    AND col.[column_id] = perm.[minor_id]
132LEFT JOIN
133    sys.objects obj ON perm.[major_id] = obj.[object_id]
134WHERE
135    princ.[type] in ('S','U')
136UNION
137--List all access provisioned to a sql user or windows user/group through a database or application role
138SELECT
139    [UserName] = CASE memberprinc.[type]
140                    WHEN 'S' THEN memberprinc.[name]
141                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
142                 END,
143    [UserType] = CASE memberprinc.[type]
144                    WHEN 'S' THEN 'SQL User'
145                    WHEN 'U' THEN 'Windows User'
146                 END,
147    [DatabaseUserName] = memberprinc.[name],
148    [Role] = roleprinc.[name],
149    [PermissionType] = perm.[permission_name],
150    [PermissionState] = perm.[state_desc],
151    [ObjectType] = obj.type_desc,--perm.[class_desc],
152    [ObjectName] = OBJECT_NAME(perm.major_id),
153    [ColumnName] = col.[name]
154FROM
155    --Role/member associations
156    sys.database_role_members members
157JOIN
158    --Roles
159    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
160JOIN
161    --Role members (database users)
162    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
163LEFT JOIN
164    --Login accounts
165    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
166LEFT JOIN
167    --Permissions
168    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
169LEFT JOIN
170    --Table columns
171    sys.columns col on col.[object_id] = perm.major_id
172                    AND col.[column_id] = perm.[minor_id]
173LEFT JOIN
174    sys.objects obj ON perm.[major_id] = obj.[object_id]
175UNION
176--List all access provisioned to the public role, which everyone gets by default
177SELECT
178    [UserName] = '{All Users}',
179    [UserType] = '{All Users}',
180    [DatabaseUserName] = '{All Users}',
181    [Role] = roleprinc.[name],
182    [PermissionType] = perm.[permission_name],
183    [PermissionState] = perm.[state_desc],
184    [ObjectType] = obj.type_desc,--perm.[class_desc],
185    [ObjectName] = OBJECT_NAME(perm.major_id),
186    [ColumnName] = col.[name]
187FROM
188    --Roles
189    sys.database_principals roleprinc
190LEFT JOIN
191    --Role permissions
192    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
193LEFT JOIN
194    --Table columns
195    sys.columns col on col.[object_id] = perm.major_id
196                    AND col.[column_id] = perm.[minor_id]
197JOIN
198    --All objects
199    sys.objects obj ON obj.[object_id] = perm.[major_id]
200WHERE
201    --Only roles
202    roleprinc.[type] = 'R' AND
203    --Only public role
204    roleprinc.[name] = 'public' AND
205    --Only objects of ours, not the MS objects
206    obj.is_ms_shipped = 0
207ORDER BY
208    princ.[Name],
209    OBJECT_NAME(perm.major_id),
210    col.[name],
211    perm.[permission_name],
212    perm.[state_desc],
213    obj.type_desc--perm.[class_desc]
214#+end_src
215
216* MySQL
217
218You can use the following SQL script to see all users and their
219privileges in a MySQL database:
220
221#+begin_src sh
222mysql -u root -p
223#+end_src
224
225Find all users and hosts with access to the database:
226
227#+begin_src sql
228SELECT ** FROM information_schema.user_privileges;
229#+end_src
230
231This script retrieves user information and their associated
232database-level privileges from the =information_schema.user_privileges=
233table in MySQL. It lists various privileges such as SELECT, INSERT,
234UPDATE, DELETE, CREATE, and more for each user and database combination.
235
236Please note that you may need appropriate privileges (e.g., =SELECT=
237privileges on =information_schema.user_privileges=) to access this
238information in a MySQL database. Additionally, some privileges like
239GRANT OPTION, EXECUTE, EVENT, and TRIGGER may not be relevant for all
240users and databases.
241
242** Alternative MySQL Query
243
244You can also grab individual sets of data from MySQL if you prefer to
245join them after extraction. I have marked the queries below with
246=SELECT ...= and excluded most =WHERE= clauses for brevity. You should
247determine the relevant privileges in-scope and query for those
248privileges to reduce the length of time to query.
249
250#+begin_src sql
251-- Global Permissions
252SELECT ... FROM mysql.user;
253
254-- Database Permissions
255SELECT ... FROM mysql.db
256WHERE db = @db_name;
257
258-- Table Permissions
259SELECT ... FROM mysql.tables
260WHERE db = @db_name;
261
262-- Column Permissions
263SELECT ... FROM mysql.columns_priv
264WHERE db = @db_name;
265
266-- Password Configuration
267SHOW GLOBAL VARIABLES LIKE 'validate_password%';
268SHOW VARIABLES LIKE 'validate_password%';
269#+end_src