Exemple #1
0
        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;
            }
        }
Exemple #2
0
        //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;
            }
        }