public static DropSecurityProfile CreateDropSecurityProfile(IDatabaseContainer container, string databaseName, string tableName)
{
DropSecurityProfile dropSecurityProfile = new DropSecurityProfile(container);
dropSecurityProfile.targetDatabase = databaseName;
dropSecurityProfile.targetTableName = tableName;
return(dropSecurityProfile);
}
public void DropSecurityProfile_TheProfileCannotDrop_NoticeInValidate()
{
IDatabaseContainer databaseContainer = ObjectConstructor.CreateDatabaseContainer();
DropSecurityProfile dropSecurityProfile = CreateDropSecurityProfile(databaseContainer, SystemeConstants.SystemDatabaseName, SystemeConstants.ProfilesTableName);
dropSecurityProfile.SetTargetSecurityProfile(SystemeConstants.DefaultProfile);
Assert.IsFalse(dropSecurityProfile.ValidateParameters());
}
public Boolean parseDropSecurityProfile()
{
Sentence sentence = Query.parse("DROP SECURITY PROFILE Employee;");
DropSecurityProfile DropSecurityProfile = sentence as DropSecurityProfile;
if (DropSecurityProfile.SecurityProfileName.Equals("Employee"))
{
return(true);
}
return(false);
}
public void DropSecurityProfile_TheProfileDoesntExist_NoticeInValidate()
{
IDatabaseContainer databaseContainer = ObjectConstructor.CreateDatabaseContainer();
Column column = databaseContainer.GetDatabase(SystemeConstants.SystemDatabaseName).GetTable(SystemeConstants.ProfilesTableName).GetColumn(SystemeConstants.ProfileNameColumn);
string profileName = VariousFunctions.GenerateRandomString(8);
while (column.ExistCells(profileName))
{
profileName = VariousFunctions.GenerateRandomString(8);
}
DropSecurityProfile dropSecurityProfile = CreateDropSecurityProfile(databaseContainer, SystemeConstants.SystemDatabaseName, SystemeConstants.ProfilesTableName);
dropSecurityProfile.SetTargetSecurityProfile(profileName);
Assert.IsFalse(dropSecurityProfile.ValidateParameters());
}
public void DropSecurity_TheProfileCanDrop_DropTheProfile()
{
IDatabaseContainer databaseContainer = ObjectConstructor.CreateDatabaseContainer();
ITable table = databaseContainer.GetDatabase(SystemeConstants.SystemDatabaseName).GetTable(SystemeConstants.ProfilesTableName);
Column column = table.GetColumn(SystemeConstants.ProfileNameColumn);
string profileName = VariousFunctions.GenerateRandomString(8);
while (column.ExistCells(profileName))
{
profileName = VariousFunctions.GenerateRandomString(8);
}
Row row = table.CreateRowDefinition();
row.GetCell(SystemeConstants.ProfileNameColumn).data = profileName;
table.AddRow(row);
int numberOfRow = table.GetRowCount();
DropSecurityProfile dropSecurityProfile = CreateDropSecurityProfile(databaseContainer, SystemeConstants.SystemDatabaseName, SystemeConstants.ProfilesTableName);
dropSecurityProfile.SetTargetSecurityProfile(profileName);
Assert.IsTrue(dropSecurityProfile.ValidateParameters());
dropSecurityProfile.Execute();
Assert.AreEqual(numberOfRow - 1, table.GetRowCount());
}
public static Sentence parse(String sentenc)
{
Sentence sentence = null;
String patterSelect = "SELECT\\s(\\*|\\w+(?:,*\\w+)*)\\sFROM\\s(\\w+)\\sWHERE\\s(\\w+)([<|=|>])('[^',]+'|-?\\d+\\.?\\d*);";
String patternSelectAll = "SELECT\\s(\\*|\\w+(?:,*\\w+)*)\\sFROM\\s(\\w+);";
String patterDelete = "DELETE\\sFROM\\s(\\w+)\\sWHERE\\s(\\w+)([<|=|>])('[^',]+'|-?\\d+\\.?\\d*);";
String patternInsert = "INSERT\\sINTO\\s(\\w+)\\sVALUES\\s\\(((?:'[^',]+'|-?\\d+\\.?\\d*)(?:,(?:'[^',]+'|-?\\d+\\.?\\d*))*)\\);";
String patternUpdate = "UPDATE\\s(\\w+)\\sSET\\s(\\w+=(?:'[^',]+'|-?\\d+\\.?\\d*)(?:,?\\w+=(?:'[^',]+'|-?\\d+\\.?\\d*))*)\\sWHERE\\s(\\w+)([<|=|>])('[^',]+'|-?\\d+\\.?\\d*);";
String patternCreateTable = "CREATE\\sTABLE\\s(\\w+)\\s\\((\\w+\\s[TEXT|INT|DOUBLE]+(?:,?\\w+\\s[TEXT|INT|DOUBLE]+)*)\\);";
String patterDropTable = "DROP\\sTABLE\\s(\\w+);";
String patternCreateSecurityProfile = "CREATE\\sSECURITY\\sPROFILE\\s(\\w+);";
String patternDropSecurityProfile = "DROP\\sSECURITY\\sPROFILE\\s(\\w+);";
String patternGrantPrivilege = "GRANT\\s(DELETE|INSERT|SELECT|UPDATE)\\sON\\s(\\w+)\\sTO\\s(\\w+);";
String patternRevokePrivilege = "REVOKE\\s(DELETE|INSERT|SELECT|UPDATE)\\sON\\s(\\w+)\\sTO\\s(\\w+);";
String patternAddUser = "******";
String patternDeleteUser = "******";
// For the select
if (Regex.IsMatch(sentenc, patterSelect))
{
Match match = Regex.Match(sentenc, patterSelect);
List<String> list = new List<String>();
// List of columns
list = stringToList(match.Groups[1].Value, ',');
// Table name
String table = match.Groups[2].Value;
// Where creation
Operator op = stringToOperator(match.Groups[4].Value);
Where where = new Where(match.Groups[3].Value, op, match.Groups[5].Value);
// Select creation
sentence = new Select(table, list, where);
}
// For the selectAll
else if (Regex.IsMatch(sentenc, patternSelectAll))
{
Match match = Regex.Match(sentenc, patternSelectAll);
List<String> list = new List<String>();
// List of columns
list = stringToList(match.Groups[1].Value, ',');
// Table name
String table = match.Groups[2].Value;
sentence = new SelectAll(table, list);
}
// For the delete
else if (Regex.IsMatch(sentenc, patterDelete))
{
Match match = Regex.Match(sentenc, patterDelete);
String table = match.Groups[1].Value;
Operator op = stringToOperator(match.Groups[3].Value);
Where where = new Where(match.Groups[2].Value, op, match.Groups[4].Value);
sentence = new Delete(table, where);
}
// For the insert
else if (Regex.IsMatch(sentenc, patternInsert))
{
Match match = Regex.Match(sentenc, patternInsert);
String table = match.Groups[1].Value;
List<String> list = new List<String>();
// List of columns
list = stringToList(match.Groups[2].Value, ',');
sentence = new Insert(table, list);
}
// For the update
else if (Regex.IsMatch(sentenc, patternUpdate))
{
Match match = Regex.Match(sentenc, patternUpdate);
String table = match.Groups[1].Value;
List<String> list = new List<String>();
String valuees = match.Groups[2].Value;
// List of columns
var tuple = listToTwoList(stringToList(valuees, ','));
List<string> colum = tuple.Item1;
List<string> values = tuple.Item2;
Operator op = stringToOperator(match.Groups[4].Value);
Where where = new Where(match.Groups[3].Value, op, match.Groups[5].Value);
sentence = new Update(table, colum, values, where);
}
//For the create table
else if (Regex.IsMatch(sentenc, patternCreateTable))
{
Match match = Regex.Match(sentenc, patternCreateTable);
String table = match.Groups[1].Value;
List<String> list = new List<String>();
// List of columns
list = stringToList(match.Groups[2].Value, ',');
sentence = new CreateTable(table, list);
}
// For the drop table
else if (Regex.IsMatch(sentenc, patterDropTable))
{
Match match = Regex.Match(sentenc, patterDropTable);
String table = match.Groups[1].Value;
sentence = new DropTable(table);
}
// For the Create security profile
else if (Regex.IsMatch(sentenc, patternCreateSecurityProfile))
{
Match match = Regex.Match(sentenc, patternCreateSecurityProfile);
String secutiryProfile = match.Groups[1].Value;
sentence = new CreateSecurityProfile(secutiryProfile);
}
// For the Drop security profile
else if (Regex.IsMatch(sentenc, patternDropSecurityProfile))
{
Match match = Regex.Match(sentenc, patternDropSecurityProfile);
String secutiryProfile = match.Groups[1].Value;
sentence = new DropSecurityProfile(secutiryProfile);
}
// For grant privilege
else if (Regex.IsMatch(sentenc, patternGrantPrivilege))
{
Match match = Regex.Match(sentenc, patternGrantPrivilege);
string typeString = match.Groups[1].Value;
Privilege type = stringToType(typeString);
string table = match.Groups[2].Value;
string securityProfile = match.Groups[3].Value;
sentence = new GrantPrivilege(type, table, securityProfile);
}
// For revoke privilege
else if (Regex.IsMatch(sentenc, patternRevokePrivilege))
{
Match match = Regex.Match(sentenc, patternRevokePrivilege);
string typeString = match.Groups[1].Value;
Privilege type = stringToType(typeString);
string table = match.Groups[2].Value;
string securityProfile = match.Groups[3].Value;
sentence = new RevokePrivilege(type, table, securityProfile);
}
// For Add user
else if (Regex.IsMatch(sentenc, patternAddUser))
{
Match match = Regex.Match(sentenc, patternAddUser);
string name = match.Groups[1].Value;
string pass = match.Groups[2].Value;
string securityProfile = match.Groups[3].Value;
sentence = new AddUser(name, pass, securityProfile);
}
// For delete user
else if (Regex.IsMatch(sentenc, patternDeleteUser))
{
Match match = Regex.Match(sentenc, patternDeleteUser);
string name = match.Groups[1].Value;
sentence = new DeleteUser(name);
}
return sentence;
}
public string output(string input, User user)
{
Sentence sentence = Query.parse(input);
string output = "";
if (sentence is Statements)
{
Statements statement = sentence as Statements;
if (statement is Select)
{
Select sel = statement as Select;
IList <string> columnsNames = sel.listColumns;
string tableName = sel.tableName;
Where where = sel.sentenceWhere;
Operator op = where.op;
string columnName = where.col;
string dataToCompare = where.colData;
if (hasPrivilege(user, tableName, Privilege.SELECT))
{
output = select(columnsNames, tableName, columnName, op, dataToCompare).selectToString();
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
else if (statement is SelectAll)
{
SelectAll sel = statement as SelectAll;
IList <string> columnsNames = sel.listColumns;
string tableName = sel.tableName;
if (hasPrivilege(user, tableName, Privilege.SELECT))
{
output = selectAll(columnsNames, tableName).selectToString();
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
else if (statement is Delete)
{
Delete delete = statement as Delete;
string tabName = delete.tableName;
Where where = delete.sentenceWhere;
string column = where.col;
Operator op = where.op;
string data = where.colData;
if (hasPrivilege(user, tabName, Privilege.DELETE))
{
deleteData(tabName, column, op, data);
output = Constants.TupleDeleteSuccess;
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
else if (statement is Insert)
{
Insert ins = statement as Insert;
string nameTable = ins.tableName;
List <string> dataToInsert = ins.row;
if (hasPrivilege(user, nameTable, Privilege.INSERT))
{
insert(nameTable, dataToInsert);
output = Constants.InsertSuccess;
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
else if (statement is Update)
{
Update upd = statement as Update;
string tableName = upd.tableName;
List <string> columnNames = upd.column;
List <string> newValues = upd.newValue;
Where where = upd.sentenceWhere;
string columnToCompare = where.col;
Operator op = where.op;
string data = where.colData;
if (hasPrivilege(user, tableName, Privilege.UPDATE))
{
for (int i = 0; i < columnNames.Count; i++)
{
string columnName = columnNames[i];
string newData = newValues[i];
update(tableName, columnName, newData, columnToCompare, op, data);
}
output = Constants.TupleUpdateSuccess;
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
else if (statement is DropTable)
{
DropTable drop = statement as DropTable;
string tableName = drop.tableName;
if (user.Name.Equals("admin"))
{
dropTable(tableName);
output = Constants.TableDroppedSucess;
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
else if (statement is CreateTable)
{
CreateTable create = statement as CreateTable;
string tableName = create.tableName;
List <string> colNames = create.ListOfColumns;
if (user.Name.Equals("admin"))
{
createTable(tableName, colNames);
output = Constants.CreateTableSuccess;
}
else
{
output = Constants.SecurityNotSufficientPrivileges;
}
}
}
else if (sentence is SecurityQueries)
{
SecurityQueries securityQueries = sentence as SecurityQueries;
if (!user.Equals(admin.Name) && !user.Password.Equals(admin.Password))
{
output = Constants.SecurityNotSufficientPrivileges;
}
else
{
if (securityQueries is AddUser)
{
AddUser addUseer = securityQueries as AddUser;
string name = addUseer.User;
string pass = addUseer.Password;
string security = addUseer.SecurityProfileName;
addUser(name, pass, security);
output = Constants.SecurityUserAdded;
}
else if (securityQueries is DeleteUser)
{
DeleteUser deleteeUser = securityQueries as DeleteUser;
string name = deleteeUser.User;
deleteUser(name);
output = Constants.SecurityUserDeleted;
}
else if (securityQueries is CreateSecurityProfile)
{
CreateSecurityProfile create = securityQueries as CreateSecurityProfile;
string security = create.SecurityProfileName;
createSecurityProfile(security);
output = Constants.SecurityProfileCreated;
}
else if (securityQueries is DropSecurityProfile)
{
DropSecurityProfile drop = securityQueries as DropSecurityProfile;
string name = drop.SecurityProfileName;
dropSecurityProfile(name);
output = Constants.SecurityProfileDeleted;
}
else if (securityQueries is GrantPrivilege)
{
GrantPrivilege graant = securityQueries as GrantPrivilege;
Privilege type = graant.Type;
string table = graant.Table;
string security = graant.SecurityProfileName;
grant(type, table, security);
output = Constants.SecurityPrivilegeGranted;
}
else if (securityQueries is RevokePrivilege)
{
RevokePrivilege revooke = securityQueries as RevokePrivilege;
Privilege type = revooke.Type;
string table = revooke.Table;
string security = revooke.SecurityProfileName;
revoke(type, table, security);
output = Constants.SecurityPrivilegeRevoked;
}
}
}
else
{
throw new Exception(Constants.WrongSyntax);
}
return(output);
}