1SLAPD-SQL(5) File Formats Manual SLAPD-SQL(5)
2
6 slapd-sql - SQL backend to slapd
7
9 /etc/openldap/slapd.conf
10
12 The primary purpose of this slapd(8) backend is to PRESENT information
13 stored in some RDBMS as an LDAP subtree without any programming (some
14 SQL and maybe stored procedures can't be considered programming, anyway
15 ;).
16 That is, for example, when you (some ISP) have account information you
18 use in an RDBMS, and want to use modern solutions that expect such
19 information in LDAP (to authenticate users, make email lookups etc.).
20 Or you want to synchronize or distribute information between different
21 sites/applications that use RDBMSes and/or LDAP. Or whatever else...
22 It is NOT designed as a general-purpose backend that uses RDBMS instead
24 of BerkeleyDB (as the standard BDB backend does), though it can be used
25 as such with several limitations. You can take a look at
26 http://www.openldap.org/faq/index.cgi?file=378 (OpenLDAP
27 FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional databases) to
28 find out more on this point.
29 The idea (detailed below) is to use some meta-information to translate
31 LDAP queries to SQL queries, leaving relational schema untouched, so
32 that old applications can continue using it without any modifications.
33 This allows SQL and LDAP applications to inter-operate without replica‐
34 tion, and exchange data as needed.
35 The SQL backend is designed to be tunable to virtually any relational
37 schema without having to change source (through that meta-information
38 mentioned). Also, it uses ODBC to connect to RDBMSes, and is highly
39 configurable for SQL dialects RDBMSes may use, so it may be used for
40 integration and distribution of data on different RDBMSes, OSes, hosts
41 etc., in other words, in highly heterogeneous environment.
42 This backend is experimental.
44
46 These slapd.conf options apply to the SQL backend database, which means
47 that they must follow a "database sql" line and come before any subse‐
48 quent "backend" or "database" lines. Other database options not spe‐
49 cific to this backend are described in the slapd.conf(5) manual page.
50
52 dbname <datasource name>
53 The name of the ODBC datasource to use.
54 dbhost <hostname>
56 dbpasswd <password>
57 dbuser <username>
58 The three above options are generally unneeded, because this
59 information is taken from the datasource specified by the dbname
60 directive. They allow to override datasource settings. Also,
61 several RDBMS' drivers tend to require explicit passing of
62 user/password, even if those are given in datasource (Note:
63 dbhost is currently ignored).
64
66 These options specify SQL query templates for scoping searches.
67 subtree_cond <SQL expression>
70 Specifies a where-clause template used to form a subtree search
71 condition (dn="(.+,)?<dn>$"). It may differ from one SQL
72 dialect to another (see samples). By default, it is constructed
73 based on the knowledge about how to normalize DN values (e.g.
74 "<upper_func>(ldap_entries.dn) LIKE CONCAT('%',?)"); see
75 upper_func, upper_needs_cast, concat_pattern and strcast_func in
76 "HELPER CONFIGURATION" for details.
77 children_cond <SQL expression>
80 Specifies a where-clause template used to form a children search
81 condition (dn=".+,<dn>$"). It may differ from one SQL dialect
82 to another (see samples). By default, it is constructed based
83 on the knowledge about how to normalize DN values (e.g.
84 "<upper_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"); see
85 upper_func, upper_needs_cast, concat_pattern and strcast_func in
86 "HELPER CONFIGURATION" for details.
87 use_subtree_shortcut { YES | no }
90 Do not use the subtree condition when the searchBase is the
91 database suffix, and the scope is subtree; rather collect all
92 entries.
93
96 These options specify SQL query templates for loading schema mapping
97 meta-information, adding and deleting entries to ldap_entries, etc.
98 All these and subtree_cond should have the given default values. For
99 the current value it is recommended to look at the sources, or in the
100 log output when slapd starts with "-d 5" or greater. Note that the
101 parameter number and order must not be changed.
102 oc_query <SQL expression>
105 The query that is used to collect the objectClass mapping data
106 from table ldap_oc_mappings; see "METAINFORMATION USED" for
107 details. The default is "SELECT id, name, keytbl, keycol, cre‐
108 ate_proc, delete_proc, expect_return FROM ldap_oc_mappings".
109 at_query <SQL expression>
112 The query that is used to collect the attributeType mapping data
113 from table ldap_attr_mappings; see "METAINFORMATION USED" for
114 details. The default is "SELECT name, sel_expr, from_tbls,
115 join_where, add_proc, delete_proc, param_order, expect_return
116 FROM ldap_attr_mappings WHERE oc_map_id=?".
117 id_query <SQL expression>
120 The query that is used to map a DN to an entry in table
121 ldap_entries; see "METAINFORMATION USED" for details. The
122 default is "SELECT id,keyval,oc_map_id,dn FROM ldap_entries
123 WHERE <DN match expr>", where <DN match expr> is constructed
124 based on the knowledge about how to normalize DN values (e.g.
125 "dn=?" if no means to uppercase strings are available; typi‐
126 cally, "<upper_func>(dn)=?" is used); see upper_func,
127 upper_needs_cast, concat_pattern and strcast_func in "HELPER
128 CONFIGURATION" for details.
129 insentry_stmt <SQL expression>
132 The statement that is used to insert a new entry in table
133 ldap_entries; see "METAINFORMATION USED" for details. The
134 default is "INSERT INTO ldap_entries (dn, oc_map_id, parent,
135 keyval) VALUES (?, ?, ?, ?)".
136 delentry_stmt <SQL expression>
139 The statement that is used to delete an existing entry from ta‐
140 ble ldap_entries; see "METAINFORMATION USED" for details. The
141 default is "DELETE FROM ldap_entries WHERE id=?".
142 delobjclasses_stmt <SQL expression>
145 The statement that is used to delete an existing entry's ID from
146 table ldap_objclasses; see "METAINFORMATION USED" for details.
147 The default is "DELETE FROM ldap_entry_objclasses WHERE
148 entry_id=?".
149
152 These statements are used to modify the default behavior of the backend
153 according to issues of the dialect of the RDBMS. The first options
154 essentially refer to string and DN normalization when building filters.
155 LDAP normalization is more than upper- (or lower-)casing everything;
156 however, as a reasonable trade-off, for case-sensitive RDBMSes the
157 backend can be instructed to uppercase strings and DNs by providing the
158 upper_func directive. Some RDBMSes, to use functions on arbitrary data
159 types, e.g. string constants, requires a cast, which is triggered by
160 the upper_needs_cast directive. If required, a string cast function
161 can be provided as well, by using the strcast_func directive. Finally,
162 a custom string concatenation pattern may be required; it is provided
163 by the concat_pattern directive.
164 upper_func <SQL function name>
167 Specifies the name of a function that converts a given value to
168 uppercase. This is used for case insensitive matching when the
169 RDBMS is case sensitive. It may differ from one SQL dialect to
170 another (e.g. UCASE, UPPER or whatever; see samples). By
171 default, none is used, i.e. strings are not uppercased, so
172 matches may be case sensitive.
173 upper_needs_cast { NO | yes }
176 Set this directive to yes if upper_func needs an explicit cast
177 when applied to literal strings. A cast in the form CAST (<arg>
178 AS VARCHAR(<max DN length>)) is used, where <max DN length> is
179 builtin in back-sql; see macro BACKSQL_MAX_DN_LEN (currently
180 255; note that slapd's builtin limit, in macro
181 SLAP_LDAPDN_MAXLEN, is set to 8192). This is experimental and
182 may change in future releases.
183 strcast_func <SQL function name>
186 Specifies the name of a function that converts a given value to
187 a string for appropriate ordering. This is used in "SELECT DIS‐
188 TINCT" statements for strongly typed RDBMSes with little
189 implicit casting (like PostgreSQL), when a literal string is
190 specified. This is experimental and may change in future
191 releases.
192 concat_pattern <pattern>
195 This statement defines the pattern that is used to concatenate
196 strings. The pattern MUST contain two question marks, '?', that
197 will be replaced by the two strings that must be concatenated.
198 The default value is CONCAT(?,?); a form that is known to be
199 highly portable (IBM db2, PostgreSQL) is ?||?, but an explicit
200 cast may be required when operating on literal strings:
201 CAST(?||? AS VARCHAR(<length>)). On some RDBMSes (IBM db2,
202 MSSQL) the form ?+? is known to work as well. Carefully check
203 the documentation of your RDBMS or stay with the examples for
204 supported ones. This is experimental and may change in future
205 releases.
206 aliasing_keyword <string>
209 Define the aliasing keyword. Some RDBMSes use the word "AS"
210 (the default), others don't use any.
211 aliasing_quote <string>
214 Define the quoting char of the aliasing keyword. Some RDBMSes
215 don't require any (the default), others may require single or
216 double quotes.
217 has_ldapinfo_dn_ru { NO | yes }
220 Explicitly inform the backend whether the dn_ru column (DN in
221 reverse uppercased form) is present in table ldap_entries.
222 Overrides automatic check (this is required, for instance, by
223 PostgreSQL/unixODBC). This is experimental and may change in
224 future releases.
225 fail_if_no_mapping { NO | yes }
228 When set to yes it forces attribute write operations to fail if
229 no appropriate mapping between LDAP attributes and SQL data is
230 available. The default behavior is to ignore those changes that
231 cannot be mapped. It has no impact on objectClass mapping, i.e.
232 if the structuralObjectClass of an entry cannot be mapped to SQL
233 by looking up its name in ldap_oc_mappings, an add operation
234 will fail regardless of the fail_if_no_mapping switch; see sec‐
235 tion "METAINFORMATION USED" for details. This is experimental
236 and may change in future releases.
237 allow_orphans { NO | yes }
240 When set to yes orphaned entries (i.e. without the parent entry
241 in the database) can be added. This option should be used with
242 care, possibly in conjunction with some special rule on the
243 RDBMS side that dynamically creates the missing parent.
244 baseObject [ <filename> ]
247 Instructs the database to create and manage an in-memory baseOb‐
248 ject entry instead of looking for one in the RDBMS. If the
249 (optional) <filename> argument is given, the entry is read from
250 that file in LDIF(5) format; otherwise, an entry with object‐
251 Class extensibleObject is created based on the contents of the
252 RDN of the baseObject. This is particularly useful when
253 ldap_entries information is stored in a view rather than in a
254 table, and union is not supported for views, so that the view
255 can only specify one rule to compute the entry structure for one
256 objectClass. This topic is discussed further in section
257 "METAINFORMATION USED". This is experimental and may change in
258 future releases.
259 create_needs_select { NO | yes }
262 Instructs the database whether or not entry creation in table
263 ldap_entries needs a subsequent select to collect the automati‐
264 cally assigned ID, instead of being returned by a stored proce‐
265 dure.
266 fetch_attrs <attrlist>
269 fetch_all_attrs { NO | yes }
270 The first statement allows to provide a list of attributes that
271 must always be fetched in addition to those requested by any
272 specific operation, because they are required for the proper
273 usage of the backend. For instance, all attributes used in ACLs
274 should be listed here. The second statement is a shortcut to
275 require all attributes to be always loaded. Note that the
276 dynamically generated attributes, e.g. hasSubordinates, entryDN
277 and other implementation dependent attributes are NOT generated
278 at this point, for consistency with the rest of slapd. This may
279 change in the future.
280 check_schema { YES | no }
283 Instructs the database to check schema adherence of entries
284 after modifications, and structural objectClass chain when
285 entries are built. By default it is set to yes.
286 sqllayer <name> [...]
289 Loads the layer <name> onto a stack of helpers that are used to
290 map DNs from LDAP to SQL representation and vice-versa. Subse‐
291 quent args are passed to the layer configuration routine. This
292 is highly experimental and should be used with extreme care.
293 The API of the layers is not frozen yet, so it is unpublished.
294
297 Almost everything mentioned later is illustrated in examples located in
298 the servers/slapd/back-sql/rdbms_depend/ directory in the OpenLDAP
299 source tree, and contains scripts for generating sample database for
300 Oracle, MS SQL Server, mySQL and more (including PostgreSQL and IBM
301 db2).
302 The first thing that one must arrange is what set of LDAP object
304 classes can present your RDBMS information.
305 The easiest way is to create an objectClass for each entity you had in
307 ER-diagram when designing your relational schema. Any relational
308 schema, no matter how normalized it is, was designed after some model
309 of your application's domain (for instance, accounts, services etc. in
310 ISP), and is used in terms of its entities, not just tables of normal‐
311 ized schema. It means that for every attribute of every such instance
312 there is an effective SQL query that loads its values.
313 Also you might want your object classes to conform to some of the stan‐
315 dard schemas like inetOrgPerson etc.
316 Nevertheless, when you think it out, we must define a way to translate
318 LDAP operation requests to (a series of) SQL queries. Let us deal with
319 the SEARCH operation.
320 Example: Let's suppose that we store information about persons working
322 in our organization in two tables:
323 PERSONS PHONES
325 ---------- -------------
326 id integer id integer
327 first_name varchar pers_id integer references persons(id)
328 last_name varchar phone
329 middle_name varchar
330 ...
331 (PHONES contains telephone numbers associated with persons). A person
333 can have several numbers, then PHONES contains several records with
334 corresponding pers_id, or no numbers (and no records in PHONES with
335 such pers_id). An LDAP objectclass to present such information could
336 look like this:
337 person
339 -------
340 MUST cn
341 MAY telephoneNumber $ firstName $ lastName
342 ...
343 To fetch all values for cn attribute given person ID, we construct the
345 query:
346 SELECT CONCAT(persons.first_name,' ',persons.last_name)
348 AS cn FROM persons WHERE persons.id=?
349 for telephoneNumber we can use:
351 SELECT phones.phone AS telephoneNumber FROM persons,phones
353 WHERE persons.id=phones.pers_id AND persons.id=?
354 If we wanted to service LDAP requests with filters like (telephoneNum‐
356 ber=123*), we would construct something like:
357 SELECT ... FROM persons,phones
359 WHERE persons.id=phones.pers_id
360 AND persons.id=?
361 AND phones.phone like '%1%2%3%'
362 (note how the telephoneNumber match is expanded in multiple wildcards
364 to account for interspersed ininfluential chars like spaces, dashes and
365 so; this occurs by design because telephoneNumber is defined after a
366 specially recognized syntax). So, if we had information about what
367 tables contain values for each attribute, how to join these tables and
368 arrange these values, we could try to automatically generate such
369 statements, and translate search filters to SQL WHERE clauses.
370 To store such information, we add three more tables to our schema and
372 fill it with data (see samples):
373 ldap_oc_mappings (some columns are not listed for clarity)
375 ---------------
376 id=1
377 name="person"
378 keytbl="persons"
379 keycol="id"
380 This table defines a mapping between objectclass (its name held in the
382 "name" column), and a table that holds the primary key for correspond‐
383 ing entities. For instance, in our example, the person entity, which
384 we are trying to present as "person" objectclass, resides in two tables
385 (persons and phones), and is identified by the persons.id column (that
386 we will call the primary key for this entity). Keytbl and keycol thus
387 contain "persons" (name of the table), and "id" (name of the column).
388 ldap_attr_mappings (some columns are not listed for clarity)
390 -----------
391 id=1
392 oc_map_id=1
393 name="cn"
394 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
395 from_tbls="persons"
396 join_where=NULL
397 ************
398 id=<n>
399 oc_map_id=1
400 name="telephoneNumber"
401 sel_expr="phones.phone"
402 from_tbls="persons,phones"
403 join_where="phones.pers_id=persons.id"
404 This table defines mappings between LDAP attributes and SQL queries
406 that load their values. Note that, unlike LDAP schema, these are not
407 attribute types - the attribute "cn" for "person" objectclass can have
408 its values in different tables than "cn" for some other objectclass, so
409 attribute mappings depend on objectclass mappings (unlike attribute
410 types in LDAP schema, which are indifferent to objectclasses). Thus,
411 we have oc_map_id column with link to oc_mappings table.
412 Now we cut the SQL query that loads values for a given attribute into 3
414 parts. First goes into sel_expr column - this is the expression we had
415 between SELECT and FROM keywords, which defines WHAT to load. Next is
416 table list - text between FROM and WHERE keywords. It may contain
417 aliases for convenience (see examples). The last is part of the where
418 clause, which (if it exists at all) expresses the condition for joining
419 the table containing values with the table containing the primary key
420 (foreign key equality and such). If values are in the same table as
421 the primary key, then this column is left NULL (as for cn attribute
422 above).
423 Having this information in parts, we are able to not only construct
425 queries that load attribute values by id of entry (for this we could
426 store SQL query as a whole), but to construct queries that load id's of
427 objects that correspond to a given search filter (or at least part of
428 it). See below for examples.
429 ldap_entries
431 ------------
432 id=1
433 dn=<dn you choose>
434 oc_map_id=...
435 parent=<parent record id>
436 keyval=<value of primary key>
437 This table defines mappings between DNs of entries in your LDAP tree,
439 and values of primary keys for corresponding relational data. It has
440 recursive structure (parent column references id column of the same ta‐
441 ble), which allows you to add any tree structure(s) to your flat rela‐
442 tional data. Having id of objectclass mapping, we can determine table
443 and column for primary key, and keyval stores value of it, thus defin‐
444 ing the exact tuple corresponding to the LDAP entry with this DN.
445 Note that such design (see exact SQL table creation query) implies one
447 important constraint - the key must be an integer. But all that I know
448 about well-designed schemas makes me think that it's not very narrow ;)
449 If anyone needs support for different types for keys - he may want to
450 write a patch, and submit it to OpenLDAP ITS, then I'll include it.
451 Also, several users complained that they don't really need very struc‐
453 tured trees, and they don't want to update one more table every time
454 they add or delete an instance in the relational schema. Those people
455 can use a view instead of a real table for ldap_entries, something like
456 this (by Robin Elfrink):
457 CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
459 AS
460 SELECT 0, UPPER('o=MyCompany,c=NL'),
461 3, 0, 'baseObject' FROM unixusers WHERE userid='root'
462 UNION
463 SELECT (1000000000+userid),
464 UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
465 1, 0, userid FROM unixusers
466 UNION
467 SELECT (2000000000+groupnummer),
468 UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
469 2, 0, groupnummer FROM groups;
470 If your RDBMS does not support unions in views, only one objectClass
473 can be mapped in ldap_entries, and the baseObject cannot be created; in
474 this case, see the baseObject directive for a possible workaround.
475
478 Having meta-information loaded, the SQL backend uses these tables to
479 determine a set of primary keys of candidates (depending on search
480 scope and filter). It tries to do it for each objectclass registered
481 in ldap_objclasses.
482 Example: for our query with filter (telephoneNumber=123*) we would get
484 the following query generated (which loads candidate IDs)
485 SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
487 ldap_entries.dn AS dn
488 FROM ldap_entries,persons,phones
489 WHERE persons.id=ldap_entries.keyval
490 AND ldap_entries.objclass=?
491 AND ldap_entries.parent=?
492 AND phones.pers_id=persons.id
493 AND (phones.phone LIKE '%1%2%3%')
494 (for ONELEVEL search) or "... AND dn=?" (for BASE search) or "... AND
496 dn LIKE '%?'" (for SUBTREE)
497 Then, for each candidate, we load the requested attributes using per-
499 attribute queries like
500 SELECT phones.phone AS telephoneNumber
502 FROM persons,phones
503 WHERE persons.id=? AND phones.pers_id=persons.id
504 Then, we use test_filter() from the frontend API to test the entry for
506 a full LDAP search filter match (since we cannot effectively make sense
507 of SYNTAX of corresponding LDAP schema attribute, we translate the fil‐
508 ter into the most relaxed SQL condition to filter candidates), and send
509 it to the user.
510 ADD, DELETE, MODIFY and MODRDN operations are also performed on per-
512 attribute meta-information (add_proc etc.). In those fields one can
513 specify an SQL statement or stored procedure call which can add, or
514 delete given values of a given attribute, using the given entry keyval
515 (see examples -- mostly PostgreSQL, ORACLE and MSSQL - since as of this
516 writing there are no stored procs in MySQL).
517 We just add more columns to ldap_oc_mappings and ldap_attr_mappings,
519 holding statements to execute (like create_proc, add_proc, del_proc
520 etc.), and flags governing the order of parameters passed to those
521 statements. Please see samples to find out what are the parameters
522 passed, and other information on this matter - they are self-explana‐
523 tory for those familiar with the concepts expressed above.
524
526 First of all, let's recall that among other major differences to the
527 complete LDAP data model, the above illustrated concept does not
528 directly support such features as multiple objectclasses per entry, and
529 referrals. Fortunately, they are easy to adopt in this scheme. The
530 SQL backend requires that one more table is added to the schema:
531 ldap_entry_objectclasses(entry_id,oc_name).
532 That table contains any number of objectclass names that corresponding
534 entries will possess, in addition to that mentioned in mapping. The
535 SQL backend automatically adds attribute mapping for the "objectclass"
536 attribute to each objectclass mapping that loads values from this ta‐
537 ble. So, you may, for instance, have a mapping for inetOrgPerson, and
538 use it for queries for "person" objectclass...
539 Referrals used to be implemented in a loose manner by adding an extra
541 table that allowed any entry to host a "ref" attribute, along with a
542 "referral" extra objectClass in table ldap_entry_objclasses. In the
543 current implementation, referrals are treated like any other user-
544 defined schema, since "referral" is a structural objectclass. The sug‐
545 gested practice is to define a "referral" entry in ldap_oc_mappings,
546 holding a naming attribute, e.g. "ou" or "cn", a "ref" attribute, con‐
547 taining the url; in case multiple referrals per entry are needed, a
548 separate table for urls can be created, where urls are mapped to the
549 respective entries. The use of the naming attribute usually requires
550 to add an "extensibleObject" value to ldap_entry_objclasses.
551
554 As previously stated, this backend should not be considered a replace‐
555 ment of other data storage backends, but rather a gateway to existing
556 RDBMS storages that need to be published in LDAP form.
557 The hasSubordintes operational attribute is honored by back-sql in
559 search results and in compare operations; it is partially honored also
560 in filtering. Owing to design limitations, a (brain-dead?) filter of
561 the form (!(hasSubordinates=TRUE)) will give no results instead of
562 returning all the leaf entries, because it actually expands into ...
563 AND NOT (1=1). If you need to find all the leaf entries, please use
564 (hasSubordinates=FALSE) instead.
565 A directoryString value of the form "__First___Last_" (where under‐
567 scores mean spaces, ASCII 0x20 char) corresponds to its prettified
568 counterpart "First_Last"; this is not currently honored by back-sql if
569 non-prettified data is written via RDBMS; when non-prettified data is
570 written through back-sql, the prettified values are actually used
571 instead.
572
575 When the ldap_entry_objclasses table is empty, filters on the object‐
576 Class attribute erroneously result in no candidates. A workaround con‐
577 sists in adding at least one row to that table, no matter if valid or
578 not.
579
582 The proxy cache overlay allows caching of LDAP search requests
583 (queries) in a local database. See slapo-pcache(5) for details.
584
586 There are example SQL modules in the slapd/back-sql/rdbms_depend/
587 directory in the OpenLDAP source tree.
588
590 The sql backend honors access control semantics as indicated in
591 slapd.access(5) (including the disclose access privilege when enabled
592 at compile time).
593
595 /etc/openldap/slapd.conf
596 default slapd configuration file
597
599 slapd.conf(5), slapd(8).
600OpenLDAP 2.4.23 2010/06/30 SLAPD-SQL(5)