1
.TH SLAPD-SQL 5 "RELEASEDATE" "OpenLDAP LDVERSION"
2
.\" $OpenLDAP: pkg/ldap/doc/man/man5/slapd-sql.5,v 1.26.4.2 2007/08/31 23:13:53 quanah Exp $
4
slapd-sql \- SQL backend to slapd
8
The primary purpose of this
10
backend is to PRESENT information stored in some RDBMS as an LDAP subtree
11
without any programming (some SQL and maybe stored procedures can't be
12
considered programming, anyway ;).
14
That is, for example, when you (some ISP) have account information you
15
use in an RDBMS, and want to use modern solutions that expect such
16
information in LDAP (to authenticate users, make email lookups etc.).
17
Or you want to synchronize or distribute information between different
18
sites/applications that use RDBMSes and/or LDAP.
21
It is NOT designed as a general-purpose backend that uses RDBMS instead
22
of BerkeleyDB (as the standard BDB backend does), though it can be
23
used as such with several limitations.
24
You can take a look at
25
.B http://www.openldap.org/faq/index.cgi?file=378
26
(OpenLDAP FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional
27
databases) to find out more on this point.
29
The idea (detailed below) is to use some meta-information to translate
30
LDAP queries to SQL queries, leaving relational schema untouched, so
31
that old applications can continue using it without any
33
This allows SQL and LDAP applications to inter-operate without
34
replication, and exchange data as needed.
36
The SQL backend is designed to be tunable to virtually any relational
37
schema without having to change source (through that meta-information
39
Also, it uses ODBC to connect to RDBMSes, and is highly configurable
40
for SQL dialects RDBMSes may use, so it may be used for integration
41
and distribution of data on different RDBMSes, OSes, hosts etc., in
42
other words, in highly heterogeneous environment.
44
This backend is \fIexperimental\fP.
48
options apply to the SQL backend database, which means that
49
they must follow a "database sql" line and come before any
50
subsequent "backend" or "database" lines.
51
Other database options not specific to this backend are described
55
.SH DATA SOURCE CONFIGURATION
58
.B dbname <datasource name>
59
The name of the ODBC datasource to use.
63
.B dbpasswd <password>
67
The three above options are generally unneeded, because this information
68
is taken from the datasource specified by the
71
They allow to override datasource settings.
72
Also, several RDBMS' drivers tend to require explicit passing of user/password,
73
even if those are given in datasource (Note:
75
is currently ignored).
77
.SH SCOPING CONFIGURATION
78
These options specify SQL query templates for scoping searches.
81
.B subtree_cond <SQL expression>
82
Specifies a where-clause template used to form a subtree search condition
84
It may differ from one SQL dialect to another (see samples).
85
By default, it is constructed based on the knowledge about
86
how to normalize DN values (e.g.
87
\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%',?)"\fP);
88
see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
89
and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.
92
.B children_cond <SQL expression>
93
Specifies a where-clause template used to form a children search condition
95
It may differ from one SQL dialect to another (see samples).
96
By default, it is constructed based on the knowledge about
97
how to normalize DN values (e.g.
98
\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"\fP);
99
see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
100
and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.
103
.B use_subtree_shortcut { YES | no }
104
Do not use the subtree condition when the searchBase is the database
105
suffix, and the scope is subtree; rather collect all entries.
108
.SH STATEMENT CONFIGURATION
109
These options specify SQL query templates for loading schema mapping
110
meta-information, adding and deleting entries to ldap_entries, etc.
111
All these and subtree_cond should have the given default values.
112
For the current value it is recommended to look at the sources,
113
or in the log output when slapd starts with "-d 5" or greater.
114
Note that the parameter number and order must not be changed.
117
.B oc_query <SQL expression>
118
The query that is used to collect the objectClass mapping data
119
from table \fIldap_oc_mappings\fP; see "METAINFORMATION USED" for details.
121
\fB"SELECT id, name, keytbl, keycol, create_proc, delete_proc, expect_return
122
FROM ldap_oc_mappings"\fP.
125
.B at_query <SQL expression>
126
The query that is used to collect the attributeType mapping data
127
from table \fIldap_attr_mappings\fP; see "METAINFORMATION USED" for details.
129
\fB"SELECT name, sel_expr, from_tbls, join_where, add_proc, delete_proc,
130
param_order, expect_return FROM ldap_attr_mappings WHERE oc_map_id=?"\fP.
133
.B id_query <SQL expression>
134
The query that is used to map a DN to an entry
135
in table \fIldap_entries\fP; see "METAINFORMATION USED" for details.
137
\fB"SELECT id,keyval,oc_map_id,dn FROM ldap_entries WHERE <DN match expr>"\fP,
138
where \fB<DN match expr>\fP is constructed based on the knowledge about
139
how to normalize DN values (e.g. \fB"dn=?"\fP if no means to uppercase
140
strings are available; typically, \fB"<upper_func>(dn)=?"\fP is used);
141
see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
142
and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.
145
.B insentry_stmt <SQL expression>
146
The statement that is used to insert a new entry
147
in table \fIldap_entries\fP; see "METAINFORMATION USED" for details.
149
\fB"INSERT INTO ldap_entries (dn, oc_map_id, parent, keyval) VALUES
153
.B delentry_stmt <SQL expression>
154
The statement that is used to delete an existing entry
155
from table \fIldap_entries\fP; see "METAINFORMATION USED" for details.
157
\fB"DELETE FROM ldap_entries WHERE id=?"\fP.
160
.B delobjclasses_stmt <SQL expression>
161
The statement that is used to delete an existing entry's ID
162
from table \fIldap_objclasses\fP; see "METAINFORMATION USED" for details.
164
\fB"DELETE FROM ldap_entry_objclasses WHERE entry_id=?"\fP.
167
.SH HELPER CONFIGURATION
168
These statements are used to modify the default behavior of the backend
169
according to issues of the dialect of the RDBMS.
170
The first options essentially refer to string and DN normalization
171
when building filters.
172
LDAP normalization is more than upper- (or lower-)casing everything;
173
however, as a reasonable trade-off, for case-sensitive RDBMSes the backend
174
can be instructed to uppercase strings and DNs by providing
175
the \fBupper_func\fP directive.
176
Some RDBMSes, to use functions on arbitrary data types, e.g. string
177
constants, requires a cast, which is triggered
178
by the \fBupper_needs_cast\fP directive.
179
If required, a string cast function can be provided as well,
180
by using the \fBstrcast_func\fP directive.
181
Finally, a custom string concatenation pattern may be required;
182
it is provided by the \fBconcat_pattern\fP directive.
185
.B upper_func <SQL function name>
186
Specifies the name of a function that converts a given value to uppercase.
187
This is used for case insensitive matching when the RDBMS is case sensitive.
188
It may differ from one SQL dialect to another (e.g. \fBUCASE\fP, \fBUPPER\fP
189
or whatever; see samples). By default, none is used, i.e. strings are not
190
uppercased, so matches may be case sensitive.
193
.B upper_needs_cast { NO | yes }
194
Set this directive to
198
needs an explicit cast when applied to literal strings.
200
.B CAST (<arg> AS VARCHAR(<max DN length>))
203
is builtin in back-sql; see macro
204
.B BACKSQL_MAX_DN_LEN
205
(currently 255; note that slapd's builtin limit, in macro
206
.BR SLAP_LDAPDN_MAXLEN ,
208
This is \fIexperimental\fP and may change in future releases.
211
.B strcast_func <SQL function name>
212
Specifies the name of a function that converts a given value to a string
213
for appropriate ordering. This is used in "SELECT DISTINCT" statements
214
for strongly typed RDBMSes with little implicit casting (like PostgreSQL),
215
when a literal string is specified.
216
This is \fIexperimental\fP and may change in future releases.
219
.B concat_pattern <pattern>
220
This statement defines the
222
that is used to concatenate strings. The
224
MUST contain two question marks, '?', that will be replaced
225
by the two strings that must be concatenated. The default value is
227
a form that is known to be highly portable (IBM db2, PostgreSQL) is
229
but an explicit cast may be required when operating on literal strings:
230
.BR "CAST(?||? AS VARCHAR(<length>))".
231
On some RDBMSes (IBM db2, MSSQL) the form
233
is known to work as well.
234
Carefully check the documentation of your RDBMS or stay with the examples
236
This is \fIexperimental\fP and may change in future releases.
239
.B aliasing_keyword <string>
240
Define the aliasing keyword. Some RDBMSes use the word "\fIAS\fP"
241
(the default), others don't use any.
244
.B aliasing_quote <string>
245
Define the quoting char of the aliasing keyword. Some RDBMSes
246
don't require any (the default), others may require single
250
.B has_ldapinfo_dn_ru { NO | yes }
251
Explicitly inform the backend whether the dn_ru column
252
(DN in reverse uppercased form) is present in table \fIldap_entries\fP.
253
Overrides automatic check (this is required, for instance,
254
by PostgreSQL/unixODBC).
255
This is \fIexperimental\fP and may change in future releases.
258
.B fail_if_no_mapping { NO | yes }
261
it forces \fIattribute\fP write operations to fail if no appropriate
262
mapping between LDAP attributes and SQL data is available.
263
The default behavior is to ignore those changes that cannot be mapped.
264
It has no impact on objectClass mapping, i.e. if the
265
.I structuralObjectClass
266
of an entry cannot be mapped to SQL by looking up its name
267
in ldap_oc_mappings, an
269
operation will fail regardless of the
270
.B fail_if_no_mapping
271
switch; see section "METAINFORMATION USED" for details.
272
This is \fIexperimental\fP and may change in future releases.
275
.B allow_orphans { NO | yes }
278
orphaned entries (i.e. without the parent entry in the database)
279
can be added. This option should be used with care, possibly
280
in conjunction with some special rule on the RDBMS side that
281
dynamically creates the missing parent.
284
.B baseObject [ <filename> ]
285
Instructs the database to create and manage an in-memory baseObject
286
entry instead of looking for one in the RDBMS.
289
argument is given, the entry is read from that file in
291
format; otherwise, an entry with objectClass \fBextensibleObject\fP
292
is created based on the contents of the RDN of the \fIbaseObject\fP.
293
This is particularly useful when \fIldap_entries\fP
294
information is stored in a view rather than in a table, and
296
is not supported for views, so that the view can only specify
297
one rule to compute the entry structure for one objectClass.
298
This topic is discussed further in section "METAINFORMATION USED".
299
This is \fIexperimental\fP and may change in future releases.
302
.B create_needs_select { NO | yes }
303
Instructs the database whether or not entry creation
304
in table \fIldap_entries\fP needs a subsequent select to collect
305
the automatically assigned ID, instead of being returned
306
by a stored procedure.
309
.B fetch_attrs <attrlist>
311
.B fetch_all_attrs { NO | yes }
313
The first statement allows to provide a list of attributes that
314
must always be fetched in addition to those requested by any specific
315
operation, because they are required for the proper usage of the
316
backend. For instance, all attributes used in ACLs should be listed
317
here. The second statement is a shortcut to require all attributes
318
to be always loaded. Note that the dynamically generated attributes,
319
e.g. \fIhasSubordinates\fP, \fIentryDN\fP and other implementation
320
dependent attributes are \fBNOT\fP generated at this point, for
321
consistency with the rest of slapd. This may change in the future.
325
.B check_schema { YES | no }
326
Instructs the database to check schema adherence of entries after
327
modifications, and structural objectClass chain when entries are built.
328
By default it is set to
332
.B sqllayer <name> [...]
333
Loads the layer \fB<name>\fP onto a stack of helpers that are used
334
to map DNs from LDAP to SQL representation and vice-versa.
335
Subsequent args are passed to the layer configuration routine.
336
This is \fIhighly experimental\fP and should be used with extreme care.
337
The API of the layers is not frozen yet, so it is unpublished.
339
.SH METAINFORMATION USED
341
Almost everything mentioned later is illustrated in examples located
343
.B servers/slapd/back-sql/rdbms_depend/
344
directory in the OpenLDAP source tree, and contains scripts for
345
generating sample database for Oracle, MS SQL Server, mySQL and more
346
(including PostgreSQL and IBM db2).
348
The first thing that one must arrange is what set of LDAP
349
object classes can present your RDBMS information.
351
The easiest way is to create an objectClass for each entity you had in
352
ER-diagram when designing your relational schema.
353
Any relational schema, no matter how normalized it is, was designed
354
after some model of your application's domain (for instance, accounts,
355
services etc. in ISP), and is used in terms of its entities, not just
356
tables of normalized schema.
357
It means that for every attribute of every such instance there is an
358
effective SQL query that loads its values.
360
Also you might want your object classes to conform to some of the standard
361
schemas like inetOrgPerson etc.
363
Nevertheless, when you think it out, we must define a way to translate
364
LDAP operation requests to (a series of) SQL queries.
365
Let us deal with the SEARCH operation.
368
Let's suppose that we store information about persons working in our
369
organization in two tables:
373
---------- -------------
374
id integer id integer
375
first_name varchar pers_id integer references persons(id)
376
last_name varchar phone
381
(PHONES contains telephone numbers associated with persons).
382
A person can have several numbers, then PHONES contains several
383
records with corresponding pers_id, or no numbers (and no records in
384
PHONES with such pers_id).
385
An LDAP objectclass to present such information could look like this:
391
MAY telephoneNumber $ firstName $ lastName
395
To fetch all values for cn attribute given person ID, we construct the
399
SELECT CONCAT(persons.first_name,' ',persons.last_name)
400
AS cn FROM persons WHERE persons.id=?
403
for telephoneNumber we can use:
406
SELECT phones.phone AS telephoneNumber FROM persons,phones
407
WHERE persons.id=phones.pers_id AND persons.id=?
410
If we wanted to service LDAP requests with filters like
411
(telephoneNumber=123*), we would construct something like:
414
SELECT ... FROM persons,phones
415
WHERE persons.id=phones.pers_id
417
AND phones.phone like '%1%2%3%'
420
(note how the telephoneNumber match is expanded in multiple wildcards
421
to account for interspersed ininfluential chars like spaces, dashes
422
and so; this occurs by design because telephoneNumber is defined after
423
a specially recognized syntax).
424
So, if we had information about what tables contain values for each
425
attribute, how to join these tables and arrange these values, we could
426
try to automatically generate such statements, and translate search
427
filters to SQL WHERE clauses.
429
To store such information, we add three more tables to our schema
430
and fill it with data (see samples):
433
ldap_oc_mappings (some columns are not listed for clarity)
441
This table defines a mapping between objectclass (its name held in the
442
"name" column), and a table that holds the primary key for corresponding
444
For instance, in our example, the person entity, which we are trying
445
to present as "person" objectclass, resides in two tables (persons and
446
phones), and is identified by the persons.id column (that we will call
447
the primary key for this entity).
448
Keytbl and keycol thus contain "persons" (name of the table), and "id"
449
(name of the column).
452
ldap_attr_mappings (some columns are not listed for clarity)
457
sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
463
name="telephoneNumber"
464
sel_expr="phones.phone"
465
from_tbls="persons,phones"
466
join_where="phones.pers_id=persons.id"
469
This table defines mappings between LDAP attributes and SQL queries
470
that load their values.
471
Note that, unlike LDAP schema, these are not
473
- the attribute "cn" for "person" objectclass can
474
have its values in different tables than "cn" for some other objectclass,
475
so attribute mappings depend on objectclass mappings (unlike attribute
476
types in LDAP schema, which are indifferent to objectclasses).
477
Thus, we have oc_map_id column with link to oc_mappings table.
479
Now we cut the SQL query that loads values for a given attribute into 3 parts.
480
First goes into sel_expr column - this is the expression we had
481
between SELECT and FROM keywords, which defines WHAT to load.
482
Next is table list - text between FROM and WHERE keywords.
483
It may contain aliases for convenience (see examples).
484
The last is part of the where clause, which (if it exists at all) expresses the
485
condition for joining the table containing values with the table
486
containing the primary key (foreign key equality and such).
487
If values are in the same table as the primary key, then this column is
488
left NULL (as for cn attribute above).
490
Having this information in parts, we are able to not only construct
491
queries that load attribute values by id of entry (for this we could
492
store SQL query as a whole), but to construct queries that load id's
493
of objects that correspond to a given search filter (or at least part of
495
See below for examples.
503
parent=<parent record id>
504
keyval=<value of primary key>
507
This table defines mappings between DNs of entries in your LDAP tree,
508
and values of primary keys for corresponding relational data.
509
It has recursive structure (parent column references id column of the
510
same table), which allows you to add any tree structure(s) to your
511
flat relational data.
512
Having id of objectclass mapping, we can determine table and column
513
for primary key, and keyval stores value of it, thus defining the exact
514
tuple corresponding to the LDAP entry with this DN.
516
Note that such design (see exact SQL table creation query) implies one
517
important constraint - the key must be an integer.
518
But all that I know about well-designed schemas makes me think that it's
519
not very narrow ;) If anyone needs support for different types for
520
keys - he may want to write a patch, and submit it to OpenLDAP ITS,
521
then I'll include it.
523
Also, several users complained that they don't really need very
524
structured trees, and they don't want to update one more table every
525
time they add or delete an instance in the relational schema.
526
Those people can use a view instead of a real table for ldap_entries, something
527
like this (by Robin Elfrink):
530
CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
532
SELECT 0, UPPER('o=MyCompany,c=NL'),
533
3, 0, 'baseObject' FROM unixusers WHERE userid='root'
535
SELECT (1000000000+userid),
536
UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
537
1, 0, userid FROM unixusers
539
SELECT (2000000000+groupnummer),
540
UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
541
2, 0, groupnummer FROM groups;
545
If your RDBMS does not support
547
in views, only one objectClass can be mapped in
549
and the baseObject cannot be created; in this case, see the
551
directive for a possible workaround.
554
.SH TYPICAL SQL BACKEND OPERATION
555
Having meta-information loaded, the SQL backend uses these tables to
556
determine a set of primary keys of candidates (depending on search
558
It tries to do it for each objectclass registered in ldap_objclasses.
561
for our query with filter (telephoneNumber=123*) we would get the following
562
query generated (which loads candidate IDs)
565
SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
566
ldap_entries.dn AS dn
567
FROM ldap_entries,persons,phones
568
WHERE persons.id=ldap_entries.keyval
569
AND ldap_entries.objclass=?
570
AND ldap_entries.parent=?
571
AND phones.pers_id=persons.id
572
AND (phones.phone LIKE '%1%2%3%')
575
(for ONELEVEL search)
576
or "... AND dn=?" (for BASE search)
577
or "... AND dn LIKE '%?'" (for SUBTREE)
579
Then, for each candidate, we load the requested attributes using
580
per-attribute queries like
583
SELECT phones.phone AS telephoneNumber
585
WHERE persons.id=? AND phones.pers_id=persons.id
588
Then, we use test_filter() from the frontend API to test the entry for a full
589
LDAP search filter match (since we cannot effectively make sense of
590
SYNTAX of corresponding LDAP schema attribute, we translate the filter
591
into the most relaxed SQL condition to filter candidates), and send it to
594
ADD, DELETE, MODIFY and MODRDN operations are also performed on per-attribute
595
meta-information (add_proc etc.).
596
In those fields one can specify an SQL statement or stored procedure
597
call which can add, or delete given values of a given attribute, using
598
the given entry keyval (see examples -- mostly PostgreSQL, ORACLE and MSSQL
599
- since as of this writing there are no stored procs in MySQL).
601
We just add more columns to ldap_oc_mappings and ldap_attr_mappings, holding
602
statements to execute (like create_proc, add_proc, del_proc etc.), and
603
flags governing the order of parameters passed to those statements.
604
Please see samples to find out what are the parameters passed, and other
605
information on this matter - they are self-explanatory for those familiar
606
with the concepts expressed above.
608
.SH COMMON TECHNIQUES
609
First of all, let's recall that among other major differences to the
610
complete LDAP data model, the above illustrated concept does not directly
611
support such features as multiple objectclasses per entry, and referrals.
612
Fortunately, they are easy to adopt in this scheme.
613
The SQL backend requires that one more table is added to the schema:
614
ldap_entry_objectclasses(entry_id,oc_name).
616
That table contains any number of objectclass names that corresponding
617
entries will possess, in addition to that mentioned in mapping.
618
The SQL backend automatically adds attribute mapping for the "objectclass"
619
attribute to each objectclass mapping that loads values from this table.
620
So, you may, for instance, have a mapping for inetOrgPerson, and use it
621
for queries for "person" objectclass...
623
Referrals used to be implemented in a loose manner by adding an extra
624
table that allowed any entry to host a "ref" attribute, along with
625
a "referral" extra objectClass in table ldap_entry_objclasses.
626
In the current implementation, referrals are treated like any other
627
user-defined schema, since "referral" is a structural objectclass.
628
The suggested practice is to define a "referral" entry in ldap_oc_mappings,
629
holding a naming attribute, e.g. "ou" or "cn", a "ref" attribute,
630
containing the url; in case multiple referrals per entry are needed,
631
a separate table for urls can be created, where urls are mapped
632
to the respective entries.
633
The use of the naming attribute usually requires to add
634
an "extensibleObject" value to ldap_entry_objclasses.
638
As previously stated, this backend should not be considered
639
a replacement of other data storage backends, but rather a gateway
640
to existing RDBMS storages that need to be published in LDAP form.
642
The \fBhasSubordintes\fP operational attribute is honored by back-sql
643
in search results and in compare operations; it is partially honored
644
also in filtering. Owing to design limitations, a (brain-dead?) filter
646
\fB(!(hasSubordinates=TRUE))\fP
647
will give no results instead of returning all the leaf entries, because
648
it actually expands into \fB... AND NOT (1=1)\fP.
649
If you need to find all the leaf entries, please use
650
\fB(hasSubordinates=FALSE)\fP
653
A directoryString value of the form "__First___Last_"
654
(where underscores mean spaces, ASCII 0x20 char) corresponds
655
to its prettified counterpart "First_Last"; this is not currently
656
honored by back-sql if non-prettified data is written via RDBMS;
657
when non-prettified data is written through back-sql, the prettified
658
values are actually used instead.
663
.B ldap_entry_objclasses
664
table is empty, filters on the
666
attribute erroneously result in no candidates.
667
A workaround consists in adding at least one row to that table,
668
no matter if valid or not.
671
.SH PROXY CACHE OVERLAY
672
The proxy cache overlay
673
allows caching of LDAP search requests (queries) in a local database.
678
There are example SQL modules in the slapd/back-sql/rdbms_depend/
679
directory in the OpenLDAP source tree.
683
backend honors access control semantics as indicated in
687
access privilege when enabled at compile time).
692
default slapd configuration file
added
removed
doc/man/man5/slapd-sql.5
