OsAccountRealmManager.java

Go to the documentation of this file.

/*
 * Sleuth Kit Data Model
 *
 * Copyright 2020-2022 Basis Technology Corp.
 * Contact: carrier <at> sleuthkit <dot> org
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.sleuthkit.datamodel;

import com.google.common.annotations.Beta;
import com.google.common.base.Strings;
import org.apache.commons.lang3.StringUtils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.Objects;
import java.util.Optional;
import java.util.UUID;
import java.util.logging.Logger;
import org.sleuthkit.datamodel.OsAccountRealm.ScopeConfidence;
import org.sleuthkit.datamodel.SleuthkitCase.CaseDbConnection;
import org.sleuthkit.datamodel.SleuthkitCase.CaseDbTransaction;


public final class OsAccountRealmManager {

        private static final Logger LOGGER = Logger.getLogger(OsAccountRealmManager.class.getName());
        private static final String LOCAL_REALM_NAME = "local";

        private final SleuthkitCase db;

        OsAccountRealmManager(SleuthkitCase skCase) {
                this.db = skCase;
        }
                
        public OsAccountRealm newWindowsRealm(String accountSid, String realmName, Host referringHost, OsAccountRealm.RealmScope realmScope) throws TskCoreException, OsAccountManager.NotUserSIDException {

                if (realmScope == null) {
                        throw new TskCoreException("RealmScope cannot be null. Use UNKNOWN if scope is not known.");
                }
                if (referringHost == null) {
                        throw new TskCoreException("A referring host is required to create a realm.");
                }
                if ((StringUtils.isBlank(accountSid) || accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) 
                        && StringUtils.isBlank(realmName)) {
                        throw new TskCoreException("Either an address or a name is required to create a realm.");
                }

                if (StringUtils.isNotBlank(accountSid)) {
                        // SID Normalized to uppercase
                        accountSid = accountSid.toUpperCase(Locale.ENGLISH);
                }
                if (StringUtils.isNotBlank(realmName)) {
                        // Windows realm names are case insensitive. saving them in lower case.
                        realmName = realmName.toLowerCase(Locale.ENGLISH);
                }
                
                Host scopeHost;
                OsAccountRealm.ScopeConfidence scopeConfidence;
                
                switch (realmScope) {
                        case DOMAIN:
                                scopeHost = null;
                                scopeConfidence = OsAccountRealm.ScopeConfidence.KNOWN;
                                break;
                        case LOCAL:
                                scopeHost = referringHost;
                                scopeConfidence = OsAccountRealm.ScopeConfidence.KNOWN;
                                break;

                        case UNKNOWN:
                        default:
                                // NOTE: if there's a well known SID, the scope will be changed to LOCAL later. 
                                // check if the referring host already has a realm
                                boolean isHostRealmKnown = isHostRealmKnown(referringHost);
                                if (isHostRealmKnown) {
                                        scopeHost = null;       // the realm does not scope to the referring host since it already has one.
                                        scopeConfidence = OsAccountRealm.ScopeConfidence.KNOWN;
                                } else {
                                        scopeHost = referringHost;
                                        scopeConfidence = OsAccountRealm.ScopeConfidence.INFERRED;
                                }
                                break;

                }
                
                // get windows realm address from sid
                String realmAddr = null;
                String resolvedRealmName = WindowsAccountUtils.toWellknownEnglishRealmName(realmName);
                if (!Strings.isNullOrEmpty(accountSid) && !accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) {
                        
                        if (!WindowsAccountUtils.isWindowsUserSid(accountSid)) {
                                throw new OsAccountManager.NotUserSIDException(String.format("SID = %s is not a user SID.", accountSid ));
                        }
                        
                        realmAddr = WindowsAccountUtils.getWindowsRealmAddress(accountSid);
                        
                        
                        if (WindowsAccountUtils.isWindowsWellKnownSid(accountSid)) {
                                
                                // if the sid is a Windows well known SID, create a local realm for it.
                                scopeHost = referringHost;
                                scopeConfidence = OsAccountRealm.ScopeConfidence.KNOWN;
                                
                                // if the SID is a Windows well known SID, then prefer to use the default well known name to create the realm 
                                String wellKnownRealmName = WindowsAccountUtils.getWindowsWellKnownSidRealmName(accountSid);
                                if (!StringUtils.isEmpty(wellKnownRealmName)) {
                                        resolvedRealmName = wellKnownRealmName;
                                }
                        }
                }
                
                String signature = makeRealmSignature(realmAddr, resolvedRealmName, scopeHost);
                
                // create a realm
                return newRealm(resolvedRealmName, realmAddr, signature, scopeHost, scopeConfidence);
        }
        
        @Beta
        public OsAccountRealm newLocalLinuxRealm(Host referringHost) throws TskCoreException {

                if (referringHost == null) {
                        throw new TskCoreException("A referring host is required to create a realm.");
                }
                
                String realmName = LOCAL_REALM_NAME;
                OsAccountRealm.ScopeConfidence scopeConfidence = OsAccountRealm.ScopeConfidence.KNOWN;          
                String signature = makeRealmSignature("", realmName, referringHost);
                
                // create a realm
                return newRealm(realmName, "", signature, referringHost, scopeConfidence);
        }
        
        @Beta
        public Optional<OsAccountRealm> getLocalLinuxRealm(Host referringHost) throws TskCoreException {
                if (referringHost == null) {
                        throw new TskCoreException("A referring host is required get a realm.");
                }
                
                try (CaseDbConnection connection = this.db.getConnection()) {
                        return getRealmByName(LOCAL_REALM_NAME, referringHost, connection);
                }
        }
        
        public Optional<OsAccountRealm> getWindowsRealm(String accountSid, String realmName, Host referringHost) throws TskCoreException, OsAccountManager.NotUserSIDException {
                
                if (referringHost == null) {
                        throw new TskCoreException("A referring host is required get a realm.");
                }
                
                // need at least one of the two, the addr or name to look up
                if ((Strings.isNullOrEmpty(accountSid) || accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID) )
                                && Strings.isNullOrEmpty(realmName)) {
                        throw new TskCoreException("Realm address or name is required get a realm.");
                }
                if (StringUtils.isNotBlank(accountSid)) {
                        // SID Normalized to uppercase
                        accountSid = accountSid.toUpperCase(Locale.ENGLISH);
                } 
                if (StringUtils.isNotBlank(realmName)) {
                        // Windows realm names are case insensitive. saving them in lower case.
                        realmName = realmName.toLowerCase(Locale.ENGLISH);
                }
                
                try (CaseDbConnection connection = this.db.getConnection()) {
                        return getWindowsRealm(accountSid, realmName, referringHost, connection);
                }
        }
        
        
        Optional<OsAccountRealm> getWindowsRealm(String accountSid, String realmName, Host referringHost, CaseDbConnection connection) throws TskCoreException, OsAccountManager.NotUserSIDException {
                
                if (referringHost == null) {
                        throw new TskCoreException("A referring host is required get a realm.");
                }
                
                // need at least one of the two, the addr or name to look up
                if ((StringUtils.isBlank(accountSid) || accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) 
                                && StringUtils.isBlank(realmName)) {
                        throw new TskCoreException("Realm address or name is required get a realm.");
                }
                
                // If a non null accountSID is provided search for realm by addr.
                if (!Strings.isNullOrEmpty(accountSid) && !accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) {
                        
                        if (!WindowsAccountUtils.isWindowsUserSid(accountSid)) {
                                throw new OsAccountManager.NotUserSIDException(String.format("SID = %s is not a user SID.", accountSid ));
                        }
                        // get realm addr from the account SID.
                        String realmAddr = WindowsAccountUtils.getWindowsRealmAddress(accountSid);
                        Optional<OsAccountRealm> realm = getRealmByAddr(realmAddr, referringHost, connection);
                        if (realm.isPresent()) {
                                return realm;
                        }
                }

                // ensure we are using English names for any well known SIDs. 
                String resolvedRealmName = WindowsAccountUtils.toWellknownEnglishRealmName(realmName);
                
                // No realm addr so search by name.
                Optional<OsAccountRealm> realm = getRealmByName(resolvedRealmName, referringHost, connection);
                if (realm.isPresent() && !Strings.isNullOrEmpty(accountSid) && !accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) {
                        // If we were given a non-null accountSID, make sure there isn't one set on the matching realm.
                        // We know it won't match because the previous search by SID failed.
                        if (realm.get().getRealmAddr().isPresent()) {
                                return Optional.empty();
                        }
                }
                return realm;
        }
        
        
        OsRealmUpdateResult getAndUpdateWindowsRealm(String accountSid, String realmName, Host referringHost, CaseDbConnection connection) throws TskCoreException, OsAccountManager.NotUserSIDException {
                
                // get realm
                Optional<OsAccountRealm> realmOptional = getWindowsRealm(accountSid, realmName, referringHost, connection);

                // if found, update it if needed
                if (realmOptional.isPresent()) {
                        String realmAddr = (StringUtils.isNotBlank(accountSid) && !accountSid.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID)) ? WindowsAccountUtils.getWindowsRealmAddress(accountSid) : null;
                        OsRealmUpdateResult realmUpdateResult = updateRealm(realmOptional.get(), realmAddr, realmName, connection);

                        return realmUpdateResult;

                } else {
                        return new OsRealmUpdateResult(OsRealmUpdateStatus.NO_CHANGE, null);
                }

        }
        
        
        public OsRealmUpdateResult updateRealm(OsAccountRealm realm, String realmAddr, String realmName) throws TskCoreException {
                
                try (CaseDbConnection connection = db.getConnection())  {
                        return updateRealm(realm, realmAddr, realmName, connection);
                }
        }
                
        private OsRealmUpdateResult updateRealm(OsAccountRealm realm, String realmAddr, String realmName, CaseDbConnection connection) throws TskCoreException {

                // need at least one of the two
                if ( (StringUtils.isBlank(realmAddr) || realmAddr.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))  
                                && StringUtils.isBlank(realmName)) {
                        throw new TskCoreException("Realm address or name is required to update realm.");
                }

                OsRealmUpdateStatus updateStatusCode = OsRealmUpdateStatus.NO_CHANGE;
                OsAccountRealm updatedRealm = null;

                db.acquireSingleUserCaseWriteLock();
                try {
                        String currRealmAddr = realm.getRealmAddr().orElse(null);

                        // set name and address to new values only if the current value is blank and the new value isn't.               
                        if ((StringUtils.isBlank(currRealmAddr) && StringUtils.isNotBlank(realmAddr) && !realmAddr.equalsIgnoreCase(WindowsAccountUtils.WINDOWS_NULL_SID))) {
                                updateRealmColumn(realm.getRealmId(), "realm_addr", realmAddr, connection);
                                currRealmAddr = realmAddr;
                                updateStatusCode = OsRealmUpdateStatus.UPDATED;
                        }
                        
                        List<String> realmNames = realm.getRealmNames();
                        String currRealmName = realmNames.isEmpty() ? null : realmNames.get(0); // currently there is only one name.
                        
                        // Update realm name if:
                        //       Current realm name is empty
                        //       The passed in realm name is not empty
                        if (StringUtils.isBlank(currRealmName) && StringUtils.isNotBlank(realmName)) {
                                updateRealmColumn(realm.getRealmId(), "realm_name", realmName, connection);
                                updateStatusCode = OsRealmUpdateStatus.UPDATED;
                        }
                        
                        // if nothing is to be changed, return
                        if (updateStatusCode == OsRealmUpdateStatus.NO_CHANGE) {
                                return new OsRealmUpdateResult(updateStatusCode, realm);
                        }

                        // update realm signature - based on the most current address and name
                        OsAccountRealm currRealm = getRealmByRealmId(realm.getRealmId(), connection);
                        String newRealmAddr = currRealm.getRealmAddr().orElse(null);
                        String newRealmName = (currRealm.getRealmNames().isEmpty() == false) ? currRealm.getRealmNames().get(0) : null;

                        // make new signature
                        String newSignature = makeRealmSignature(newRealmAddr, newRealmName, realm.getScopeHost().orElse(null));

                        // Use a random string as the signature if the realm is not active.
                        String updateSQL = "UPDATE tsk_os_account_realms SET  "
                                        + " realm_signature = "
                                        + "   CASE WHEN db_status = " + OsAccountRealm.RealmDbStatus.ACTIVE.getId() + " THEN ? ELSE realm_signature END "
                                        + " WHERE id = ?";
                        PreparedStatement preparedStatement = connection.getPreparedStatement(updateSQL, Statement.NO_GENERATED_KEYS);
                        preparedStatement.clearParameters();

                        preparedStatement.setString(1, newSignature); // Is only set for active accounts
                        preparedStatement.setLong(2, realm.getRealmId());
                        connection.executeUpdate(preparedStatement);

                        // read the updated realm
                        updatedRealm = this.getRealmByRealmId(realm.getRealmId(), connection);

                        return new OsRealmUpdateResult(updateStatusCode, updatedRealm);
                } catch (SQLException ex) {
                        throw new TskCoreException(String.format("Error updating realm with id = %d, name = %s, addr = %s", realm.getRealmId(), realmName != null ? realmName : "Null", realm.getRealmAddr().orElse("Null")), ex);
                } finally {
                        db.releaseSingleUserCaseWriteLock();
                }

        }

        private <T> void updateRealmColumn(long realmId, String colName, T colValue, CaseDbConnection connection) throws SQLException, TskCoreException {

                String updateSQL = "UPDATE tsk_os_account_realms "
                                + " SET " + colName + " = ? "
                                + " WHERE id = ?";

                db.acquireSingleUserCaseWriteLock();
                try {
                        PreparedStatement preparedStatement = connection.getPreparedStatement(updateSQL, Statement.NO_GENERATED_KEYS);
                        preparedStatement.clearParameters();

                        if (Objects.isNull(colValue)) {
                                preparedStatement.setNull(1, Types.NULL); // handle null value
                        } else {
                                if (colValue instanceof String) {
                                        preparedStatement.setString(1, (String) colValue);
                                } else if (colValue instanceof Long) {
                                        preparedStatement.setLong(1, (Long) colValue);
                                } else if (colValue instanceof Integer) {
                                        preparedStatement.setInt(1, (Integer) colValue);
                                } else {
                                        throw new TskCoreException(String.format("Unhandled column data type received while updating the realm (id = %d) ", realmId));
                                }
                        }

                        preparedStatement.setLong(2, realmId);

                        connection.executeUpdate(preparedStatement);
                } finally {
                        db.releaseSingleUserCaseWriteLock();
                }
        }
        
        private final static String REALM_QUERY_STRING = "SELECT realms.id as realm_id, realms.realm_name as realm_name,"
                        + " realms.realm_addr as realm_addr, realms.realm_signature as realm_signature, realms.scope_host_id, realms.scope_confidence, realms.db_status,"
                        + " hosts.id, hosts.name as host_name "
                        + " FROM tsk_os_account_realms as realms"
                        + "             LEFT JOIN tsk_hosts as hosts"
                        + " ON realms.scope_host_id = hosts.id";
        
        public OsAccountRealm getRealmByRealmId(long id) throws TskCoreException {
                try (CaseDbConnection connection = this.db.getConnection()) {
                        return getRealmByRealmId(id, connection);
                }
        }
        
        OsAccountRealm getRealmByRealmId(long id, CaseDbConnection connection) throws TskCoreException {
                
                String queryString = REALM_QUERY_STRING
                                        + " WHERE realms.id = " + id;
                
                db.acquireSingleUserCaseReadLock();
                try (   Statement s = connection.createStatement();
                                ResultSet rs = connection.executeQuery(s, queryString)) {
                        OsAccountRealm accountRealm = null;
                        if (rs.next()) { 
                                accountRealm = resultSetToAccountRealm(rs);
                        } else {
                                throw new TskCoreException(String.format("No realm found with id = %d", id));
                        }

                        return accountRealm;
                } catch (SQLException ex) {
                        throw new TskCoreException(String.format("Error running the realms query = %s", queryString), ex);
                }
                finally {
                        db.releaseSingleUserCaseReadLock();
                }
        }
        
        Optional<OsAccountRealm> getRealmByAddr(String realmAddr, Host host, CaseDbConnection connection) throws TskCoreException {
                
                // If a host is specified, we want to match the realm with matching addr and specified host, or a realm with matching addr and no host.
                // If no host is specified, then we return the first realm with matching addr.
                String whereHostClause = (host == null) 
                                                        ? " 1 = 1 " 
                                                        : " ( realms.scope_host_id = " + host.getHostId() + " OR realms.scope_host_id IS NULL) ";
                String queryString = REALM_QUERY_STRING
                                                + " WHERE realms.realm_addr = '"+ realmAddr + "' "
                                                + " AND " + whereHostClause
                                        + " AND realms.db_status = " + OsAccountRealm.RealmDbStatus.ACTIVE.getId()
                                                + " ORDER BY realms.scope_host_id IS NOT NULL, realms.scope_host_id";   // ensure that non null host_id is at the front
                                    
                return getRealmUsingQuery(queryString, host,  connection);
        }
        
        Optional<OsAccountRealm> getAnotherRealmByAddr(OsAccountRealm realm, String realmAddr, Host host, CaseDbConnection connection) throws TskCoreException {
                        
                // If the given realm has a host id, then the other realm should have the same host id
                // If the given realm has no host id,  then the other realm should have no host id
                String whereHostClause = realm.getScopeHost().isPresent() 
                                                        ? " ( realms.scope_host_id = " + realm.getScopeHost().get().getHostId() + " ) " 
                                                        : " realms.scope_host_id IS NULL ";
                String queryString = REALM_QUERY_STRING
                                                + " WHERE realms.realm_addr = '"+ realmAddr + "' "
                                                + " AND " + whereHostClause
                                                + " AND realms.id <> " + realm.getRealmId()
                                        + " AND realms.db_status = " + OsAccountRealm.RealmDbStatus.ACTIVE.getId()
                                                + " ORDER BY realms.scope_host_id IS NOT NULL, realms.scope_host_id";   // ensure that non null host_id is at the front
                                    
                return getRealmUsingQuery(queryString, host,  connection);
        }
        
        Optional<OsAccountRealm> getRealmByName(String realmName, Host host, CaseDbConnection connection) throws TskCoreException {
                
                // If a host is specified, we want to match the realm with matching name and specified host, or a realm with matching name and no host.
                // If no host is specified, then we return the first realm with matching name.
                String whereHostClause = (host == null)
                                ? " 1 = 1 "
                                : " ( realms.scope_host_id = " + host.getHostId() + " OR realms.scope_host_id IS NULL ) ";
                String queryString = REALM_QUERY_STRING
                                + " WHERE realms.realm_name = '" + realmName + "'"
                                + " AND " + whereHostClause
                                + " AND realms.db_status = " + OsAccountRealm.RealmDbStatus.ACTIVE.getId()
                                + " ORDER BY realms.scope_host_id IS NOT NULL, realms.scope_host_id";   // ensure that non null host_id are at the front

                return getRealmUsingQuery(queryString, host,  connection);
        }
        
        Optional<OsAccountRealm> getAnotherRealmByName(OsAccountRealm realm, String realmName, Host host, CaseDbConnection connection) throws TskCoreException {
                
                // If the given realm has a host id, then the other realm should have the same host id
                // If the given realm has no host id,  then the other realm should have no host id
                String whereHostClause = realm.getScopeHost().isPresent()
                                                        ? " ( realms.scope_host_id = " + realm.getScopeHost().get().getHostId() + " ) " 
                                                        : " realms.scope_host_id IS NULL ";
                String queryString = REALM_QUERY_STRING
                                + " WHERE realms.realm_name = '" + realmName + "'"
                                + " AND " + whereHostClause
                                + " AND realms.id <> " + realm.getRealmId()
                                + " AND realms.db_status = " + OsAccountRealm.RealmDbStatus.ACTIVE.getId()
                                + " ORDER BY realms.scope_host_id IS NOT NULL, realms.scope_host_id";   // ensure that non null host_id are at the front

                return getRealmUsingQuery(queryString, host,  connection);
                
        }
        
        private Optional<OsAccountRealm> getRealmUsingQuery(String queryString,  Host host, CaseDbConnection connection) throws TskCoreException {
                
                db.acquireSingleUserCaseReadLock();
                try (Statement s = connection.createStatement();
                                ResultSet rs = connection.executeQuery(s, queryString)) {
                        
                        OsAccountRealm accountRealm = null;
                        if (rs.next()) {
                                Host realmHost = null;
                                long hostId = rs.getLong("scope_host_id");
                                if (!rs.wasNull()) {
                                        if (host != null ) {
                                                realmHost = host;
                                        } else {
                                                realmHost = new Host(hostId, rs.getString("host_name"));
                                        }
                                }
                                
                                accountRealm = new OsAccountRealm(rs.getLong("realm_id"), rs.getString("realm_name"), 
                                                                                                rs.getString("realm_addr"), rs.getString("realm_signature"), 
                                                                                                realmHost, ScopeConfidence.fromID(rs.getInt("scope_confidence")),
                                                                                                OsAccountRealm.RealmDbStatus.fromID(rs.getInt("db_status")));
                                
                        } 
                        return Optional.ofNullable(accountRealm);
                } catch (SQLException ex) {
                        throw new TskCoreException(String.format("Error getting realm using query = %s", queryString), ex);
                } finally {
                        db.releaseSingleUserCaseReadLock();
                }
        }
        
        private boolean isHostRealmKnown(Host host) throws TskCoreException {
        
                // check if this host has a local known realm aleady, other than the special windows realm.
                String queryString = REALM_QUERY_STRING
                                + " WHERE realms.scope_host_id = " + host.getHostId()
                                + " AND realms.scope_confidence = " + OsAccountRealm.ScopeConfidence.KNOWN.getId()
                                + " AND realms.db_status = " + OsAccountRealm.RealmDbStatus.ACTIVE.getId();

                db.acquireSingleUserCaseReadLock();
                try (CaseDbConnection connection = this.db.getConnection();
                                Statement s = connection.createStatement();
                                ResultSet rs = connection.executeQuery(s, queryString)) {
                        
                        // return true if there is any match.
                        return rs.next();
                } catch (SQLException ex) {
                        throw new TskCoreException(String.format("Error getting account realm for with host = %s", host.getName()), ex);
                }
                finally {
                        db.releaseSingleUserCaseReadLock();
                }

        }

        private OsAccountRealm resultSetToAccountRealm(ResultSet rs) throws SQLException {
                
                long hostId = rs.getLong("scope_host_id");
                Host realmHost = null;
                if (!rs.wasNull()) {
                        realmHost = new Host(hostId, rs.getString("host_name"));
                }

                return new OsAccountRealm(rs.getLong("realm_id"), rs.getString("realm_name"), 
                                                                                                rs.getString("realm_addr"), rs.getString("realm_signature"), 
                                                                                                realmHost, ScopeConfidence.fromID(rs.getInt("scope_confidence")),
                                                                                                OsAccountRealm.RealmDbStatus.fromID(rs.getInt("db_status")));
        }
        
//      /**
//       * Get all realms.
//       * 
//       * @return Collection of OsAccountRealm
//       */
//      Collection<OsAccountRealm> getRealms() throws TskCoreException {
//              String queryString = "SELECT realms.id as realm_id, realms.realm_name as realm_name, realms.realm_addr as realm_addr, realms.scope_host_id, realms.scope_confidence, "
//                              + " hosts.id, hosts.name as host_name "
//                              + " FROM tsk_os_account_realms as realms"
//                              + "             LEFT JOIN tsk_hosts as hosts"
//                              + " ON realms.scope_host_id = hosts.id";
//
//              db.acquireSingleUserCaseReadLock();
//              try (CaseDbConnection connection = this.db.getConnection();
//                              Statement s = connection.createStatement();
//                              ResultSet rs = connection.executeQuery(s, queryString)) {
//
//                      ArrayList<OsAccountRealm> accountRealms = new ArrayList<>();
//                      while (rs.next()) {
//                              long hostId = rs.getLong("scope_host_id");
//                              Host host = null;
//                              if (!rs.wasNull()) {
//                                      host = new Host(hostId, rs.getString("host_name"));
//                              }
//
//                              accountRealms.add(new OsAccountRealm(rs.getLong("realm_id"), rs.getString("realm_name"),
//                                              ScopeConfidence.fromID(rs.getInt("scope_confidence")),
//                                              rs.getString("realm_addr"), host));
//                      }
//
//                      return accountRealms;
//              } catch (SQLException ex) {
//                      throw new TskCoreException(String.format("Error running the realms query = %s", queryString), ex);
//              }
//              finally {
//                      db.releaseSingleUserCaseReadLock();
//              }
//      }
        
        
        private OsAccountRealm newRealm(String realmName, String realmAddr, String signature, Host host, OsAccountRealm.ScopeConfidence scopeConfidence) throws TskCoreException {

                db.acquireSingleUserCaseWriteLock();
                try (CaseDbConnection connection = this.db.getConnection()) {
                        String realmInsertSQL = "INSERT INTO tsk_os_account_realms(realm_name, realm_addr, realm_signature, scope_host_id, scope_confidence)"
                                        + " VALUES (?, ?, ?, ?, ?)"; // NON-NLS

                        PreparedStatement preparedStatement = connection.getPreparedStatement(realmInsertSQL, Statement.RETURN_GENERATED_KEYS);
                        preparedStatement.clearParameters();

                        preparedStatement.setString(1, realmName);
                        preparedStatement.setString(2, realmAddr);
                        preparedStatement.setString(3, signature);
                        if (host != null) {
                                preparedStatement.setLong(4, host.getHostId());
                        } else {
                                preparedStatement.setNull(4, java.sql.Types.BIGINT);
                        }
                        preparedStatement.setInt(5, scopeConfidence.getId());

                        connection.executeUpdate(preparedStatement);

                        // Read back the row id
                        try (ResultSet resultSet = preparedStatement.getGeneratedKeys();) {
                                long rowId = resultSet.getLong(1); // last_insert_rowid()
                                return new OsAccountRealm(rowId, realmName, realmAddr, signature, host, scopeConfidence, OsAccountRealm.RealmDbStatus.ACTIVE);
                        }

                } catch (SQLException ex) {
                        // Create may have failed if the realm already exists. Try and get the matching realm 
                        try (CaseDbConnection connection = this.db.getConnection()) {
                                if (!Strings.isNullOrEmpty(realmAddr)) {
                                        Optional<OsAccountRealm> accountRealm = this.getRealmByAddr(realmAddr, host, connection);
                                        if (accountRealm.isPresent()) {
                                                return accountRealm.get();
                                        }
                                } else if (!Strings.isNullOrEmpty(realmName)) {
                                        Optional<OsAccountRealm> accountRealm = this.getRealmByName(realmName, host, connection);
                                        if (accountRealm.isPresent()) {
                                                return accountRealm.get();
                                        }
                                }

                                // some other failure - throw an exception
                                throw new TskCoreException(String.format("Error creating realm with address = %s and name = %s, with host = %s",
                                                realmAddr != null ? realmAddr : "", realmName != null ? realmName : "", host != null ? host.getName() : ""), ex);
                        }
                } finally {
                        db.releaseSingleUserCaseWriteLock();
                }
        }
        

        static String makeRealmSignature(String realmAddr, String realmName, Host scopeHost) throws TskCoreException {

                // need at least one of the two, the addr or name to look up
                if (Strings.isNullOrEmpty(realmAddr) && Strings.isNullOrEmpty(realmName)) {
                        throw new TskCoreException("Realm address and name can't both be null.");
                }
                
                String signature = String.format("%s_%s", !Strings.isNullOrEmpty(realmAddr) ?  realmAddr : realmName,
                                                                                                scopeHost != null ? scopeHost.getHostId() : "DOMAIN");
                return signature;
        }
        
        private String makeMergedRealmSignature() {
                return "MERGED " +  UUID.randomUUID().toString();
        }
        
        
        void moveOrMergeRealm(OsAccountRealm sourceRealm, Host destHost, CaseDbTransaction trans) throws TskCoreException {
                // Look for a matching realm by address
                Optional<OsAccountRealm> optDestRealmAddr = Optional.empty();
                if (sourceRealm.getRealmAddr().isPresent()) {
                        optDestRealmAddr = db.getOsAccountRealmManager().getRealmByAddr(sourceRealm.getRealmAddr().get(), destHost, trans.getConnection());
                }
                
                // Look for a matching realm by name
                Optional<OsAccountRealm> optDestRealmName = Optional.empty();
                if (!sourceRealm.getRealmNames().isEmpty()) {
                        optDestRealmName = db.getOsAccountRealmManager().getRealmByName(sourceRealm.getRealmNames().get(0), destHost, trans.getConnection());
                }
                
                // Decide how to proceed:
                // - If we only got one match:
                // -- If the address matched, set destRealm to the matching address realm
                // -- If the name matched but the original and the matching realm have different addresses, leave destRealm null (it'll be a move)
                // -- If the name matched and at least one of the address fields was null, set destRealm to the matching name realm
                // - If we got no matches, leave destRealm null (we'll do a move not a merge)
                // - If we got two of the same matches, set destRealm to that realm
                // - If we got two different matches:
                // -- If the name match has no address set, merge the matching name realm into the matching address realm, then
                //        set destRealm to the matching address realm
                // -- Otherwise we're in the case where the addresses are different. We will consider the address the 
                //        stronger match and set destRealm to the matching address realm and leave the matching name realm as-is.               
                OsAccountRealm destRealm = null;
                if (optDestRealmAddr.isPresent() && optDestRealmName.isPresent()) {
                        if (optDestRealmAddr.get().getRealmId() == optDestRealmName.get().getRealmId()) {
                                // The two matches are the same
                                destRealm = optDestRealmAddr.get();
                        } else {
                                if (optDestRealmName.get().getRealmAddr().isPresent()) {
                                        // The addresses are different, so use the one with the matching address
                                        destRealm = optDestRealmAddr.get();
                                } else {
                                        // Merge the realm with the matching name into the realm with the matching address.
                                        // Reload from database afterward to make sure everything is up-to-date.
                                        mergeRealms(optDestRealmName.get(), optDestRealmAddr.get(), trans);
                                        destRealm = getRealmByRealmId(optDestRealmAddr.get().getRealmId(), trans.getConnection());
                                }
                        }
                } else if (optDestRealmAddr.isPresent()) {
                        // Only address matched - use it
                        destRealm = optDestRealmAddr.get();
                } else if (optDestRealmName.isPresent()) {
                        // Only name matched - check whether both have addresses set.
                        // Due to earlier checks we know the address fields can't be the same, so
                        // don't do anything if both have addresses - we consider the address to be a stronger identifier than the name
                        if (! (optDestRealmName.get().getRealmAddr().isPresent() && sourceRealm.getRealmAddr().isPresent())) {
                                destRealm = optDestRealmName.get();
                        }
                }
                
                // Move or merge the source realm
                if (destRealm == null) {
                        moveRealm(sourceRealm, destHost, trans);
                } else {
                        mergeRealms(sourceRealm, destRealm, trans);
                }
        }
        
        private void moveRealm(OsAccountRealm sourceRealm, Host destHost, CaseDbTransaction trans) throws TskCoreException {
                try(Statement s = trans.getConnection().createStatement()) {
                        String query = "UPDATE tsk_os_account_realms SET scope_host_id = " + destHost.getHostId() + " WHERE id = " + sourceRealm.getRealmId();
                        s.executeUpdate(query);
                } catch (SQLException ex) {
                        throw new TskCoreException("Error moving realm with id: " + sourceRealm.getRealmId() + " to host with id: " + destHost.getHostId(), ex);
                }
        }
        
        
        void mergeRealms(OsAccountRealm sourceRealm, OsAccountRealm destRealm, CaseDbTransaction trans) throws TskCoreException {

                // Update accounts
                db.getOsAccountManager().mergeOsAccountsForRealms(sourceRealm, destRealm, trans);

                // Update the sourceRealm realm
                CaseDbConnection connection = trans.getConnection();
                try (Statement statement = connection.createStatement()) {
                        String updateStr = "UPDATE tsk_os_account_realms SET db_status = " + OsAccountRealm.RealmDbStatus.MERGED.getId() 
                                        + ", merged_into = " + destRealm.getRealmId()
                                        + ", realm_signature = '" + makeMergedRealmSignature() + "' "
                                        + " WHERE id = " + sourceRealm.getRealmId();
                        connection.executeUpdate(statement, updateStr);
                } catch (SQLException ex) {
                        throw new TskCoreException ("Error updating status of realm with id: " + sourceRealm.getRealmId(), ex);
                }
                
                // Update the destination realm if it doesn't have the name or addr set and the source realm does
                if (!destRealm.getRealmAddr().isPresent() && sourceRealm.getRealmAddr().isPresent()) {
                        updateRealm(destRealm, sourceRealm.getRealmAddr().get(), null, trans.getConnection());
                } else if (destRealm.getRealmNames().isEmpty() && !sourceRealm.getRealmNames().isEmpty()) {
                        updateRealm(destRealm, null, sourceRealm.getRealmNames().get(0), trans.getConnection());
                }
        }
        
        List<OsAccountRealm> getRealmsByHost(Host host, CaseDbConnection connection) throws TskCoreException {
                List<OsAccountRealm> results = new ArrayList<>();
                String queryString = REALM_QUERY_STRING
                        + " WHERE realms.scope_host_id = " + host.getHostId();
                
                db.acquireSingleUserCaseReadLock();
                try (   Statement s = connection.createStatement();
                                ResultSet rs = connection.executeQuery(s, queryString)) {
                        while (rs.next()) { 
                                results.add(resultSetToAccountRealm(rs));
                        } 
                        return results;
                } catch (SQLException ex) {
                        throw new TskCoreException(String.format("Error gettings realms for host with id = " + host.getHostId()), ex);
                }
                finally {
                        db.releaseSingleUserCaseReadLock();
                }
        }
        
        public enum OsRealmUpdateStatus {

                NO_CHANGE,      
                UPDATED,        
                MERGED          
        }
        
        public final static class OsRealmUpdateResult {
                
                private final OsRealmUpdateStatus updateStatus;
                private final OsAccountRealm updatedRealm;
                
                OsRealmUpdateResult(OsRealmUpdateStatus updateStatus, OsAccountRealm updatedRealm) {
                        this.updateStatus = updateStatus;
                        this.updatedRealm = updatedRealm;
                }

                public OsRealmUpdateStatus getUpdateStatus() {
                        return updateStatus;
                }

                public Optional<OsAccountRealm> getUpdatedRealm() {
                        return Optional.ofNullable(updatedRealm);
                }
        }
}