Application and Data Security Question

[TrekFan]

Hi there,

Got a nice challenge here .. we must implement a security system, that should enable us to handle :

+ Application Security (that is, what sections of the application a user can “see” … menu options, screen controls and so on) and
+ Data Security (that is, what user the can see and or update).

In the case of data security, we need to be able to handle “data hierarchies”. That is, a user in the hierarchy can only see information related to them , their “sons”, their grandsons and so on , and of course, they cannot see the information of their parents and of their brothers in most cases (ahh what would be our world without exceptions). Nice thing is that we could have different hierarchies !


What we are using now is .Net 2003 and Sql Server 2000 .. we know that “Yukon” will have row level security, but we need the product to be stable before we can consider using it.


Another interesting challenge would be to implement this security on the database somehow, to make sure that all possible interface clients that access it would be see what they are allowed to see.



If you have any article or experience you can share about his, would be greatly appreciated



Thanks !


TF

 

[NoCoolHandle]

Primary key / foreign key would allow for what you are talking about...

what you would need is a column that refers to a parent..

Very similar to what you have in the reportsto column of the northwind db.

(the reportsto column refers to the employeeid col - it is a self-referencing table)

At that point only expand what is a child of the last record...

Another option might be to have all data come from a table function where you pass in the parameter of X and it then does a recursive lookup to return only the rows you need.


HTH

Rob