Re: Data access security issue (long)

how about using oracle's built in field level security combined with roles. it's a lot easier. to do this, one only need to fill out a "CRUD" matrix (Create, Read, Update, Delete) for the lowest grain or level of entity (fields, rows, tables, views, whatever you need) by role/user (roles are easier).

"Jeff Amiel" <jeff_at_usb.com> wrote in message news: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:
>
> 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.
Received on Sun Jul 22 2001 - 01:31:28 CEST