MGFoster <me@privacy.com> wrote:[color=blue]
> Br@dley wrote:[color=green]
>> I'm going to go into a fair bit of detail as I'm hoping my methods
>> may be of assistance to anyone else wanting to implement something
>> similar (or totally confusing:)
>>
>> One of systems I've developed has three levels of security.
>>
>> Admins - can see all records
>> Manager - can only see records based on an organisation structure
>> held in a table (simple tree structure)
>> Employee - can only see own records
>>
>> The system uses a table which contains 'login', 'employeeid' (level
>> is determined from security group/role)
>>
>> User account creation is controlled by a form which forces the
>> account to be associated with an employeeid (if manager/employee
>> level). This is the critical step.
>>
>> I then use queries (row-level security) to return only the employee
>> records that person can view based on the user role and the
>> employeeid. (This has been implemented in both Access and for SQL
>> backends). Still with me? :)
>>
>> In our Access version that is all fine and works well. In SQL
>> however it means that all security still has to be administered
>> through the client software to link employee's to a login.
>>
>> User account details are entered into a form and then I use a stored
>> procedure that creates an SQL account, give permissions to the
>> database, assign the user to a database role and then create an
>> entry in my users table linking them to an employee record. I then
>> put a user-defined function as the criteria in my views so they
>> return only the appropriate employee records. The function checks
>> the current database role and gets the current user's employeeid
>> from the user table and then returns a true/false if that employeeid
>> is valid for the person to see. Admin level - allow all records
>>
>> Manager level - This one is a little trickier since it's not just
>> returning all the employee's that belong to the organisation unit
>> that the person manages but also all the people of the sub-units as
>> well (it needs to cascade all the way down the tree levels). This is
>> done by creating a special field in the organisation table which
>> contains a string of all the parent ID's in the structure for each
>> unit (eg. unit 12's string might be ';1;5;12;'). This allows a
>> simple query to be used (eg. a criteria of LIKE *;5;* returns all
>> the organisation units who are children, grand-children, etc of unit
>> 5). Employee level - return only records for current user's
>> employeeid
>>
>> The link between the login and an employee record is the hurdle I
>> can't seem to get past. I can't see any other way to implement this
>> type of security.
>>
>> The reason we want to remove administration from the client is so the
>> administration can be done solely on the server or even better using
>> Active Directory.
>>
>> This is probably "pie in the sky" stuff but it would be nice to be
>> able to determine this link automatically or use some other method.
>>
>> ie. if domain\group is given manager level access to the database
>> (ie. made a member of my manager database role in SQL) then a member
>> of this windows group then has access to the database but somehow
>> can only see the records of the people they manage???
>>
>> I might have to create a mixed environment where manager/employee
>> level is for user accounts (requiring a link to be made to an
>> employee record manually via the client), whereas admin level can be
>> for either user accounts or groups.....[/color][/color]
[color=blue]
> First a carp, then a suggestion.
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Carp: Having more than one value in a column violates 1NF[/color]
Yes I know that. I've been developing for 10 years. The tables are
designed properly but I've included this concatenated field as it is the
only fast, easy way I could devise to filter out the records that I
want. I'm more than happy to live with it if it works and it is simple.
Other more elegant solution are very complex from what I've read so far
and in the real world justifying development time on something that
already works well is not always easy:)
[color=blue]
> , i.e., each
> cell must be atomic (only one piece of data in a cell [aka Field]).
> You can solve your hierarchical problem by using nested sets. See
> this site for an MS SQL version of a nested set solution:
>
>
http://toponewithties.blogspot.com/[/color]
Funnily enough I've done a very similar thing to what this site is doing
except they are using prime numbers wheras I am just using a string of
ID's. My design works fine and is far less complicated for returning all
the branches below a unit. I'll read into it though as it's some good
theory.. but at this stage it's the least of my worries as the current
design works really well.
My biggest problem is having to rely on a table stored link between the
user and the employee in order to implement this type of security which
means security admin has to be done throught the client.
[color=blue]
> Joe Celko has 2 books that discuss hierarchical tables. One is _Joe
> Celko's SQL For Smarties: Advanced SQL Programming_. The other is
> something like _Hierarchical SQL Tables_, not sure. There are also
> WEB sites that talk about nested sets and other hierarchial solutions.
>
> Suggestion: You can use a combination of Views & Stored Procedures to
> limit users access to data. Once you set up a hierarchical employees
> table you can use a View to retrieve only the rows/columns that user
> is entitled to see. You can also use a stored procedure to determine
> which View a user can use.[/color]
This is pretty much what I am doing.....
- Organisation structure table (unitid, parentid, managerid, unitname,
parentstring)
- The only way I've found to cascade down every branch of the tree
structure is to build a string on each field that contains the path from
that node back to the top parent and then search for the parent's id in
each record as the criteria. It's not ideal but it is very simple and
works really well.
- I use a user-defined function as part of the criteria in a view to
work out if a record is viewable for the current user. I had to do it
this way as store procedures are not updateable nor are views with more
than one table in them (user's don't have access to underlying tables
obviously and I'm using an Access2000 ADP).
--
regards,
Bradley
A Christian Response
http://www.pastornet.net.au/response 
