Data access security issue (long)
Date: 7 Jun 2001 09:01:20 -0700
Message-ID: <458d5212.0106070801.3bff1312_at_posting.google.com>
Have an generic interesting database/security issue I thought others might be able to assist with.
sorry for the x-post...but there is really no single group that covers this generic issue....
We have solved 'system/subsystem' access issues (which user can access
which system) with a home-grown technique of using API calls to a
'security server' which then authinticates that user's permissions to
'use' various systems.
No problem.
The real issue now is authenticating the DATA that they have access
to.
We essentially have a heirarchy of data objects that we allow people
to manipulate or see data about...these include: (I've changed their
real 'names' to protect the innocent :) )
merchant
salesperson
salesoffice
salesorganization
groupofsalesorganizations
groupofgroupsofsalesorganizations
At each 'level', we have data ABOUT that organizational unit (name, address, contact etc, etc)
We also have transactional data (a variety of different normalized
tables) FOR the lowest 'Merchant' level. Merchants are the ONLY level
for which transactional data exists.
Keep in mind that the information denoting which level of the
heirarchy that a particular merchant 'belongs' to is in each merchant
record, it is often not indexed and is NOT in the transactional data
at all.
A salesperson should be able to see merchant transactional data for the merchants 'under' him in the heirarchy.
A salesoffice should be able to see merchant transactional data for all merchants for all salespersons 'under' him.
a groupofsalesorganizations should be able to see merchant transactional data for all merchants for all salespersons for all salesoffices 'under' him.
There are 2 problems:
- Lookups.
- Reports.
Problem #1 comes from the issue that the 'user' should be able to do a
partial 'lookup' of merchant names that meet certain criteria (i.e.
they only know the first few letters of the merchant name). They
should be able to enter what they know and be provided with a list to
choose from. Problem is, we don't want them to 'see' merchants that
don't fall 'under' them.
Keep in mind we are still using the API calls to the 'security server'
to
authenticate system access.
We intend to do the same for user access. However, passing (via API call) a list of merchants to the security server and getting back a list of 'valid' ones has much too high of a cost (not to mention the communications overhead of potentially sending/receiving 50,000 merchant names because the 'user' is at one of the highest levels in the heirarchy. Another option is to pass the query to the security server and have it return the merchants that are valid. This cuts the communications in half, but it's still cumbersome. We could have the server determine a count of rows in the result set and return only 100 or so (letting the caller know)....this would prob work ok...but I feel there is a better solution somewhere
Problem #2 is the nasty one. We have a variety of reports that we can
produce now that show transactional data for any merchant range we
want. Some of it is summary data (total for all merchants) and some
has merchant level detail. We want to provide ONLY the data that the
user is authorized to see. For example, if a user requests a report
showing total number of transactions this month, his numbers should
reflect ONLY merchants that he is authorized for as per the
herirarchy. We've come up with our own ideas with some bizzarre
joins logic but I can't help but beleive that an elegant solution is
out there somewhere........
To make it more complicated, a user can hold multiple 'roles' as far
as the heirarchy is concerned.....they might be a 'salesoffice', but
also be able to see merchant data for 2 other 'salesoffices' (as he
owns all three!!)
If anyone has any input on what I've described, please respond....
I can provide more info if I've been TOO obtuse...
And if I've been TOO obtuse, please don't flame me....I think there is
enough info here to get the ball rolling on some ideas!
sample simple schema listed below:
Thanks in advance!!
Jeff
--Table merchant-- (approx 100,000 rows)
merchantname char(30) merchantnumber char(16) address char(30) city char(30) state char(2) salesperson char(8) salesoffice char(5) salesorganization char(3)
etc
--Table salesperson--(approx 5,000 rows)
name char(30) number char(8) address char(30)
city, state, etc
--Table salesoffice--(approx (1,000 rows)
name char(30) number char(5)
address, city, state,
---Table transactions--- (millions of rows...multiple tables)
merchantnumber char(16) trans_date date amount double trans_type char(2)
------------------------
The security server houses tables of users along with which systems
they can access...
theoretically, new tables that determine what 'levels' and specifics
of data they can access would be added.
Received on Thu Jun 07 2001 - 18:01:20 CEST