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); }