CredentialStore.cc

Go to the documentation of this file.

#include "CondCore/CondDB/interface/CredentialStore.h"
#include "CondCore/CondDB/interface/Cipher.h"
#include "CondCore/CondDB/interface/Exception.h"
#include "CondCore/CondDB/interface/Auth.h"
//
#include "CoralBase/AttributeList.h"
#include "CoralBase/Attribute.h"
#include "CoralKernel/Context.h"
#include "CoralCommon/URIParser.h"
#include "RelationalAccess/AuthenticationCredentials.h"
#include "RelationalAccess/IBulkOperation.h"
#include "RelationalAccess/IConnection.h"
#include "RelationalAccess/ISession.h"
#include "RelationalAccess/IRelationalService.h"
#include "RelationalAccess/IRelationalDomain.h"
#include "RelationalAccess/ITransaction.h"
#include "RelationalAccess/ISchema.h"
#include "RelationalAccess/ITable.h"
#include "RelationalAccess/TableDescription.h"
#include "RelationalAccess/ITableDataEditor.h"
#include "RelationalAccess/ITablePrivilegeManager.h"
#include "RelationalAccess/IQuery.h"
#include "RelationalAccess/ICursor.h"
//
#include "RelationalAccess/AuthenticationCredentials.h"
//
#include <sstream>
#include <fstream>
#include <boost/filesystem.hpp>

static const std::string serviceName = "CondAuthenticationService";

coral_bridge::AuthenticationCredentialSet::AuthenticationCredentialSet() : m_data() {}

coral_bridge::AuthenticationCredentialSet::~AuthenticationCredentialSet() { reset(); }

void coral_bridge::AuthenticationCredentialSet::reset() {
  for (std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>::iterator iData =
           m_data.begin();
       iData != m_data.end();
       ++iData)
    delete iData->second;
  m_data.clear();
}

void coral_bridge::AuthenticationCredentialSet::registerItem(const std::string& connectionString,
                                                             const std::string& itemName,
                                                             const std::string& itemValue) {
  registerItem(connectionString, cond::auth::COND_DEFAULT_ROLE, itemName, itemValue);
}

void coral_bridge::AuthenticationCredentialSet::registerItem(const std::string& connectionString,
                                                             const std::string& role,
                                                             const std::string& itemName,
                                                             const std::string& itemValue) {
  std::pair<std::string, std::string> connKey(connectionString, role);
  std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>::iterator iData =
      m_data.find(connKey);
  if (iData == m_data.end()) {
    iData = m_data.insert(std::make_pair(connKey, new coral::AuthenticationCredentials(serviceName))).first;
  }
  iData = m_data.insert(std::make_pair(connKey, new coral::AuthenticationCredentials(serviceName))).first;
  iData->second->registerItem(itemName, itemValue);
}

void coral_bridge::AuthenticationCredentialSet::registerCredentials(const std::string& connectionString,
                                                                    const std::string& userName,
                                                                    const std::string& password) {
  registerCredentials(connectionString, cond::auth::COND_DEFAULT_ROLE, userName, password);
}

void coral_bridge::AuthenticationCredentialSet::registerCredentials(const std::string& connectionString,
                                                                    const std::string& role,
                                                                    const std::string& userName,
                                                                    const std::string& password) {
  std::pair<std::string, std::string> connKey(connectionString, role);
  std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>::iterator iData =
      m_data.find(connKey);
  if (iData != m_data.end()) {
    delete iData->second;
    m_data.erase(connKey);
  }
  iData = m_data.insert(std::make_pair(connKey, new coral::AuthenticationCredentials(serviceName))).first;
  iData->second->registerItem(coral::IAuthenticationCredentials::userItem(), userName);
  iData->second->registerItem(coral::IAuthenticationCredentials::passwordItem(), password);
}

void coral_bridge::AuthenticationCredentialSet::import(const AuthenticationCredentialSet& data) {
  for (std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>::const_iterator iData =
           data.m_data.begin();
       iData != data.m_data.end();
       ++iData) {
    registerCredentials(iData->first.first,
                        iData->first.second,
                        iData->second->valueForItem(coral::IAuthenticationCredentials::userItem()),
                        iData->second->valueForItem(coral::IAuthenticationCredentials::passwordItem()));
  }
}

const coral::IAuthenticationCredentials* coral_bridge::AuthenticationCredentialSet::get(
    const std::string& connectionString) const {
  return get(connectionString, cond::auth::COND_DEFAULT_ROLE);
}

const coral::IAuthenticationCredentials* coral_bridge::AuthenticationCredentialSet::get(
    const std::string& connectionString, const std::string& role) const {
  const coral::IAuthenticationCredentials* ret = nullptr;
  std::pair<std::string, std::string> connKey(connectionString, role);
  std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>::const_iterator iData =
      m_data.find(connKey);
  if (iData != m_data.end()) {
    ret = iData->second;
  }
  return ret;
}

const std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>&
coral_bridge::AuthenticationCredentialSet::data() const {
  return m_data;
}

static const std::string SEQUENCE_TABLE_NAME("COND_CREDENTIAL_SEQUENCE");
static const std::string SEQUENCE_NAME_COL("NAME");
static const std::string SEQUENCE_VALUE_COL("VALUE");

static const std::string COND_AUTHENTICATION_TABLE("COND_AUTHENTICATION");
static const std::string PRINCIPAL_ID_COL("P_ID");
static const std::string PRINCIPAL_NAME_COL("P_NAME");
static const std::string VERIFICATION_COL("CRED0");
static const std::string PRINCIPAL_KEY_COL("CRED1");
static const std::string ADMIN_KEY_COL("CRED2");

static const std::string COND_AUTHORIZATION_TABLE("COND_AUTHORIZATION");
static const std::string AUTH_ID_COL("AUTH_ID");
static const std::string P_ID_COL("P_ID");
static const std::string ROLE_COL("C_ROLE");
static const std::string SCHEMA_COL("C_SCHEMA");
static const std::string AUTH_KEY_COL("CRED3");
static const std::string C_ID_COL("C_ID");

static const std::string COND_CREDENTIAL_TABLE("COND_CREDENTIAL");
static const std::string CONNECTION_ID_COL("CONN_ID");
static const std::string CONNECTION_LABEL_COL("CONN_LABEL");
static const std::string USERNAME_COL("CRED4");
static const std::string PASSWORD_COL("CRED5");
static const std::string VERIFICATION_KEY_COL("CRED6");
static const std::string CONNECTION_KEY_COL("CRED7");

const std::string DEFAULT_DATA_SOURCE("Cond_Default_Authentication");

// implementation functions and helpers
namespace cond {

  std::string schemaLabel(const std::string& serviceName, const std::string& userName) {
    std::string ret = userName;
    if (!serviceName.empty()) {
      ret += "@" + serviceName;
    }
    return ret;
  }

  std::string schemaLabelForCredentialStore(const std::string& connectionString) {
    coral::URIParser parser;
    parser.setURI(connectionString);
    std::string serviceName = parser.hostName();
    std::string schemaName = parser.databaseOrSchemaName();
    return schemaLabel(serviceName, schemaName);
  }

  class CSScopedSession {
  public:
    CSScopedSession(CredentialStore& store) : m_store(store) {}
    ~CSScopedSession() { m_store.closeSession(false); }
    void startSuper(const std::string& connectionString, const std::string& userName, const std::string& password) {
      m_store.startSuperSession(connectionString, userName, password);
    }
    void start(bool readOnly = true) { m_store.startSession(readOnly); }
    void close() { m_store.closeSession(); }

  private:
    CredentialStore& m_store;
  };

