SLAPD-SQL(5)              FILE FORMATS               SLAPD-SQL(5)


     slapd-sql - SQL backend to slapd




     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     (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.


     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

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

     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

     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.


     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

       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

OpenLDAP LDVERSION  Last change: RELEASEDATE                    4

SLAPD-SQL(5)              FILE FORMATS               SLAPD-SQL(5)

     for telephoneNumber we can use:

       SELECT AS telephoneNumber FROM persons,phones
        WHERE AND

     If we wanted to service  LDAP  requests  with  filters  like
     (telephoneNumber=123*), we would construct something like:

       SELECT ... FROM persons,phones
          AND 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

     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)

     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 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)
       sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"

OpenLDAP LDVERSION  Last change: RELEASEDATE                    5

SLAPD-SQL(5)              FILE FORMATS               SLAPD-SQL(5)


     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.

       dn=<dn you choose>
       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

     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

       CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
           AS SELECT (1000000000+userid),
       1, 0, userid FROM unixusers UNION
               SELECT (2000000000+groupnummer),
       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,, 'person' AS objectClass,
              ldap_entries.dn AS dn
         FROM ldap_entries,persons,phones
          AND ldap_entries.objclass=?
          AND ldap_entries.parent=?
          AND ( 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 AS telephoneNumber
         FROM persons,phones
        WHERE AND

     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

     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.


     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).


     There  are  example   SQL   modules   in   the   slapd/back-
     sql/rdbms_depend/ directory in the OpenLDAP source tree.


          default slapd configuration file


     slapd.conf(5), slapd(8).

OpenLDAP LDVERSION  Last change: RELEASEDATE                    9

Man(1) output converted with man2html