Roles and Security by Page, Control and Data Level

Im researching a easy, efficient and flexible way to do the following:

 1.) The admin of the system will be able to create roles (easy)

 2.) The admin can assign which page(s) that role can view (not difficult)

 3.) The admin can assign what functions can be performed on each page (insert,
updates, deletes) (getting harder)

 4.) The admin can then assign what columns, data or rows can be viewable or
editable.

       a. For instance, Lets say there is an inventory system and it the
products screen for items and the screen is pretty much a gridview with a
listing of products. 

            If the user is in the role of

                     Admin (all access; all columns; full permissions), 

                    Store Employee (a little less information; inventory levels
nationwide, wholesale prices, etc; Can modify only certain columns (inventory
level) 

                    Manager (inventory level nationwide, wholesale prices,
supplier info, recalls, and special info ; Can Delete a product, Edit only name
and price, but not supplier info and can add a product)


                    Customer (only sees the product, sale price and how many are
in stock in the current store; read-only).
 

Also with some exceptions like:

                  Maybe a manager can delete a product, but cannot view the
Amount Sold per month and this column is only viewable to the Accounting Role.



A ll this is all happening on one screen and I need to find a way to allow the
Admin to set this up (well me first, lol). But a brief summary, depending upon
which role a user is in on each page, they can add, update, delete curtain
columns or rows, view different information and controls.


This is no easy question, with no right answer. This is not like the system we
will be creating, but the example I hope will be easy to follow. Im looking for
views and opinions from others that may have did something like this before. I
know its going to be a pain and make me pull most of my hair out, but something
to get me that much closer to finding a solution would be very helpful.
Thanks!

Hi CSharpSean,

From your description, I understand that you have no difficulty on the first and
second questions. Hence, I try to provide my suggestions for the 3th and 4th
questions. Hope they will be helpful to you.

1. In my opionin, we can specify which functions can be performed on each page
neither on the page side nor on the SQL Server side. We should put the function
permission settings in a XML file or database, and check those settings in the
DAL (data access layer). For example, you can put the settings in web.config
like the following:

<appSettings>
<add key="default.aspx"
value="insert,delete,select,update" />
<add key="testfolder/test.aspx" value="select" />
</appSettings>

In the DAL, you can first get the request URL by
“HttpContext.Current.Request.Url.ToString()”, then query the corresponding
value from the settings in web.config. At last, check if the returned value
contains required permission (value.Contains("insert") for insert
method, value.Contains("delete") for delete method, etc ), continue if
require permission exist. otherwise, throw an exception.

2. We should set the column permissions on the SQL Server side. For more
information, please refer to the following.

add column permissions to a new column in SQL server 2005
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1694015&SiteID=1

Conclusion: In your Admin page, add permission setting of each page in
somewhere. In DAL, check the permission. In your Admin page, add column
permission for each table by stored procedure. SQL Server will maintain them.