  struct PrincipalData {
    int id;
    std::string verifKey;
    std::string principalKey;
    std::string adminKey;
    PrincipalData() : id(-1), verifKey(""), principalKey(""), adminKey("") {}
  };
  bool selectPrincipal(coral::ISchema& schema, const std::string& principal, PrincipalData& destination) {
    std::unique_ptr<coral::IQuery> query(schema.tableHandle(COND_AUTHENTICATION_TABLE).newQuery());
    coral::AttributeList readBuff;
    readBuff.extend<int>(PRINCIPAL_ID_COL);
    readBuff.extend<std::string>(VERIFICATION_COL);
    readBuff.extend<std::string>(PRINCIPAL_KEY_COL);
    readBuff.extend<std::string>(ADMIN_KEY_COL);
    coral::AttributeList whereData;
    whereData.extend<std::string>(PRINCIPAL_NAME_COL);
    whereData[PRINCIPAL_NAME_COL].data<std::string>() = principal;
    std::string whereClause = PRINCIPAL_NAME_COL + " = :" + PRINCIPAL_NAME_COL;
    query->defineOutput(readBuff);
    query->addToOutputList(PRINCIPAL_ID_COL);
    query->addToOutputList(VERIFICATION_COL);
    query->addToOutputList(PRINCIPAL_KEY_COL);
    query->addToOutputList(ADMIN_KEY_COL);
    query->setCondition(whereClause, whereData);
    coral::ICursor& cursor = query->execute();
    bool found = false;
    if (cursor.next()) {
      found = true;
      const coral::AttributeList& row = cursor.currentRow();
      destination.id = row[PRINCIPAL_ID_COL].data<int>();
      destination.verifKey = row[VERIFICATION_COL].data<std::string>();
      destination.principalKey = row[PRINCIPAL_KEY_COL].data<std::string>();
      destination.adminKey = row[ADMIN_KEY_COL].data<std::string>();
    }
    return found;
  }

  struct CredentialData {
    int id;
    std::string userName;
    std::string password;
    std::string connectionKey;
    std::string verificationKey;
    CredentialData() : id(-1), userName(""), password(""), connectionKey("") {}
  };

  bool selectConnection(coral::ISchema& schema, const std::string& connectionLabel, CredentialData& destination) {
    std::unique_ptr<coral::IQuery> query(schema.tableHandle(COND_CREDENTIAL_TABLE).newQuery());
    coral::AttributeList readBuff;
    readBuff.extend<int>(CONNECTION_ID_COL);
    readBuff.extend<std::string>(USERNAME_COL);
    readBuff.extend<std::string>(PASSWORD_COL);
    readBuff.extend<std::string>(VERIFICATION_KEY_COL);
    readBuff.extend<std::string>(CONNECTION_KEY_COL);
    coral::AttributeList whereData;
    whereData.extend<std::string>(CONNECTION_LABEL_COL);
    whereData[CONNECTION_LABEL_COL].data<std::string>() = connectionLabel;
    std::string whereClause = CONNECTION_LABEL_COL + " = :" + CONNECTION_LABEL_COL;
    query->defineOutput(readBuff);
    query->addToOutputList(CONNECTION_ID_COL);
    query->addToOutputList(USERNAME_COL);
    query->addToOutputList(PASSWORD_COL);
    query->addToOutputList(VERIFICATION_KEY_COL);
    query->addToOutputList(CONNECTION_KEY_COL);
    query->setCondition(whereClause, whereData);
    coral::ICursor& cursor = query->execute();
    bool found = false;
    if (cursor.next()) {
      const coral::AttributeList& row = cursor.currentRow();
      destination.id = row[CONNECTION_ID_COL].data<int>();
      destination.userName = row[USERNAME_COL].data<std::string>();
      destination.password = row[PASSWORD_COL].data<std::string>();
      destination.verificationKey = row[VERIFICATION_KEY_COL].data<std::string>();
      destination.connectionKey = row[CONNECTION_KEY_COL].data<std::string>();
      found = true;
    }
    return found;
  }

  struct AuthorizationData {
    int id;
    int connectionId;
    std::string key;
    AuthorizationData() : id(-1), connectionId(-1), key("") {}
  };

  bool selectAuthorization(coral::ISchema& schema,
                           int principalId,
                           const std::string& role,
                           const std::string& connectionString,
                           AuthorizationData& destination) {
    std::unique_ptr<coral::IQuery> query(schema.tableHandle(COND_AUTHORIZATION_TABLE).newQuery());
    coral::AttributeList readBuff;
    readBuff.extend<int>(AUTH_ID_COL);
    readBuff.extend<int>(C_ID_COL);
    readBuff.extend<std::string>(AUTH_KEY_COL);
    coral::AttributeList whereData;
    whereData.extend<int>(P_ID_COL);
    whereData.extend<std::string>(ROLE_COL);
    whereData.extend<std::string>(SCHEMA_COL);
    whereData[P_ID_COL].data<int>() = principalId;
    whereData[ROLE_COL].data<std::string>() = role;
    whereData[SCHEMA_COL].data<std::string>() = connectionString;
    std::stringstream whereClause;
    whereClause << P_ID_COL << " = :" << P_ID_COL;
    whereClause << " AND " << ROLE_COL << " = :" << ROLE_COL;
    whereClause << " AND " << SCHEMA_COL << " = :" << SCHEMA_COL;
    query->defineOutput(readBuff);
    query->addToOutputList(AUTH_ID_COL);
    query->addToOutputList(C_ID_COL);
    query->addToOutputList(AUTH_KEY_COL);
    query->setCondition(whereClause.str(), whereData);
    coral::ICursor& cursor = query->execute();
    bool found = false;
    if (cursor.next()) {
      const coral::AttributeList& row = cursor.currentRow();
      destination.id = row[AUTH_ID_COL].data<int>();
      destination.connectionId = row[C_ID_COL].data<int>();
      destination.key = row[AUTH_KEY_COL].data<std::string>();
      found = true;
    }
    return found;
  }

  bool getNextSequenceValue(coral::ISchema& schema, const std::string& sequenceName, int& value) {
    bool ret = false;
    std::unique_ptr<coral::IQuery> query(schema.tableHandle(SEQUENCE_TABLE_NAME).newQuery());
    query->limitReturnedRows(1, 0);
    query->addToOutputList(SEQUENCE_VALUE_COL);
    query->defineOutputType(SEQUENCE_VALUE_COL, coral::AttributeSpecification::typeNameForType<int>());
    query->setForUpdate();
    std::string whereClause(SEQUENCE_NAME_COL + " = :" + SEQUENCE_NAME_COL);
    coral::AttributeList rowData;
    rowData.extend<std::string>(SEQUENCE_NAME_COL);
    rowData.begin()->data<std::string>() = sequenceName;
    query->setCondition(whereClause, rowData);
    coral::ICursor& cursor = query->execute();
    if (cursor.next()) {
      value = cursor.currentRow().begin()->data<int>() + 1;
      ret = true;
    } else {
      return false;
    }
    // update...
    coral::AttributeList updateData;
    updateData.extend<std::string>(SEQUENCE_NAME_COL);
    updateData.extend<int>(SEQUENCE_VALUE_COL);
    std::string setClause(SEQUENCE_VALUE_COL + " = :" + SEQUENCE_VALUE_COL);
    std::string whClause(SEQUENCE_NAME_COL + " = :" + SEQUENCE_NAME_COL);
    coral::AttributeList::iterator iAttribute = updateData.begin();
    iAttribute->data<std::string>() = sequenceName;
    ++iAttribute;
    iAttribute->data<int>() = value;
    schema.tableHandle(SEQUENCE_TABLE_NAME).dataEditor().updateRows(setClause, whClause, updateData);
    return ret;
  }

