19 package org.sleuthkit.datamodel;
21 import com.google.common.base.Strings;
22 import org.apache.commons.lang3.StringUtils;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.SQLException;
26 import java.sql.Statement;
27 import java.sql.Types;
28 import java.util.Collections;
29 import java.util.ArrayList;
30 import java.util.List;
31 import java.util.NavigableMap;
32 import java.util.Objects;
33 import java.util.Optional;
34 import java.util.UUID;
35 import java.util.concurrent.ConcurrentSkipListMap;
36 import java.util.stream.Collectors;
56 private final Object osAcctInstancesCacheLock;
57 private final NavigableMap<OsAccountInstanceKey, OsAccountInstance> osAccountInstanceCache;
67 osAcctInstancesCacheLock =
new Object();
68 osAccountInstanceCache =
new ConcurrentSkipListMap<>();
87 if (Strings.isNullOrEmpty(uniqueAccountId)) {
104 }
catch (SQLException ex) {
110 Optional<OsAccount> osAccount = this.getOsAccountByAddr(uniqueAccountId, realm);
111 if (osAccount.isPresent()) {
112 return osAccount.get();
116 throw new TskCoreException(String.format(
"Error creating OsAccount with uniqueAccountId = %s in realm id = %d", uniqueAccountId, realm.getRealmId()), ex);
151 if (realmScope == null) {
152 throw new TskCoreException(
"RealmScope cannot be null. Use UNKNOWN if scope is not known.");
154 if (referringHost == null) {
155 throw new TskCoreException(
"A referring host is required to create an account.");
159 if ((StringUtils.isBlank(sid) || sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))
160 && StringUtils.isBlank(loginName)) {
161 throw new TskCoreException(
"Cannot create OS account with both uniqueId and loginName as null.");
164 if ((StringUtils.isBlank(sid) || sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))
165 && StringUtils.isBlank(realmName)) {
166 throw new TskCoreException(
"Realm name or SID is required to create a Windows account.");
169 if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && !WindowsAccountUtils.isWindowsUserSid(sid)) {
174 if (StringUtils.isBlank(sid)
175 && !StringUtils.isBlank(loginName) && !StringUtils.isBlank(realmName)
176 && WindowsAccountUtils.isWindowsWellKnownAccountName(loginName, realmName)) {
177 sid = WindowsAccountUtils.getWindowsWellKnownAccountSid(loginName, realmName);
182 Optional<OsAccountRealm> anotherRealmWithSameName = Optional.empty();
183 Optional<OsAccountRealm> anotherRealmWithSameAddr = Optional.empty();
187 try (CaseDbConnection connection = db.getConnection()) {
188 realmUpdateResult = db.
getOsAccountRealmManager().getAndUpdateWindowsRealm(sid, realmName, referringHost, connection);
190 Optional<OsAccountRealm> realmOptional = realmUpdateResult.
getUpdatedRealm();
191 if (realmOptional.isPresent()) {
192 realm = realmOptional.get();
200 anotherRealmWithSameName = db.
getOsAccountRealmManager().getAnotherRealmByName(realmOptional.get(), realmName, referringHost, connection);
203 anotherRealmWithSameAddr = db.
getOsAccountRealmManager().getAnotherRealmByAddr(realmOptional.get(), realmName, referringHost, connection);
213 if (anotherRealmWithSameName.isPresent() || anotherRealmWithSameAddr.isPresent()) {
217 if (anotherRealmWithSameName.isPresent()) {
220 if (anotherRealmWithSameAddr.isPresent()) {
225 }
catch (TskCoreException ex) {
256 if ((StringUtils.isBlank(sid) || sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))
257 && StringUtils.isBlank(loginName)) {
258 throw new TskCoreException(
"Cannot create OS account with both uniqueId and loginName as null.");
261 if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && !WindowsAccountUtils.isWindowsUserSid(sid)) {
266 String resolvedLoginName = WindowsAccountUtils.toWellknownEnglishLoginName(loginName);
272 String uniqueId = (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) ? sid : null;
273 if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && isWindowsWellKnownSid(sid)) {
275 String wellKnownLoginName = WindowsAccountUtils.getWindowsWellKnownSidLoginName(sid);
276 if (!StringUtils.isEmpty(wellKnownLoginName)) {
277 resolvedLoginName = wellKnownLoginName;
284 if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && isWindowsWellKnownSid(sid)) {
285 String fullName = getWindowsWellKnownSidFullName(sid);
286 if (StringUtils.isNotBlank(fullName)) {
296 }
catch (SQLException ex) {
302 Optional<OsAccount> osAccount;
305 if (!Strings.isNullOrEmpty(sid)) {
306 osAccount = getOsAccountByAddr(sid, realm);
307 if (osAccount.isPresent()) {
308 return osAccount.get();
313 if (!Strings.isNullOrEmpty(resolvedLoginName)) {
314 osAccount = getOsAccountByLoginName(resolvedLoginName, realm);
315 if (osAccount.isPresent()) {
316 return osAccount.get();
321 throw new TskCoreException(String.format(
"Error creating OsAccount with sid = %s, loginName = %s, realm = %s, referring host = %s",
322 (sid != null) ? sid :
"Null",
323 (resolvedLoginName != null) ? resolvedLoginName :
"Null",
350 if (Objects.isNull(realm)) {
351 throw new TskCoreException(
"Cannot create an OS Account, realm is NULL.");
354 String signature = getOsAccountSignature(uniqueId, loginName);
357 CaseDbConnection connection = trans.getConnection();
362 long parentObjId = 0;
364 int objTypeId = TskData.ObjectType.OS_ACCOUNT.getObjectType();
365 long osAccountObjId = db.addObject(parentObjId, objTypeId, connection);
367 String accountInsertSQL =
"INSERT INTO tsk_os_accounts(os_account_obj_id, login_name, realm_id, addr, signature, status)"
368 +
" VALUES (?, ?, ?, ?, ?, ?)";
370 PreparedStatement preparedStatement = connection.getPreparedStatement(accountInsertSQL, Statement.NO_GENERATED_KEYS);
371 preparedStatement.clearParameters();
373 preparedStatement.setLong(1, osAccountObjId);
375 preparedStatement.setString(2, loginName);
376 preparedStatement.setLong(3, realm.getRealmId());
378 preparedStatement.setString(4, uniqueId);
379 preparedStatement.setString(5, signature);
380 preparedStatement.setInt(6, accountStatus.getId());
382 connection.executeUpdate(preparedStatement);
384 account =
new OsAccount(db, osAccountObjId, realm.getRealmId(), loginName, uniqueId, signature,
385 null, null, null, accountStatus, OsAccount.OsAccountDbStatus.ACTIVE);
387 trans.registerAddedOsAccount(account);
402 private Optional<OsAccount> getOsAccountByAddr(String addr, Host host)
throws TskCoreException {
404 try (CaseDbConnection connection = db.getConnection()) {
405 return getOsAccountByAddr(addr, host, connection);
421 private Optional<OsAccount> getOsAccountByAddr(String uniqueId, Host host, CaseDbConnection connection)
throws TskCoreException {
423 String whereHostClause = (host == null)
425 :
" ( realms.scope_host_id = " + host.getHostId() +
" OR realms.scope_host_id IS NULL) ";
427 String queryString =
"SELECT accounts.os_account_obj_id as os_account_obj_id, accounts.login_name, accounts.full_name, "
428 +
" accounts.realm_id, accounts.addr, accounts.signature, "
429 +
" accounts.type, accounts.status, accounts.admin, accounts.created_date, accounts.db_status, "
430 +
" realms.realm_name as realm_name, realms.realm_addr as realm_addr, realms.realm_signature, realms.scope_host_id, realms.scope_confidence, realms.db_status as realm_db_status "
431 +
" FROM tsk_os_accounts as accounts"
432 +
" LEFT JOIN tsk_os_account_realms as realms"
433 +
" ON accounts.realm_id = realms.id"
434 +
" WHERE " + whereHostClause
436 +
" AND LOWER(accounts.addr) = LOWER('" + uniqueId +
"')";
439 try (Statement s = connection.createStatement();
440 ResultSet rs = connection.executeQuery(s, queryString)) {
443 return Optional.empty();
445 return Optional.of(osAccountFromResultSet(rs));
447 }
catch (SQLException ex) {
448 throw new TskCoreException(String.format(
"Error getting OS account for unique id = %s and host = %s", uniqueId, (host != null ? host.getName() :
"null")), ex);
465 Optional<OsAccount> getOsAccountByAddr(String uniqueId, OsAccountRealm realm)
throws TskCoreException {
467 String queryString =
"SELECT * FROM tsk_os_accounts"
468 +
" WHERE LOWER(addr) = LOWER('" + uniqueId +
"')"
470 +
" AND realm_id = " + realm.getRealmId();
473 try (CaseDbConnection connection = this.db.getConnection();
474 Statement s = connection.createStatement();
475 ResultSet rs = connection.executeQuery(s, queryString)) {
478 return Optional.empty();
480 return Optional.of(osAccountFromResultSet(rs));
482 }
catch (SQLException ex) {
483 throw new TskCoreException(String.format(
"Error getting OS account for realm = %s and uniqueId = %s.", (realm != null) ? realm.getSignature() :
"NULL", uniqueId), ex);
500 Optional<OsAccount> getOsAccountByLoginName(String loginName, OsAccountRealm realm)
throws TskCoreException {
502 String queryString =
"SELECT * FROM tsk_os_accounts"
503 +
" WHERE LOWER(login_name) = LOWER('" + loginName +
"')"
505 +
" AND realm_id = " + realm.getRealmId();
508 try (CaseDbConnection connection = this.db.getConnection();
509 Statement s = connection.createStatement();
510 ResultSet rs = connection.executeQuery(s, queryString)) {
513 return Optional.empty();
515 return Optional.of(osAccountFromResultSet(rs));
517 }
catch (SQLException ex) {
518 throw new TskCoreException(String.format(
"Error getting OS account for realm = %s and loginName = %s.", (realm != null) ? realm.getSignature() :
"NULL", loginName), ex);
535 try (CaseDbConnection connection = this.db.getConnection()) {
552 String queryString =
"SELECT * FROM tsk_os_accounts"
553 +
" WHERE os_account_obj_id = " + osAccountObjId;
556 try (Statement s = connection.createStatement();
557 ResultSet rs = connection.executeQuery(s, queryString)) {
560 throw new TskCoreException(String.format(
"No account found with obj id = %d ", osAccountObjId));
562 return osAccountFromResultSet(rs);
564 }
catch (SQLException ex) {
565 throw new TskCoreException(String.format(
"Error getting account with obj id = %d ", osAccountObjId), ex);
596 if (osAccount == null) {
597 throw new TskCoreException(
"Cannot create account instance with null account.");
599 if (dataSource == null) {
600 throw new TskCoreException(
"Cannot create account instance with null data source.");
604 Optional<OsAccountInstance> existingInstance = cachedAccountInstance(osAccount.
getId(), dataSource.
getId(), instanceType);
605 if (existingInstance.isPresent()) {
606 return existingInstance.get();
609 try (CaseDbConnection connection = this.db.getConnection()) {
631 Optional<OsAccountInstance> existingInstance = cachedAccountInstance(osAccountId, dataSourceObjId, instanceType);
632 if (existingInstance.isPresent()) {
633 return existingInstance.get();
641 String accountInsertSQL = db.getInsertOrIgnoreSQL(
"INTO tsk_os_account_instances(os_account_obj_id, data_source_obj_id, instance_type)"
642 +
" VALUES (?, ?, ?)");
643 PreparedStatement preparedStatement = connection.getPreparedStatement(accountInsertSQL, Statement.RETURN_GENERATED_KEYS);
644 preparedStatement.clearParameters();
645 preparedStatement.setLong(1, osAccountId);
646 preparedStatement.setLong(2, dataSourceObjId);
647 preparedStatement.setInt(3, instanceType.getId());
648 connection.executeUpdate(preparedStatement);
649 try (ResultSet resultSet = preparedStatement.getGeneratedKeys();) {
650 if (resultSet.next()) {
651 OsAccountInstance accountInstance =
new OsAccountInstance(db, resultSet.getLong(1), osAccountId, dataSourceObjId, instanceType);
652 synchronized (osAcctInstancesCacheLock) {
653 OsAccountInstanceKey key =
new OsAccountInstanceKey(osAccountId, dataSourceObjId);
655 for (OsAccountInstance.OsAccountInstanceType type : OsAccountInstance.OsAccountInstanceType.values()) {
656 if (accountInstance.getInstanceType().compareTo(type) < 0) {
657 osAccountInstanceCache.remove(key);
661 osAccountInstanceCache.put(key, accountInstance);
678 db.fireTSKEvent(
new TskEvent.OsAcctInstancesAddedTskEvent(Collections.singletonList(accountInstance)));
680 return accountInstance;
685 Optional<OsAccountInstance> existingInstanceRetry = cachedAccountInstance(osAccountId, dataSourceObjId, instanceType);
686 if (existingInstanceRetry.isPresent()) {
687 return existingInstanceRetry.get();
691 }
catch (SQLException ex) {
692 throw new TskCoreException(String.format(
"Error adding OS account instance for OS account object id = %d, data source object id = %d", osAccountId, dataSourceObjId), ex);
700 String whereClause =
"tsk_os_account_instances.os_account_obj_id = " + osAccountId
701 +
"AND tsk_os_account_instances.data_source_obj_id = " + dataSourceObjId;
703 if (instances.isEmpty()) {
704 throw new TskCoreException(String.format(
"Could not get autogen key after row insert or reload instance for OS account instance. OS account object id = %d, data source object id = %d", osAccountId, dataSourceObjId));
707 OsAccountInstance accountInstance = instances.get(0);
708 synchronized (osAcctInstancesCacheLock) {
709 OsAccountInstanceKey key =
new OsAccountInstanceKey(osAccountId, dataSourceObjId);
711 for (OsAccountInstance.OsAccountInstanceType type : OsAccountInstance.OsAccountInstanceType.values()) {
712 if (accountInstance.getInstanceType().compareTo(type) < 0) {
713 osAccountInstanceCache.remove(key);
717 osAccountInstanceCache.put(key, accountInstance);
719 return accountInstance;
738 private Optional<OsAccountInstance> cachedAccountInstance(
long osAccountId,
long dataSourceObjId, OsAccountInstance.OsAccountInstanceType instanceType) {
747 synchronized (osAcctInstancesCacheLock) {
748 OsAccountInstanceKey key =
new OsAccountInstanceKey(osAccountId, dataSourceObjId);
749 OsAccountInstance instance = osAccountInstanceCache.get(key);
750 if (instance != null) {
752 if (instanceType.compareTo(instance.getInstanceType()) >= 0) {
753 return Optional.of(instance);
756 return Optional.empty();
770 String queryString =
"SELECT * FROM tsk_os_accounts accounts "
771 +
"WHERE accounts.os_account_obj_id IN "
772 +
"(SELECT instances.os_account_obj_id "
773 +
"FROM tsk_os_account_instances instances "
774 +
"INNER JOIN data_source_info datasources ON datasources.obj_id = instances.data_source_obj_id "
775 +
"WHERE datasources.host_id = " + host.getHostId() +
") "
779 try (CaseDbConnection connection = this.db.getConnection();
780 Statement s = connection.createStatement();
781 ResultSet rs = connection.executeQuery(s, queryString)) {
783 List<OsAccount> accounts =
new ArrayList<>();
785 accounts.add(osAccountFromResultSet(rs));
788 }
catch (SQLException ex) {
789 throw new TskCoreException(String.format(
"Error getting OS accounts for host id = %d", host.getHostId()), ex);
805 String queryString =
"SELECT * FROM tsk_os_accounts acc "
806 +
"WHERE acc.os_account_obj_id IN "
807 +
"(SELECT instance.os_account_obj_id "
808 +
"FROM tsk_os_account_instances instance "
809 +
"WHERE instance.data_source_obj_id = " + dataSourceId +
") "
813 try (CaseDbConnection connection = this.db.getConnection();
814 Statement s = connection.createStatement();
815 ResultSet rs = connection.executeQuery(s, queryString)) {
817 List<OsAccount> accounts =
new ArrayList<>();
819 accounts.add(osAccountFromResultSet(rs));
822 }
catch (SQLException ex) {
823 throw new TskCoreException(String.format(
"Error getting OS accounts for data source id = %d", dataSourceId), ex);
842 List<OsAccount> destinationAccounts =
getOsAccounts(destRealm, trans.getConnection());
843 List<OsAccount> sourceAccounts =
getOsAccounts(sourceRealm, trans.getConnection());
845 for (
OsAccount sourceAccount : sourceAccounts) {
852 if (sourceAccount.getAddr().isPresent() && sourceAccount.getLoginName().isPresent()) {
853 List<OsAccount> duplicateDestAccounts = destinationAccounts.stream()
854 .filter(p -> p.getAddr().equals(sourceAccount.getAddr())
855 || (p.getLoginName().equals(sourceAccount.getLoginName()) && (!p.getAddr().isPresent())))
856 .collect(Collectors.toList());
857 if (duplicateDestAccounts.size() > 1) {
858 OsAccount combinedDestAccount = duplicateDestAccounts.get(0);
859 duplicateDestAccounts.remove(combinedDestAccount);
860 for (
OsAccount dupeDestAccount : duplicateDestAccounts) {
861 mergeOsAccounts(dupeDestAccount, combinedDestAccount, trans);
867 Optional<OsAccount> matchingDestAccount = getMatchingAccountForMerge(sourceAccount, destinationAccounts);
870 if (matchingDestAccount.isPresent()) {
871 mergeOsAccounts(sourceAccount, matchingDestAccount.get(), trans);
873 String query =
"UPDATE tsk_os_accounts SET realm_id = " + destRealm.getRealmId() +
" WHERE os_account_obj_id = " + sourceAccount.getId();
874 try (Statement s = trans.getConnection().createStatement()) {
875 s.executeUpdate(query);
876 }
catch (SQLException ex) {
877 throw new TskCoreException(
"Error executing SQL update: " + query, ex);
879 trans.registerChangedOsAccount(sourceAccount);
890 private Optional<OsAccount> getMatchingAccountForMerge(OsAccount sourceAccount, List<OsAccount> destinationAccounts) {
892 OsAccount matchingDestAccount = null;
895 if (sourceAccount.getAddr().isPresent()) {
896 List<OsAccount> matchingDestAccounts = destinationAccounts.stream()
897 .filter(p -> p.getAddr().equals(sourceAccount.getAddr()))
898 .collect(Collectors.toList());
899 if (!matchingDestAccounts.isEmpty()) {
900 matchingDestAccount = matchingDestAccounts.get(0);
909 if (matchingDestAccount == null && sourceAccount.getLoginName().isPresent()) {
910 List<OsAccount> matchingDestAccounts = destinationAccounts.stream()
911 .filter(p -> p.getLoginName().isPresent())
912 .filter(p -> (p.getLoginName().get().equalsIgnoreCase(sourceAccount.getLoginName().get())
913 && ((!sourceAccount.getAddr().isPresent()) || (!p.getAddr().isPresent()))))
914 .collect(Collectors.toList());
915 if (!matchingDestAccounts.isEmpty()) {
916 matchingDestAccount = matchingDestAccounts.get(0);
920 return Optional.ofNullable(matchingDestAccount);
930 private void mergeOsAccount(OsAccount account, CaseDbTransaction trans)
throws TskCoreException {
936 List<OsAccount> osAccounts =
getOsAccounts(realm, trans.getConnection());
937 osAccounts.removeIf(acc -> Objects.equals(acc.getId(), account.getId()));
940 Optional<OsAccount> matchingAccount = getMatchingAccountForMerge(account, osAccounts);
943 if (matchingAccount.isPresent()) {
944 mergeOsAccounts(matchingAccount.get(), account, trans);
962 private void mergeOsAccounts(OsAccount sourceAccount, OsAccount destAccount, CaseDbTransaction trans)
throws TskCoreException {
965 try (Statement s = trans.getConnection().createStatement()) {
968 query = makeOsAccountUpdateQuery(
"tsk_os_account_attributes", sourceAccount, destAccount);
969 s.executeUpdate(query);
973 query =
"DELETE FROM tsk_os_account_instances "
976 +
" sourceAccountInstance.id "
978 +
" tsk_os_account_instances destAccountInstance "
979 +
"INNER JOIN tsk_os_account_instances sourceAccountInstance ON destAccountInstance.data_source_obj_id = sourceAccountInstance.data_source_obj_id "
980 +
"WHERE destAccountInstance.os_account_obj_id = " + destAccount.getId()
981 +
" AND sourceAccountInstance.os_account_obj_id = " + sourceAccount.getId()
982 +
" AND sourceAccountInstance.instance_type = destAccountInstance.instance_type" +
")";
984 s.executeUpdate(query);
986 query = makeOsAccountUpdateQuery(
"tsk_os_account_instances", sourceAccount, destAccount);
987 s.executeUpdate(query);
988 synchronized (osAcctInstancesCacheLock) {
989 osAccountInstanceCache.clear();
992 query = makeOsAccountUpdateQuery(
"tsk_files", sourceAccount, destAccount);
993 s.executeUpdate(query);
995 query = makeOsAccountUpdateQuery(
"tsk_data_artifacts", sourceAccount, destAccount);
996 s.executeUpdate(query);
1000 trans.registerMergedOsAccount(sourceAccount.getId(), destAccount.getId());
1003 String mergedSignature = makeMergedOsAccountSignature();
1004 query =
"UPDATE tsk_os_accounts SET merged_into = " + destAccount.getId()
1005 +
", db_status = " + OsAccount.OsAccountDbStatus.MERGED.getId()
1006 +
", signature = '" + mergedSignature +
"' "
1007 +
" WHERE os_account_obj_id = " + sourceAccount.getId();
1009 s.executeUpdate(query);
1010 trans.registerDeletedOsAccount(sourceAccount.getId());
1015 mergeOsAccountObjectsAndUpdateDestAccount(sourceAccount, destAccount, trans);
1016 }
catch (SQLException ex) {
1017 throw new TskCoreException(
"Error executing SQL update: " + query, ex);
1026 private String makeMergedOsAccountSignature() {
1027 return "MERGED " + UUID.randomUUID().toString();
1039 private String makeOsAccountUpdateQuery(String tableName, OsAccount sourceAccount, OsAccount destAccount) {
1040 return "UPDATE " + tableName +
" SET os_account_obj_id = " + destAccount.getId() +
" WHERE os_account_obj_id = " + sourceAccount.getId();
1054 private OsAccount mergeOsAccountObjectsAndUpdateDestAccount(OsAccount sourceAccount, OsAccount destAccount, CaseDbTransaction trans)
throws TskCoreException {
1056 OsAccount mergedDestAccount = destAccount;
1058 String destLoginName = null;
1059 String destAddr = null;
1062 if (!destAccount.getLoginName().isPresent() && sourceAccount.getLoginName().isPresent()) {
1066 if (!destAccount.getAddr().isPresent() && sourceAccount.getAddr().isPresent()) {
1067 destAddr = sourceAccount.getAddr().get();
1071 OsAccountUpdateResult updateStatus = this.updateOsAccountCore(destAccount, destAddr, destLoginName, trans);
1073 if (updateStatus.getUpdateStatusCode() == OsAccountUpdateStatus.UPDATED && updateStatus.getUpdatedAccount().isPresent()) {
1074 mergedDestAccount = updateStatus.getUpdatedAccount().get();
1077 String destFullName = null;
1078 Long destCreationTime = null;
1079 if (!destAccount.getFullName().isPresent() && sourceAccount.getFullName().isPresent()) {
1080 destFullName = sourceAccount.getFullName().get();
1083 if (!destAccount.getCreationTime().isPresent() && sourceAccount.getCreationTime().isPresent()) {
1084 destCreationTime = sourceAccount.getCreationTime().get();
1090 if (updateStatus.getUpdateStatusCode() == OsAccountUpdateStatus.UPDATED && updateStatus.getUpdatedAccount().isPresent()) {
1091 mergedDestAccount = updateStatus.getUpdatedAccount().get();
1094 return mergedDestAccount;
1107 private List<OsAccount>
getOsAccounts(OsAccountRealm realm, CaseDbConnection connection)
throws TskCoreException {
1108 String queryString =
"SELECT * FROM tsk_os_accounts"
1110 +
" AND db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId()
1111 +
" ORDER BY os_account_obj_id";
1113 try (Statement s = connection.createStatement();
1114 ResultSet rs = connection.executeQuery(s, queryString)) {
1116 List<OsAccount> accounts =
new ArrayList<>();
1118 accounts.add(osAccountFromResultSet(rs));
1121 }
catch (SQLException ex) {
1122 throw new TskCoreException(String.format(
"Error getting OS accounts for realm id = %d", realm.getRealmId()), ex);
1134 String queryString =
"SELECT * FROM tsk_os_accounts"
1138 try (CaseDbConnection connection = this.db.getConnection();
1139 Statement s = connection.createStatement();
1140 ResultSet rs = connection.executeQuery(s, queryString)) {
1142 List<OsAccount> accounts =
new ArrayList<>();
1144 accounts.add(osAccountFromResultSet(rs));
1147 }
catch (SQLException ex) {
1148 throw new TskCoreException(String.format(
"Error getting OS accounts"), ex);
1171 if (referringHost == null) {
1172 throw new TskCoreException(
"A referring host is required to get an account.");
1176 if ((StringUtils.isBlank(sid) || (sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) ) && StringUtils.isBlank(loginName)) {
1177 throw new TskCoreException(
"Cannot get an OS account with both SID and loginName as null.");
1181 if (StringUtils.isBlank(sid)
1182 && !StringUtils.isBlank(loginName) && !StringUtils.isBlank(realmName)
1183 && WindowsAccountUtils.isWindowsWellKnownAccountName(loginName, realmName)) {
1184 sid = WindowsAccountUtils.getWindowsWellKnownAccountSid(loginName, realmName);
1190 if (!realm.isPresent()) {
1191 return Optional.empty();
1195 if (!Strings.isNullOrEmpty(sid) && !(sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))) {
1196 if (!WindowsAccountUtils.isWindowsUserSid(sid)) {
1200 Optional<OsAccount> account = this.getOsAccountByAddr(sid, realm.get());
1201 if (account.isPresent()) {
1207 if (!Strings.isNullOrEmpty(loginName)) {
1208 String resolvedLoginName = WindowsAccountUtils.toWellknownEnglishLoginName(loginName);
1209 return this.getOsAccountByLoginName(resolvedLoginName, realm.get());
1211 return Optional.empty();
1226 synchronized (account) {
1229 try (CaseDbConnection connection = db.getConnection()) {
1232 String attributeInsertSQL =
"INSERT INTO tsk_os_account_attributes(os_account_obj_id, host_id, source_obj_id, attribute_type_id, value_type, value_byte, value_text, value_int32, value_int64, value_double)"
1233 +
" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
1235 PreparedStatement preparedStatement = connection.getPreparedStatement(attributeInsertSQL, Statement.RETURN_GENERATED_KEYS);
1236 preparedStatement.clearParameters();
1238 preparedStatement.setLong(1, account.getId());
1239 if (accountAttribute.getHostId().isPresent()) {
1240 preparedStatement.setLong(2, accountAttribute.getHostId().get());
1242 preparedStatement.setNull(2, java.sql.Types.NULL);
1244 if (accountAttribute.getSourceObjectId().isPresent()) {
1245 preparedStatement.setLong(3, accountAttribute.getSourceObjectId().get());
1247 preparedStatement.setNull(3, java.sql.Types.NULL);
1250 preparedStatement.setLong(4, accountAttribute.getAttributeType().getTypeID());
1251 preparedStatement.setLong(5, accountAttribute.getAttributeType().getValueType().getType());
1254 preparedStatement.setBytes(6, accountAttribute.getValueBytes());
1256 preparedStatement.setBytes(6, null);
1261 preparedStatement.setString(7, accountAttribute.getValueString());
1263 preparedStatement.setString(7, null);
1266 preparedStatement.setInt(8, accountAttribute.getValueInt());
1268 preparedStatement.setNull(8, java.sql.Types.NULL);
1273 preparedStatement.setLong(9, accountAttribute.getValueLong());
1275 preparedStatement.setNull(9, java.sql.Types.NULL);
1279 preparedStatement.setDouble(10, accountAttribute.getValueDouble());
1281 preparedStatement.setNull(10, java.sql.Types.NULL);
1284 connection.executeUpdate(preparedStatement);
1286 }
catch (SQLException ex) {
1287 throw new TskCoreException(String.format(
"Error adding OS Account attribute for account id = %d", account.getId()), ex);
1292 List<OsAccountAttribute> currentAttribsList = getOsAccountAttributes(account);
1293 account.setAttributesInternal(currentAttribsList);
1295 fireChangeEvent(account);
1307 List<OsAccountAttribute> getOsAccountAttributes(
OsAccount account)
throws TskCoreException {
1309 String queryString =
"SELECT attributes.os_account_obj_id as os_account_obj_id, attributes.host_id as host_id, attributes.source_obj_id as source_obj_id, "
1310 +
" attributes.attribute_type_id as attribute_type_id, attributes.value_type as value_type, attributes.value_byte as value_byte, "
1311 +
" attributes.value_text as value_text, attributes.value_int32 as value_int32, attributes.value_int64 as value_int64, attributes.value_double as value_double, "
1312 +
" hosts.id, hosts.name as host_name, hosts.db_status as host_status "
1313 +
" FROM tsk_os_account_attributes as attributes"
1314 +
" LEFT JOIN tsk_hosts as hosts "
1315 +
" ON attributes.host_id = hosts.id "
1316 +
" WHERE os_account_obj_id = " + account.getId();
1319 try (CaseDbConnection connection = this.db.getConnection();
1320 Statement s = connection.createStatement();
1321 ResultSet rs = connection.executeQuery(s, queryString)) {
1323 List<OsAccountAttribute> attributes =
new ArrayList<>();
1327 long hostId = rs.getLong(
"host_id");
1328 if (!rs.wasNull()) {
1329 host =
new Host(hostId, rs.getString(
"host_name"),
Host.
HostDbStatus.fromID(rs.getInt(
"host_status")));
1332 Content sourceContent = null;
1333 long sourceObjId = rs.getLong(
"source_obj_id");
1334 if (!rs.wasNull()) {
1338 OsAccountAttribute attribute = account.new OsAccountAttribute(attributeType, rs.getInt(
"value_int32"), rs.getLong(
"value_int64"),
1339 rs.getDouble(
"value_double"), rs.getString(
"value_text"), rs.getBytes(
"value_byte"),
1340 db, account, host, sourceContent);
1342 attributes.add(attribute);
1345 }
catch (SQLException ex) {
1346 throw new TskCoreException(String.format(
"Error getting OS account attributes for account obj id = %d", account.getId()), ex);
1362 String whereClause =
"tsk_os_account_instances.os_account_obj_id = " + account.getId();
1377 String instanceIds = instanceIDs.stream().map(
id ->
id.toString()).collect(Collectors.joining(
","));
1379 List<OsAccountInstance> osAcctInstances =
new ArrayList<>();
1381 String querySQL =
"SELECT * FROM tsk_os_account_instances "
1382 +
" WHERE tsk_os_account_instances.id IN (" + instanceIds +
")";
1385 try (CaseDbConnection connection = db.getConnection();
1386 PreparedStatement preparedStatement = connection.getPreparedStatement(querySQL, Statement.NO_GENERATED_KEYS);
1387 ResultSet results = connection.executeQuery(preparedStatement)) {
1389 osAcctInstances = getOsAccountInstancesFromResultSet(results);
1391 }
catch (SQLException ex) {
1392 throw new TskCoreException(
"Failed to get OsAccountInstances (SQL = " + querySQL +
")", ex);
1396 return osAcctInstances;
1413 List<OsAccountInstance> osAcctInstances =
new ArrayList<>();
1416 =
"SELECT tsk_os_account_instances.* "
1417 +
" FROM tsk_os_account_instances "
1418 +
" INNER JOIN ( SELECT os_account_obj_id, data_source_obj_id, MIN(instance_type) AS min_instance_type "
1419 +
" FROM tsk_os_account_instances"
1420 +
" GROUP BY os_account_obj_id, data_source_obj_id ) grouped_instances "
1421 +
" ON tsk_os_account_instances.os_account_obj_id = grouped_instances.os_account_obj_id "
1422 +
" AND tsk_os_account_instances.instance_type = grouped_instances.min_instance_type "
1423 +
" WHERE " + whereClause;
1426 try (CaseDbConnection connection = db.getConnection();
1427 PreparedStatement preparedStatement = connection.getPreparedStatement(querySQL, Statement.NO_GENERATED_KEYS);
1428 ResultSet results = connection.executeQuery(preparedStatement)) {
1430 osAcctInstances = getOsAccountInstancesFromResultSet(results);
1432 }
catch (SQLException ex) {
1433 throw new TskCoreException(
"Failed to get OsAccountInstances (SQL = " + querySQL +
")", ex);
1437 return osAcctInstances;
1449 private List<OsAccountInstance> getOsAccountInstancesFromResultSet(ResultSet results)
throws SQLException {
1451 List<OsAccountInstance> osAcctInstances =
new ArrayList<>();
1452 while (results.next()) {
1453 long instanceId = results.getLong(
"id");
1454 long osAccountObjID = results.getLong(
"os_account_obj_id");
1455 long dataSourceObjId = results.getLong(
"data_source_obj_id");
1456 int instanceType = results.getInt(
"instance_type");
1457 osAcctInstances.add(
new OsAccountInstance(db, instanceId, osAccountObjID, dataSourceObjId, OsAccountInstance.OsAccountInstanceType.fromID(instanceType)));
1460 return osAcctInstances;
1488 return updateStatus;
1490 if (trans != null) {
1515 OsAccountUpdateStatus updateStatusCode = OsAccountUpdateStatus.NO_CHANGE;
1518 CaseDbConnection connection = trans.getConnection();
1520 if (!StringUtils.isBlank(fullName)) {
1521 updateAccountColumn(osAccount.getId(),
"full_name", fullName, connection);
1522 updateStatusCode = OsAccountUpdateStatus.UPDATED;
1525 if (Objects.nonNull(accountType)) {
1526 updateAccountColumn(osAccount.getId(),
"type", accountType.getId(), connection);
1527 updateStatusCode = OsAccountUpdateStatus.UPDATED;
1530 if (Objects.nonNull(accountStatus)) {
1531 updateAccountColumn(osAccount.getId(),
"status", accountStatus.getId(), connection);
1532 updateStatusCode = OsAccountUpdateStatus.UPDATED;
1535 if (Objects.nonNull(creationTime)) {
1536 updateAccountColumn(osAccount.getId(),
"created_date", creationTime, connection);
1537 updateStatusCode = OsAccountUpdateStatus.UPDATED;
1541 if (updateStatusCode == OsAccountUpdateStatus.NO_CHANGE) {
1542 return new OsAccountUpdateResult(updateStatusCode, null);
1549 trans.registerChangedOsAccount(updatedAccount);
1551 return new OsAccountUpdateResult(updateStatusCode, updatedAccount);
1553 }
catch (SQLException ex) {
1554 throw new TskCoreException(String.format(
"Error updating account with addr = %s, account id = %d", osAccount.getAddr().orElse(
"Unknown"), osAccount.getId()), ex);
1571 private <T>
void updateAccountColumn(
long accountObjId, String colName, T colValue, CaseDbConnection connection)
throws SQLException, TskCoreException {
1573 String updateSQL =
"UPDATE tsk_os_accounts "
1574 +
" SET " + colName +
" = ? "
1575 +
" WHERE os_account_obj_id = ?";
1579 PreparedStatement preparedStatement = connection.getPreparedStatement(updateSQL, Statement.NO_GENERATED_KEYS);
1580 preparedStatement.clearParameters();
1582 if (Objects.isNull(colValue)) {
1583 preparedStatement.setNull(1, Types.NULL);
1585 if (colValue instanceof String) {
1586 preparedStatement.setString(1, (String) colValue);
1587 }
else if (colValue instanceof Long) {
1588 preparedStatement.setLong(1, (Long) colValue);
1589 }
else if (colValue instanceof Integer) {
1590 preparedStatement.setInt(1, (Integer) colValue);
1592 throw new TskCoreException(String.format(
"Unhandled column data type received while updating the account (%d) ", accountObjId));
1596 preparedStatement.setLong(2, accountObjId);
1598 connection.executeUpdate(preparedStatement);
1614 private void updateAccountSignature(
long accountObjId, String signature, CaseDbConnection connection)
throws SQLException {
1616 String updateSQL =
"UPDATE tsk_os_accounts SET "
1618 +
" CASE WHEN db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId() +
" THEN ? ELSE signature END "
1619 +
" WHERE os_account_obj_id = ?";
1621 PreparedStatement preparedStatement = connection.getPreparedStatement(updateSQL, Statement.NO_GENERATED_KEYS);
1622 preparedStatement.clearParameters();
1624 preparedStatement.setString(1, signature);
1625 preparedStatement.setLong(2, accountObjId);
1627 connection.executeUpdate(preparedStatement);
1657 return updateStatus;
1659 if (trans != null) {
1688 if ((!StringUtils.isBlank(accountSid) && !accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) || !StringUtils.isBlank(realmName)) {
1690 String resolvedRealmName = WindowsAccountUtils.toWellknownEnglishRealmName(realmName);
1696 Optional<OsAccountRealm> realmOptional = realmUpdateResult.
getUpdatedRealm();
1698 if (realmOptional.isPresent()) {
1705 Optional<OsAccountRealm> anotherRealmWithSameName = db.
getOsAccountRealmManager().getAnotherRealmByName(realmOptional.get(), realmName, referringHost, trans.getConnection());
1708 Optional<OsAccountRealm> anotherRealmWithSameAddr = db.
getOsAccountRealmManager().getAnotherRealmByAddr(realmOptional.get(), realmName, referringHost, trans.getConnection());
1710 if (anotherRealmWithSameName.isPresent()) {
1713 if (anotherRealmWithSameAddr.isPresent()) {
1721 String resolvedLoginName = WindowsAccountUtils.toWellknownEnglishLoginName(loginName);
1722 OsAccountUpdateResult updateStatus = this.updateOsAccountCore(osAccount, accountSid, resolvedLoginName, trans);
1724 Optional<OsAccount> updatedAccount = updateStatus.getUpdatedAccount();
1725 if (updatedAccount.isPresent()) {
1727 mergeOsAccount(updatedAccount.get(), trans);
1730 return updateStatus;
1755 private OsAccountUpdateResult updateOsAccountCore(OsAccount osAccount, String address, String loginName, CaseDbTransaction trans)
throws TskCoreException {
1757 OsAccountUpdateStatus updateStatusCode = OsAccountUpdateStatus.NO_CHANGE;
1758 OsAccount updatedAccount;
1761 CaseDbConnection connection = trans.getConnection();
1764 if (!StringUtils.isBlank(address) && !address.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && !StringUtils.isBlank(osAccount.getAddr().orElse(null)) && !address.equalsIgnoreCase(osAccount.getAddr().orElse(
""))) {
1765 throw new TskCoreException(String.format(
"Account (%d) already has an address (%s), address cannot be updated.", osAccount.getId(), osAccount.getAddr().orElse(
"NULL")));
1768 if (StringUtils.isBlank(osAccount.getAddr().orElse(null)) && !StringUtils.isBlank(address) && !address.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) {
1769 updateAccountColumn(osAccount.getId(),
"addr", address, connection);
1770 updateStatusCode = OsAccountUpdateStatus.UPDATED;
1773 if (StringUtils.isBlank(osAccount.getLoginName().orElse(null)) && !StringUtils.isBlank(loginName)) {
1774 updateAccountColumn(osAccount.getId(),
"login_name", loginName, connection);
1775 updateStatusCode = OsAccountUpdateStatus.UPDATED;
1779 if (updateStatusCode == OsAccountUpdateStatus.NO_CHANGE) {
1780 return new OsAccountUpdateResult(updateStatusCode, osAccount);
1785 String newAddress = currAccount.
getAddr().orElse(null);
1786 String newLoginName = currAccount.getLoginName().orElse(null);
1788 String newSignature = getOsAccountSignature(newAddress, newLoginName);
1789 updateAccountSignature(osAccount.getId(), newSignature, connection);
1795 trans.registerChangedOsAccount(updatedAccount);
1797 return new OsAccountUpdateResult(updateStatusCode, updatedAccount);
1799 }
catch (SQLException ex) {
1800 throw new TskCoreException(String.format(
"Error updating account with unique id = %s, account id = %d", osAccount.getAddr().orElse(
"Unknown"), osAccount.getId()), ex);
1814 List<Host> hostList =
new ArrayList<>();
1816 String query =
"SELECT tsk_hosts.id AS hostId, name, db_status FROM tsk_hosts "
1817 +
" JOIN data_source_info ON tsk_hosts.id = data_source_info.host_id"
1818 +
" JOIN tsk_os_account_instances ON data_source_info.obj_id = tsk_os_account_instances.data_source_obj_id"
1819 +
" WHERE os_account_obj_id = " + account.getId();
1822 try (CaseDbConnection connection = db.getConnection();
1823 Statement s = connection.createStatement();
1824 ResultSet rs = connection.executeQuery(s, query)) {
1827 hostList.add(
new Host(rs.getLong(
"hostId"), rs.getString(
"name"),
Host.
HostDbStatus.fromID(rs.getInt(
"db_status"))));
1830 }
catch (SQLException ex) {
1831 throw new TskCoreException(String.format(
"Failed to get host list for os account %d", account.getId()), ex);
1849 private OsAccount osAccountFromResultSet(ResultSet rs)
throws SQLException {
1852 int typeId = rs.getInt(
"type");
1853 if (!rs.wasNull()) {
1857 Long creationTime = rs.getLong(
"created_date");
1859 creationTime = null;
1862 return new OsAccount(db, rs.getLong(
"os_account_obj_id"), rs.getLong(
"realm_id"), rs.getString(
"login_name"), rs.getString(
"addr"),
1863 rs.getString(
"signature"), rs.getString(
"full_name"), creationTime, accountType, OsAccount.OsAccountStatus.
fromID(rs.getInt(
"status")),
1864 OsAccount.OsAccountDbStatus.
fromID(rs.getInt(
"db_status")));
1874 private void fireChangeEvent(OsAccount account) {
1875 db.fireTSKEvent(
new OsAccountsUpdatedTskEvent(Collections.singletonList(account)));
1892 static String getOsAccountSignature(String uniqueId, String loginName)
throws TskCoreException {
1895 if (Strings.isNullOrEmpty(uniqueId) ==
false) {
1896 signature = uniqueId;
1897 }
else if (Strings.isNullOrEmpty(loginName) ==
false) {
1898 signature = loginName;
1900 throw new TskCoreException(
"OS Account must have either a uniqueID or a login name.");
1911 private static final long serialVersionUID = 1L;
1917 super(
"No error message available.");
1960 this.updateStatus = updateStatus;
1961 this.updatedAccount = updatedAccount;
1965 return updateStatus;
1969 return Optional.ofNullable(updatedAccount);
1977 private class OsAccountInstanceKey
implements Comparable<OsAccountInstanceKey>{
1979 private final long osAccountId;
1980 private final long dataSourceId;
1982 OsAccountInstanceKey(
long osAccountId,
long dataSourceId) {
1983 this.osAccountId = osAccountId;
1984 this.dataSourceId = dataSourceId;
1988 public boolean equals(Object other) {
1989 if (
this == other) {
1992 if (other == null) {
1995 if (getClass() != other.getClass()) {
1999 final OsAccountInstanceKey otherKey = (OsAccountInstanceKey) other;
2001 if (osAccountId != otherKey.osAccountId) {
2005 return dataSourceId == otherKey.dataSourceId;
2009 public int hashCode() {
2011 hash = 53 * hash + (int) (this.osAccountId ^ (this.osAccountId >>> 32));
2012 hash = 53 * hash + (int) (this.dataSourceId ^ (this.dataSourceId >>> 32));
2017 public int compareTo(OsAccountInstanceKey other) {
2018 if(this.equals(other)) {
2022 if (dataSourceId != other.dataSourceId) {
2023 return Long.compare(dataSourceId, other.dataSourceId);
2026 return Long.compare(osAccountId, other.osAccountId);
NotUserSIDException(String msg, Exception ex)
List< OsAccount > getOsAccounts()
Optional< String > getAddr()
OsAccountRealm newWindowsRealm(String accountSid, String realmName, Host referringHost, OsAccountRealm.RealmScope realmScope)
CaseDbTransaction beginTransaction()
List< Host > getHosts(OsAccount account)
OsAccount getOsAccountByObjectId(long osAccountObjId)
Optional< OsAccountRealm > getUpdatedRealm()
static OsAccountType fromID(int typeId)
Blackboard getBlackboard()
Optional< OsAccount > getWindowsOsAccount(String sid, String loginName, String realmName, Host referringHost)
OsAccountUpdateStatus getUpdateStatusCode()
OsAccountUpdateResult updateStandardOsAccountAttributes(OsAccount osAccount, String fullName, OsAccountType accountType, OsAccountStatus accountStatus, Long creationTime)
Content getContentById(long id)
OsAccount newWindowsOsAccount(String sid, String loginName, OsAccountRealm realm)
NotUserSIDException(String msg)
List< OsAccount > getOsAccounts(Host host)
OsAccountRealmManager getOsAccountRealmManager()
void releaseSingleUserCaseReadLock()
Optional< OsAccountRealm > getWindowsRealm(String accountSid, String realmName, Host referringHost)
List< OsAccount > getOsAccountsByDataSourceObjId(long dataSourceId)
List< OsAccountInstance > getOsAccountInstances(List< Long > instanceIDs)
void acquireSingleUserCaseWriteLock()
OsAccountInstance newOsAccountInstance(OsAccount osAccount, DataSource dataSource, OsAccountInstance.OsAccountInstanceType instanceType)
void releaseSingleUserCaseWriteLock()
List< OsAccountInstance > getOsAccountInstances(OsAccount account)
UPDATED
no change was made to account.
Optional< String > getLoginName()
Optional< Host > getScopeHost()
void acquireSingleUserCaseReadLock()
UPDATED
no change was made to account.
void addExtendedOsAccountAttributes(OsAccount account, List< OsAccountAttribute > accountAttributes)
OsAccountRealm getRealmByRealmId(long id)
BlackboardAttribute.Type getAttributeType(String attrTypeName)
OsRealmUpdateStatus getUpdateStatus()
Optional< OsAccount > getUpdatedAccount()
OsAccountUpdateResult updateCoreWindowsOsAccountAttributes(OsAccount osAccount, String accountSid, String loginName, String realmName, Host referringHost)
OsAccount newWindowsOsAccount(String sid, String loginName, String realmName, Host referringHost, OsAccountRealm.RealmScope realmScope)
List< String > getRealmNames()