--- In Firebird-Architect@yahoogroups.com, "Leyne, Sean" <Sean@B...>
wrote:

>
> > The PAM approach means that low-end systems can use lighter weight
> > security, while larger and more robust security requirements can be
> > enforced in the DBMS with minimum impact on performance.
>
> I can see how PAM can perform database **access** authorization
> (replacing the password database), I don't see how it can take over the
> management of user/role/group management. Perhaps I need to be shown
> the light.
>
>

We are starting with the common acceptance that SQL-92 concepts of
user/role/group are too coarse grained for many real-world systems.
In practice they are largely ignored, and applications implement
security. This makes most databases insecure.

Views are another mechanism that is used, but attaining row level
security requires writing a complete set of views for every role in
the enterprise. In enterprises with more than a few roles, this
becomes unweildy and maintenance costs increase with the size of the
company.


Moving forward ...

PAM is a meta-model for you to plug in your preferred security schema
via dll/so.

Jim's proposed replacement for the current security model is very LDAP
like. It is sufficiently close that I think of it as LDAP, although I
don't think Jim sees it that way. LDAP is essentially a specialized
authorization and authentication database system that is arbitrarily
fine grained, standardized, and not tied to any specific application
or security model. It is capable of implementing almost any model to
any degree of resolution, including the classical SQL-92 model.

With PAM, the use of an LDAP like structure, either embedded or
external, becomes a plug-in replacement for the current model.

Prepare time is mostly spent finding access paths, and may make up the
majority of the time spent in many SQL statements. Authorization, on
the other hand, expressed in database terms, is an inner join lookup
against primary keys where the paths are well-known and may be
prepared in advance, and it is often possible to cache the small
amount of data required for user authorization at authentication time.

LDAP (or something like it) offers the ability to represent and
arbitrarily fine-grained security model that is capable of cutting
across all functions the enterprise. LDAP is capable of supporting
poor and/or crude security models, but it is also capable of
supporting arbitrarily fine-grained and robust models. However, your
software must understand how to ask LDAP for permission (look-asides)
to perform work in order for it to be effective.

When applied at the RDBMS level, LDAP like security prevents
application coding errors from creating security holes. If a user
gets to a part of the application they aren't supposed to get to, they
can't see anything that they aren't entitled to and they can't change
anything that they couldn't otherwise change. It also prevents the
use of back doors to alter data (Barings Bank collapse).

LDAP based security is usually maintained by a GUI application, but
there are a number of command line tools for managing it too. We
have a department that manages LDAP so my personal exposure to its
mechanics is limited. However, its impacts on application design are
that extremely complex security models can be implemented with almost
no code through lookasides to the LDAP server, and changes to the
security model do not impact application code.

Since LDAP must be backed by a database of some sort, it is a natural
fit when revamping the security model of an RDBMS to plug LDAP right
inside the database architecture. That way you can minimze the costs
of the lookasides - lookasides that applications programmers are
making anyways.

For connection pooling, you might configure a model something like
this if you used the LDAP like PAM:

Users are authenticated (logged on) via the LDAP mechanisms. These
mechanisms are well established industry standards that are becoming
more supported by applications and operating systems over time. For
java programmers, there are even JDBC drivers to talk to LDAP.
Windows and linux both support login authentication via LDAP.

In the database section of the model "anonymous" only has authority to
prepare statements. "Anonymous" can prepare any statement against any
table, but cannot submit, alter, or receive data.

Users are authorized to perform actions if (a) they are authenticated
and (b) whatever other conditions are set for authorization are true.

Rows that would violate security model constratints are filtered out
of result sets before return to the calling application.

Columns that would violate security model constraints are filtered out
of result sets by subsituting NULLs.

Each position within the company would be defined as a role in the
LDAP database, and every user would be cross-referenced to his roles.
Manager_for_store_1 is a different role than Manager_for_store_2.

In SQL92 terms, they would have identical database object permissions.
But they would have the additional restrictions of only allow inserts
and updates "where store = <<their store>>" and "allow select of
columns a, b, c when store != <<their store>>", and to deal with their
own employees.

So, if you get a clerk who is Firebird savvy, it doesn't matter if he
knows how to run isql any more. His authority inside or outside the
application are identical. If he gets his manager's password, he
can't alter or view anything but his own store's information. Damage
potential is limited.

Role to person is a many-to-many relationship, so one role may be
filled by many people (clerk-at-store-1) and one person may fulfill
many roles (manager_for_store_1 and sysdba).

To be fair, these security and roll up models are a pain to set up.
However, they pay for themselves in software maintenance and problem
prevention. Once set up, things just work.

If we went so far as to expose the security PAM through an LDAP
interface to the outside world, standard LDAP maintenance tools would
be able to configure the security model and Firebird security would be
capable of controlling all application security through configurations.