Re: Oracle database design question - security

In this case how do you feel about not using roles?

Instead, it seems that a relatively complex algorithm of determining privileges is necessary.
Assuming that you only need to grant select, update etc. on complete tables/views, you
could have a PL/SQL program process business rules to generate a grant script for the individual,
which would grant each privilege directly to the user.

The algorithm could then look at the various classifications of the users geography and make
decisions based on tables you would create about which tables to grant to what geographies.

This is a complex solution to a complex problem, but if you analyze it it should come out
to be doable. It will be costly to develop, but that is up to the client.

Just a thought.
:-)

Akshay Jain

Database Analyst
Firmbuy, Inc.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Firmbuy e-Purchasing Services
Nobody works harder to help your company save money. Visit us online at <http://www.firmbuy.com> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= This email may contain confidential and/or privileged information for the sole use of the intended recipient. Any review or distribution by others is strictly prohibited. If you have received this email in error, please contact the sender and delete all copies. Opinions, conclusions or other information expressed or contained in this email are not given or endorsed by the sender unless otherwise affirmed independently by the sender.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• David Nemeht <dpn_ora_at_yahoo.com> a icrit : > What about coming up with a cost for maintaining
  > (changing) the roles and privileges? When you come
  > up
  > with a price, you will usually get someone's
  > attention.
  >
  > --- Manasa Rao <rao_manasa_at_hotmail.com> wrote:
  > > Oh, I wish my client understood that, I need a
  > very
  > > good business case to
  > > support that.
  > >
  > > I definitely appreciate your suggestion.
  > > Thanks
  > > Radhika
  > >
  > >
  > > >From: paquette stephane
  > > <stephane_paquette_at_yahoo.com>
  > > >Reply-To: ORACLE-L_at_fatcity.com
  > > >To: Multiple recipients of list ORACLE-L
  > > <ORACLE-L_at_fatcity.com>
  > > >Subject: Re: Oracle database design question.
  > > >Date: Fri, 29 Dec 2000 06:15:22 -0800
  > > >
  > > >Do not try to do something too complex, I've been
  > > to a
  > > >client where they had create a role for each job
  > > >description in the company and each role was
  > > accessing
  > > > only the tables it needed.
  > > >
  > > >People were changing job in the company and the
  > job
  > > >description was evolving.
  > > >Also, they were 2 major restructurations in the
  > > same
  > > >year, it was a nightmare to manage.
  > > >
  > > >
  > > >--- Manasa Rao <rao_manasa_at_hotmail.com> a icrit :
  > >
  > > >All,
  > > > >
  > > > > For our Workers Injury Compensation database,
  > > > > customer visions to secure the
  > > > > data based on the type of role in the company
  > > and
  > > > > based on their
  > > > > geographical location.
  > > > >
  > > > > Question is how do you limit the data based on
  > > their
  > > > > geographical location?
  > > > >
  > > > > And an employee can have 13 geographical
  > > > > locations.(Area, DistrictCode,
  > > > > FinanceNumber, Paylocation etc) And there
  > is
  > > a
  > > > > table for the geo location
  > > > > that ties to the employee table, and has one
  > > record
  > > > > for every employee in
  > > > > the company.
  > > > >
  > > > > And an employee requests for authorization, at
  > > the
  > > > > time of joining the
  > > > > company which is how I know what geographical
  > > data
  > > > > he could see. But, how do
  > > > > I give roles and privileges based on this?
  > And
  > > we
  > > > > are using Fine Grain
  > > > > Access Control of Oracle 8I for security
  > > designing.