 |
Index for
Section 5 |
|
 |
Alphabetical
listing for S |
|
 |
Bottom of
page |
|
SLAPD-SQL(5)
NAME
slapd-sql - SQL backend to slapd
SYNOPSIS
/usr/internet/openldap/etc/slapd.conf
DESCRIPTION
The primary purpose of this slapd(8) backend is to PRESENT information
stored in some RDBMS as an LDAP subtree without any programming (some SQL
and maybe stored procedures can't be considered programming, anyway ;).
That is, for example, when you (some ISP) have account information you use
in an RDBMS, and want to use modern solutions that expect such information
in LDAP (to authenticate users, make email lookups etc.). Or you want to
synchronize or distribute information between different sites/applications
that use RDBMSes and/or LDAP. Or whatever else...
It is NOT designed as a general-purpose backend that uses RDBMS instead of
BerkeleyDB (as the standard BDB backend does), though it can be used as
such with several limitations. You can take a look at
http://www.openldap.org/faq/index.cgi?file=378 (OpenLDAP FAQ-O-
Matic/General LDAP FAQ/Directories vs. conventional databases) to find out
more on this point.
The idea (detailed below) is to use some metainformation to translate LDAP
queries to SQL queries, leaving relational schema untouched, so that old
applications can continue using it without any modifications. This allows
SQL and LDAP applications to inter-operate without replication, and
exchange data as needed.
The SQL backend is designed to be tunable to virtually any relational
schema without having to change source (through that metainformation
mentioned). Also, it uses ODBC to connect to RDBMSes, and is highly
configurable for SQL dialects RDBMSes may use, so it may be used for
integration and distribution of data on different RDBMSes, OSes, hosts
etc., in other words, in highly heterogeneous environment.
This backend is experimental.
CONFIGURATION
These slapd.conf options apply to the SQL backend database. That is, they
must follow a "database sql" line and come before any subsequent "backend"
or "database" lines. Other database options are described in the
slapd.conf(5) manual page.
dbname <datasource name>
The name of the ODBC datasource to use.
dbhost <hostname>
dbuser <username>
dbpasswd <password>
These three options are generally unneeded, because this information
is already taken from the datasource. Use them if you need to
override datasource settings. Also, several RDBMS' drivers tend to
require explicit passing of user/password, even if those are given in
datasource (Note: dbhost is currently ignored).
subtree_cond <SQL expression>
Specifies a where-clause template used to form a subtree search
condition (dn=".*<dn>"). It may differ from one SQL dialect to
another (see samples).
children_cond <SQL expression>
Specifies a where-clause template used to form a children search
condition (dn=".+,<dn>"). It may differ from one SQL dialect to
another (see samples).
oc_query <SQL expression>
The default is SELECT id, name, keytbl, keycol, create_proc,
delete_proc, expect_return FROM ldap_oc_mappings
at_query <SQL expression>
The default is SELECT name, sel_expr, from_tbls, join_where, add_proc,
delete_proc, param_order, expect_return FROM ldap_attr_mappings WHERE
oc_map_id=?
insentry_query <SQL expression>
The default is INSERT INTO ldap_entries (dn, oc_map_id, parent,
keyval) VALUES (?, ?, ?, ?)
delentry_query <SQL expression>
The default is DELETE FROM ldap_entries WHERE id=?
These four options specify SQL query templates for loading schema
mapping metainformation, adding and deleting entries to ldap_entries,
etc. All these and subtree_cond should have the given default values.
For the current value it is recommended to look at the sources, or in
the log output when slapd starts with "-d 5" or greater. Note that
the parameter number and order must not be changed.
upper_func <SQL function name>
Specifies the name of a function that converts a given value to
uppercase. This is used for CIS matching when the RDBMS is case
sensitive.
upper_needs_cast { yes | no }
Set this directive to yes if upper_func needs an explicit cast when
applied to literal strings. The form cast (<arg> as varchar(<max DN
length>)) is used, where <max DN length> is builtin. This is
experimental and may change in future releases.
concat_pattern <pattern>
This statement defines the pattern to be used to concatenate strings.
The pattern MUST contain two question marks, '?', that will be
replaced by the two strings that must be concatenated. The default
value is CONCAT(?,?); a form that is known to be highly portable is
?||?, but an explicit cast may be required when operating on literal
strings: cast(?||? as varchar(<length>)). On some RDBMSes the form
?+? is known to work. Carefully check the documentation of your RDBMS
or stay with the examples for supported ones. This is experimental
and may change in future releases.
strcast_func <SQL function name>
Specifies the name of a function that converts a given value to a
string for appropriate ordering. This is used in "SELECT DISTINCT"
statements for strongly typed RDBMSes with little implicit casting
(like PostgreSQL), when a literal string is specified. This is
experimental and may change in future releases.
has_ldapinfo_dn_ru { yes | no }
Explicitly inform the backend whether the SQL schema has dn_ru column
(dn in reverse uppercased form) or not. Overrides automatic check
(required by PostgreSQL/unixODBC). This is experimental and may
change in future releases.
fail_if_no_mapping { yes | no }
When set to yes it forces write operations to fail if no appropriate
mapping between LDAP attributes and SQL data is available. The
default behavior is to ignore those changes that cannot be mapped
correctly. This is experimental and may change in future releases.
METAINFORMATION USED
Almost everything mentioned later is illustrated in examples located in the
servers/slapd/back-sql/rdbms_depend/ directory in the OpenLDAP source tree,
and contains scripts for generating sample database for Oracle, MS SQL
Server, mySQL and more (including PostgreSQL and IBM db2).
The first thing that one must arrange is what set of LDAP object classes
can present your RDBMS information.
The easiest way is to create an objectclass for each entity you had in ER-
diagram when designing your relational schema. Any relational schema, no
matter how normalized it is, was designed after some model of your
application's domain (for instance, accounts, services etc. in ISP), and is
used in terms of its entities, not just tables of normalized schema. It
means that for every attribute of every such instance there is an effective
SQL query that loads its values.
Also you might want your object classes to conform to some of the standard
schemas like inetOrgPerson etc.
Nevertheless, when you think it out, we must define a way to translate LDAP
operation requests to (a series of) SQL queries. Let us deal with the
SEARCH operation.
Example: Let's suppose that we store information about persons working in
our organization in two tables:
PERSONS PHONES
---------- -------------
id integer id integer
first_name varchar pers_id integer references persons(id)
last_name varchar phone
middle_name varchar
...
(PHONES contains telephone numbers associated with persons). A person can
have several numbers, then PHONES contains several records with
corresponding pers_id, or no numbers (and no records in PHONES with such
pers_id). An LDAP objectclass to present such information could look like
this:
person
-------
MUST cn
MAY telephoneNumber $ firstName $ lastName
...
To fetch all values for cn attribute given person ID, we construct the
query:
SELECT CONCAT(persons.first_name,' ',persons.last_name)
AS cn FROM persons WHERE persons.id=?
for telephoneNumber we can use:
SELECT phones.phone AS telephoneNumber FROM persons,phones
WHERE persons.id=phones.pers_id AND persons.id=?
If we wanted to service LDAP requests with filters like
(telephoneNumber=123*), we would construct something like:
SELECT ... FROM persons,phones
WHERE persons.id=phones.pers_id
AND persons.id=?
AND phones.phone like '123%'
So, if we had information about what tables contain values for each
attribute, how to join these tables and arrange these values, we could try
to automatically generate such statements, and translate search filters to
SQL WHERE clauses.
To store such information, we add three more tables to our schema and fill
it with data (see samples):
ldap_oc_mappings (some columns are not listed for clarity)
---------------
id=1
name="person"
keytbl="persons"
keycol="id"
This table defines a mapping between objectclass (its name held in the
"name" column), and a table that holds the primary key for corresponding
entities. For instance, in our example, the person entity, which we are
trying to present as "person" objectclass, resides in two tables (persons
and phones), and is identified by the persons.id column (that we will call
the primary key for this entity). Keytbl and keycol thus contain "persons"
(name of the table), and "id" (name of the column).
ldap_attr_mappings (some columns are not listed for clarity)
-----------
id=1
oc_map_id=1
name="cn"
sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
from_tbls="persons"
join_where=NULL
************
id=<n>
oc_map_id=1
name="telephoneNumber"
sel_expr="phones.phone"
from_tbls="persons,phones"
join_where="phones.pers_id=persons.id"
This table defines mappings between LDAP attributes and SQL queries that
load their values. Note that, unlike LDAP schema, these are not attribute
types - the attribute "cn" for "person" objectclass can have its values in
different tables than "cn" for some other objectclass, so attribute
mappings depend on objectclass mappings (unlike attribute types in LDAP
schema, which are indifferent to objectclasses). Thus, we have oc_map_id
column with link to oc_mappings table.
Now we cut the SQL query that loads values for a given attribute into 3
parts. First goes into sel_expr column - this is the expression we had
between SELECT and FROM keywords, which defines WHAT to load. Next is
table list - text between FROM and WHERE keywords. It may contain aliases
for convenience (see examples). The last is part of the where clause,
which (if it exists at all) expresses the condition for joining the table
containing values with the table containing the primary key (foreign key
equality and such). If values are in the same table as the primary key,
then this column is left NULL (as for cn attribute above).
Having this information in parts, we are able to not only construct queries
that load attribute values by id of entry (for this we could store SQL
query as a whole), but to construct queries that load id's of objects that
correspond to a given search filter (or at least part of it). See below
for examples.
ldap_entries
------------
id=1
dn=<dn you choose>
oc_map_id=...
parent=<parent record id>
keyval=<value of primary key>
This table defines mappings between DNs of entries in your LDAP tree, and
values of primary keys for corresponding relational data. It has recursive
structure (parent column references id column of the same table), which
allows you to add any tree structure(s) to your flat relational data.
Having id of objectclass mapping, we can determine table and column for
primary key, and keyval stores value of it, thus defining the exact tuple
corresponding to the LDAP entry with this DN.
Note that such design (see exact SQL table creation query) implies one
important constraint - the key must be an integer. But all that I know
about well-designed schemas makes me think that it's not very narrow ;) If
anyone needs support for different types for keys - he may want to write a
patch, and submit it to OpenLDAP ITS, then I'll include it.
Also, several people complained that they don't really need very structured
trees, and they don't want to update one more table every time they add or
delete an instance in the relational schema. Those people can use a view
instead of a real table for ldap_entries, something like this (by Robin
Elfrink):
CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
AS SELECT (1000000000+userid),
UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
1, 0, userid FROM unixusers UNION
SELECT (2000000000+groupnummer),
UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
2, 0, groupnummer FROM groups;
Typical SQL backend operation
Having metainformation loaded, the SQL backend uses these tables to
determine a set of primary keys of candidates (depending on search scope
and filter). It tries to do it for each objectclass registered in
ldap_objclasses.
Example: for our query with filter (telephoneNumber=123*) we would get the
following query generated (which loads candidate IDs)
SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
ldap_entries.dn AS dn
FROM ldap_entries,persons,phones
WHERE persons.id=ldap_entries.keyval
AND ldap_entries.objclass=?
AND ldap_entries.parent=?
AND phones.pers_id=persons.id
AND (phones.phone LIKE '123%')
(for ONELEVEL search) or "... AND dn=?" (for BASE search) or "... AND dn
LIKE '%?'" (for SUBTREE)
Then, for each candidate, we load the requested attributes using per-
attribute queries like
SELECT phones.phone AS telephoneNumber
FROM persons,phones
WHERE persons.id=? AND phones.pers_id=persons.id
Then, we use test_filter() from the frontend API to test the entry for a
full LDAP search filter match (since we cannot effectively make sense of
SYNTAX of corresponding LDAP schema attribute, we translate the filter into
the most relaxed SQL condition to filter candidates), and send it to the
user.
ADD, DELETE, MODIFY operations are also performed on per-attribute
metainformation (add_proc etc.). In those fields one can specify an SQL
statement or stored procedure call which can add, or delete given values of
a given attribute, using the given entry keyval (see examples -- mostly
ORACLE and MSSQL - since there're no stored procs in mySQL).
We just add more columns to oc_mappings and attr_mappings, holding
statements to execute (like create_proc, add_proc, del_proc etc.), and
flags governing the order of parameters passed to those statements. Please
see samples to find out what are the parameters passed, and other
information on this matter - they are self-explanatory for those familiar
with concept expressed above.
common techniques (referrals, multiclassing etc.)
First of all, let's remember that among other major differences to the
complete LDAP data model, the concept above does not directly support such
things as multiple objectclasses per entry, and referrals. Fortunately,
they are easy to adopt in this scheme. The SQL backend suggests two more
tables being added to the schema -
ldap_entry_objectclasses(entry_id,oc_name), and
ldap_referrals(entry_id,url).
The first contains any number of objectclass names that corresponding
entries will be found by, in addition to that mentioned in mapping. The
SQL backend automatically adds attribute mapping for the "objectclass"
attribute to each objectclass mapping that loads values from this table.
So, you may, for instance, have a mapping for inetOrgPerson, and use it for
queries for "person" objectclass...
The second table contains any number of referrals associated with a given
entry. The SQL backend automatically adds attribute mapping for "ref"
attribute to each objectclass mapping that loads values from this table.
So, if you add objectclass "referral" to this entry, and make one or more
tuples in ldap_referrals for this entry (they will be seen as values of
"ref" attribute), you will have slapd return a referral, as described in
the Administrators Guide.
EXAMPLES
There are example SQL modules in the slapd/back-sql/rdbms_depend/ directory
in the OpenLDAP source tree.
FILES
/usr/internet/openldap/etc/slapd.conf
default slapd configuration file
SEE ALSO
slapd.conf(5), slapd(8).
|
Index for
Section 5 |
|
|
Alphabetical
listing for S |
|
 |
Top of
page |
|