/// <inheritdoc/>
public void CreateLinkBetween(DataAccessCredentials credentials, TableInfo tableInfo, DataAccessContext context)
{
using (var con = _repository.GetConnection())
{
var cmd = DatabaseCommandHelper.GetCommand("INSERT INTO DataAccessCredentials_TableInfo(DataAccessCredentials_ID,TableInfo_ID,Context) VALUES (@cid,@tid,@context)", con.Connection, con.Transaction);
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@cid", cmd));
cmd.Parameters["@cid"].Value = credentials.ID;
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@tid", cmd));
cmd.Parameters["@tid"].Value = tableInfo.ID;
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@context", cmd));
cmd.Parameters["@context"].Value = context;
cmd.ExecuteNonQuery();
}
}
public void DataAccessCredentialsEncryption()
{
//cleanup
foreach (var c in CatalogueRepository.GetAllObjects <DataAccessCredentials>().Where(c => c.Name.Equals("frankieFran")))
{
c.DeleteInDatabase();
}
DataAccessCredentials creds = new DataAccessCredentials(CatalogueRepository, "frankieFran");
try
{
//as soon as you set a password it should be encrypted by the credentials class in memory
creds.Password = "******";
Assert.AreNotEqual("fish", creds.Password);
Assert.AreEqual("fish", creds.GetDecryptedPassword());//but we should still be able to decrypt it
//save it
creds.SaveToDatabase();
using (var con = CatalogueRepository.GetConnection())
{
string value;
using (var cmd = DatabaseCommandHelper.GetCommand("Select Password from DataAccessCredentials where Name='frankieFran'", con.Connection, con.Transaction))
value = (string)cmd.ExecuteScalar();
//ensure password in database is encrypted
Assert.AreNotEqual("fish", value);
Assert.AreEqual(creds.Password, value);//does value in database match value in memory (encrypted)
}
//get a new copy out of the database
DataAccessCredentials newCopy = CatalogueRepository.GetObjectByID <DataAccessCredentials>(creds.ID);
Assert.AreEqual(creds.Password, newCopy.Password); //passwords should match
Assert.AreNotEqual("fish", creds.Password); //neither should be fish
Assert.AreNotEqual("fish", newCopy.Password);
//both should decrypt to the same value (fish
Assert.AreEqual("fish", creds.GetDecryptedPassword());
Assert.AreEqual("fish", newCopy.GetDecryptedPassword());
}
finally
{
creds.DeleteInDatabase();
}
}
public ActionResult Login(FormCollection form)
{
if (ModelState.IsValid)
{
string name = form["name"];
string password = form["pwd"];
string logintype = form["ltype"];
List <Admin> user = new List <Admin>();
DataAccessCredentials dac = new DataAccessCredentials();
AdminProfile ap = new AdminProfile();
user = ap.LoginCheck(name, name, password, logintype);
if (user != null)
{
return(this.RedirectToAction("Main", "Admin"));
}
}
return(View());
}
public void GetConnectionStringFromCatalogueWhereOneTableInfoUsesACredentialsOverride()
{
Catalogue c = new Catalogue(CatalogueRepository, "GetConnectionStringFromCatalogueWhereOneTableInfoUsesACredentialsOverride");
CatalogueItem ci = new CatalogueItem(CatalogueRepository, c, "GetConnectionStringFromCatalogueWhereOneTableInfoUsesACredentialsOverride");
TableInfo t = new TableInfo(CatalogueRepository, "Test");
ColumnInfo col = new ColumnInfo(CatalogueRepository, "[mydatabase].dbo.test.col", "varchar(10)", t);
var extractionInformation = new ExtractionInformation(CatalogueRepository, ci, col, col.Name);
DataAccessCredentials cred = null;
try
{
t.Server = "myserver";
t.Database = "mydatabase";
cred = new DataAccessCredentials(CatalogueRepository, "bob");
cred.Username = "******";
cred.Password = "******";
Assert.AreNotEqual("pass", cred.Password);
Assert.AreEqual("pass", cred.GetDecryptedPassword());
cred.SaveToDatabase();
t.SetCredentials(cred, DataAccessContext.InternalDataProcessing);
t.SaveToDatabase();
var constr = (SqlConnectionStringBuilder)c.GetDistinctLiveDatabaseServer(DataAccessContext.InternalDataProcessing, false).Builder;
Assert.AreEqual("myserver", constr.DataSource);
Assert.False(constr.IntegratedSecurity);
Assert.AreEqual("bob", constr.UserID);
Assert.AreEqual("pass", constr.Password);
}
finally
{
t.DeleteInDatabase();
if (cred != null)
{
cred.DeleteInDatabase();
}
c.DeleteInDatabase();//no need to delete ci because of cascades
}
}
public void Create(CatalogueRepository repository, DiscoveredDatabase database,
ILoadDirectory directory)
{
TableInfo = new TableInfo(repository, "TestData")
{
Server = database.Server.Name,
Database = database.GetRuntimeName()
};
TableInfo.SaveToDatabase();
if (!string.IsNullOrWhiteSpace(database.Server.ExplicitUsernameIfAny))
{
Credentials = new DataAccessCredentialsFactory(repository).Create(TableInfo,
database.Server.ExplicitUsernameIfAny, database.Server.ExplicitPasswordIfAny,
DataAccessContext.Any);
}
ColumnInfo = new ColumnInfo(repository, "Col1", "int", TableInfo)
{
IsPrimaryKey = true
};
ColumnInfo.SaveToDatabase();
LoadMetadata = new LoadMetadata(repository, "HICLoadPipelineTests")
{
LocationOfFlatFiles = directory.RootPath.FullName
};
LoadMetadata.SaveToDatabase();
Catalogue = new Catalogue(repository, "HICLoadPipelineTests")
{
LoggingDataTask = "Test",
LoadMetadata_ID = LoadMetadata.ID
};
Catalogue.SaveToDatabase();
var catalogueItem = new CatalogueItem(repository, Catalogue, "Test");
catalogueItem.SetColumnInfo(ColumnInfo);
SetupLoadProcessTasks(repository);
}
public void GetAllUsersOfACredential()
{
//Get all TableInfos that share this credential
TableInfo tableInfo1 = new TableInfo(CatalogueRepository, "Create2TableInfosThatShareTheSameCredentialAndTestDeletingIt1");
TableInfo tableInfo2 = new TableInfo(CatalogueRepository, "Create2TableInfosThatShareTheSameCredentialAndTestDeletingIt2");
var creds = new DataAccessCredentials(CatalogueRepository, "bob");
tableInfo1.SetCredentials(creds, DataAccessContext.InternalDataProcessing);
tableInfo2.SetCredentials(creds, DataAccessContext.InternalDataProcessing);
tableInfo1.SaveToDatabase();
tableInfo2.SaveToDatabase();
TableInfo[] TablesThatUseCredential = creds.GetAllTableInfosThatUseThis()[DataAccessContext.InternalDataProcessing].ToArray();
Assert.AreEqual(TablesThatUseCredential[0], tableInfo1);
Assert.AreEqual(TablesThatUseCredential[1], tableInfo2);
tableInfo1.DeleteInDatabase();
tableInfo2.DeleteInDatabase();
creds.DeleteInDatabase();
}
public void TestRemoteTableAttacher_Normal(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
var attacher = new RemoteTableAttacher();
//where to go for data
attacher.RemoteServer = db.Server.Name;
attacher.RemoteDatabaseName = db.GetRuntimeName();
attacher.DatabaseType = db.Server.DatabaseType;
if (db.Server.ExplicitUsernameIfAny != null)
{
var creds = new DataAccessCredentials(CatalogueRepository);
creds.Username = db.Server.ExplicitUsernameIfAny;
creds.Password = db.Server.ExplicitPasswordIfAny;
creds.SaveToDatabase();
attacher.RemoteTableAccessCredentials = creds;
}
RunAttachStageWithNormalJob(attacher, db);
}
public void Create2TableInfosThatShareTheSameCredentialAndTestDeletingIt_ThrowsThatCredentialsHasDependencies()
{
//Get all TableInfos that share this credential
TableInfo tableInfo1 = new TableInfo(CatalogueRepository, "Dependency1");
TableInfo tableInfo2 = new TableInfo(CatalogueRepository, "Dependency2");
var creds = new DataAccessCredentials(CatalogueRepository, "bob");
try
{
tableInfo1.SetCredentials(creds, DataAccessContext.InternalDataProcessing);
tableInfo2.SetCredentials(creds, DataAccessContext.InternalDataProcessing);
tableInfo1.SaveToDatabase();
tableInfo2.SaveToDatabase();
var ex = Assert.Throws <CredentialsInUseException>(creds.DeleteInDatabase);//the bit that fails (because tables are there)
Assert.AreEqual("Cannot delete credentials bob because it is in use by one or more TableInfo objects(Dependency1,Dependency2)", ex.Message);
}
finally
{
tableInfo1.DeleteInDatabase(); //will work
tableInfo2.DeleteInDatabase(); //will work
creds.DeleteInDatabase(); //will work
}
}
public void TestGettingConnectionStrings()
{
foreach (TableInfo tbl in CatalogueRepository.GetAllObjects <TableInfo>().Where(table => table.Name.ToLower().Equals("bob")))
{
tbl.DeleteInDatabase();
}
foreach (var c in CatalogueRepository.GetAllObjects <DataAccessCredentials>().Where(cred => cred.Name.ToLower().Equals("bob")))
{
c.DeleteInDatabase();
}
//test it with TableInfos
TableInfo t = new TableInfo(CatalogueRepository, "Bob");
try
{
t.Server = "fish";
t.Database = "bobsDatabase";
t.SaveToDatabase();
//t has no credentials
var server = DataAccessPortal.GetInstance().ExpectServer(t, DataAccessContext.InternalDataProcessing);
Assert.AreEqual(typeof(SqlConnectionStringBuilder), server.Builder.GetType());
Assert.AreEqual("fish", ((SqlConnectionStringBuilder)server.Builder).DataSource);
Assert.AreEqual("bobsDatabase", ((SqlConnectionStringBuilder)server.Builder).InitialCatalog);
Assert.AreEqual(true, ((SqlConnectionStringBuilder)server.Builder).IntegratedSecurity);
var creds = new DataAccessCredentials(CatalogueRepository, "Bob");
try
{
t.SetCredentials(creds, DataAccessContext.InternalDataProcessing, true);
creds.Username = "******";
creds.Password = "******";
creds.SaveToDatabase();
//credentials are cached
t.ClearAllInjections();
////t has some credentials now
server = DataAccessPortal.GetInstance().ExpectServer(t, DataAccessContext.InternalDataProcessing);
Assert.AreEqual(typeof(SqlConnectionStringBuilder), server.Builder.GetType());
Assert.AreEqual("fish", ((SqlConnectionStringBuilder)server.Builder).DataSource);
Assert.AreEqual("bobsDatabase", ((SqlConnectionStringBuilder)server.Builder).InitialCatalog);
Assert.AreEqual("frank", ((SqlConnectionStringBuilder)server.Builder).UserID);
Assert.AreEqual("bobsPassword", ((SqlConnectionStringBuilder)server.Builder).Password);
Assert.AreEqual(false, ((SqlConnectionStringBuilder)server.Builder).IntegratedSecurity);
}
finally
{
var linker = new TableInfoCredentialsManager(CatalogueRepository);
linker.BreakAllLinksBetween(creds, t);
creds.DeleteInDatabase();
}
}
finally
{
t.DeleteInDatabase();
}
}
public DataAccessCredentialsCombineable(DataAccessCredentials dataAccessCredentials)
{
DataAccessCredentials = dataAccessCredentials;
}
public DataAccessCredentialsCommand(DataAccessCredentials dataAccessCredentials)
{
DataAccessCredentials = dataAccessCredentials;
CurrentUsage = DataAccessCredentials.GetAllTableInfosThatUseThis();
}
private void AddCredentials()
{
var newCredentials = new DataAccessCredentials(RepositoryLocator.CatalogueRepository, "New Blank Credentials " + Guid.NewGuid());
Publish(newCredentials);
}
public ExecuteCommandUseCredentialsToAccessTableInfoData(IBasicActivateItems activator,
DataAccessCredentials credentials, TableInfo targetTableInfo) : this(activator,
new DataAccessCredentialsCombineable(credentials), targetTableInfo)
{
}
/// <inheritdoc/>
public Dictionary <DataAccessContext, List <TableInfo> > GetAllTablesUsingCredentials(DataAccessCredentials credentials)
{
Dictionary <DataAccessContext, List <int> > toReturn = new Dictionary <DataAccessContext, List <int> >();
toReturn.Add(DataAccessContext.Any, new List <int>());
toReturn.Add(DataAccessContext.DataExport, new List <int>());
toReturn.Add(DataAccessContext.DataLoad, new List <int>());
toReturn.Add(DataAccessContext.InternalDataProcessing, new List <int>());
toReturn.Add(DataAccessContext.Logging, new List <int>());
using (var con = _repository.GetConnection())
{
using (var cmd = DatabaseCommandHelper.GetCommand(
"SELECT TableInfo_ID,Context FROM DataAccessCredentials_TableInfo WHERE DataAccessCredentials_ID = @cid",
con.Connection, con.Transaction))
{
cmd.Parameters.Add(DatabaseCommandHelper.GetParameter("@cid", cmd));
cmd.Parameters["@cid"].Value = credentials.ID;
using (var r = cmd.ExecuteReader())
while (r.Read())
{
//get the context
DataAccessContext context = GetContext(r);
//add the TableInfo under that context
toReturn[context].Add((int)r["TableInfo_ID"]);
}
}
}
return(toReturn.ToDictionary(k => k.Key, v => _repository.GetAllObjectsInIDList <TableInfo>(v.Value).ToList()));
}
public DataAccessCredentialUsageNode(DataAccessCredentials credentials, ITableInfo tableInfo, DataAccessContext context)
{
Credentials = credentials;
TableInfo = tableInfo;
Context = context;
}