  std::pair<int, std::string> updatePrincipalData(coral::ISchema& schema,
                                                  const std::string& authenticationKey,
                                                  const std::string& principalName,
                                                  const std::string& adminKey,
                                                  bool init = false) {
    PrincipalData princData;
    bool found = selectPrincipal(schema, principalName, princData);

    auth::Cipher cipher0(authenticationKey);
    auth::Cipher cipher1(adminKey);

    std::string verifStr = cipher0.b64encrypt(principalName);
    std::string principalKey("");
    int principalId = princData.id;

    coral::ITableDataEditor& editor = schema.tableHandle(COND_AUTHENTICATION_TABLE).dataEditor();
    if (found) {
      principalKey = cipher1.b64decrypt(princData.adminKey);
      coral::AttributeList updateData;
      updateData.extend<int>(PRINCIPAL_ID_COL);
      updateData.extend<std::string>(VERIFICATION_COL);
      updateData.extend<std::string>(PRINCIPAL_KEY_COL);
      updateData.extend<std::string>(ADMIN_KEY_COL);
      updateData[PRINCIPAL_ID_COL].data<int>() = principalId;
      updateData[VERIFICATION_COL].data<std::string>() = verifStr;
      updateData[PRINCIPAL_KEY_COL].data<std::string>() = cipher0.b64encrypt(principalKey);
      updateData[ADMIN_KEY_COL].data<std::string>() = cipher1.b64encrypt(principalKey);
      std::stringstream setClause;
      setClause << VERIFICATION_COL << " = :" << VERIFICATION_COL << ", ";
      setClause << PRINCIPAL_KEY_COL << " = :" << PRINCIPAL_KEY_COL << ", ";
      setClause << ADMIN_KEY_COL << " = :" << ADMIN_KEY_COL;
      std::string whereClause = PRINCIPAL_ID_COL + " = :" + PRINCIPAL_ID_COL;
      editor.updateRows(setClause.str(), whereClause, updateData);
    } else {
      if (init) {
        principalKey = adminKey;
      } else {
        auth::KeyGenerator gen;
        principalKey = gen.make(auth::COND_DB_KEY_SIZE);
      }
      coral::ITableDataEditor& editor0 = schema.tableHandle(COND_AUTHENTICATION_TABLE).dataEditor();

      if (!getNextSequenceValue(schema, COND_AUTHENTICATION_TABLE, principalId))
        throwException("Can't find " + COND_AUTHENTICATION_TABLE + " sequence.", "CredentialStore::updatePrincipal");

      coral::AttributeList authData;
      editor0.rowBuffer(authData);
      authData[PRINCIPAL_ID_COL].data<int>() = principalId;
      authData[PRINCIPAL_NAME_COL].data<std::string>() = principalName;
      authData[VERIFICATION_COL].data<std::string>() = verifStr;
      authData[PRINCIPAL_KEY_COL].data<std::string>() = cipher0.b64encrypt(principalKey);
      authData[ADMIN_KEY_COL].data<std::string>() = cipher1.b64encrypt(principalKey);
      editor0.insertRow(authData);
    }

    return std::make_pair(principalId, principalKey);
  }

  bool setPermissionData(coral::ISchema& schema,
                         int principalId,
                         const std::string& principalKey,
                         const std::string& role,
                         const std::string& connectionString,
                         int connectionId,
                         const std::string& connectionKey) {
    auth::Cipher cipher(principalKey);
    std::string encryptedConnectionKey = cipher.b64encrypt(connectionKey);
    AuthorizationData authData;
    bool found = selectAuthorization(schema, principalId, role, connectionString, authData);

    coral::ITableDataEditor& editor = schema.tableHandle(COND_AUTHORIZATION_TABLE).dataEditor();
    if (found) {
      coral::AttributeList updateData;
      updateData.extend<int>(AUTH_ID_COL);
      updateData.extend<int>(C_ID_COL);
      updateData.extend<std::string>(AUTH_KEY_COL);
      updateData[AUTH_ID_COL].data<int>() = authData.id;
      updateData[C_ID_COL].data<int>() = connectionId;
      updateData[AUTH_KEY_COL].data<std::string>() = encryptedConnectionKey;
      std::string setCl = C_ID_COL + " = :" + C_ID_COL + ", " + AUTH_KEY_COL + " = :" + AUTH_KEY_COL;
      std::string whereCl = AUTH_ID_COL + " = :" + AUTH_ID_COL;
      editor.updateRows(setCl, whereCl, updateData);
    } else {
      int next = -1;
      if (!getNextSequenceValue(schema, COND_AUTHORIZATION_TABLE, next))
        throwException("Can't find " + COND_AUTHORIZATION_TABLE + " sequence.", "CredentialStore::setPermission");

      coral::AttributeList insertData;
      insertData.extend<int>(AUTH_ID_COL);
      insertData.extend<int>(P_ID_COL);
      insertData.extend<std::string>(ROLE_COL);
      insertData.extend<std::string>(SCHEMA_COL);
      insertData.extend<std::string>(AUTH_KEY_COL);
      insertData.extend<int>(C_ID_COL);
      insertData[AUTH_ID_COL].data<int>() = next;
      insertData[P_ID_COL].data<int>() = principalId;
      insertData[ROLE_COL].data<std::string>() = role;
      insertData[SCHEMA_COL].data<std::string>() = connectionString;
      insertData[AUTH_KEY_COL].data<std::string>() = encryptedConnectionKey;
      insertData[C_ID_COL].data<int>() = connectionId;
      editor.insertRow(insertData);
    }
    return true;
  }

  std::pair<int, std::string> updateConnectionData(coral::ISchema& schema,
                                                   const std::string& adminKey,
                                                   const std::string& connectionLabel,
                                                   const std::string& userName,
                                                   const std::string& password,
                                                   bool forceUpdate) {
    CredentialData credsData;
    bool found = selectConnection(schema, connectionLabel, credsData);
    int connId = credsData.id;

    auth::Cipher adminCipher(adminKey);
    std::string connectionKey("");
    coral::ITableDataEditor& editor = schema.tableHandle(COND_CREDENTIAL_TABLE).dataEditor();
    if (found) {
      connectionKey = adminCipher.b64decrypt(credsData.connectionKey);
      auth::Cipher cipher(connectionKey);
      std::string verificationKey = cipher.b64decrypt(credsData.verificationKey);
      if (verificationKey != connectionLabel) {
        throwException("Decoding of connection key failed.", "CredentialStore::updateConnection");
      }
      if (forceUpdate) {
        std::string encryptedUserName = cipher.b64encrypt(userName);
        std::string encryptedPassword = cipher.b64encrypt(password);

        coral::AttributeList updateData;
        updateData.extend<int>(CONNECTION_ID_COL);
        updateData.extend<std::string>(USERNAME_COL);
        updateData.extend<std::string>(PASSWORD_COL);
        updateData[CONNECTION_ID_COL].data<int>() = connId;
        updateData[USERNAME_COL].data<std::string>() = encryptedUserName;
        updateData[PASSWORD_COL].data<std::string>() = encryptedPassword;
        std::stringstream setCl;
        setCl << USERNAME_COL << " = :" << USERNAME_COL;
        setCl << ", " << PASSWORD_COL << " = :" << PASSWORD_COL;
        std::string whereCl = CONNECTION_ID_COL + " = :" + CONNECTION_ID_COL;
        editor.updateRows(setCl.str(), whereCl, updateData);
      }
    }

    if (!found) {
      auth::KeyGenerator gen;
      connectionKey = gen.make(auth::COND_DB_KEY_SIZE);
      auth::Cipher cipher(connectionKey);
      std::string encryptedUserName = cipher.b64encrypt(userName);
      std::string encryptedPassword = cipher.b64encrypt(password);
      std::string encryptedLabel = cipher.b64encrypt(connectionLabel);

      if (!getNextSequenceValue(schema, COND_CREDENTIAL_TABLE, connId))
        throwException("Can't find " + COND_CREDENTIAL_TABLE + " sequence.", "CredentialStore::updateConnection");

      coral::AttributeList insertData;
      insertData.extend<int>(CONNECTION_ID_COL);
      insertData.extend<std::string>(CONNECTION_LABEL_COL);
      insertData.extend<std::string>(USERNAME_COL);
      insertData.extend<std::string>(PASSWORD_COL);
      insertData.extend<std::string>(VERIFICATION_KEY_COL);
      insertData.extend<std::string>(CONNECTION_KEY_COL);
      insertData[CONNECTION_ID_COL].data<int>() = connId;
      insertData[CONNECTION_LABEL_COL].data<std::string>() = connectionLabel;
      insertData[USERNAME_COL].data<std::string>() = encryptedUserName;
      insertData[PASSWORD_COL].data<std::string>() = encryptedPassword;
      insertData[VERIFICATION_KEY_COL].data<std::string>() = encryptedLabel;
      insertData[CONNECTION_KEY_COL].data<std::string>() = adminCipher.b64encrypt(connectionKey);
      ;
      editor.insertRow(insertData);
    }
    return std::make_pair(connId, connectionKey);
  }

}  // namespace cond

