Subject Re: User name SYSDBA
Author johnson_dave2003
--- In, "Roman Rokytskyy"
<rrokytskyy@a...> wrote:
> > If an LDAP like system (or an LDAP implementation) were embedded in
> > the Firebird engine, then the performance degradation would be
> > limited.
> The main question is how the permissions are determined during request
> execution. Currently this is performed during statement compilation
> and it seems that moving permission checks to the execution time
> without degrading the performance is not an easy task.
> > The authorization does not have to be implemented at the crude level
> > of querying on every statement. RACF, for example, ties all of the
> > authorizations to the user session at connect time. When you
> > disconnect and reconnect, you get your new authorities. It would
> > increase the memory requirements of a connection, and the time to
> > connect/disconnect, but it does not have to impact every statement.
> Then please define what the "authority" in this case is.
> SQL defines a concept of "role" to which the permissions on different
> database objects are granted. There was a suggestion to introduce the
> concept of a "group" with similar capabilities to the role. The main
> difference would be that within a session user can belong to one role
> but to multiple groups. Jim suggested to add a possibility to switch
> the role within one session as a nice feature to support application
> servers.
> How would the authorization look in your case? Would admin be required
> to define permissions for each database object in LDAP (e.g.
> permission to select from some table)? Or would it go in the direction
> of role or group, where actual grants are stored in the database? If
> former, does this concept really belong to the LDAP? If latter - isn't
> it part of the PAM concept (sorry, I actually know only JAAS - there
> it is)?
> Roman

I hope that this makes sense - I've got a huge install coming up this
week and I'm a bit short on the sleep/coffee.

To a database person, authority is typically tied to the table level.
To the applications developer, authority usually cuts across tables
at the row and/or column level. In java terms, authorization is a
cross-cutting concern - an N-dimensional aspect that impacts all
database objects at the row and column level.

A well defined and efficiently implemented LDAP structure with users
tied to roles allows row-level authorization for data, for the expense
of an initial map load at user session connection time and a lookup
against a Map on each row fetched.

Let's take a typical application in my industry:

SYSDBA's need authority to create, delete, and alter database objects,
but they should not be allowed to insert, select, update, or delete
data. SYSDBA's are the only ones permitted to alter database objects.

Security administrators have authority to change people's authorities,
except their own, but no authority to view or alter any of the data or
the database objects.

Developers have full permission to select all data except for that
protected by privacy laws.

Executives have permission to select all data, and authority to
insert, update, and delete information in tables for their region.

Area Managers have authority to view anything in their region, but
only to maintain data for their area.

Local Managers have full authority for their location and limited read
authority for their area and region.

Data entry clerks have data entry authority for information that is
not protected by privacy laws.

Personnel managers are allowed access to the data for the employees
that they are responsible for, and no others. They are not allowed
access to any other information.

A single person may fulfil several roles concurrently - he may be an
Local Manager for his franchise, and a data entry clerk for other
franchises. He has differing authority by row across several database

Role based security, if implemented at the DBMS level as opposed to
the application level as Jim suggests, must allow for row+column level
security that cuts across database objects. That is, the security
must tie in not only to the database objects, but to the business
hierarchy of the enterprise using the DBMS.

In short, you cannot implement effective enterprise security at the
database object level.

In the past, the workaround has been to grant authorization to the
application at the object level, and then handle the rest in the
application. If I understand him correctly, Jim wants to take this
out of the application layer and put it right in the DBMS, because
the application authority is efectively no security at all.

To achieve his goal, every row fetch must include a lookaside to the
security module to see if the row and columns requested are allowed.
In Java terms, the lookaside can be implemented as a Map attached to
the session.

Pluggable modules allow application type security at the database
level where the security needs outweigh the cost, and for lighter
weight applications, a less intrusive model that resolves
authorization at the databse object level.

The internal Security API must define row and column level lookasides,
which in most PAM's would resolve to immediate return with success
(70ms per 1,000,000 calls on 800 MHz Pentium III). In a "really"
secure model that resolves to the row and column, serious attention
must be paid to the performance penalty.