Re: Data access security issue (long)

On 7 Jun 2001 09:01:20 -0700, jeff_at_usb.com (Jeff Amiel) wrote:

>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:
>
>1. Lookups.
>2. 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.

Use dynamic SQL with the security server returning clauses to be added to the transaction query, e.g.

AND sales*.name = 'user name'
AND merchant.sales* = sales*.number
AND transaction.merchantnumber = merchant.merchantnumber

or:

combine all the sales* info (data all looks the same) into a salesorg table with a common id and level code (person, office, group, ...: symbolic rather than numeric preferred), remove the sales and hierarchy info from the merchant table, add a saleshierarchy table with the details of the hierarchy, a merchantsales table with only the merchantnumber and salesperson id; create a view which given a salesorg id selection explodes it into all salespersons at the lowest level, using unions to explode each possible level, and use those ids to select the merchants

or:

go one further and combine the salesorg and merchant tables into a generic name and address table with an extra merchant level code and add the merchants as the lowest level of the saleshierarchy.

The saleshierarchy table could either be a binary relation or a list of higher entities for each entity, rebuilt when updated, which is useful for reporting, but expensive if you merge the merchants and sales hierarchies.

You have to evaluate the usages required and space/time tradeoffs.

Thanks. Take care, Brian Inglis Calgary, Alberta, Canada

-- 
Brian.Inglis_at_CSi.com 	(Brian dot Inglis at SystematicSw dot ab dot ca)
    fake address		use address above to reply
tosspam_at_aol.com abuse_at_aol.com abuse_at_yahoo.com abuse_at_hotmail.com abuse_at_msn.com abuse_at_sprint.com abuse_at_earthlink.com abuse_at_cadvision.com abuse_at_ibsystems.com uce_at_ftc.gov
						spam traps