// class private methods
void cond::CredentialStore::closeSession(bool commit) {
  if (m_session.get()) {
    if (m_session->transaction().isActive()) {
      if (commit) {
        m_session->transaction().commit();
      } else {
        m_session->transaction().rollback();
      }
    }
    m_session->endUserSession();
  }
  m_session.reset();
  if (m_connection.get()) {
    m_connection->disconnect();
  }
  m_connection.reset();
}

std::pair<std::string, std::string> cond::CredentialStore::openConnection(const std::string& connectionString) {
  coral::IHandle<coral::IRelationalService> relationalService =
      coral::Context::instance().query<coral::IRelationalService>();
  if (!relationalService.isValid()) {
    coral::Context::instance().loadComponent("CORAL/Services/RelationalService");
    relationalService = coral::Context::instance().query<coral::IRelationalService>();
  }
  coral::IRelationalDomain& domain = relationalService->domainForConnection(connectionString);
  std::pair<std::string, std::string> connTokens = domain.decodeUserConnectionString(connectionString);
  m_connection.reset(domain.newConnection(connTokens.first));
  m_connection->connect();
  return connTokens;
}

void cond::CredentialStore::openSession(const std::string& schemaName,
                                        const std::string& userName,
                                        const std::string& password,
                                        bool readMode) {
  coral::AccessMode accessMode = coral::ReadOnly;
  if (!readMode)
    accessMode = coral::Update;
  m_session.reset(m_connection->newSession(schemaName, accessMode));
  m_session->startUserSession(userName, password);
  // open read-only transaction
  m_session->transaction().start(readMode);
}

void cond::CredentialStore::startSuperSession(const std::string& connectionString,
                                              const std::string& userName,
                                              const std::string& password) {
  std::pair<std::string, std::string> connTokens = openConnection(connectionString);
  openSession(connTokens.second, userName, password, false);
}

// open session on the storage
void cond::CredentialStore::startSession(bool readMode) {
  if (!m_serviceData) {
    throwException("The credential store has not been initialized.", "cond::CredentialStore::openConnection");
  }
  const std::string& storeConnectionString = m_serviceData->connectionString;

  std::pair<std::string, std::string> connTokens = openConnection(storeConnectionString);

  const std::string& userName = m_serviceData->userName;
  const std::string& password = m_serviceData->password;

  openSession(connTokens.second, userName, password, true);

  coral::ISchema& schema = m_session->nominalSchema();
  if (!schema.existsTable(COND_AUTHENTICATION_TABLE) || !schema.existsTable(COND_AUTHORIZATION_TABLE) ||
      !schema.existsTable(COND_CREDENTIAL_TABLE)) {
    throwException("Credential database does not exists in \"" + storeConnectionString + "\"",
                   "CredentialStore::startSession");
  }

  const std::string& principalName = m_key.principalName();
  // now authenticate...
  PrincipalData princData;
  if (!selectPrincipal(m_session->nominalSchema(), principalName, princData)) {
    throwException("Invalid credentials provided.(0)", "CredentialStore::startSession");
  }
  auth::Cipher cipher0(m_key.principalKey());
  std::string verifStr = cipher0.b64decrypt(princData.verifKey);
  if (verifStr != principalName) {
    throwException("Invalid credentials provided (1)", "CredentialStore::startSession");
  }
  // ok, authenticated!
  m_principalId = princData.id;
  m_principalKey = cipher0.b64decrypt(princData.principalKey);

  if (!readMode) {
    auth::Cipher cipher0(m_principalKey);
    std::string adminKey = cipher0.b64decrypt(princData.adminKey);
    if (adminKey != m_principalKey) {
      // not admin user!
      throwException("Provided credentials does not allow admin operation.", "CredentialStore::openSession");
    }

    // first find the credentials for WRITING in the security tables
    std::unique_ptr<coral::IQuery> query(schema.newQuery());
    query->addToTableList(COND_AUTHORIZATION_TABLE, "AUTHO");
    query->addToTableList(COND_CREDENTIAL_TABLE, "CREDS");
    coral::AttributeList readBuff;
    readBuff.extend<std::string>("CREDS." + CONNECTION_LABEL_COL);
    readBuff.extend<std::string>("CREDS." + CONNECTION_KEY_COL);
    readBuff.extend<std::string>("CREDS." + USERNAME_COL);
    readBuff.extend<std::string>("CREDS." + PASSWORD_COL);
    readBuff.extend<std::string>("CREDS." + VERIFICATION_KEY_COL);
    coral::AttributeList whereData;
    whereData.extend<int>(P_ID_COL);
    whereData.extend<std::string>(ROLE_COL);
    whereData.extend<std::string>(SCHEMA_COL);
    whereData[P_ID_COL].data<int>() = m_principalId;
    whereData[ROLE_COL].data<std::string>() = auth::COND_ADMIN_ROLE;
    whereData[SCHEMA_COL].data<std::string>() = storeConnectionString;
    std::stringstream whereClause;
    whereClause << "AUTHO." << C_ID_COL << " = CREDS." << CONNECTION_ID_COL;
    whereClause << " AND AUTHO." << P_ID_COL << " = :" << P_ID_COL;
    whereClause << " AND AUTHO." << ROLE_COL << " = :" << ROLE_COL;
    whereClause << " AND AUTHO." << SCHEMA_COL << " = :" << SCHEMA_COL;
    query->defineOutput(readBuff);
    query->addToOutputList("CREDS." + CONNECTION_LABEL_COL);
    query->addToOutputList("CREDS." + CONNECTION_KEY_COL);
    query->addToOutputList("CREDS." + USERNAME_COL);
    query->addToOutputList("CREDS." + PASSWORD_COL);
    query->addToOutputList("CREDS." + VERIFICATION_KEY_COL);
    query->setCondition(whereClause.str(), whereData);
    coral::ICursor& cursor = query->execute();
    bool found = false;
    std::string writeUserName("");
    std::string writePassword("");
    if (cursor.next()) {
      const coral::AttributeList& row = cursor.currentRow();
      const std::string& connLabel = row["CREDS." + CONNECTION_LABEL_COL].data<std::string>();
      const std::string& encryptedConnectionKey = row["CREDS." + CONNECTION_KEY_COL].data<std::string>();
      std::string connectionKey = cipher0.b64decrypt(encryptedConnectionKey);
      auth::Cipher cipher1(connectionKey);
      const std::string& encryptedUserName = row["CREDS." + USERNAME_COL].data<std::string>();
      const std::string& encryptedPassword = row["CREDS." + PASSWORD_COL].data<std::string>();
      const std::string& verificationKey = row["CREDS." + VERIFICATION_KEY_COL].data<std::string>();
      if (cipher1.b64decrypt(verificationKey) != connLabel) {
        throwException("Could not decrypt credentials.Provided key is invalid.", "CredentialStore::startSession");
      }
      writeUserName = cipher1.b64decrypt(encryptedUserName);
      writePassword = cipher1.b64decrypt(encryptedPassword);
      found = true;
    }
    if (!found) {
      throwException("Provided credentials are invalid for write access.", "CredentialStore::openSession");
    }
    m_session->transaction().commit();
    m_session->endUserSession();
    openSession(connTokens.second, writeUserName, writePassword, false);
  }
}

// class public methods
cond::CredentialStore::CredentialStore()
    : m_connection(),
      m_session(),
      m_principalId(-1),
      m_principalKey(""),
      m_serviceName(""),
      m_serviceData(nullptr),
      m_key() {}

cond::CredentialStore::~CredentialStore() {}

std::string cond::CredentialStore::setUpForService(const std::string& serviceName, const std::string& authPath) {
  if (serviceName.empty()) {
    throwException("Service name has not been provided.", "cond::CredentialStore::setUpConnection");
  }
  m_serviceName.clear();
  m_serviceData = nullptr;

  if (authPath.empty()) {
    throwException("The authentication Path has not been provided.", "cond::CredentialStore::setUpForService");
  }
  boost::filesystem::path fullPath(authPath);
  if (!boost::filesystem::exists(authPath) || !boost::filesystem::is_directory(authPath)) {
    throwException("Authentication Path is invalid.", "cond::CredentialStore::setUpForService");
  }
  boost::filesystem::path file(auth::DecodingKey::FILE_PATH);
  fullPath /= file;

  m_key.init(fullPath.string(), auth::COND_KEY);

  std::map<std::string, auth::ServiceCredentials>::const_iterator iK = m_key.services().find(serviceName);
  if (iK == m_key.services().end()) {
    std::string msg("");
    msg += "Service \"" + serviceName + "\" can't be open with the current key.";
    throwException(msg, "cond::CredentialStore::setUpConnection");
  }
  m_serviceName = serviceName;
  m_serviceData = &iK->second;
  return m_serviceData->connectionString;
}

