private string GrantAccessSql(string username, DatabaseType type, TestLowPrivilegePermissions permissions)
{
switch (type)
{
case DatabaseType.MicrosoftSQLServer:
return(string.Format(@"
if exists (select * from sys.sysusers where name = '{0}')
drop user [{0}]
GO
CREATE USER [{0}] FOR LOGIN [{0}]
GO
{1} ALTER ROLE [db_datareader] ADD MEMBER [{0}]
{2} ALTER ROLE [db_datawriter] ADD MEMBER [{0}]
{3} ALTER ROLE [db_ddladmin] ADD MEMBER [{0}]
GO
", username,
permissions.HasFlag(TestLowPrivilegePermissions.Reader) ? "" : "--",
permissions.HasFlag(TestLowPrivilegePermissions.Reader) ? "" : "--",
permissions.HasFlag(TestLowPrivilegePermissions.CreateAndDropTables) ? "" : "--"));
case DatabaseType.MySql:
break;
case DatabaseType.Oracle:
break;
default:
throw new ArgumentOutOfRangeException();
}
throw new NotImplementedException();
}
private void SetupLowPrivilegeUserRightsFor(DiscoveredDatabase db, TestLowPrivilegePermissions permissions, ITableInfo ti)
{
var dbType = db.Server.DatabaseType;
//get access to the database using the current credentials
var username = TestDatabaseSettings.GetLowPrivilegeUsername(dbType);
var password = TestDatabaseSettings.GetLowPrivilegePassword(dbType);
if (string.IsNullOrWhiteSpace(username) || string.IsNullOrWhiteSpace(password))
{
Assert.Inconclusive();
}
//give the user access to the table
var sql = GrantAccessSql(username, dbType, permissions);
using (var con = db.Server.GetConnection())
UsefulStuff.ExecuteBatchNonQuery(sql, con);
if (ti != null)
{
//remove any existing credentials
foreach (DataAccessCredentials cred in CatalogueRepository.GetAllObjects <DataAccessCredentials>())
{
CatalogueRepository.TableInfoCredentialsManager.BreakAllLinksBetween(cred, ti);
}
//set the new ones
DataAccessCredentialsFactory credentialsFactory = new DataAccessCredentialsFactory(CatalogueRepository);
credentialsFactory.Create(ti, username, password, DataAccessContext.Any);
}
}
protected void SetupLowPrivilegeUserRightsFor(ITableInfo ti, TestLowPrivilegePermissions permissions)
{
var db = DataAccessPortal.GetInstance().ExpectDatabase(ti, DataAccessContext.InternalDataProcessing);
SetupLowPrivilegeUserRightsFor(db, permissions, ti);
}
protected void SetupLowPrivilegeUserRightsFor(DiscoveredDatabase db, TestLowPrivilegePermissions permissions)
{
SetupLowPrivilegeUserRightsFor(db, permissions, null);
}
