slapd-sql(5)
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
NAME
slapd-sql - SQL backend to slapd
SYNOPSIS
ETCDIR/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 authenti-
cate users, make email lookups etc.). Or you want to syn-
chronize or distribute information between different
sites/applications that use RDBMSes and/or LDAP. Or what-
ever 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 limita-
tions. 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 con-
nect to RDBMSes, and is highly configurable for SQL dialects
RDBMSes may use, so it may be used for integration and dis-
tribution 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.
OpenLDAP LDVERSION Last change: RELEASEDATE 1
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
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 sub-
tree 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 chil-
dren 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
OpenLDAP LDVERSION Last change: RELEASEDATE 2
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
the RDBMS is case sensitive.
upper_needs_cast { yes | no }
Set this directive to yes if upper_func needs an expli-
cit 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 con-
catenate strings. The pattern MUST contain two ques-
tion 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 port-
able 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 experimen-
tal 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/ direc-
tory in the OpenLDAP source tree, and contains scripts for
generating sample database for Oracle, MS SQL Server, mySQL
OpenLDAP LDVERSION Last change: RELEASEDATE 3
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
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 per-
sons 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=?
OpenLDAP LDVERSION Last change: RELEASEDATE 4
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
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 pri-
mary 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 key-
col 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"
OpenLDAP LDVERSION Last change: RELEASEDATE 5
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
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 objectc-
lasses). 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 key-
words, 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 con-
struct 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
OpenLDAP LDVERSION Last change: RELEASEDATE 6
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
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 can-
didate 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
OpenLDAP LDVERSION Last change: RELEASEDATE 7
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
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 and MODRDN 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 attri-
bute, 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 differ-
ences to the complete LDAP data model, the concept above
does not directly support such things as multiple objectc-
lasses 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.
Caveats
As previously stated, this backend should not be considered
a replacement of other data storage backends, but rather a
gateway to existing RDBMS storages that need to be published
in LDAP form.
OpenLDAP LDVERSION Last change: RELEASEDATE 8
SLAPD-SQL(5) FILE FORMATS SLAPD-SQL(5)
The hasSubordintes operational attribute is honored by
back-sql in search results and in compare operations; it is
partially honored also in filtering. Owing to design limi-
tations, a (braindead) filter of the form
(!(hasSubordinates=TRUE)) will give no results instead of
returning all the leaf entries. If you need to find all the
leaf entries, please use (hasSubordinates=FALSE) instead.
Attribute and ObjectClass inheritance is honored neither in
filters nor in search results. So you may search for
(givenName=Mitya) in the examples, but not for (name=Mitya).
EXAMPLES
There are example SQL modules in the slapd/back-
sql/rdbms_depend/ directory in the OpenLDAP source tree.
FILES
ETCDIR/slapd.conf
default slapd configuration file
SEE ALSO
slapd.conf(5), slapd(8).
OpenLDAP LDVERSION Last change: RELEASEDATE 9
Man(1) output converted with
man2html