std::string cond::CredentialStore::setUpForConnectionString(const std::string& connectionString,
                                                            const std::string& authPath) {
  coral::IHandle<coral::IRelationalService> relationalService =
      coral::Context::instance().query<coral::IRelationalService>();
  if (!relationalService.isValid()) {
    coral::Context::instance().loadComponent("CORAL/Services/RelationalService");
    relationalService = coral::Context::instance().query<coral::IRelationalService>();
  }
  coral::IRelationalDomain& domain = relationalService->domainForConnection(connectionString);
  std::pair<std::string, std::string> connTokens = domain.decodeUserConnectionString(connectionString);
  std::string& serviceName = connTokens.first;
  return setUpForService(serviceName, authPath);
}

void addSequence(coral::ISchema& schema, const std::string& name) {
  // Create the entry in the table
  coral::AttributeList insertData;
  insertData.extend<std::string>(SEQUENCE_NAME_COL);
  insertData.extend<int>(SEQUENCE_VALUE_COL);
  coral::AttributeList::iterator iAttribute = insertData.begin();
  iAttribute->data<std::string>() = name;
  ++iAttribute;
  iAttribute->data<int>() = -1;
  schema.tableHandle(SEQUENCE_TABLE_NAME).dataEditor().insertRow(insertData);
}

