Provided by: slapd_2.2.26-5ubuntu2_i386 bug

NAME

       slapd-sql - SQL backend to slapd

SYNOPSIS

       /etc/ldap/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 (IBM db2, PostgreSQL) is ?||?, but  an  explicit
              cast   may  be  required  when  operating  on  literal  strings:
              CAST(?||? AS VARCHAR(<length>)).   On  some  RDBMSes  (IBM  db2,
              MSSQL)  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 attribute 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.   It has no impact on objectClass
              mapping, i.e. if the structuralObjectClass of an entry cannot be
              mapped to SQL by looking up its name in ldap_oc_mappings, an add
              operation will fail regardless of the fail_if_no_mapping switch;
              see   section  "METAINFORMATION  USED"  for  details.   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 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 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.

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.

       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 limitations, 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.

EXAMPLES

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

FILES

       /etc/ldap/slapd.conf
              default slapd configuration file

SEE ALSO

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