Re: Implementing row-level security (SQL/AD)
- From: "Br@dley" <n0mail@xxxxxx>
- Date: Tue, 26 Jul 2005 00:32:32 GMT
Br@dley <n0mail@xxxxxx> wrote:
> MGFoster <me@xxxxxxxxxxx> wrote:
>> Br@dley wrote:
>>> 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.....
>
>> First a carp, then a suggestion.
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Carp: Having more than one value in a column violates 1NF
<>
>> , 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/
> Funnily enough I've done a very similar thing....
Actually I've implemented the Materialised Path method without knowing
it:)
"[using a] node's primary keys within the path string"
http://www.dbazine.com/oracle/or-articles/tropashko4
Obviously the limitation of this method is that the path strings needs
to be updated if records are changed. I simply rebuild all the strings
every time the user leaves the organisation screen as it is a very fast
operation to perform.
<>
--
regards,
Bradley
A Christian Response
http://www.pastornet.net.au/response
.
- References:
- Implementing row-level security (SQL/AD)
- From: Br@dley
- Re: Implementing row-level security (SQL/AD)
- From: MGFoster
- Re: Implementing row-level security (SQL/AD)
- From: Br@dley
- Implementing row-level security (SQL/AD)
- Prev by Date: ADO parameters queries and stored procedures - and Connections
- Next by Date: Re: supporting text fields >255 in Access 2003
- Previous by thread: Re: Implementing row-level security (SQL/AD)
- Next by thread: DISTINCT problem in query?
- Index(es):
Relevant Pages
|
