| Subject | Re: User name SYSDBA |
|---|---|
| Author | johnson_dave2003 |
| Post date | 2005-08-09T12:23:58Z |
--- In Firebird-Architect@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:
Take a large company with a web application architecture for its
operational needs.
That web app is used by several thousand people using prepared
statements from a pool of 50 conections. There are a limited number
of prepared statements available because of system resources.
Since there is priveleged information encapsulated in every table,
which people need to know their share of _only_ to do their jobs and
keep the company rolling, a role based security system has been
constructed.
Role based security ties directly in to the personnel database, where
about 75% of the information is protected by privacy laws. It is
public information that Helen is a Senior Firebird Specialist.
However, it is not public information that she makes $1,000,000 per year.
So far, the traditional SQL security holds up - you separate salary
from employee by putting them on different tables. Authorization at
prepare time works.
However, Helen has a manager, who is one of about 100 managers. Roman
is allowed to see her salary, but not Jim. Both Roman and Jim have
authority to the Salaries table, but Roman is only permitted to see
the employees that report to him. Jim is permitted to see his
employees information and the public information about other manager's
employees, but not Helen's salary.
At prepare time, the prepared statments in the connection pool do not
know which of several thousand users will be using them. Helen, Jim,
and Roman are just three of the possible users.
The traditional work around has been to open up the database with a
generic user ID for the connection pool (Jim would say no security),
and handle the authorization in the application.
Jim's proposal would allow the capacity to move this application layer
security into the database. His example was changing roles for
testing, which is of interest to developers. But that is really a
side effect of moving a generic role-based enterprise application
security model into the database rather than a primary goal.
In today's world the overheads for processing this is being incurred
anyways (by the application), but it is kind of like closing the barn
door after the horses are gone.
Hope this helps.
wrote:
> At 10:30 AM 9/08/2005 +0400, you wrote:request
> >"Roman Rokytskyy" <rrokytskyy@a...> wrote:
> > >
> > > The main question is how the permissions are determined during
> > > execution. Currently this is performed during statement compilationbut whether
> > > and it seems that moving permission checks to the execution time
> > > without degrading the performance is not an easy task.
> >
> >The question is not whether it can be done at the execution time
> >it should be. IIRC, the SQL spec explicitly requires permissions to bepre-empts
> >checked at the prepare time. And it makes a lot of sense to me.
>
> Me too. I've been really struggling to understand why it would ever be
> necessary (or even rational) to defer authorisation until execution
> time. AFAIK, even ExecuteImmediate has an implicit Prepare that
> an unauthorised execution request.I'll try again.
>
> Helen
Take a large company with a web application architecture for its
operational needs.
That web app is used by several thousand people using prepared
statements from a pool of 50 conections. There are a limited number
of prepared statements available because of system resources.
Since there is priveleged information encapsulated in every table,
which people need to know their share of _only_ to do their jobs and
keep the company rolling, a role based security system has been
constructed.
Role based security ties directly in to the personnel database, where
about 75% of the information is protected by privacy laws. It is
public information that Helen is a Senior Firebird Specialist.
However, it is not public information that she makes $1,000,000 per year.
So far, the traditional SQL security holds up - you separate salary
from employee by putting them on different tables. Authorization at
prepare time works.
However, Helen has a manager, who is one of about 100 managers. Roman
is allowed to see her salary, but not Jim. Both Roman and Jim have
authority to the Salaries table, but Roman is only permitted to see
the employees that report to him. Jim is permitted to see his
employees information and the public information about other manager's
employees, but not Helen's salary.
At prepare time, the prepared statments in the connection pool do not
know which of several thousand users will be using them. Helen, Jim,
and Roman are just three of the possible users.
The traditional work around has been to open up the database with a
generic user ID for the connection pool (Jim would say no security),
and handle the authorization in the application.
Jim's proposal would allow the capacity to move this application layer
security into the database. His example was changing roles for
testing, which is of interest to developers. But that is really a
side effect of moving a generic role-based enterprise application
security model into the database rather than a primary goal.
In today's world the overheads for processing this is being incurred
anyways (by the application), but it is kind of like closing the barn
door after the horses are gone.
Hope this helps.