public void GrantColumnsToRole(string schemaName, string tableName, string rolename,
IEnumerable <string> selectList, IEnumerable <string> updateList,
IEnumerable <string> insertList,
bool selChanged, bool updChanged, bool insChanged)
{
List <string> sql_queries = new List <String>();
List <List <string> > seq_list = GetListOfSeq(schemaName, tableName);
void revoke(string action) //revoke action from table and sequences
{
sql_queries.Add(GrantsQueries.RevokeActionOnTable(schemaName, tableName, rolename, action));
if (action != "DELETE")
{
if (action == "INSERT")
{
action = "USAGE";
}
foreach (List <string> seq in seq_list)
{
sql_queries.Add(GrantsQueries.RevokeActionOnSeq(seq[0], seq[1], rolename, action)); //Grant sequences
}
}
}
void grant(string action, IEnumerable <string> columnsList)
{
sql_queries.Add(GrantsQueries.GrantActionOnColumns(schemaName, tableName, rolename, action, string.Join(", ", columnsList)));
if (action != "DELETE")
{
if (action == "INSERT")
{
action = "USAGE";
}
foreach (List <string> seq in seq_list)
{
sql_queries.Add(GrantsQueries.GrantActionOnSeq(seq[0], seq[1], rolename, action)); //Grant sequences
}
}
}
if (selChanged)
{
if (selectList.Count() > 0)
{
revoke("SELECT");
grant("SELECT", selectList);
}
else
{
revoke("SELECT");
}
}
if (updChanged)
{
if (updateList.Count() > 0)
{
revoke("UPDATE");
grant("UPDATE", updateList);
}
else
{
revoke("UPDATE");
}
}
if (insChanged)
{
if (insertList.Count() > 0)
{
revoke("INSERT");
grant("INSERT", insertList);
}
else
{
revoke("INSERT");
}
}
try
{
ExecuteSqlNonQuery(sql_queries);
//return sql_queries;
}
catch
{
throw;
}
}
//Grant privileges to selected role
public void GrantTableToRole(string table_schema, string table_name, string role,
bool IsSelect, bool IsUpdate, bool IsInsert, bool IsDelete,
bool selChanged, bool updChanged, bool insChanged, bool delChanged)
{
List <string> sql_queries = new List <String>();
List <List <string> > seq_list = GetListOfSeq(table_schema, table_name);
void revoke(string action) //revoke action from table and sequences
{
sql_queries.Add(GrantsQueries.RevokeActionOnTable(table_schema, table_name, role, action));
if (action != "DELETE")
{
if (action == "INSERT")
{
action = "USAGE";
}
foreach (List <string> seq in seq_list)
{
sql_queries.Add(GrantsQueries.RevokeActionOnSeq(seq[0], seq[1], role, action)); //Grant sequences
}
}
}
void grant(string action)
{
sql_queries.Add(GrantsQueries.GrantActionOnTable(table_schema, table_name, role, action));
if (action != "DELETE")
{
if (action == "INSERT")
{
action = "USAGE";
}
foreach (List <string> seq in seq_list)
{
sql_queries.Add(GrantsQueries.GrantActionOnSeq(seq[0], seq[1], role, action)); //Grant sequences
}
}
}
if (selChanged)
{
if (IsSelect)
{
revoke("SELECT");
grant("SELECT");
}
else
{
revoke("SELECT");
}
}
if (updChanged)
{
if (IsUpdate)
{
revoke("UPDATE");
grant("UPDATE");
}
else
{
revoke("UPDATE");
}
}
if (insChanged)
{
if (IsInsert)
{
revoke("INSERT");
grant("INSERT");
}
else
{
revoke("INSERT");
}
}
if (delChanged)
{
if (IsDelete)
{
revoke("DELETE");
grant("DELETE");
}
else
{
revoke("DELETE");
}
}
try
{
ExecuteSqlNonQuery(sql_queries);
//return sql_queries;
}
catch
{
throw;
}
}