private void AddJoin(QualifiedJoinType joinType, List <RbacSelectColumn> columns)
{
for (int c = 0; c < columns.Count; c += 2)
{
RbacJoin ajoin = new RbacJoin();
ajoin.JoinType = (RbacJoinTypes)Enum.Parse(typeof(RbacJoinTypes), joinType.ToString(), true);
ajoin.FromTableName = columns[c].Table.Name;
ajoin.FromTableAlias = columns[c].Table.Alias;
ajoin.FromTableColumn = columns[c].Name;
ajoin.WithTableName = columns[c + 1].Table.Name;
ajoin.WithTableAlias = columns[c + 1].Table.Alias;
ajoin.WithTableColumn = columns[c + 1].Name;
if (string.IsNullOrEmpty(ajoin.FromTableName))
{
RbacTable table = Context.User.Role.CrudPermissions.Find(ajoin.FromTableAlias);
ajoin.FromTableName = table.Name;
}
if (string.IsNullOrEmpty(ajoin.WithTableName))
{
RbacTable table = Context.User.Role.CrudPermissions.Find(ajoin.FromTableAlias);
ajoin.WithTableName = table.Name;
}
JoinClauses.Add(ajoin);
}
}
public void ApplyPermissionUpdate()
{
var tables = Columns.GroupBy(c => c.Table.Name).Select(grp => grp.ToList()).ToList();
foreach (var allColumnnsInATable in tables)
{
if (allColumnnsInATable.Count > 0)
{
RbacTable rbacTable = TablesReferred.Find(allColumnnsInATable[0].Table.Name);
if (rbacTable == null)
{
throw new Exception("Could not find table name in referred tables!");
}
if (rbacTable.AllowedOperations.HasFlag(RbacDBOperations.Update))
{
foreach (RbacSelectColumn column in allColumnnsInATable)
{
RbacColumn rbacColumn = rbacTable.FindColumn(column.Name);
if (!rbacColumn.AllowedOperations.HasFlag(RbacDBOperations.Update))
{
RbacException.Raise(string.Format("User '{0}' has permission to update table '{1}', however has no permission to update column '{2}'!",
Context.User.UserName, rbacTable.Name, rbacColumn.Name), RbacExceptionCategories.Parser);
}
}
}
else
{
RbacException.Raise(string.Format("User '{0}' does not have permission to update table '{1}'!",
Context.User.UserName, rbacTable.Name), RbacExceptionCategories.Parser);
}
}
}
IsPermissionApplied = true;
}
public static string GetTableNameOrAlias(this SqlQueryParser sqlQueryParser, string tableName)
{
//List<RbacSelectColumn> filtered = sqlQueryParser.Columns.Where(c => c.Table.Name.ToLower() == tableName.ToLower()).ToList();
//if ((filtered.Count > 0) && (string.IsNullOrEmpty(filtered[0].Table.Alias) == false))
// return filtered[0].Table.Alias;
RbacTable table = sqlQueryParser.TablesReferred.Where(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase)).SingleOrDefault();
if (table != null)
{
if (table.ReferencedOnly)
{
return(table.TempAlias);
}
else if (!string.IsNullOrEmpty(table.Alias))
{
return(table.Alias);
}
else
{
return(table.Name);
}
}
return(tableName);
}
private void AddRelationalJoins(RbacRelation relation, List <RbacCondition> conditions)
{
//was relation already referred in the query?
RbacTable relationTable = TablesReferred.Find(relation.WithTable);
if (relationTable == null)
{
//NO:
//Is there already join with that table?
RbacJoin join = JoinClauses.JoinExists(relation.WithTable, relation.SelfName);
if (join == null)
{
//add new join, at the end of operation we will stringify sequentially
join = RbacJoin.AddNewJoin(this,
relation.SelfName, relation.SelfColumnName,
relation.WithTable, relation.WithColumn);
}
//add into referred table as 'ReferencedOnly'
relationTable = new RbacTable(string.Empty, relation.WithTable, false);
relationTable.Conditions.AddRange(conditions);
relationTable.ReferencedOnly = true;
relationTable.TempAlias = join.WithTableAlias;
TablesReferred.Add(relationTable);
}
//add condition
ApplyCondition(relationTable);
}
public bool ParseUsingSqlCommand()
{
if (TablesReferred == null)
{
TablesReferred = new List <RbacTable>();
}
else
{
TablesReferred.Clear();
}
try
{
using (SqlConnection connection = new SqlConnection(Context.ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(OriginalQuery, connection);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
//if (row["BaseTableName"].ToString() == "City")
// Debugger.Break();
RbacSelectColumn column = new RbacSelectColumn();
column.Alias = row["ColumnName"].ToString();
column.Name = row["BaseColumnName"].ToString();
column.Table.Name = row["BaseTableName"].ToString();
Columns.Add(column);
RbacTable table = Context.User.Role.CrudPermissions.Find(column.Table.Name);
if (table != null)
{
TablesReferred.Add(table);
}
else
{
throw new Exception(string.Format("The referred table {0} was not found in meta data!", row["BaseTableName"].ToString()));
}
}
TablesReferred = new List <RbacTable>(TablesReferred.DistinctBy(t => t.Name));
connection.Close();
}
ParsedMethod = RbacSelectQueryParsedMethods.CommandBehavior;
ParsedQuery = ParsedQuery.Replace("*", Columns.ToCommaSeparatedString());
IsParsed = true;
return(true);
}
catch (Exception ex)
{
Errors.Add(ex.Message);
}
return(false);
}
private void ApplyCondition(RbacTable table)
{
if (table.Conditions.Count == 0)
{
return;
}
if (ConditionAppliedOn.Where(c => c == table.Name).SingleOrDefault() != null)
{
return;
}
string selfName = this.GetTableNameOrAlias(table.Name);
//if (!table.ReferencedOnly)
// selfName = this.GetTableNameOrAlias(table.Name);
//else
// selfName = table.TempAlias;
foreach (RbacCondition condition in table.Conditions)
{
//if this condition's column name already exists in the original query, we need to remove that condition from original query
IfSameColumnConditionExistsRemoveCondition(selfName, condition);
string thisItemWhereClause = condition.WhereClause.Replace("__self__", selfName);
SqlQueryStringParser.SqlStringParser strParser = new SqlQueryStringParser.SqlStringParser();
strParser.Parse(ParsedQuery);
string originalWhereClause = strParser.WhereClause;
if (string.IsNullOrEmpty(originalWhereClause))
{
strParser.WhereClause = thisItemWhereClause;
}
else
{
strParser.WhereClause = string.Format("({0}) AND ({1})", originalWhereClause, thisItemWhereClause);
}
ParsedQuery = strParser.ToTextAndFixSpaces();
}
//if (!string.IsNullOrEmpty(table.OrderBy))
//{
// string orginalOrderByClause = strParser.OrderByClause;
// if (string.IsNullOrEmpty(orginalOrderByClause))
// strParser.OrderByClause = table.OrderBy;
// else
// strParser.OrderByClause = string.Format("{0}, {1}", orginalOrderByClause, OrderbyClause.Text);
//}
ConditionAppliedOn.Add(table.Name);
}
private static string Merge(List <RbacTable> freshPermissions, List <RbacTable> existingPermissions)
{
foreach (RbacTable table in freshPermissions)
{
RbacTable existingTable = existingPermissions.Find(table.Name);
if (existingTable != null)
{
table.Conditions = existingTable.Conditions;
table.Parameters = existingTable.Parameters;
}
}
return(ToXml(freshPermissions));
}
private void UpdateReferredTables(string tableName, string tableAlias)
{
RbacTable actualTable = Context.User.Role.CrudPermissions.Find(tableName);
actualTable.Alias = tableAlias;
if (actualTable != null)
{
TablesReferred.Add(actualTable);
}
else
{
RbacException.Raise(string.Format("The referred table {0} was not found in meta data!", tableName),
RbacExceptionCategories.Parser);
}
}
private void UpdateReferredTables(List <RbacSelectColumn> columns)
{
foreach (RbacSelectColumn column in columns)
{
if (string.IsNullOrEmpty(column.Table.Name))
{
RbacTable table = TablesReferred.Find(column.Table.Alias);
if (table == null)
{
UpdateReferredTables(column.Table.Name, column.Table.Alias);
}
else
{
column.Table = table;
}
}
}
}
public override void ExplicitVisit(NamedTableReference node)
{
string tableName = node.SchemaObject.BaseIdentifier != null ? node.SchemaObject.BaseIdentifier.Value : string.Empty;
string tableAlias = node.Alias != null ? node.Alias.Value : string.Empty;
RbacTable table = Context.User.Role.CrudPermissions.Find(tableName);
if (table == null)
{
table = Context.User.Role.CrudPermissions.Find(tableAlias);
}
if (table == null)
{
RbacException.Raise(string.Format("The referred table {0} was not found in meta data!", tableName),
RbacExceptionCategories.Parser);
}
table.Alias = tableAlias;
table.Server = node.SchemaObject.ServerIdentifier != null ? node.SchemaObject.ServerIdentifier.Value : string.Empty;
table.Database = node.SchemaObject.DatabaseIdentifier != null ? node.SchemaObject.DatabaseIdentifier.Value : string.Empty;
table.Schema = node.SchemaObject.SchemaIdentifier != null ? node.SchemaObject.SchemaIdentifier.Value : string.Empty;
Tables.Add(table);
}
private void AddRelationalJoin(RbacTable table)
{
if (table.Relations.Count != 0)
{
foreach (RbacRelation relation in table.Relations)
{
RbacTable withTable = Context.User.Role.CrudPermissions.Find(relation.WithTable);
if (withTable != null)
{
AddRelationalJoins(relation, withTable.Conditions);
}
AddRelationalJoin(withTable);
}
}
else
{
//we have reached end of relation (linked list), there is no further relations ahead of us, time to cleanup
//let's see if we need those joins, if not let's remove those.
List <RbacTable> tables = new List <RbacTable>(TablesReferred);
tables = tables.Where(t => t.ReferencedOnly).ToList();
tables.Reverse();
foreach (RbacTable t in tables)
{
if (t.Conditions.Count == 0)
{
var joinTobeRemoved = JoinClauses.Where(jc => jc.WithTableName == t.Name).SingleOrDefault();
JoinClauses.Remove(joinTobeRemoved);
}
else
{
//oops while reversing back we found a condition, let's stop here!
break;
}
}
}
}
public static RbacColumn FindColumn(this RbacTable table, string columnName)
{
return(table.Columns.Where(c => c.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase)).SingleOrDefault());
}
private void ParseSqlUpdateStatement(TSqlStatement sqlStatement)
{
if (sqlStatement.GetType() == typeof(UpdateStatement))
{
UpdateStatement aUpdateStatement = (UpdateStatement)sqlStatement;
#region Handle Target Table
#endregion Handle Target Table
RbacTable targetTable = new RbacTable();
#region Handle From Clause - When Update with Join
if (aUpdateStatement.UpdateSpecification.FromClause != null)
{
//mostly update with join case
NamedTableReferenceVisitor fromClauseNtVisitor = new NamedTableReferenceVisitor(Context);
aUpdateStatement.UpdateSpecification.FromClause.AcceptChildren(fromClauseNtVisitor);
this.TablesReferred = fromClauseNtVisitor.Tables;
if (TablesReferred.Count > 0)
{
targetTable = TablesReferred[0];
//if alias is being updated, we need to fix table name
RbacTable tryTable = Context.User.Role.CrudPermissions.Find(targetTable.Name);
if (tryTable == null)
{
//alias is being updated, lets get the actual table name
var tt = fromClauseNtVisitor.Tables.Where(t => t.Alias == targetTable.Name).ToList()[0];
if (tt != null)
{
targetTable.Name = tt.Name;
}
}
}
else
{
RbacException.Raise("No target table found in the update query!");
}
}
else
{
NamedTableReferenceVisitor ntVisitor = new NamedTableReferenceVisitor(Context);
aUpdateStatement.UpdateSpecification.Target.Accept(ntVisitor);
if (ntVisitor.Tables.Count == 0)
{
RbacException.Raise("No target table found in the update query!");
}
else if (ntVisitor.Tables.Count == 1)
{
targetTable = ntVisitor.Tables[0];
}
else
{
RbacException.Raise("More than 1 target tables found in the update query! Currently not supported.");
}
TablesReferred.Add(targetTable);
}
#endregion Handle From Clause - When Update with Join
#region Handle Columns
SetClauseVisitor scVisitor = new SetClauseVisitor(targetTable.Name);
aUpdateStatement.UpdateSpecification.AcceptChildren(scVisitor);
Columns = scVisitor.Columns;
UpdateReferredTables(Columns);
#endregion Handle Columns
}
else
{
Errors.Add("Not a update statement!");
}
}
public RbacSelectColumn()
{
Table = new RbacTable();
}
public List <RbacTable> ReadPermissions(string metaDataxml)
{
List <RbacTable> tables = new List <RbacTable>();
if (string.IsNullOrEmpty(metaDataxml))
{
return(tables);
}
//XmlDocument doc = new XmlDocument();
//doc.LoadXml(metaDataxml);
XmlDocument doc = ValidateAndGetRbacXmlDocument(metaDataxml);
foreach (XmlNode tableNode in doc.DocumentElement.ChildNodes[1])
{
if (tableNode.NodeType == XmlNodeType.Comment)
{
continue;
}
RbacTable table = new RbacTable(tableNode.Attributes["Id"].Value, tableNode.Attributes["Name"].Value,
tableNode.Attributes["Create"].Value, tableNode.Attributes["Read"].Value,
tableNode.Attributes["Update"].Value, tableNode.Attributes["Delete"].Value);
foreach (XmlNode node in tableNode)
{
if (node.NodeType == XmlNodeType.Comment)
{
continue;
}
if (node.Name == "Columns")
{
foreach (XmlNode columnNode in node.ChildNodes)
{
if (columnNode.NodeType == XmlNodeType.Comment)
{
continue;
}
//if ((table.Name == "Author") && (columnNode.Attributes["Name"].Value == "AuthorId"))
// Debugger.Break();
table.Columns.Add(new RbacColumn(columnNode.Attributes["Name"].Value, columnNode.Attributes["Type"].Value,
columnNode.Attributes["Create"].Value, columnNode.Attributes["Read"].Value,
columnNode.Attributes["Update"].Value));
}
}
else if (node.Name == "Conditions")
{
//if (tableNode.Attributes["Name"].Value == "City")
// Debugger.Break();
foreach (XmlNode condnNode in node.ChildNodes)
{
if (condnNode.NodeType == XmlNodeType.Comment)
{
continue;
}
if (condnNode.Attributes.Count == 3)
{
table.Conditions.Add(new RbacCondition(table.Name, condnNode.Attributes["Name"].Value, condnNode.Attributes["Columns"].Value, condnNode.Attributes["WhereClause"].Value));
}
}
}
else if (node.Name == "Relations")
{
//if (tableNode.Attributes["Name"].Value == "Author")
// Debugger.Break();
foreach (XmlNode relationNode in node.ChildNodes)
{
if (relationNode.NodeType == XmlNodeType.Comment)
{
continue;
}
if (relationNode.Attributes.Count >= 2)
{
table.Relations.Add(new RbacRelation(table.Name, relationNode.Attributes["My"].Value, relationNode.Attributes["With"].Value));
}
}
}
else if (node.Name == "Parameters")
{
//if (tableNode.Attributes["Name"].Value == "City")
// Debugger.Break();
foreach (XmlNode paramNode in node.ChildNodes)
{
if (paramNode.NodeType == XmlNodeType.Comment)
{
continue;
}
if (paramNode.Attributes.Count >= 1)
{
table.Parameters.Add(new RbacParameter(paramNode.Attributes["Name"].Value, paramNode.Attributes["Description"].Value));
}
}
}
}
tables.Add(table);
}
return(tables);
}
public override void ExplicitVisit(SelectStarExpression node)
{
string query = String.Join(string.Empty, node.ScriptTokenStream.Select(sts => sts.Text).ToArray());
string tableNameOrAlias = string.Empty;
bool hasIdentifier = false;
if (node.Qualifier != null)
{
tableNameOrAlias = node.Qualifier.Identifiers[0].Value;
hasIdentifier = true;
}
else
{
int pos = node.ScriptTokenStream.Select((v, i) => new { token = v, index = i }).First(sts => sts.token.TokenType == TSqlTokenType.From).index;
tableNameOrAlias = node.ScriptTokenStream[pos + 2].Text;
if ((node.ScriptTokenStream.Count >
(pos + 2 + 2)) &&
(node.ScriptTokenStream[pos + 4].TokenType == TSqlTokenType.Identifier))
{
//e.g. 'select * from Author a' getting 'a'
tableNameOrAlias = node.ScriptTokenStream[pos + 4].Text;
}
}
bool isAlias = false;
RbacTable table = Context.User.Role.CrudPermissions.Find(tableNameOrAlias, ref isAlias);
if (table != null)
{
foreach (RbacColumn col in table.Columns)
{
RbacSelectColumn column = new RbacSelectColumn();
if (isAlias)
{
column.Table.Alias = tableNameOrAlias;
column.Table.Name = table.Name;
}
else
{
column.Table.Name = tableNameOrAlias;
}
column.Table = table;
column.Name = col.Name;
Columns.Add(column);
}
if ((isAlias) && (hasIdentifier))
{
ParsedQuery = query.Replace(tableNameOrAlias + ".*", table.Columns.ToCommaSeparatedString(tableNameOrAlias));
}
else
{
ParsedQuery = query.Replace("*", table.Columns.ToCommaSeparatedString(tableNameOrAlias));
}
}
else
{
RbacException.Raise(string.Format("The referred table {0} was not found in meta data!", tableNameOrAlias),
RbacExceptionCategories.Parser);
}
}
public ScalarExpressionVisitor(Rbac context, RbacTable table = null)
{
this.Context = context;
this.Table = table;
}
