Implementing row-level security (SQL/AD)

[Br]

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.....
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[MGFoster]

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/

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.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQuVBnYechKqOuFEgEQL6jACeNPWzjuTZqQC7OinsPhbti3 +LdwkAn04f
PVB2oJqII42qgBlJPYDgCbs7
=EajH
-----END PGP SIGNATURE-----

[Br]

MGFoster <[e-mail]> 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
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:)
, 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 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.
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.

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

[Br]

Br@dley <n0****@4u.com> wrote:

MGFoster <[e-mail]> 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