This chapter is from the book
This chapter is from the book
This chapter is from the book
Artifact Analysis
Once back at the forensic lab, you prepare collected artifacts and imported them into an analysis database named Inv_308_Scenario for further investigation. The first analysis step after confirming unauthorized database access is to determine the level of access the account (or related accounts) has within the database server.
Authorization
Earlier in your investigation, you identified that the MSmith account was the attacker's point of entry and was used to create the EASYACCESS login. Determining the level of access that these accounts have within the database server is a critical factor in meeting your investigation objective of determining whether the attacker was able to access the credit card data.
You execute the following syntax, which will return a list of notable permissions assigned to the MSmith login:
USE INV_308_Scenario DECLARE @LOGIN VARCHAR (100) DECLARE @DBNAME VARCHAR (100) SET @DBNAME = 'ONLINESALES' SET @LOGIN = 'MSmith' -- ENDPOINTS SELECT '1) Server | Endpoint' as 'category', ssr.name as 'grantee', ssp.state_desc as 'assignment', ssp.permission_name, CASE WHEN ssp.class = 105 THEN RTRIM(ssp.class_desc) + '.' + CONVERT (VARCHAR, ssp.major_id) ELSE ssp.class_desc END as 'object', ssp.class_desc as 'object_type', ssi.name as 'grantor' FROM SERV_Perm ssp, SERV_Prin ssr, SERV_Prin ssi WHERE ssp.grantee_principal_id = ssr.principal_id and ssp.grantor_principal_id = ssi.principal_id and ssp.class = 105 and rtrim(ssr.name) IN (@LOGIN, 'PUBLIC') -- LOGINS UNION ALL SELECT '2) Server | Login', name, 'N/A', CASE WHEN logn_sql.status = 1 THEN 'ENABLED' ELSE 'DISABLED' END, 'N/A', 'N/A', 'N/A' from logn_sql WHERE rtrim(logn_sql.name) IN (@LOGIN, 'PUBLIC') --Fixed Server Role Membership UNION ALL SELECT '3) Server | Fixed-server role', syu.name, 'N/A', sys.name , 'N/A', 'N/A', 'N/A' from SERV_Prin syu, SERV_Prin sys, SERV_Rmem sym where sym.member_principal_id = syu.[principal_id] and sym.role_principal_id = sys.[principal_id] and syu.name IN (@LOGIN, 'PUBLIC') -- Database UNION ALL SELECT '4) Database | User', sdr.name, CONVERT(VARCHAR, sde.state_desc), CONVERT(VARCHAR, sde.permission_name), sde.class_desc, sde.class_desc, sdi.name from DBSE_Prin sdr, DBSE_Prin sdi, DBSE_Perm sde where sde.grantee_principal_id = sdr.principal_id and sde.grantor_principal_id = sdi.principal_id and class = 0 and sdr.name IN (@LOGIN, 'PUBLIC') --Database Role Membership UNION ALL SELECT '5) Database | Fixed/User-defined role', syu.name, 'N/A', sys.name , 'N/A', 'N/A', 'N/A' from DBSE_Prin syu, DBSE_Prin sys, DBSE_Rmem sym where sym.member_principal_id = syu.[principal_id] and sym.role_principal_id = sys.[principal_id] and syu.sid IN ((select sid from LOGN_SQL where name = @LOGIN), (select sid from LOGN_SQL where name = 'PUBLIC')) -- Schema UNION ALL SELECT '6) Schema | Schema', sdi.name as 'grantee', sde.state_desc, sde.permission_name, sao.name, sde.class_desc, sdr.name as 'grantor' from DBSE_Perm sde, DBSE_Prin sdi, DBSE_Prin sdr, SCHM_Data sao where sdi.principal_id = sde.grantee_principal_id and sdr.principal_id = sde.grantor_principal_id and sao.schema_id = sde.major_id and sde.class = 3 and sao.[database] = @DBNAME and sdi.sid = (select sid from LOGN_SQL where name = @LOGIN or name = 'PUBLIC') --Database Object UNION ALL SELECT '7) Schema | Object', dbr.name, dbe.state_desc, dbe.permission_name, RTRIM(scd.name) + '.' + CASE WHEN dbe.minor_id >0 THEN RTRIM(syo.name) + '.' + (select name from CLDT_Data where ID = syo.object_id and syo.[database] = @DBNAME and colid = dbe.minor_id) ELSE syo.name END as 'Object', CASE WHEN dbe.minor_id >0 THEN RTRIM(syo.type_desc) + '_COLUMN' ELSE RTRIM(syo.type_desc) END as 'type', dbi.name from DOBJ_Data syo,SCHM_Data scd, DBSE_Prin dbr, DBSE_Prin dbi, DBSE_Perm dbe where dbr.principal_id = dbe.grantee_principal_id and dbi.principal_id = dbe.grantor_principal_id and syo.[database] = @DBNAME and scd.[database] = @DBNAME and scd.schema_id = syo.schema_id and dbr.sid = (select sid from LOGN_SQL where name = @LOGIN or name = 'PUBLIC') and dbe.major_id = syo.object_id order by category, grantee, object
When you run the preceding syntax within your INV_308_Scenario database, you receive the results captured in Figure 11.4. This data reveals that the MSmith login has been granted permissions through server and database fixed-role membership.
)
Figure 11.4 Permissions granted to the MSmith login
Briefly reviewing the permission hierarchy, you make the following observations:
- Category 1: The MSmith account has implied permission to connect to the SQL Server endpoint through membership in the public server role.
- Category 2: The second stage in the hierarchy shows that MSmith has an enabled login account, which will allow this account access to the SQL Server instance.
- Category 3: Membership in the securityadmin, serveradmin, and setupadmin roles grants the MSmith account the ability to assign server-wide permissions, alter the server and/or endpoints, change permissions on the server, and alter linked servers. (Refer to Chapters 2 and 8 for additional details about permissions.)
- Category 4: The CONNECT permission gives the MSmith login access to the OnlineSales database.
- Category 5: Fixed-database role membership in the db_datareader and db_datawriter roles grants the MSmith account read and write access within the OnlineSales database, including the Orders table—which contains sensitive information.
Next, you rerun your earlier executed syntax, this time using the EASYACCESS as the login, to determine the level of permission the EASYACCESS account had within the database. The results you receive, which are captured in Figure 11.5, show that no explicit or implied permissions were assigned to the EASYACCESS login.
)
Figure 11.5 Permissions granted to the EASYACCESS login
Because no permission has been explicitly denied to the MSmith login, and because the highest level of access is the permission assigned within Category 3 (fixed-server role membership), you check the permissions placed on encryption keys and certificates. These permissions will be the deciding factor in confirming whether the MSmith account has sufficient access within the database to access the credit card data.
Native SQL Server Encryption
As you discussed with the client at the beginning of the investigation, encryption is in use on the database server. The MSmith account, which you verified was compromised during the brute-force attack, is believed to have access to the encryption keys.
To observe a listing of keys gathered from the server during artifact collection, you execute the following syntax against the Inv_308_Scenario database. This syntax returns a list of all symmetric keys on the victim system:
Select name, create_date, modify_date, key_guid from dbse_semk
The results of this query, which are captured in Figure 11.6, show that in addition to the database master key, there are two encryption keys on the system. This finding is a good indicator that encryption is in use.
)
Figure 11.6 Identified symmetric keys on the victim system
Because native SQL Server encryption supports only the varbinary data type, to further confirm the use of encryption, you perform a search on all columns in use within the database using the varbinary data type. To do so, you execute the following syntax within the INV_308_Scenario database:
select dbj.name as 'object', dbj.type_desc as 'object_type', cdt.name as 'column_name', st.name as 'datatype' FROM CLDT_Data cdt, systypes st, DOBJ_Data dbj where st.xusertype = cdt.xusertype and dbj.object_id = cdt.id and st.name = 'Varbinary' and dbj.is_ms_shipped = 0
Once this query is run, you receive the results captured in Figure 11.7. These results indicate that three tables use the varbinary data type.
)
Figure 11.7 Identified tables using the varbinary data type
The sysdiagrams table, although set with an is_ms_shipped value of 0, is not a user object; thus the client would not use it to store application data. With this caveat in mind, you determine that the only two tables using the varbinary data type are the Orders and ORDERSBKU tables. Earlier in the investigation, the client mentioned to you that the ORDERSBKU table did not contain production credit card information and that your investigation should focus on the Orders table. Therefore you exclude the ORDERSBKU table from the remainder of the investigation.
You've now confirmed that encrypted data is likely to be found within the CCNumber column of the Orders table. You execute the following statement to determine if the compromised database account has access to the encryption keys created on the victim server:
SELECT syk.name as 'key_name', class_desc, spr.name as 'db_user', permission_name, state_desc, major_id from dbse_perm sdp, dbse_semk syk, dbse_prin spr where class IN (24, 25, 26) and syk.symmetric_key_id = sdp.major_id and spr.principal_id = sdp.grantee_principal_id and class = 24 ORDER BY key_name, spr.name, permission_name, state_desc
When this code is run, you receive the results captured in Figure 11.8. As seen in the figure, the MSmith login was denied VIEW DEFINITION permission on the CCProtect_Key. This login does, however, have VIEW DEFINITION permission on the CCProtect_NewKey along with another SQL Server user.
)
Figure 11.8 Symmetric key permissions
For a user to have adequate permissions to encrypt and decrypt data using an encryption key within SQL Server, the user would need to have any level of permission on the key granted to that user. As your findings in Figure 11.8 reveal, the VIEW DEFINITION permission on the CCProtect_NewKey would have allowed the attacker to decrypt or encrypt data encrypted by it. However, the CCProtect_Key is another symmetric key on the system to which the MSmith account has been explicitly denied access. Earlier the client stated that CCProtect_Key is the current key being used for encryption. You have now proved the compromised account did not have access to this key to decrypt the sensitive data.
The final objective of your investigation is to determine the actions performed by the unauthorized user within the database. This objective can be satisfied through activity reconstruction.