bool cond::CredentialStore::createSchema(const std::string& connectionString,
                                         const std::string& userName,
                                         const std::string& password) {
  CSScopedSession session(*this);
  session.startSuper(connectionString, userName, password);

  coral::ISchema& schema = m_session->nominalSchema();
  if (schema.existsTable(COND_AUTHENTICATION_TABLE)) {
    throwException("Credential database, already exists.", "CredentialStore::create");
  }

  coral::TableDescription dseq;
  dseq.setName(SEQUENCE_TABLE_NAME);

  dseq.insertColumn(SEQUENCE_NAME_COL, coral::AttributeSpecification::typeNameForType<std::string>());
  dseq.setNotNullConstraint(SEQUENCE_NAME_COL);
  dseq.insertColumn(SEQUENCE_VALUE_COL, coral::AttributeSpecification::typeNameForType<int>());
  dseq.setNotNullConstraint(SEQUENCE_VALUE_COL);
  dseq.setPrimaryKey(std::vector<std::string>(1, SEQUENCE_NAME_COL));
  schema.createTable(dseq);

  int columnSize = 2000;

  // authentication table

  addSequence(schema, COND_AUTHENTICATION_TABLE);
  coral::TableDescription descr0;
  descr0.setName(COND_AUTHENTICATION_TABLE);
  descr0.insertColumn(PRINCIPAL_ID_COL, coral::AttributeSpecification::typeNameForType<int>());
  descr0.insertColumn(
      PRINCIPAL_NAME_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr0.insertColumn(
      VERIFICATION_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr0.insertColumn(
      PRINCIPAL_KEY_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr0.insertColumn(ADMIN_KEY_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr0.setNotNullConstraint(PRINCIPAL_ID_COL);
  descr0.setNotNullConstraint(PRINCIPAL_NAME_COL);
  descr0.setNotNullConstraint(VERIFICATION_COL);
  descr0.setNotNullConstraint(PRINCIPAL_KEY_COL);
  descr0.setNotNullConstraint(ADMIN_KEY_COL);
  std::vector<std::string> columnsUnique;
  columnsUnique.push_back(PRINCIPAL_NAME_COL);
  descr0.setUniqueConstraint(columnsUnique);
  std::vector<std::string> columnsForIndex;
  columnsForIndex.push_back(PRINCIPAL_ID_COL);
  descr0.setPrimaryKey(columnsForIndex);
  schema.createTable(descr0);

  // authorization table
  addSequence(schema, COND_AUTHORIZATION_TABLE);
  coral::TableDescription descr1;
  descr1.setName(COND_AUTHORIZATION_TABLE);
  descr1.insertColumn(AUTH_ID_COL, coral::AttributeSpecification::typeNameForType<int>());
  descr1.insertColumn(P_ID_COL, coral::AttributeSpecification::typeNameForType<int>());
  descr1.insertColumn(ROLE_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr1.insertColumn(SCHEMA_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr1.insertColumn(AUTH_KEY_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr1.insertColumn(C_ID_COL, coral::AttributeSpecification::typeNameForType<int>());
  descr1.setNotNullConstraint(AUTH_ID_COL);
  descr1.setNotNullConstraint(P_ID_COL);
  descr1.setNotNullConstraint(ROLE_COL);
  descr1.setNotNullConstraint(SCHEMA_COL);
  descr1.setNotNullConstraint(AUTH_KEY_COL);
  descr1.setNotNullConstraint(C_ID_COL);
  columnsUnique.clear();
  columnsUnique.push_back(P_ID_COL);
  columnsUnique.push_back(ROLE_COL);
  columnsUnique.push_back(SCHEMA_COL);
  descr1.setUniqueConstraint(columnsUnique);
  columnsForIndex.clear();
  columnsForIndex.push_back(AUTH_ID_COL);
  descr1.setPrimaryKey(columnsForIndex);
  schema.createTable(descr1);

  // credential table
  addSequence(schema, COND_CREDENTIAL_TABLE);
  coral::TableDescription descr2;
  descr2.setName(COND_CREDENTIAL_TABLE);
  descr2.insertColumn(CONNECTION_ID_COL, coral::AttributeSpecification::typeNameForType<int>());
  descr2.insertColumn(
      CONNECTION_LABEL_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr2.insertColumn(USERNAME_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr2.insertColumn(PASSWORD_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr2.insertColumn(
      VERIFICATION_KEY_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr2.insertColumn(
      CONNECTION_KEY_COL, coral::AttributeSpecification::typeNameForType<std::string>(), columnSize, false);
  descr2.setNotNullConstraint(CONNECTION_ID_COL);
  descr2.setNotNullConstraint(CONNECTION_LABEL_COL);
  descr2.setNotNullConstraint(USERNAME_COL);
  descr2.setNotNullConstraint(PASSWORD_COL);
  descr2.setNotNullConstraint(VERIFICATION_KEY_COL);
  descr2.setNotNullConstraint(CONNECTION_KEY_COL);
  columnsUnique.clear();
  columnsUnique.push_back(CONNECTION_LABEL_COL);
  descr2.setUniqueConstraint(columnsUnique);
  columnsForIndex.clear();
  columnsForIndex.push_back(CONNECTION_ID_COL);
  descr2.setPrimaryKey(columnsForIndex);
  schema.createTable(descr2);

  try {
    schema.tableHandle(COND_AUTHENTICATION_TABLE)
        .privilegeManager()
        .grantToUser(m_serviceData->userName, coral::ITablePrivilegeManager::Select);
    schema.tableHandle(COND_AUTHORIZATION_TABLE)
        .privilegeManager()
        .grantToUser(m_serviceData->userName, coral::ITablePrivilegeManager::Select);
    schema.tableHandle(COND_CREDENTIAL_TABLE)
        .privilegeManager()
        .grantToUser(m_serviceData->userName, coral::ITablePrivilegeManager::Select);
  } catch (const coral::Exception& e) {
    std::cout << "WARNING: Could not grant select access to user " << m_serviceData->userName << ": [" << e.what()
              << "]" << std::endl;
  }
  auth::KeyGenerator gen;
  m_principalKey = gen.make(auth::COND_DB_KEY_SIZE);
  auto princData = updatePrincipalData(schema, m_key.principalKey(), m_key.principalName(), m_principalKey, true);
  std::string credentialAccessLabel = schemaLabel(m_serviceName, userName);
  auto connParams = updateConnectionData(schema, m_principalKey, credentialAccessLabel, userName, password, true);
  bool ret = setPermissionData(schema,
                               princData.first,
                               m_principalKey,
                               auth::COND_ADMIN_ROLE,
                               connectionString,
                               connParams.first,
                               connParams.second);
  session.close();
  return ret;
}

bool cond::CredentialStore::drop(const std::string& connectionString,
                                 const std::string& userName,
                                 const std::string& password) {
  CSScopedSession session(*this);
  session.startSuper(connectionString, userName, password);

  coral::ISchema& schema = m_session->nominalSchema();
  schema.dropIfExistsTable(COND_AUTHORIZATION_TABLE);
  schema.dropIfExistsTable(COND_CREDENTIAL_TABLE);
  schema.dropIfExistsTable(COND_AUTHENTICATION_TABLE);
  schema.dropIfExistsTable(SEQUENCE_TABLE_NAME);
  session.close();
  return true;
}

bool cond::CredentialStore::resetAdmin(const std::string& userName, const std::string& password) {
  if (!m_serviceData) {
    throwException("The credential store has not been initialized.", "cond::CredentialStore::installAdmin");
  }
  const std::string& connectionString = m_serviceData->connectionString;

  CSScopedSession session(*this);
  session.startSuper(connectionString, userName, password);

  coral::ISchema& schema = m_session->nominalSchema();
  const std::string& principalName = m_key.principalName();
  const std::string& authenticationKey = m_key.principalKey();
  PrincipalData princData;
  if (!selectPrincipal(schema, principalName, princData)) {
    std::string msg("User \"");
    msg += principalName + "\" has not been found.";
    throwException(msg, "CredentialStore::resetAdmin");
  }
  auth::Cipher cipher0(authenticationKey);
  m_principalKey = cipher0.b64decrypt(princData.principalKey);

  auto p = updatePrincipalData(schema, authenticationKey, principalName, m_principalKey);
  std::string credentialAccessLabel = schemaLabel(m_serviceName, userName);
  auto connParams = updateConnectionData(schema, m_principalKey, credentialAccessLabel, userName, password, true);
  bool ret = setPermissionData(
      schema, p.first, m_principalKey, auth::COND_ADMIN_ROLE, connectionString, connParams.first, connParams.second);
  session.close();
  return ret;
}

bool cond::CredentialStore::updatePrincipal(const std::string& principalName,
                                            const std::string& authenticationKey,
                                            bool setAdmin) {
  CSScopedSession session(*this);
  session.start(false);
  coral::ISchema& schema = m_session->nominalSchema();
  auto princData = updatePrincipalData(schema, m_principalKey, principalName, authenticationKey);
  bool ret = false;
  if (setAdmin) {
    int princId = princData.first;
    std::string princKey = m_principalKey;
    std::string connString = m_serviceData->connectionString;
    std::vector<Permission> permissions;
    if (!selectPermissions(m_key.principalName(), auth::COND_ADMIN_ROLE, connString, permissions)) {
      throwException("The current operating user is not admin user on the underlying Credential Store.",
                     "CredentialStore::updatePrincipal");
    }
    std::string connLabel = permissions.front().connectionLabel;
    CredentialData credsData;
    if (!selectConnection(schema, connLabel, credsData)) {
      throwException("Credential Store connection has not been defined.", "CredentialStore::updatePrincipal");
    }
    auth::Cipher adminCipher(m_principalKey);
    ret = setPermissionData(schema,
                            princId,
                            princKey,
                            auth::COND_ADMIN_ROLE,
                            connString,
                            credsData.id,
                            adminCipher.b64decrypt(credsData.connectionKey));
  }
  session.close();
  return ret;
}

bool cond::CredentialStore::setPermission(const std::string& principal,
                                          const std::string& role,
                                          const std::string& connectionString,
                                          const std::string& connectionLabel) {
  CSScopedSession session(*this);
  session.start(false);

  coral::ISchema& schema = m_session->nominalSchema();

  PrincipalData princData;
  bool found = selectPrincipal(schema, principal, princData);

  if (!found) {
    std::string msg = "Principal \"" + principal + "\" does not exist in the database.";
    throwException(msg, "CredentialStore::setPermission");
  }

  CredentialData credsData;
  found = selectConnection(schema, connectionLabel, credsData);

  if (!found) {
    std::string msg = "Connection named \"" + connectionLabel + "\" does not exist in the database.";
    throwException(msg, "CredentialStore::setPermission");
  }

  auth::Cipher cipher(m_principalKey);
  bool ret = setPermissionData(schema,
                               princData.id,
                               cipher.b64decrypt(princData.adminKey),
                               role,
                               connectionString,
                               credsData.id,
                               cipher.b64decrypt(credsData.connectionKey));
  session.close();
  return ret;
}

bool cond::CredentialStore::unsetPermission(const std::string& principal,
                                            const std::string& role,
                                            const std::string& connectionString) {
  CSScopedSession session(*this);
  session.start(false);
  coral::ISchema& schema = m_session->nominalSchema();

  PrincipalData princData;
  bool found = selectPrincipal(schema, principal, princData);

  if (!found) {
    std::string msg = "Principal \"" + principal + "\" does not exist in the database.";
    throwException(msg, "CredentialStore::unsetPermission");
  }

  coral::ITableDataEditor& editor = schema.tableHandle(COND_AUTHORIZATION_TABLE).dataEditor();
  coral::AttributeList deleteData;
  deleteData.extend<int>(P_ID_COL);
  deleteData.extend<std::string>(ROLE_COL);
  deleteData.extend<std::string>(SCHEMA_COL);
  deleteData[P_ID_COL].data<int>() = princData.id;
  deleteData[ROLE_COL].data<std::string>() = role;
  deleteData[SCHEMA_COL].data<std::string>() = connectionString;
  std::stringstream whereClause;
  whereClause << P_ID_COL + " = :" + P_ID_COL;
  whereClause << " AND " << ROLE_COL << " = :" << ROLE_COL;
  whereClause << " AND " << SCHEMA_COL << " = :" << SCHEMA_COL;
  editor.deleteRows(whereClause.str(), deleteData);
  session.close();
  return true;
}

bool cond::CredentialStore::updateConnection(const std::string& connectionLabel,
                                             const std::string& userName,
                                             const std::string& password) {
  CSScopedSession session(*this);
  session.start(false);

  m_session->transaction().start();
  coral::ISchema& schema = m_session->nominalSchema();
  updateConnectionData(schema, m_principalKey, connectionLabel, userName, password, true);

  session.close();
  return true;
}

bool cond::CredentialStore::removePrincipal(const std::string& principal) {
  CSScopedSession session(*this);
  session.start(false);
  coral::ISchema& schema = m_session->nominalSchema();

  PrincipalData princData;
  bool found = selectPrincipal(schema, principal, princData);

  if (!found) {
    std::string msg = "Principal \"" + principal + "\" does not exist in the database.";
    throwException(msg, "CredentialStore::removePrincipal");
  }

  coral::ITableDataEditor& editor0 = schema.tableHandle(COND_AUTHORIZATION_TABLE).dataEditor();

  coral::AttributeList deleteData0;
  deleteData0.extend<int>(P_ID_COL);
  deleteData0[P_ID_COL].data<int>() = princData.id;
  std::string whereClause0 = P_ID_COL + " = :" + P_ID_COL;
  editor0.deleteRows(whereClause0, deleteData0);

  coral::ITableDataEditor& editor1 = schema.tableHandle(COND_AUTHENTICATION_TABLE).dataEditor();

  coral::AttributeList deleteData1;
  deleteData1.extend<int>(PRINCIPAL_ID_COL);
  deleteData1[PRINCIPAL_ID_COL].data<int>() = princData.id;
  std::string whereClause1 = PRINCIPAL_ID_COL + " = :" + PRINCIPAL_ID_COL;
  editor1.deleteRows(whereClause1, deleteData1);

  session.close();

  return true;
}

bool cond::CredentialStore::removeConnection(const std::string& connectionLabel) {
  CSScopedSession session(*this);
  session.start(false);
  coral::ISchema& schema = m_session->nominalSchema();

  CredentialData credsData;
  bool found = selectConnection(schema, connectionLabel, credsData);

  if (!found) {
    std::string msg = "Connection named \"" + connectionLabel + "\" does not exist in the database.";
    throwException(msg, "CredentialStore::removeConnection");
  }

  coral::ITableDataEditor& editor0 = schema.tableHandle(COND_AUTHORIZATION_TABLE).dataEditor();

  coral::AttributeList deleteData0;
  deleteData0.extend<int>(C_ID_COL);
  deleteData0[C_ID_COL].data<int>() = credsData.id;
  std::string whereClause0 = C_ID_COL + " = :" + C_ID_COL;
  editor0.deleteRows(whereClause0, deleteData0);

  coral::ITableDataEditor& editor1 = schema.tableHandle(COND_CREDENTIAL_TABLE).dataEditor();

  coral::AttributeList deleteData1;
  deleteData1.extend<int>(CONNECTION_ID_COL);
  deleteData1[CONNECTION_ID_COL].data<int>() = credsData.id;
  std::string whereClause1 = CONNECTION_ID_COL + " = :" + CONNECTION_ID_COL;
  editor1.deleteRows(whereClause1, deleteData1);

  session.close();

  return true;
}

bool cond::CredentialStore::selectForUser(coral_bridge::AuthenticationCredentialSet& destinationData) {
  CSScopedSession session(*this);
  session.start(true);
  coral::ISchema& schema = m_session->nominalSchema();

  auth::Cipher cipher(m_principalKey);

  std::unique_ptr<coral::IQuery> query(schema.newQuery());
  query->addToTableList(COND_AUTHORIZATION_TABLE, "AUTHO");
  query->addToTableList(COND_CREDENTIAL_TABLE, "CREDS");
  coral::AttributeList readBuff;
  readBuff.extend<std::string>("AUTHO." + ROLE_COL);
  readBuff.extend<std::string>("AUTHO." + SCHEMA_COL);
  readBuff.extend<std::string>("AUTHO." + AUTH_KEY_COL);
  readBuff.extend<std::string>("CREDS." + CONNECTION_LABEL_COL);
  readBuff.extend<std::string>("CREDS." + USERNAME_COL);
  readBuff.extend<std::string>("CREDS." + PASSWORD_COL);
  readBuff.extend<std::string>("CREDS." + VERIFICATION_KEY_COL);
  coral::AttributeList whereData;
  whereData.extend<int>(P_ID_COL);
  whereData[P_ID_COL].data<int>() = m_principalId;
  std::stringstream whereClause;
  whereClause << "AUTHO." << C_ID_COL << "="
              << "CREDS." << CONNECTION_ID_COL;
  whereClause << " AND "
              << "AUTHO." << P_ID_COL << " = :" << P_ID_COL;
  query->defineOutput(readBuff);
  query->addToOutputList("AUTHO." + ROLE_COL);
  query->addToOutputList("AUTHO." + SCHEMA_COL);
  query->addToOutputList("AUTHO." + AUTH_KEY_COL);
  query->addToOutputList("CREDS." + CONNECTION_LABEL_COL);
  query->addToOutputList("CREDS." + USERNAME_COL);
  query->addToOutputList("CREDS." + PASSWORD_COL);
  query->addToOutputList("CREDS." + VERIFICATION_KEY_COL);
  query->setCondition(whereClause.str(), whereData);
  coral::ICursor& cursor = query->execute();
  while (cursor.next()) {
    const coral::AttributeList& row = cursor.currentRow();
    const std::string& role = row["AUTHO." + ROLE_COL].data<std::string>();
    const std::string& connectionString = row["AUTHO." + SCHEMA_COL].data<std::string>();
    const std::string& encryptedAuthKey = row["AUTHO." + AUTH_KEY_COL].data<std::string>();
    const std::string& connectionLabel = row["CREDS." + CONNECTION_LABEL_COL].data<std::string>();
    const std::string& encryptedUserName = row["CREDS." + USERNAME_COL].data<std::string>();
    const std::string& encryptedPassword = row["CREDS." + PASSWORD_COL].data<std::string>();
    const std::string& encryptedLabel = row["CREDS." + VERIFICATION_KEY_COL].data<std::string>();
    std::string authKey = cipher.b64decrypt(encryptedAuthKey);
    auth::Cipher connCipher(authKey);
    if (connCipher.b64decrypt(encryptedLabel) == connectionLabel) {
      destinationData.registerCredentials(
          connectionString, role, connCipher.b64decrypt(encryptedUserName), connCipher.b64decrypt(encryptedPassword));
    }
  }
  session.close();
  return true;
}

bool cond::CredentialStore::importForPrincipal(const std::string& principal,
                                               const coral_bridge::AuthenticationCredentialSet& dataSource,
                                               bool forceUpdateConnection) {
  CSScopedSession session(*this);
  session.start(false);
  coral::ISchema& schema = m_session->nominalSchema();

  PrincipalData princData;
  bool found = selectPrincipal(schema, principal, princData);

  if (!found) {
    std::string msg = "Principal \"" + principal + "\" does not exist in the database.";
    throwException(msg, "CredentialStore::importForPrincipal");
  }

  bool imported = false;
  auth::Cipher cipher(m_principalKey);
  std::string princKey = cipher.b64decrypt(princData.adminKey);

  const std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>& creds = dataSource.data();
  for (std::map<std::pair<std::string, std::string>, coral::AuthenticationCredentials*>::const_iterator iConn =
           creds.begin();
       iConn != creds.end();
       ++iConn) {
    const std::string& connectionString = iConn->first.first;
    coral::URIParser parser;
    parser.setURI(connectionString);
    std::string serviceName = parser.hostName();
    const std::string& role = iConn->first.second;
    std::string userName = iConn->second->valueForItem(coral::IAuthenticationCredentials::userItem());
    std::string password = iConn->second->valueForItem(coral::IAuthenticationCredentials::passwordItem());
    // first import the connections
    std::pair<int, std::string> conn = updateConnectionData(
        schema, m_principalKey, schemaLabel(serviceName, userName), userName, password, forceUpdateConnection);
    auth::Cipher cipher(m_principalKey);
    // than set the permission for the specific role
    setPermissionData(schema, princData.id, princKey, role, connectionString, conn.first, conn.second);
    imported = true;
  }
  session.close();
  return imported;
}

bool cond::CredentialStore::listPrincipals(std::vector<std::string>& destination) {
  CSScopedSession session(*this);
  session.start(true);
  coral::ISchema& schema = m_session->nominalSchema();

  std::unique_ptr<coral::IQuery> query(schema.tableHandle(COND_AUTHENTICATION_TABLE).newQuery());
  coral::AttributeList readBuff;
  readBuff.extend<std::string>(PRINCIPAL_NAME_COL);
  query->defineOutput(readBuff);
  query->addToOutputList(PRINCIPAL_NAME_COL);
  coral::ICursor& cursor = query->execute();
  bool found = false;
  while (cursor.next()) {
    found = true;
    const coral::AttributeList& row = cursor.currentRow();
    destination.push_back(row[PRINCIPAL_NAME_COL].data<std::string>());
  }
  session.close();
  return found;
}

bool cond::CredentialStore::listConnections(std::map<std::string, std::pair<std::string, std::string> >& destination) {
  CSScopedSession session(*this);
  session.start(true);
  coral::ISchema& schema = m_session->nominalSchema();

  std::unique_ptr<coral::IQuery> query(schema.tableHandle(COND_CREDENTIAL_TABLE).newQuery());
  coral::AttributeList readBuff;
  readBuff.extend<std::string>(CONNECTION_LABEL_COL);
  readBuff.extend<std::string>(USERNAME_COL);
  readBuff.extend<std::string>(PASSWORD_COL);
  readBuff.extend<std::string>(VERIFICATION_KEY_COL);
  readBuff.extend<std::string>(CONNECTION_KEY_COL);
  query->defineOutput(readBuff);
  query->addToOutputList(CONNECTION_LABEL_COL);
  query->addToOutputList(USERNAME_COL);
  query->addToOutputList(PASSWORD_COL);
  query->addToOutputList(VERIFICATION_KEY_COL);
  query->addToOutputList(CONNECTION_KEY_COL);
  coral::ICursor& cursor = query->execute();
  bool found = false;
  auth::Cipher cipher0(m_principalKey);
  while (cursor.next()) {
    std::string userName("");
    std::string password("");
    const coral::AttributeList& row = cursor.currentRow();
    const std::string& connLabel = row[CONNECTION_LABEL_COL].data<std::string>();
    const std::string& encryptedKey = row[CONNECTION_KEY_COL].data<std::string>();
    const std::string& encryptedVerif = row[VERIFICATION_KEY_COL].data<std::string>();
    std::string connKey = cipher0.b64decrypt(encryptedKey);
    auth::Cipher cipher1(connKey);
    std::string verif = cipher1.b64decrypt(encryptedVerif);
    if (verif == connLabel) {
      const std::string& encryptedUserName = row[USERNAME_COL].data<std::string>();
      const std::string& encryptedPassword = row[PASSWORD_COL].data<std::string>();
      userName = cipher1.b64decrypt(encryptedUserName);
      password = cipher1.b64decrypt(encryptedPassword);
    }
    destination.insert(std::make_pair(connLabel, std::make_pair(userName, password)));
    found = true;
  }
  session.close();
  return found;
}

bool cond::CredentialStore::selectPermissions(const std::string& principalName,
                                              const std::string& role,
                                              const std::string& connectionString,
                                              std::vector<Permission>& destination) {
  CSScopedSession session(*this);
  session.start(true);
  coral::ISchema& schema = m_session->nominalSchema();
  std::unique_ptr<coral::IQuery> query(schema.newQuery());
  query->addToTableList(COND_AUTHENTICATION_TABLE, "AUTHE");
  query->addToTableList(COND_AUTHORIZATION_TABLE, "AUTHO");
  query->addToTableList(COND_CREDENTIAL_TABLE, "CREDS");
  coral::AttributeList readBuff;
  readBuff.extend<std::string>("AUTHE." + PRINCIPAL_NAME_COL);
  readBuff.extend<std::string>("AUTHO." + ROLE_COL);
  readBuff.extend<std::string>("AUTHO." + SCHEMA_COL);
  readBuff.extend<std::string>("CREDS." + CONNECTION_LABEL_COL);
  coral::AttributeList whereData;
  std::stringstream whereClause;
  whereClause << "AUTHE." << PRINCIPAL_ID_COL << "= AUTHO." << P_ID_COL;
  whereClause << " AND AUTHO." << C_ID_COL << "="
              << "CREDS." << CONNECTION_ID_COL;
  if (!principalName.empty()) {
    whereData.extend<std::string>(PRINCIPAL_NAME_COL);
    whereData[PRINCIPAL_NAME_COL].data<std::string>() = principalName;
    whereClause << " AND AUTHE." << PRINCIPAL_NAME_COL << " = :" << PRINCIPAL_NAME_COL;
  }
  if (!role.empty()) {
    whereData.extend<std::string>(ROLE_COL);
    whereData[ROLE_COL].data<std::string>() = role;
    whereClause << " AND AUTHO." << ROLE_COL << " = :" << ROLE_COL;
  }
  if (!connectionString.empty()) {
    whereData.extend<std::string>(SCHEMA_COL);
    whereData[SCHEMA_COL].data<std::string>() = connectionString;
    whereClause << " AND AUTHO." << SCHEMA_COL << " = :" << SCHEMA_COL;
  }

  query->defineOutput(readBuff);
  query->addToOutputList("AUTHE." + PRINCIPAL_NAME_COL);
  query->addToOutputList("AUTHO." + ROLE_COL);
  query->addToOutputList("AUTHO." + SCHEMA_COL);
  query->addToOutputList("CREDS." + CONNECTION_LABEL_COL);
  query->setCondition(whereClause.str(), whereData);
  query->addToOrderList("AUTHO." + SCHEMA_COL);
  query->addToOrderList("AUTHE." + PRINCIPAL_NAME_COL);
  query->addToOrderList("AUTHO." + ROLE_COL);
  coral::ICursor& cursor = query->execute();
  bool found = false;
  while (cursor.next()) {
    const coral::AttributeList& row = cursor.currentRow();
    destination.resize(destination.size() + 1);
    Permission& perm = destination.back();
    perm.principalName = row["AUTHE." + PRINCIPAL_NAME_COL].data<std::string>();
    perm.role = row["AUTHO." + ROLE_COL].data<std::string>();
    perm.connectionString = row["AUTHO." + SCHEMA_COL].data<std::string>();
    perm.connectionLabel = row["CREDS." + CONNECTION_LABEL_COL].data<std::string>();
    found = true;
  }
  session.close();
  return found;
}

bool cond::CredentialStore::exportAll(coral_bridge::AuthenticationCredentialSet& data) {
  CSScopedSession session(*this);
  session.start(true);
  coral::ISchema& schema = m_session->nominalSchema();
  std::unique_ptr<coral::IQuery> query(schema.newQuery());
  query->addToTableList(COND_AUTHORIZATION_TABLE, "AUTHO");
  query->addToTableList(COND_CREDENTIAL_TABLE, "CREDS");
  coral::AttributeList readBuff;
  readBuff.extend<std::string>("AUTHO." + ROLE_COL);
  readBuff.extend<std::string>("AUTHO." + SCHEMA_COL);
  readBuff.extend<std::string>("CREDS." + CONNECTION_LABEL_COL);
  readBuff.extend<std::string>("CREDS." + VERIFICATION_KEY_COL);
  readBuff.extend<std::string>("CREDS." + CONNECTION_KEY_COL);
  readBuff.extend<std::string>("CREDS." + USERNAME_COL);
  readBuff.extend<std::string>("CREDS." + PASSWORD_COL);
  coral::AttributeList whereData;
  std::stringstream whereClause;
  whereClause << "AUTHO." << C_ID_COL << "="
              << "CREDS." << CONNECTION_ID_COL;

  query->defineOutput(readBuff);
  query->addToOutputList("AUTHO." + ROLE_COL);
  query->addToOutputList("AUTHO." + SCHEMA_COL);
  query->addToOutputList("CREDS." + CONNECTION_LABEL_COL);
  query->addToOutputList("CREDS." + VERIFICATION_KEY_COL);
  query->addToOutputList("CREDS." + CONNECTION_KEY_COL);
  query->addToOutputList("CREDS." + USERNAME_COL);
  query->addToOutputList("CREDS." + PASSWORD_COL);
  query->setCondition(whereClause.str(), whereData);
  coral::ICursor& cursor = query->execute();
  bool found = false;
  auth::Cipher cipher0(m_principalKey);
  while (cursor.next()) {
    const coral::AttributeList& row = cursor.currentRow();
    const std::string& role = row["AUTHO." + ROLE_COL].data<std::string>();
    const std::string& connectionString = row["AUTHO." + SCHEMA_COL].data<std::string>();
    const std::string& connectionLabel = row["CREDS." + CONNECTION_LABEL_COL].data<std::string>();
    const std::string& encryptedVerifKey = row["CREDS." + VERIFICATION_KEY_COL].data<std::string>();
    const std::string& encryptedConnection = row["CREDS." + CONNECTION_KEY_COL].data<std::string>();
    std::string userName("");
    std::string password("");
    std::string connectionKey = cipher0.b64decrypt(encryptedConnection);
    auth::Cipher cipher1(connectionKey);
    std::string verifKey = cipher1.b64decrypt(encryptedVerifKey);
    if (verifKey == connectionLabel) {
      const std::string& encryptedUserName = row["CREDS." + USERNAME_COL].data<std::string>();
      const std::string& encryptedPassword = row["CREDS." + PASSWORD_COL].data<std::string>();
      userName = cipher1.b64decrypt(encryptedUserName);
      password = cipher1.b64decrypt(encryptedPassword);
    }
    data.registerCredentials(connectionString, role, userName, password);
    found = true;
  }
  session.close();
  return found;
}

const std::string& cond::CredentialStore::keyPrincipalName() { return m_key.principalName(); }