Sleuth Kit Java Bindings (JNI)  4.12.0
Java bindings for using The Sleuth Kit
OsAccountManager.java
Go to the documentation of this file.
1 /*
2  * Sleuth Kit Data Model
3  *
4  * Copyright 2020-2022 Basis Technology Corp.
5  * Contact: carrier <at> sleuthkit <dot> org
6  *
7  * Licensed under the Apache License, Version 2.0 (the "License");
8  * you may not use this file except in compliance with the License.
9  * You may obtain a copy of the License at
10  *
11  * http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing, software
14  * distributed under the License is distributed on an "AS IS" BASIS,
15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16  * See the License for the specific language governing permissions and
17  * limitations under the License.
18  */
19 package org.sleuthkit.datamodel;
20 
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;
46 import static org.sleuthkit.datamodel.WindowsAccountUtils.isWindowsWellKnownSid;
47 import static org.sleuthkit.datamodel.WindowsAccountUtils.getWindowsWellKnownSidFullName;
48 
53 public final class OsAccountManager {
54 
55  private final SleuthkitCase db;
56  private final Object osAcctInstancesCacheLock;
57  private final NavigableMap<OsAccountInstanceKey, OsAccountInstance> osAccountInstanceCache;
58 
66  db = skCase;
67  osAcctInstancesCacheLock = new Object();
68  osAccountInstanceCache = new ConcurrentSkipListMap<>();
69  }
70 
84  OsAccount newOsAccount(String uniqueAccountId, OsAccountRealm realm) throws TskCoreException {
85 
86  // ensure unique id is provided
87  if (Strings.isNullOrEmpty(uniqueAccountId)) {
88  throw new TskCoreException("Cannot create OS account with null uniqueId.");
89  }
90 
91  if (realm == null) {
92  throw new TskCoreException("Cannot create OS account without a realm.");
93  }
94 
96  try {
97 
98  // try to create account
99  try {
100  OsAccount account = newOsAccount(uniqueAccountId, null, realm, OsAccount.OsAccountStatus.UNKNOWN, trans);
101  trans.commit();
102  trans = null;
103  return account;
104  } catch (SQLException ex) {
105  // Close the transaction before moving on
106  trans.rollback();
107  trans = null;
108 
109  // Create may fail if an OsAccount already exists.
110  Optional<OsAccount> osAccount = this.getOsAccountByAddr(uniqueAccountId, realm);
111  if (osAccount.isPresent()) {
112  return osAccount.get();
113  }
114 
115  // create failed for some other reason, throw an exception
116  throw new TskCoreException(String.format("Error creating OsAccount with uniqueAccountId = %s in realm id = %d", uniqueAccountId, realm.getRealmId()), ex);
117  }
118  } finally {
119  if (trans != null) {
120  trans.rollback();
121  }
122  }
123  }
124 
149  public OsAccount newWindowsOsAccount(String sid, String loginName, String realmName, Host referringHost, OsAccountRealm.RealmScope realmScope) throws TskCoreException, NotUserSIDException {
150 
151  if (realmScope == null) {
152  throw new TskCoreException("RealmScope cannot be null. Use UNKNOWN if scope is not known.");
153  }
154  if (referringHost == null) {
155  throw new TskCoreException("A referring host is required to create an account.");
156  }
157 
158  // ensure at least one of the two is supplied - a non-null unique id or a login name
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.");
162  }
163  // Realm name is required if the sid is 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.");
167  }
168 
169  if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && !WindowsAccountUtils.isWindowsUserSid(sid)) {
170  throw new OsAccountManager.NotUserSIDException(String.format("SID = %s is not a user SID.", sid));
171  }
172 
173  // If no SID is given and the given realm/login names is a well known account, get and use the well known SID
174  if (StringUtils.isBlank(sid)
175  && !StringUtils.isBlank(loginName) && !StringUtils.isBlank(realmName)
176  && WindowsAccountUtils.isWindowsWellKnownAccountName(loginName, realmName)) {
177  sid = WindowsAccountUtils.getWindowsWellKnownAccountSid(loginName, realmName);
178  }
179 
180 
181  OsRealmUpdateResult realmUpdateResult;
182  Optional<OsAccountRealm> anotherRealmWithSameName = Optional.empty();
183  Optional<OsAccountRealm> anotherRealmWithSameAddr = Optional.empty();
184 
185  // get the realm for the account, and update it if it is missing addr or name.
186  OsAccountRealm realm = null;
187  try (CaseDbConnection connection = db.getConnection()) {
188  realmUpdateResult = db.getOsAccountRealmManager().getAndUpdateWindowsRealm(sid, realmName, referringHost, connection);
189 
190  Optional<OsAccountRealm> realmOptional = realmUpdateResult.getUpdatedRealm();
191  if (realmOptional.isPresent()) {
192  realm = realmOptional.get();
193 
194  if (realmUpdateResult.getUpdateStatus() == OsRealmUpdateStatus.UPDATED) {
195 
196  // Check if update of the realm triggers a merge with any other realm,
197  // say another realm with same name but no SID, or same SID but no name
198 
199  //1. Check if there is any OTHER realm with the same name, same host but no addr
200  anotherRealmWithSameName = db.getOsAccountRealmManager().getAnotherRealmByName(realmOptional.get(), realmName, referringHost, connection);
201 
202  // 2. Check if there is any OTHER realm with same addr and host, but NO name
203  anotherRealmWithSameAddr = db.getOsAccountRealmManager().getAnotherRealmByAddr(realmOptional.get(), realmName, referringHost, connection);
204  }
205  }
206  }
207 
208  if (null == realm) {
209  // realm was not found, create it.
210  realm = db.getOsAccountRealmManager().newWindowsRealm(sid, realmName, referringHost, realmScope);
211  } else if (realmUpdateResult.getUpdateStatus() == OsRealmUpdateStatus.UPDATED) {
212  // if the realm already existed and was updated, and there are other realms with same name or addr that should now be merged into the updated realm
213  if (anotherRealmWithSameName.isPresent() || anotherRealmWithSameAddr.isPresent()) {
214 
215  CaseDbTransaction trans = this.db.beginTransaction();
216  try {
217  if (anotherRealmWithSameName.isPresent()) {
218  db.getOsAccountRealmManager().mergeRealms(anotherRealmWithSameName.get(), realm, trans);
219  }
220  if (anotherRealmWithSameAddr.isPresent()) {
221  db.getOsAccountRealmManager().mergeRealms(anotherRealmWithSameAddr.get(), realm, trans);
222  }
223 
224  trans.commit();
225  } catch (TskCoreException ex) {
226  trans.rollback();
227  throw ex; // rethrow
228  }
229  }
230  }
231 
232 
233  return newWindowsOsAccount(sid, loginName, realm);
234  }
235 
253  public OsAccount newWindowsOsAccount(String sid, String loginName, OsAccountRealm realm) throws TskCoreException, NotUserSIDException {
254 
255  // ensure at least one of the two is supplied - a non-null unique id or a login name
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.");
259  }
260 
261  if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && !WindowsAccountUtils.isWindowsUserSid(sid)) {
262  throw new OsAccountManager.NotUserSIDException(String.format("SID = %s is not a user SID.", sid));
263  }
264 
265  // If the login name is well known, we use the well known english name.
266  String resolvedLoginName = WindowsAccountUtils.toWellknownEnglishLoginName(loginName);
267 
268  CaseDbTransaction trans = db.beginTransaction();
269  try {
270  // try to create account
271  try {
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)) {
274  // if the SID is a Windows well known SID, then prefer to use the default well known login name
275  String wellKnownLoginName = WindowsAccountUtils.getWindowsWellKnownSidLoginName(sid);
276  if (!StringUtils.isEmpty(wellKnownLoginName)) {
277  resolvedLoginName = wellKnownLoginName;
278  }
279  }
280 
281  OsAccount account = newOsAccount(uniqueId, resolvedLoginName, realm, OsAccount.OsAccountStatus.UNKNOWN, trans);
282 
283  // If the SID indicates a special windows account, then set its full name.
284  if (!StringUtils.isBlank(sid) && !sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) && isWindowsWellKnownSid(sid)) {
285  String fullName = getWindowsWellKnownSidFullName(sid);
286  if (StringUtils.isNotBlank(fullName)) {
287  OsAccountUpdateResult updateResult = updateStandardOsAccountAttributes(account, fullName, null, null, null, trans);
288  if (updateResult.getUpdatedAccount().isPresent()) {
289  account = updateResult.getUpdatedAccount().get();
290  }
291  }
292  }
293  trans.commit();
294  trans = null;
295  return account;
296  } catch (SQLException ex) {
297  // Rollback the transaction before proceeding
298  trans.rollback();
299  trans = null;
300 
301  // Create may fail if an OsAccount already exists.
302  Optional<OsAccount> osAccount;
303 
304  // First search for account by uniqueId
305  if (!Strings.isNullOrEmpty(sid)) {
306  osAccount = getOsAccountByAddr(sid, realm);
307  if (osAccount.isPresent()) {
308  return osAccount.get();
309  }
310  }
311 
312  // search by loginName
313  if (!Strings.isNullOrEmpty(resolvedLoginName)) {
314  osAccount = getOsAccountByLoginName(resolvedLoginName, realm);
315  if (osAccount.isPresent()) {
316  return osAccount.get();
317  }
318  }
319 
320  // create failed for some other reason, throw an exception
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",
324  (!realm.getRealmNames().isEmpty()) ? realm.getRealmNames().get(0) : "Null",
325  realm.getScopeHost().isPresent() ? realm.getScopeHost().get().getName() : "Null"), ex);
326 
327  }
328  } finally {
329  if (trans != null) {
330  trans.rollback();
331  }
332  }
333  }
334 
348  private OsAccount newOsAccount(String uniqueId, String loginName, OsAccountRealm realm, OsAccount.OsAccountStatus accountStatus, CaseDbTransaction trans) throws TskCoreException, SQLException {
349 
350  if (Objects.isNull(realm)) {
351  throw new TskCoreException("Cannot create an OS Account, realm is NULL.");
352  }
353 
354  String signature = getOsAccountSignature(uniqueId, loginName);
355  OsAccount account;
356 
357  CaseDbConnection connection = trans.getConnection();
358 
359  // first create a tsk_object for the OsAccount.
360  // RAMAN TODO: need to get the correct parent obj id.
361  // Create an Object Directory parent and used its id.
362  long parentObjId = 0;
363 
364  int objTypeId = TskData.ObjectType.OS_ACCOUNT.getObjectType();
365  long osAccountObjId = db.addObject(parentObjId, objTypeId, connection);
366 
367  String accountInsertSQL = "INSERT INTO tsk_os_accounts(os_account_obj_id, login_name, realm_id, addr, signature, status)"
368  + " VALUES (?, ?, ?, ?, ?, ?)"; // NON-NLS
369 
370  PreparedStatement preparedStatement = connection.getPreparedStatement(accountInsertSQL, Statement.NO_GENERATED_KEYS);
371  preparedStatement.clearParameters();
372 
373  preparedStatement.setLong(1, osAccountObjId);
374 
375  preparedStatement.setString(2, loginName);
376  preparedStatement.setLong(3, realm.getRealmId());
377 
378  preparedStatement.setString(4, uniqueId);
379  preparedStatement.setString(5, signature);
380  preparedStatement.setInt(6, accountStatus.getId());
381 
382  connection.executeUpdate(preparedStatement);
383 
384  account = new OsAccount(db, osAccountObjId, realm.getRealmId(), loginName, uniqueId, signature,
385  null, null, null, accountStatus, OsAccount.OsAccountDbStatus.ACTIVE);
386 
387  trans.registerAddedOsAccount(account);
388  return account;
389  }
390 
402  private Optional<OsAccount> getOsAccountByAddr(String addr, Host host) throws TskCoreException {
403 
404  try (CaseDbConnection connection = db.getConnection()) {
405  return getOsAccountByAddr(addr, host, connection);
406  }
407  }
408 
421  private Optional<OsAccount> getOsAccountByAddr(String uniqueId, Host host, CaseDbConnection connection) throws TskCoreException {
422 
423  String whereHostClause = (host == null)
424  ? " 1 = 1 "
425  : " ( realms.scope_host_id = " + host.getHostId() + " OR realms.scope_host_id IS NULL) ";
426 
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
435  + " AND accounts.db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId()
436  + " AND LOWER(accounts.addr) = LOWER('" + uniqueId + "')";
437 
439  try (Statement s = connection.createStatement();
440  ResultSet rs = connection.executeQuery(s, queryString)) {
441 
442  if (!rs.next()) {
443  return Optional.empty(); // no match found
444  } else {
445  return Optional.of(osAccountFromResultSet(rs));
446  }
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);
449  } finally {
451  }
452  }
453 
465  Optional<OsAccount> getOsAccountByAddr(String uniqueId, OsAccountRealm realm) throws TskCoreException {
466 
467  String queryString = "SELECT * FROM tsk_os_accounts"
468  + " WHERE LOWER(addr) = LOWER('" + uniqueId + "')"
469  + " AND db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId()
470  + " AND realm_id = " + realm.getRealmId();
471 
473  try (CaseDbConnection connection = this.db.getConnection();
474  Statement s = connection.createStatement();
475  ResultSet rs = connection.executeQuery(s, queryString)) {
476 
477  if (!rs.next()) {
478  return Optional.empty(); // no match found
479  } else {
480  return Optional.of(osAccountFromResultSet(rs));
481  }
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);
484  } finally {
486  }
487  }
488 
500  Optional<OsAccount> getOsAccountByLoginName(String loginName, OsAccountRealm realm) throws TskCoreException {
501 
502  String queryString = "SELECT * FROM tsk_os_accounts"
503  + " WHERE LOWER(login_name) = LOWER('" + loginName + "')"
504  + " AND db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId()
505  + " AND realm_id = " + realm.getRealmId();
506 
508  try (CaseDbConnection connection = this.db.getConnection();
509  Statement s = connection.createStatement();
510  ResultSet rs = connection.executeQuery(s, queryString)) {
511 
512  if (!rs.next()) {
513  return Optional.empty(); // no match found
514  } else {
515  return Optional.of(osAccountFromResultSet(rs));
516  }
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);
519  } finally {
521  }
522  }
523 
533  public OsAccount getOsAccountByObjectId(long osAccountObjId) throws TskCoreException {
534 
535  try (CaseDbConnection connection = this.db.getConnection()) {
536  return getOsAccountByObjectId(osAccountObjId, connection);
537  }
538  }
539 
550  OsAccount getOsAccountByObjectId(long osAccountObjId, CaseDbConnection connection) throws TskCoreException {
551 
552  String queryString = "SELECT * FROM tsk_os_accounts"
553  + " WHERE os_account_obj_id = " + osAccountObjId;
554 
556  try (Statement s = connection.createStatement();
557  ResultSet rs = connection.executeQuery(s, queryString)) {
558 
559  if (!rs.next()) {
560  throw new TskCoreException(String.format("No account found with obj id = %d ", osAccountObjId));
561  } else {
562  return osAccountFromResultSet(rs);
563  }
564  } catch (SQLException ex) {
565  throw new TskCoreException(String.format("Error getting account with obj id = %d ", osAccountObjId), ex);
566  } finally {
568  }
569  }
570 
595  public OsAccountInstance newOsAccountInstance(OsAccount osAccount, DataSource dataSource, OsAccountInstance.OsAccountInstanceType instanceType) throws TskCoreException {
596  if (osAccount == null) {
597  throw new TskCoreException("Cannot create account instance with null account.");
598  }
599  if (dataSource == null) {
600  throw new TskCoreException("Cannot create account instance with null data source.");
601  }
602 
603  // check the cache first
604  Optional<OsAccountInstance> existingInstance = cachedAccountInstance(osAccount.getId(), dataSource.getId(), instanceType);
605  if (existingInstance.isPresent()) {
606  return existingInstance.get();
607  }
608 
609  try (CaseDbConnection connection = this.db.getConnection()) {
610  return newOsAccountInstance(osAccount.getId(), dataSource.getId(), instanceType, connection);
611  }
612  }
613 
629  OsAccountInstance newOsAccountInstance(long osAccountId, long dataSourceObjId, OsAccountInstance.OsAccountInstanceType instanceType, CaseDbConnection connection) throws TskCoreException {
630 
631  Optional<OsAccountInstance> existingInstance = cachedAccountInstance(osAccountId, dataSourceObjId, instanceType);
632  if (existingInstance.isPresent()) {
633  return existingInstance.get();
634  }
635 
636  /*
637  * Create the OS account instance.
638  */
640  try {
641  String accountInsertSQL = db.getInsertOrIgnoreSQL("INTO tsk_os_account_instances(os_account_obj_id, data_source_obj_id, instance_type)"
642  + " VALUES (?, ?, ?)"); // NON-NLS
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);
654  // remove from cache any instances less significant (higher ordinal) than this instance
655  for (OsAccountInstance.OsAccountInstanceType type : OsAccountInstance.OsAccountInstanceType.values()) {
656  if (accountInstance.getInstanceType().compareTo(type) < 0) {
657  osAccountInstanceCache.remove(key);
658  }
659  }
660  // add the new most significant instance to the cache
661  osAccountInstanceCache.put(key, accountInstance);
662  }
663  /*
664  * There is a potential issue here. The cache of OS account
665  * instances is an optimization and was not intended to be
666  * used as an authoritative indicator of whether or not a
667  * particular OS account instance was already added to the
668  * case. In fact, the entire cache is flushed during merge
669  * operations. But regardless, there is a check-then-act
670  * race condition for multi-user cases, with or without the
671  * cache. And although the case database schema and the SQL
672  * returned by getInsertOrIgnoreSQL() seamlessly prevents
673  * duplicates in the case database, a valid row ID is
674  * returned here even if the INSERT is not done. So the
675  * bottom line is that a redundant event may be published
676  * from time to time.
677  */
678  db.fireTSKEvent(new TskEvent.OsAcctInstancesAddedTskEvent(Collections.singletonList(accountInstance)));
679 
680  return accountInstance;
681  } else {
682  // there is the possibility that another thread may be adding the same os account instance at the same time
683  // the database may be updated prior to the cache being updated so this provides an extra opportunity to check
684  // the cache before throwing the exception
685  Optional<OsAccountInstance> existingInstanceRetry = cachedAccountInstance(osAccountId, dataSourceObjId, instanceType);
686  if (existingInstanceRetry.isPresent()) {
687  return existingInstanceRetry.get();
688  }
689  }
690  }
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);
693  } finally {
695  }
696 
697  // It's possible that we weren't able to load the account instance because it
698  // is already in the database but the instance cache was cleared during an account merge.
699  // Try loading it here and re-adding to the cache.
700  String whereClause = "tsk_os_account_instances.os_account_obj_id = " + osAccountId
701  + "AND tsk_os_account_instances.data_source_obj_id = " + dataSourceObjId;
702  List<OsAccountInstance> instances = getOsAccountInstances(whereClause);
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));
705  }
706 
707  OsAccountInstance accountInstance = instances.get(0);
708  synchronized (osAcctInstancesCacheLock) {
709  OsAccountInstanceKey key = new OsAccountInstanceKey(osAccountId, dataSourceObjId);
710  // remove from cache any instances less significant (higher ordinal) than this instance
711  for (OsAccountInstance.OsAccountInstanceType type : OsAccountInstance.OsAccountInstanceType.values()) {
712  if (accountInstance.getInstanceType().compareTo(type) < 0) {
713  osAccountInstanceCache.remove(key);
714  }
715  }
716  // add the most significant instance to the cache
717  osAccountInstanceCache.put(key, accountInstance);
718  }
719  return accountInstance;
720  }
721 
738  private Optional<OsAccountInstance> cachedAccountInstance(long osAccountId, long dataSourceObjId, OsAccountInstance.OsAccountInstanceType instanceType) {
739 
740  /*
741  * Check the cache of OS account instances for an existing instance for
742  * this OS account and data source. Note that the account instance
743  * created here has a bogus instance ID. This is possible since the
744  * instance ID is not considered in the equals() and hashCode() methods
745  * of this class.
746  */
747  synchronized (osAcctInstancesCacheLock) {
748  OsAccountInstanceKey key = new OsAccountInstanceKey(osAccountId, dataSourceObjId);
749  OsAccountInstance instance = osAccountInstanceCache.get(key);
750  if (instance != null) {
751  // if the new instance type same or less significant than the existing instance (i.e. same or higher ordinal value) it's a match.
752  if (instanceType.compareTo(instance.getInstanceType()) >= 0) {
753  return Optional.of(instance);
754  }
755  }
756  return Optional.empty();
757  }
758  }
759 
769  public List<OsAccount> getOsAccounts(Host host) throws TskCoreException {
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() + ") "
776  + "AND accounts.db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId();
777 
779  try (CaseDbConnection connection = this.db.getConnection();
780  Statement s = connection.createStatement();
781  ResultSet rs = connection.executeQuery(s, queryString)) {
782 
783  List<OsAccount> accounts = new ArrayList<>();
784  while (rs.next()) {
785  accounts.add(osAccountFromResultSet(rs));
786  }
787  return accounts;
788  } catch (SQLException ex) {
789  throw new TskCoreException(String.format("Error getting OS accounts for host id = %d", host.getHostId()), ex);
790  } finally {
792  }
793  }
794 
804  public List<OsAccount> getOsAccountsByDataSourceObjId(long dataSourceId) throws TskCoreException {
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 + ") "
810  + "AND acc.db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId();
811 
813  try (CaseDbConnection connection = this.db.getConnection();
814  Statement s = connection.createStatement();
815  ResultSet rs = connection.executeQuery(s, queryString)) {
816 
817  List<OsAccount> accounts = new ArrayList<>();
818  while (rs.next()) {
819  accounts.add(osAccountFromResultSet(rs));
820  }
821  return accounts;
822  } catch (SQLException ex) {
823  throw new TskCoreException(String.format("Error getting OS accounts for data source id = %d", dataSourceId), ex);
824  } finally {
826  }
827  }
828 
841  void mergeOsAccountsForRealms(OsAccountRealm sourceRealm, OsAccountRealm destRealm, CaseDbTransaction trans) throws TskCoreException {
842  List<OsAccount> destinationAccounts = getOsAccounts(destRealm, trans.getConnection());
843  List<OsAccount> sourceAccounts = getOsAccounts(sourceRealm, trans.getConnection());
844 
845  for (OsAccount sourceAccount : sourceAccounts) {
846 
847  // First a check for the case where the source account has both the login name and unique ID set and
848  // we have separate matches in the destination account for both. If we find this case, we need to first merge
849  // the two accounts in the destination realm. This will ensure that all source accounts match at most one
850  // destination account.
851  // Note that we only merge accounts based on login name if the unique ID is empty.
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);
862  }
863  }
864  }
865 
866  // Look for matching destination account
867  Optional<OsAccount> matchingDestAccount = getMatchingAccountForMerge(sourceAccount, destinationAccounts);
868 
869  // If we found a match, merge the accounts. Otherwise simply update the realm id
870  if (matchingDestAccount.isPresent()) {
871  mergeOsAccounts(sourceAccount, matchingDestAccount.get(), trans);
872  } else {
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);
878  }
879  trans.registerChangedOsAccount(sourceAccount);
880  }
881  }
882  }
883 
890  private Optional<OsAccount> getMatchingAccountForMerge(OsAccount sourceAccount, List<OsAccount> destinationAccounts) {
891  // Look for matching destination account
892  OsAccount matchingDestAccount = null;
893 
894  // First look for matching unique id
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);
901  }
902  }
903 
904  // If a match wasn't found yet, look for a matching login name.
905  // We will merge only if:
906  // - We didn't already find a unique ID match
907  // - The source account has no unique ID OR the destination account has no unique ID
908  // - destination account has a login name and matches the source account login name
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);
917  }
918  }
919 
920  return Optional.ofNullable(matchingDestAccount);
921  }
922 
930  private void mergeOsAccount(OsAccount account, CaseDbTransaction trans) throws TskCoreException {
931  // Get the realm for the account
932  Long realmId = account.getRealmId();
933  OsAccountRealm realm = db.getOsAccountRealmManager().getRealmByRealmId(realmId, trans.getConnection());
934 
935  // Get all users in the realm (excluding the account)
936  List<OsAccount> osAccounts = getOsAccounts(realm, trans.getConnection());
937  osAccounts.removeIf(acc -> Objects.equals(acc.getId(), account.getId()));
938 
939  // Look for matching account
940  Optional<OsAccount> matchingAccount = getMatchingAccountForMerge(account, osAccounts);
941 
942  // If we find a match, merge the accounts.
943  if (matchingAccount.isPresent()) {
944  mergeOsAccounts(matchingAccount.get(), account, trans);
945  }
946  }
947 
962  private void mergeOsAccounts(OsAccount sourceAccount, OsAccount destAccount, CaseDbTransaction trans) throws TskCoreException {
963 
964  String query = "";
965  try (Statement s = trans.getConnection().createStatement()) {
966 
967  // Update all references
968  query = makeOsAccountUpdateQuery("tsk_os_account_attributes", sourceAccount, destAccount);
969  s.executeUpdate(query);
970 
971  // tsk_os_account_instances has a unique constraint on os_account_obj_id, data_source_obj_id, and instance_type,
972  // so delete any rows that would be duplicates.
973  query = "DELETE FROM tsk_os_account_instances "
974  + "WHERE id IN ( "
975  + "SELECT "
976  + " sourceAccountInstance.id "
977  + "FROM "
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" + ")";
983 
984  s.executeUpdate(query);
985 
986  query = makeOsAccountUpdateQuery("tsk_os_account_instances", sourceAccount, destAccount);
987  s.executeUpdate(query);
988  synchronized (osAcctInstancesCacheLock) {
989  osAccountInstanceCache.clear();
990  }
991 
992  query = makeOsAccountUpdateQuery("tsk_files", sourceAccount, destAccount);
993  s.executeUpdate(query);
994 
995  query = makeOsAccountUpdateQuery("tsk_data_artifacts", sourceAccount, destAccount);
996  s.executeUpdate(query);
997 
998 
999  // register the merged accounts with the transaction to fire off an event
1000  trans.registerMergedOsAccount(sourceAccount.getId(), destAccount.getId());
1001 
1002  // Update the source account. Make a dummy signature to prevent problems with the unique constraint.
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();
1008 
1009  s.executeUpdate(query);
1010  trans.registerDeletedOsAccount(sourceAccount.getId());
1011 
1012  // Merge and update the destination account. Note that this must be done after updating
1013  // the source account to prevent conflicts when merging two accounts in the
1014  // same realm.
1015  mergeOsAccountObjectsAndUpdateDestAccount(sourceAccount, destAccount, trans);
1016  } catch (SQLException ex) {
1017  throw new TskCoreException("Error executing SQL update: " + query, ex);
1018  }
1019  }
1020 
1026  private String makeMergedOsAccountSignature() {
1027  return "MERGED " + UUID.randomUUID().toString();
1028  }
1029 
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();
1041  }
1042 
1054  private OsAccount mergeOsAccountObjectsAndUpdateDestAccount(OsAccount sourceAccount, OsAccount destAccount, CaseDbTransaction trans) throws TskCoreException {
1055 
1056  OsAccount mergedDestAccount = destAccount;
1057 
1058  String destLoginName = null;
1059  String destAddr = null;
1060 
1061  // Copy any fields that aren't set in the destination to the value from the source account.
1062  if (!destAccount.getLoginName().isPresent() && sourceAccount.getLoginName().isPresent()) {
1063  destLoginName = sourceAccount.getLoginName().get();
1064  }
1065 
1066  if (!destAccount.getAddr().isPresent() && sourceAccount.getAddr().isPresent()) {
1067  destAddr = sourceAccount.getAddr().get();
1068  }
1069 
1070  // update the dest account core
1071  OsAccountUpdateResult updateStatus = this.updateOsAccountCore(destAccount, destAddr, destLoginName, trans);
1072 
1073  if (updateStatus.getUpdateStatusCode() == OsAccountUpdateStatus.UPDATED && updateStatus.getUpdatedAccount().isPresent()) {
1074  mergedDestAccount = updateStatus.getUpdatedAccount().get();
1075  }
1076 
1077  String destFullName = null;
1078  Long destCreationTime = null;
1079  if (!destAccount.getFullName().isPresent() && sourceAccount.getFullName().isPresent()) {
1080  destFullName = sourceAccount.getFullName().get();
1081  }
1082 
1083  if (!destAccount.getCreationTime().isPresent() && sourceAccount.getCreationTime().isPresent()) {
1084  destCreationTime = sourceAccount.getCreationTime().get();
1085  }
1086 
1087  // update the dest account properties
1088  updateStatus = this.updateStandardOsAccountAttributes(destAccount, destFullName, null, null, destCreationTime, trans);
1089 
1090  if (updateStatus.getUpdateStatusCode() == OsAccountUpdateStatus.UPDATED && updateStatus.getUpdatedAccount().isPresent()) {
1091  mergedDestAccount = updateStatus.getUpdatedAccount().get();
1092  }
1093 
1094  return mergedDestAccount;
1095  }
1096 
1107  private List<OsAccount> getOsAccounts(OsAccountRealm realm, CaseDbConnection connection) throws TskCoreException {
1108  String queryString = "SELECT * FROM tsk_os_accounts"
1109  + " WHERE realm_id = " + realm.getRealmId()
1110  + " AND db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId()
1111  + " ORDER BY os_account_obj_id";
1112 
1113  try (Statement s = connection.createStatement();
1114  ResultSet rs = connection.executeQuery(s, queryString)) {
1115 
1116  List<OsAccount> accounts = new ArrayList<>();
1117  while (rs.next()) {
1118  accounts.add(osAccountFromResultSet(rs));
1119  }
1120  return accounts;
1121  } catch (SQLException ex) {
1122  throw new TskCoreException(String.format("Error getting OS accounts for realm id = %d", realm.getRealmId()), ex);
1123  }
1124  }
1125 
1133  public List<OsAccount> getOsAccounts() throws TskCoreException {
1134  String queryString = "SELECT * FROM tsk_os_accounts"
1135  + " WHERE db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId();
1136 
1138  try (CaseDbConnection connection = this.db.getConnection();
1139  Statement s = connection.createStatement();
1140  ResultSet rs = connection.executeQuery(s, queryString)) {
1141 
1142  List<OsAccount> accounts = new ArrayList<>();
1143  while (rs.next()) {
1144  accounts.add(osAccountFromResultSet(rs));
1145  }
1146  return accounts;
1147  } catch (SQLException ex) {
1148  throw new TskCoreException(String.format("Error getting OS accounts"), ex);
1149  } finally {
1151  }
1152  }
1153 
1169  public Optional<OsAccount> getWindowsOsAccount(String sid, String loginName, String realmName, Host referringHost) throws TskCoreException, NotUserSIDException {
1170 
1171  if (referringHost == null) {
1172  throw new TskCoreException("A referring host is required to get an account.");
1173  }
1174 
1175  // ensure at least one of the two is supplied - a non-null sid or a login name
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.");
1178  }
1179 
1180  // If no SID is given and the given realm/login names is a well known account, get and use the well known SID
1181  if (StringUtils.isBlank(sid)
1182  && !StringUtils.isBlank(loginName) && !StringUtils.isBlank(realmName)
1183  && WindowsAccountUtils.isWindowsWellKnownAccountName(loginName, realmName)) {
1184  sid = WindowsAccountUtils.getWindowsWellKnownAccountSid(loginName, realmName);
1185 
1186  }
1187 
1188  // first get the realm for the given sid
1189  Optional<OsAccountRealm> realm = db.getOsAccountRealmManager().getWindowsRealm(sid, realmName, referringHost);
1190  if (!realm.isPresent()) {
1191  return Optional.empty();
1192  }
1193 
1194  // search by SID
1195  if (!Strings.isNullOrEmpty(sid) && !(sid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))) {
1196  if (!WindowsAccountUtils.isWindowsUserSid(sid)) {
1197  throw new OsAccountManager.NotUserSIDException(String.format("SID = %s is not a user SID.", sid));
1198  }
1199 
1200  Optional<OsAccount> account = this.getOsAccountByAddr(sid, realm.get());
1201  if (account.isPresent()) {
1202  return account;
1203  }
1204  }
1205 
1206  // search by login name
1207  if (!Strings.isNullOrEmpty(loginName)) {
1208  String resolvedLoginName = WindowsAccountUtils.toWellknownEnglishLoginName(loginName);
1209  return this.getOsAccountByLoginName(resolvedLoginName, realm.get());
1210  } else {
1211  return Optional.empty();
1212  }
1213  }
1214 
1224  public void addExtendedOsAccountAttributes(OsAccount account, List<OsAccountAttribute> accountAttributes) throws TskCoreException {
1225 
1226  synchronized (account) { // synchronized to prevent multiple threads trying to add osAccount attributes concurrently to the same osAccount.
1228 
1229  try (CaseDbConnection connection = db.getConnection()) {
1230  for (OsAccountAttribute accountAttribute : accountAttributes) {
1231 
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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; // NON-NLS
1234 
1235  PreparedStatement preparedStatement = connection.getPreparedStatement(attributeInsertSQL, Statement.RETURN_GENERATED_KEYS);
1236  preparedStatement.clearParameters();
1237 
1238  preparedStatement.setLong(1, account.getId());
1239  if (accountAttribute.getHostId().isPresent()) {
1240  preparedStatement.setLong(2, accountAttribute.getHostId().get());
1241  } else {
1242  preparedStatement.setNull(2, java.sql.Types.NULL);
1243  }
1244  if (accountAttribute.getSourceObjectId().isPresent()) {
1245  preparedStatement.setLong(3, accountAttribute.getSourceObjectId().get());
1246  } else {
1247  preparedStatement.setNull(3, java.sql.Types.NULL);
1248  }
1249 
1250  preparedStatement.setLong(4, accountAttribute.getAttributeType().getTypeID());
1251  preparedStatement.setLong(5, accountAttribute.getAttributeType().getValueType().getType());
1252 
1253  if (accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.BYTE) {
1254  preparedStatement.setBytes(6, accountAttribute.getValueBytes());
1255  } else {
1256  preparedStatement.setBytes(6, null);
1257  }
1258 
1259  if (accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.STRING
1260  || accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.JSON) {
1261  preparedStatement.setString(7, accountAttribute.getValueString());
1262  } else {
1263  preparedStatement.setString(7, null);
1264  }
1265  if (accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.INTEGER) {
1266  preparedStatement.setInt(8, accountAttribute.getValueInt());
1267  } else {
1268  preparedStatement.setNull(8, java.sql.Types.NULL);
1269  }
1270 
1271  if (accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.DATETIME
1272  || accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.LONG) {
1273  preparedStatement.setLong(9, accountAttribute.getValueLong());
1274  } else {
1275  preparedStatement.setNull(9, java.sql.Types.NULL);
1276  }
1277 
1278  if (accountAttribute.getAttributeType().getValueType() == TSK_BLACKBOARD_ATTRIBUTE_VALUE_TYPE.DOUBLE) {
1279  preparedStatement.setDouble(10, accountAttribute.getValueDouble());
1280  } else {
1281  preparedStatement.setNull(10, java.sql.Types.NULL);
1282  }
1283 
1284  connection.executeUpdate(preparedStatement);
1285  }
1286  } catch (SQLException ex) {
1287  throw new TskCoreException(String.format("Error adding OS Account attribute for account id = %d", account.getId()), ex);
1288  } finally {
1290  }
1291  // set the atrribute list in account to the most current list from the database
1292  List<OsAccountAttribute> currentAttribsList = getOsAccountAttributes(account);
1293  account.setAttributesInternal(currentAttribsList);
1294  }
1295  fireChangeEvent(account);
1296  }
1297 
1307  List<OsAccountAttribute> getOsAccountAttributes(OsAccount account) throws TskCoreException {
1308 
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();
1317 
1319  try (CaseDbConnection connection = this.db.getConnection();
1320  Statement s = connection.createStatement();
1321  ResultSet rs = connection.executeQuery(s, queryString)) {
1322 
1323  List<OsAccountAttribute> attributes = new ArrayList<>();
1324  while (rs.next()) {
1325 
1326  Host host = null;
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")));
1330  }
1331 
1332  Content sourceContent = null;
1333  long sourceObjId = rs.getLong("source_obj_id");
1334  if (!rs.wasNull()) {
1335  sourceContent = this.db.getContentById(sourceObjId);
1336  }
1337  BlackboardAttribute.Type attributeType = db.getBlackboard().getAttributeType(rs.getInt("attribute_type_id"));
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);
1341 
1342  attributes.add(attribute);
1343  }
1344  return attributes;
1345  } catch (SQLException ex) {
1346  throw new TskCoreException(String.format("Error getting OS account attributes for account obj id = %d", account.getId()), ex);
1347  } finally {
1349  }
1350  }
1351 
1361  public List<OsAccountInstance> getOsAccountInstances(OsAccount account) throws TskCoreException {
1362  String whereClause = "tsk_os_account_instances.os_account_obj_id = " + account.getId();
1363  return getOsAccountInstances(whereClause);
1364  }
1365 
1376  public List<OsAccountInstance> getOsAccountInstances(List<Long> instanceIDs) throws TskCoreException {
1377  String instanceIds = instanceIDs.stream().map(id -> id.toString()).collect(Collectors.joining(","));
1378 
1379  List<OsAccountInstance> osAcctInstances = new ArrayList<>();
1380 
1381  String querySQL = "SELECT * FROM tsk_os_account_instances "
1382  + " WHERE tsk_os_account_instances.id IN (" + instanceIds + ")";
1383 
1385  try (CaseDbConnection connection = db.getConnection();
1386  PreparedStatement preparedStatement = connection.getPreparedStatement(querySQL, Statement.NO_GENERATED_KEYS);
1387  ResultSet results = connection.executeQuery(preparedStatement)) {
1388 
1389  osAcctInstances = getOsAccountInstancesFromResultSet(results);
1390 
1391  } catch (SQLException ex) {
1392  throw new TskCoreException("Failed to get OsAccountInstances (SQL = " + querySQL + ")", ex);
1393  } finally {
1395  }
1396  return osAcctInstances;
1397  }
1398 
1412  private List<OsAccountInstance> getOsAccountInstances(String whereClause) throws TskCoreException {
1413  List<OsAccountInstance> osAcctInstances = new ArrayList<>();
1414 
1415  String querySQL
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;
1424 
1426  try (CaseDbConnection connection = db.getConnection();
1427  PreparedStatement preparedStatement = connection.getPreparedStatement(querySQL, Statement.NO_GENERATED_KEYS);
1428  ResultSet results = connection.executeQuery(preparedStatement)) {
1429 
1430  osAcctInstances = getOsAccountInstancesFromResultSet(results);
1431 
1432  } catch (SQLException ex) {
1433  throw new TskCoreException("Failed to get OsAccountInstances (SQL = " + querySQL + ")", ex);
1434  } finally {
1436  }
1437  return osAcctInstances;
1438  }
1439 
1449  private List<OsAccountInstance> getOsAccountInstancesFromResultSet(ResultSet results) throws SQLException {
1450 
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)));
1458  }
1459 
1460  return osAcctInstances;
1461  }
1462 
1479  public OsAccountUpdateResult updateStandardOsAccountAttributes(OsAccount osAccount, String fullName, OsAccountType accountType, OsAccountStatus accountStatus, Long creationTime) throws TskCoreException {
1480 
1481  CaseDbTransaction trans = db.beginTransaction();
1482  try {
1483  OsAccountUpdateResult updateStatus = updateStandardOsAccountAttributes(osAccount, fullName, accountType, accountStatus, creationTime, trans);
1484 
1485  trans.commit();
1486  trans = null;
1487 
1488  return updateStatus;
1489  } finally {
1490  if (trans != null) {
1491  trans.rollback();
1492  }
1493  }
1494  }
1495 
1513  OsAccountUpdateResult updateStandardOsAccountAttributes(OsAccount osAccount, String fullName, OsAccountType accountType, OsAccountStatus accountStatus, Long creationTime, CaseDbTransaction trans) throws TskCoreException {
1514 
1515  OsAccountUpdateStatus updateStatusCode = OsAccountUpdateStatus.NO_CHANGE;
1516 
1517  try {
1518  CaseDbConnection connection = trans.getConnection();
1519 
1520  if (!StringUtils.isBlank(fullName)) {
1521  updateAccountColumn(osAccount.getId(), "full_name", fullName, connection);
1522  updateStatusCode = OsAccountUpdateStatus.UPDATED;
1523  }
1524 
1525  if (Objects.nonNull(accountType)) {
1526  updateAccountColumn(osAccount.getId(), "type", accountType.getId(), connection);
1527  updateStatusCode = OsAccountUpdateStatus.UPDATED;
1528  }
1529 
1530  if (Objects.nonNull(accountStatus)) {
1531  updateAccountColumn(osAccount.getId(), "status", accountStatus.getId(), connection);
1532  updateStatusCode = OsAccountUpdateStatus.UPDATED;
1533  }
1534 
1535  if (Objects.nonNull(creationTime)) {
1536  updateAccountColumn(osAccount.getId(), "created_date", creationTime, connection);
1537  updateStatusCode = OsAccountUpdateStatus.UPDATED;
1538  }
1539 
1540  // if nothing has been changed, return
1541  if (updateStatusCode == OsAccountUpdateStatus.NO_CHANGE) {
1542  return new OsAccountUpdateResult(updateStatusCode, null);
1543  }
1544 
1545  // get the updated account from database
1546  OsAccount updatedAccount = getOsAccountByObjectId(osAccount.getId(), connection);
1547 
1548  // register the updated account with the transaction to fire off an event
1549  trans.registerChangedOsAccount(updatedAccount);
1550 
1551  return new OsAccountUpdateResult(updateStatusCode, updatedAccount);
1552 
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);
1555  }
1556  }
1557 
1571  private <T> void updateAccountColumn(long accountObjId, String colName, T colValue, CaseDbConnection connection) throws SQLException, TskCoreException {
1572 
1573  String updateSQL = "UPDATE tsk_os_accounts "
1574  + " SET " + colName + " = ? "
1575  + " WHERE os_account_obj_id = ?";
1576 
1578  try {
1579  PreparedStatement preparedStatement = connection.getPreparedStatement(updateSQL, Statement.NO_GENERATED_KEYS);
1580  preparedStatement.clearParameters();
1581 
1582  if (Objects.isNull(colValue)) {
1583  preparedStatement.setNull(1, Types.NULL); // handle null value
1584  } else {
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);
1591  } else {
1592  throw new TskCoreException(String.format("Unhandled column data type received while updating the account (%d) ", accountObjId));
1593  }
1594  }
1595 
1596  preparedStatement.setLong(2, accountObjId);
1597 
1598  connection.executeUpdate(preparedStatement);
1599  } finally {
1601  }
1602  }
1603 
1614  private void updateAccountSignature(long accountObjId, String signature, CaseDbConnection connection) throws SQLException {
1615 
1616  String updateSQL = "UPDATE tsk_os_accounts SET "
1617  + " signature = "
1618  + " CASE WHEN db_status = " + OsAccount.OsAccountDbStatus.ACTIVE.getId() + " THEN ? ELSE signature END "
1619  + " WHERE os_account_obj_id = ?"; // 8
1620 
1621  PreparedStatement preparedStatement = connection.getPreparedStatement(updateSQL, Statement.NO_GENERATED_KEYS);
1622  preparedStatement.clearParameters();
1623 
1624  preparedStatement.setString(1, signature);
1625  preparedStatement.setLong(2, accountObjId);
1626 
1627  connection.executeUpdate(preparedStatement);
1628  }
1629 
1650  public OsAccountUpdateResult updateCoreWindowsOsAccountAttributes(OsAccount osAccount, String accountSid, String loginName, String realmName, Host referringHost) throws TskCoreException, NotUserSIDException {
1651  CaseDbTransaction trans = db.beginTransaction();
1652  try {
1653  OsAccountUpdateResult updateStatus = this.updateCoreWindowsOsAccountAttributes(osAccount, accountSid, loginName, realmName, referringHost, trans);
1654 
1655  trans.commit();
1656  trans = null;
1657  return updateStatus;
1658  } finally {
1659  if (trans != null) {
1660  trans.rollback();
1661  }
1662  }
1663  }
1664 
1684  private OsAccountUpdateResult updateCoreWindowsOsAccountAttributes(OsAccount osAccount, String accountSid, String loginName, String realmName, Host referringHost, CaseDbTransaction trans) throws TskCoreException, NotUserSIDException {
1685 
1686  // first get and update the realm - if we have the info to find the realm
1687 
1688  if ((!StringUtils.isBlank(accountSid) && !accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) || !StringUtils.isBlank(realmName)) {
1689  // If the SID is a well known SID, ensure we use the well known english name
1690  String resolvedRealmName = WindowsAccountUtils.toWellknownEnglishRealmName(realmName);
1691 
1692 
1693  OsRealmUpdateResult realmUpdateResult = db.getOsAccountRealmManager().getAndUpdateWindowsRealm(accountSid, resolvedRealmName, referringHost, trans.getConnection());
1694 
1695 
1696  Optional<OsAccountRealm> realmOptional = realmUpdateResult.getUpdatedRealm();
1697 
1698  if (realmOptional.isPresent()) {
1699 
1700  if (realmUpdateResult.getUpdateStatus() == OsRealmUpdateStatus.UPDATED) {
1701 
1702  // Check if update of the realm triggers a merge with any other realm,
1703  // say another realm with same name but no SID, or same SID but no name
1704  //1. Check if there is any OTHER realm with the same name, same host but no addr
1705  Optional<OsAccountRealm> anotherRealmWithSameName = db.getOsAccountRealmManager().getAnotherRealmByName(realmOptional.get(), realmName, referringHost, trans.getConnection());
1706 
1707  // 2. Check if there is any OTHER realm with same addr and host, but NO name
1708  Optional<OsAccountRealm> anotherRealmWithSameAddr = db.getOsAccountRealmManager().getAnotherRealmByAddr(realmOptional.get(), realmName, referringHost, trans.getConnection());
1709 
1710  if (anotherRealmWithSameName.isPresent()) {
1711  db.getOsAccountRealmManager().mergeRealms(anotherRealmWithSameName.get(), realmOptional.get(), trans);
1712  }
1713  if (anotherRealmWithSameAddr.isPresent()) {
1714  db.getOsAccountRealmManager().mergeRealms(anotherRealmWithSameAddr.get(), realmOptional.get(), trans);
1715  }
1716  }
1717  }
1718  }
1719 
1720  // now update the account core data
1721  String resolvedLoginName = WindowsAccountUtils.toWellknownEnglishLoginName(loginName);
1722  OsAccountUpdateResult updateStatus = this.updateOsAccountCore(osAccount, accountSid, resolvedLoginName, trans);
1723 
1724  Optional<OsAccount> updatedAccount = updateStatus.getUpdatedAccount();
1725  if (updatedAccount.isPresent()) {
1726  // After updating account data, check if there is matching account to merge
1727  mergeOsAccount(updatedAccount.get(), trans);
1728  }
1729 
1730  return updateStatus;
1731  }
1732 
1755  private OsAccountUpdateResult updateOsAccountCore(OsAccount osAccount, String address, String loginName, CaseDbTransaction trans) throws TskCoreException {
1756 
1757  OsAccountUpdateStatus updateStatusCode = OsAccountUpdateStatus.NO_CHANGE;
1758  OsAccount updatedAccount;
1759 
1760  try {
1761  CaseDbConnection connection = trans.getConnection();
1762 
1763  // if a new non-null addr is provided and the account already has an address, and they are not the same, throw an exception
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")));
1766  }
1767 
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;
1771  }
1772 
1773  if (StringUtils.isBlank(osAccount.getLoginName().orElse(null)) && !StringUtils.isBlank(loginName)) {
1774  updateAccountColumn(osAccount.getId(), "login_name", loginName, connection);
1775  updateStatusCode = OsAccountUpdateStatus.UPDATED;
1776  }
1777 
1778  // if nothing is changed, return
1779  if (updateStatusCode == OsAccountUpdateStatus.NO_CHANGE) {
1780  return new OsAccountUpdateResult(updateStatusCode, osAccount);
1781  }
1782 
1783  // update signature if needed, based on the most current addr/loginName
1784  OsAccount currAccount = getOsAccountByObjectId(osAccount.getId(), connection);
1785  String newAddress = currAccount.getAddr().orElse(null);
1786  String newLoginName = currAccount.getLoginName().orElse(null);
1787 
1788  String newSignature = getOsAccountSignature(newAddress, newLoginName);
1789  updateAccountSignature(osAccount.getId(), newSignature, connection);
1790 
1791  // get the updated account from database
1792  updatedAccount = getOsAccountByObjectId(osAccount.getId(), connection);
1793 
1794  // register the updated account with the transaction to fire off an event
1795  trans.registerChangedOsAccount(updatedAccount);
1796 
1797  return new OsAccountUpdateResult(updateStatusCode, updatedAccount);
1798 
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);
1801  }
1802  }
1803 
1813  public List<Host> getHosts(OsAccount account) throws TskCoreException {
1814  List<Host> hostList = new ArrayList<>();
1815 
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();
1820 
1822  try (CaseDbConnection connection = db.getConnection();
1823  Statement s = connection.createStatement();
1824  ResultSet rs = connection.executeQuery(s, query)) {
1825 
1826  while (rs.next()) {
1827  hostList.add(new Host(rs.getLong("hostId"), rs.getString("name"), Host.HostDbStatus.fromID(rs.getInt("db_status"))));
1828  }
1829 
1830  } catch (SQLException ex) {
1831  throw new TskCoreException(String.format("Failed to get host list for os account %d", account.getId()), ex);
1832  } finally {
1834  }
1835  return hostList;
1836  }
1837 
1849  private OsAccount osAccountFromResultSet(ResultSet rs) throws SQLException {
1850 
1851  OsAccountType accountType = null;
1852  int typeId = rs.getInt("type");
1853  if (!rs.wasNull()) {
1854  accountType = OsAccount.OsAccountType.fromID(typeId);
1855  }
1856 
1857  Long creationTime = rs.getLong("created_date"); // getLong returns 0 if value is null
1858  if (rs.wasNull()) {
1859  creationTime = null;
1860  }
1861 
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")));
1865 
1866  }
1867 
1874  private void fireChangeEvent(OsAccount account) {
1875  db.fireTSKEvent(new OsAccountsUpdatedTskEvent(Collections.singletonList(account)));
1876  }
1877 
1892  static String getOsAccountSignature(String uniqueId, String loginName) throws TskCoreException {
1893  // Create a signature.
1894  String signature;
1895  if (Strings.isNullOrEmpty(uniqueId) == false) {
1896  signature = uniqueId;
1897  } else if (Strings.isNullOrEmpty(loginName) == false) {
1898  signature = loginName;
1899  } else {
1900  throw new TskCoreException("OS Account must have either a uniqueID or a login name.");
1901  }
1902  return signature;
1903  }
1904 
1909  public static class NotUserSIDException extends TskException {
1910 
1911  private static final long serialVersionUID = 1L;
1912 
1917  super("No error message available.");
1918  }
1919 
1925  public NotUserSIDException(String msg) {
1926  super(msg);
1927  }
1928 
1935  public NotUserSIDException(String msg, Exception ex) {
1936  super(msg, ex);
1937  }
1938  }
1939 
1944 
1947  MERGED
1948  }
1949 
1954  public final static class OsAccountUpdateResult {
1955 
1956  private final OsAccountUpdateStatus updateStatus;
1957  private final OsAccount updatedAccount;
1958 
1959  OsAccountUpdateResult(OsAccountUpdateStatus updateStatus, OsAccount updatedAccount) {
1960  this.updateStatus = updateStatus;
1961  this.updatedAccount = updatedAccount;
1962  }
1963 
1965  return updateStatus;
1966  }
1967 
1968  public Optional<OsAccount> getUpdatedAccount() {
1969  return Optional.ofNullable(updatedAccount);
1970  }
1971  }
1972 
1977  private class OsAccountInstanceKey implements Comparable<OsAccountInstanceKey>{
1978 
1979  private final long osAccountId;
1980  private final long dataSourceId;
1981 
1982  OsAccountInstanceKey(long osAccountId, long dataSourceId) {
1983  this.osAccountId = osAccountId;
1984  this.dataSourceId = dataSourceId;
1985  }
1986 
1987  @Override
1988  public boolean equals(Object other) {
1989  if (this == other) {
1990  return true;
1991  }
1992  if (other == null) {
1993  return false;
1994  }
1995  if (getClass() != other.getClass()) {
1996  return false;
1997  }
1998 
1999  final OsAccountInstanceKey otherKey = (OsAccountInstanceKey) other;
2000 
2001  if (osAccountId != otherKey.osAccountId) {
2002  return false;
2003  }
2004 
2005  return dataSourceId == otherKey.dataSourceId;
2006  }
2007 
2008  @Override
2009  public int hashCode() {
2010  int hash = 5;
2011  hash = 53 * hash + (int) (this.osAccountId ^ (this.osAccountId >>> 32));
2012  hash = 53 * hash + (int) (this.dataSourceId ^ (this.dataSourceId >>> 32));
2013  return hash;
2014  }
2015 
2016  @Override
2017  public int compareTo(OsAccountInstanceKey other) {
2018  if(this.equals(other)) {
2019  return 0;
2020  }
2021 
2022  if (dataSourceId != other.dataSourceId) {
2023  return Long.compare(dataSourceId, other.dataSourceId);
2024  }
2025 
2026  return Long.compare(osAccountId, other.osAccountId);
2027  }
2028  }
2029 }
Optional< String > getAddr()
Definition: OsAccount.java:268
OsAccountRealm newWindowsRealm(String accountSid, String realmName, Host referringHost, OsAccountRealm.RealmScope realmScope)
List< Host > getHosts(OsAccount account)
OsAccount getOsAccountByObjectId(long osAccountObjId)
static OsAccountType fromID(int typeId)
Definition: OsAccount.java:195
Optional< OsAccount > getWindowsOsAccount(String sid, String loginName, String realmName, Host referringHost)
OsAccountUpdateResult updateStandardOsAccountAttributes(OsAccount osAccount, String fullName, OsAccountType accountType, OsAccountStatus accountStatus, Long creationTime)
OsAccount newWindowsOsAccount(String sid, String loginName, OsAccountRealm realm)
List< OsAccount > getOsAccounts(Host host)
OsAccountRealmManager getOsAccountRealmManager()
Optional< OsAccountRealm > getWindowsRealm(String accountSid, String realmName, Host referringHost)
List< OsAccount > getOsAccountsByDataSourceObjId(long dataSourceId)
List< OsAccountInstance > getOsAccountInstances(List< Long > instanceIDs)
OsAccountInstance newOsAccountInstance(OsAccount osAccount, DataSource dataSource, OsAccountInstance.OsAccountInstanceType instanceType)
List< OsAccountInstance > getOsAccountInstances(OsAccount account)
Optional< String > getLoginName()
Definition: OsAccount.java:288
void addExtendedOsAccountAttributes(OsAccount account, List< OsAccountAttribute > accountAttributes)
BlackboardAttribute.Type getAttributeType(String attrTypeName)
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)

Copyright © 2011-2021 Brian Carrier. (carrier -at- sleuthkit -dot- org)
This work is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.