LinuxGuruz
  • Last 5 Forum Topics
    Replies
    Views
    Last post


The Web Only This Site
  • BOOKMARK

  • ADD TO FAVORITES

  • REFERENCES


  • MARC

    Mailing list ARChives
    - Search by -
     Subjects
     Authors
     Bodies





    FOLDOC

    Computing Dictionary




  • Text Link Ads






  • LINUX man pages
  • Linux Man Page Viewer


    The following form allows you to view linux man pages.

    Command:

    slapd-sql

    
    
    

    SYNOPSIS

           /etc/openldap/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 meta-information 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 replica-
           tion, 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  meta-information
           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, which means
           that  they must follow a "database sql" line and come before any subse-
           quent "backend" or "database" lines.  Other database options  not  spe-
           cific to this backend are described in the slapd.conf(5) manual page.
    
    
    

    DATA SOURCE CONFIGURATION

           dbname <datasource name>
                  The name of the ODBC datasource to use.
    
           dbhost <hostname>
           dbpasswd <password>
           dbuser <username>
                  The  three  above  options  are generally unneeded, because this
                  information is taken from the datasource specified by the dbname
                  "<upper_func>(ldap_entries.dn)   LIKE    CONCAT('%',?)");    see
                  upper_func, upper_needs_cast, concat_pattern and strcast_func in
                  "HELPER CONFIGURATION" for details.
    
           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).  By default, it is  constructed  based
                  on  the  knowledge  about  how  to  normalize  DN  values  (e.g.
                  "<upper_func>(ldap_entries.dn)   LIKE   CONCAT('%,',?)");    see
                  upper_func, upper_needs_cast, concat_pattern and strcast_func in
                  "HELPER CONFIGURATION" for details.
    
           use_subtree_shortcut { YES | no }
                  Do not use the subtree condition  when  the  searchBase  is  the
                  database  suffix,  and  the scope is subtree; rather collect all
                  entries.
    
    
    

    STATEMENT CONFIGURATION

           These options specify SQL query templates for  loading  schema  mapping
           meta-information,  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.
    
           oc_query <SQL expression>
                  The  query  that is used to collect the objectClass mapping data
                  from table  ldap_oc_mappings;  see  "METAINFORMATION  USED"  for
                  details.   The default is "SELECT id, name, keytbl, keycol, cre-
                  ate_proc, delete_proc, expect_return FROM ldap_oc_mappings".
    
           at_query <SQL expression>
                  The query that is used to collect the attributeType mapping data
                  from  table  ldap_attr_mappings;  see "METAINFORMATION USED" for
                  details.  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=?".
    
           id_query <SQL expression>
                  The query that is used  to  map  a  DN  to  an  entry  in  table
                  ldap_entries;  see  "METAINFORMATION  USED"  for  details.   The
                  default  is  "SELECT  id,keyval,oc_map_id,dn  FROM  ldap_entries
                  WHERE  <DN  match  expr>",  where <DN match expr> is constructed
                  based on the knowledge about how to normalize  DN  values  (e.g.
                  "dn=?"  if  no  means  to uppercase strings are available; typi-
                  ble ldap_entries; see "METAINFORMATION USED" for  details.   The
                  default is "DELETE FROM ldap_entries WHERE id=?".
    
           delobjclasses_stmt <SQL expression>
                  The statement that is used to delete an existing entry's ID from
                  table ldap_objclasses; see "METAINFORMATION USED"  for  details.
                  The   default   is   "DELETE  FROM  ldap_entry_objclasses  WHERE
                  entry_id=?".
    
    
    

    HELPER CONFIGURATION

           These statements are used to modify the default behavior of the backend
           according  to  issues  of  the dialect of the RDBMS.  The first options
           essentially refer to string and DN normalization when building filters.
           LDAP  normalization  is  more than upper- (or lower-)casing everything;
           however, as a reasonable  trade-off,  for  case-sensitive  RDBMSes  the
           backend can be instructed to uppercase strings and DNs by providing the
           upper_func directive.  Some RDBMSes, to use functions on arbitrary data
           types,  e.g.  string  constants, requires a cast, which is triggered by
           the upper_needs_cast directive.  If required, a  string  cast  function
           can be provided as well, by using the strcast_func directive.  Finally,
           a custom string concatenation pattern may be required; it  is  provided
           by the concat_pattern directive.
    
           upper_func <SQL function name>
                  Specifies  the name of a function that converts a given value to
                  uppercase.  This is used for case insensitive matching when  the
                  RDBMS  is case sensitive.  It may differ from one SQL dialect to
                  another (e.g.  UCASE,  UPPER  or  whatever;  see  samples).   By
                  default,  none  is  used,  i.e.  strings  are not uppercased, so
                  matches may be case sensitive.
    
           upper_needs_cast { NO | yes }
                  Set this directive to yes if upper_func needs an  explicit  cast
                  when applied to literal strings.  A cast in the form CAST (<arg>
                  AS VARCHAR(<max DN length>)) is used, where <max DN  length>  is
                  builtin  in  back-sql;  see  macro BACKSQL_MAX_DN_LEN (currently
                  255;   note   that   slapd's    builtin    limit,    in    macro
                  SLAP_LDAPDN_MAXLEN,  is  set to 8192).  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 DIS-
                  TINCT"  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.
    
           aliasing_keyword <string>
                  Define  the  aliasing  keyword.   Some RDBMSes use the word "AS"
                  (the default), others don't use any.
    
           aliasing_quote <string>
                  Define the quoting char of the aliasing keyword.   Some  RDBMSes
                  don't  require  any  (the default), others may require single or
                  double quotes.
    
           has_ldapinfo_dn_ru { NO | yes }
                  Explicitly inform the backend whether the dn_ru  column  (DN  in
                  reverse  uppercased  form)  is  present  in  table ldap_entries.
                  Overrides automatic check (this is required,  for  instance,  by
                  PostgreSQL/unixODBC).   This  is  experimental and may change in
                  future releases.
    
           fail_if_no_mapping { NO | yes }
                  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.  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  sec-
                  tion  "METAINFORMATION  USED" for details.  This is experimental
                  and may change in future releases.
    
           allow_orphans { NO | yes }
                  When set to yes orphaned entries (i.e. without the parent  entry
                  in  the database) can be added.  This option should be used with
                  care, possibly in conjunction with  some  special  rule  on  the
                  RDBMS side that dynamically creates the missing parent.
    
           baseObject [ <filename> ]
                  Instructs the database to create and manage an in-memory baseOb-
                  ject entry instead of looking for one  in  the  RDBMS.   If  the
                  (optional)  <filename> argument is given, the entry is read from
                  that file in LDIF(5) format; otherwise, an  entry  with  object-
                  Class  extensibleObject  is created based on the contents of the
                  RDN  of  the  baseObject.   This  is  particularly  useful  when
                  ldap_entries  information  is  stored in a view rather than in a
                  table, and union is not supported for views, so  that  the  view
                  can only specify one rule to compute the entry structure for one
                  objectClass.   This  topic  is  discussed  further  in   section
                  "METAINFORMATION  USED".  This is experimental and may change in
                  specific  operation,  because  they  are required for the proper
                  usage of the backend.  For instance, all attributes used in ACLs
                  should  be  listed  here.  The second statement is a shortcut to
                  require all attributes to  be  always  loaded.   Note  that  the
                  dynamically  generated attributes, e.g. hasSubordinates, entryDN
                  and other implementation dependent attributes are NOT  generated
                  at this point, for consistency with the rest of slapd.  This may
                  change in the future.
    
           check_schema { YES | no }
                  Instructs the database to  check  schema  adherence  of  entries
                  after  modifications,  and  structural  objectClass  chain  when
                  entries are built.  By default it is set to yes.
    
           sqllayer <name> [...]
                  Loads the layer <name> onto a stack of helpers that are used  to
                  map  DNs from LDAP to SQL representation and vice-versa.  Subse-
                  quent args are passed to the layer configuration routine.   This
                  is  highly  experimental  and  should be used with extreme care.
                  The API of the layers is not frozen yet, so it is unpublished.
    
           autocommit { NO | yes }
                  Activates autocommit; by default, it is off.
    
    
    

    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 normal-
           ized 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 stan-
           dard 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.
           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  (telephoneNum-
           ber=123*), we would construct something like:
    
             SELECT ... FROM persons,phones
                 WHERE persons.id=phones.pers_id
                     AND persons.id=?
                     AND phones.phone like '%1%2%3%'
    
           (note  how  the telephoneNumber match is expanded in multiple wildcards
           to account for interspersed ininfluential chars like spaces, dashes and
           so;  this  occurs  by design because telephoneNumber is defined after a
           specially recognized syntax).  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 correspond-
           ing 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
             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 ta-
           ble), which allows you to add any tree structure(s) to your flat  rela-
           tional  data.  Having id of objectclass mapping, we can determine table
           and column for primary key, and keyval stores value of it, thus  defin-
           ing 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.
    
                 UNION
                     SELECT (2000000000+groupnummer),
                         UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
                         2, 0, groupnummer FROM groups;
    
           If  your  RDBMS  does not support unions in views, only one objectClass
           can be mapped in ldap_entries, and the baseObject cannot be created; in
           this case, see the baseObject directive for a possible workaround.
    
    
    

    TYPICAL SQL BACKEND OPERATION

           Having  meta-information  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 '%1%2%3%')
    
           (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 fil-
           ter 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 meta-information (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 PostgreSQL, ORACLE and MSSQL - since as of this
           writing there are no stored procs in MySQL).
           SQL  backend  requires  that  one  more  table  is added to the schema:
           ldap_entry_objectclasses(entry_id,oc_name).
    
           That table contains any number of objectclass names that  corresponding
           entries  will  possess,  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 ta-
           ble.  So, you may, for instance, have a mapping for inetOrgPerson,  and
           use it for queries for "person" objectclass...
    
           Referrals  used  to be implemented in a loose manner by adding an extra
           table that allowed any entry to host a "ref" attribute,  along  with  a
           "referral"  extra  objectClass  in table ldap_entry_objclasses.  In the
           current implementation, referrals are  treated  like  any  other  user-
           defined schema, since "referral" is a structural objectclass.  The sug-
           gested practice is to define a "referral"  entry  in  ldap_oc_mappings,
           holding  a naming attribute, e.g. "ou" or "cn", a "ref" attribute, con-
           taining the url; in case multiple referrals per  entry  are  needed,  a
           separate  table  for  urls can be created, where urls are mapped to the
           respective entries.  The use of the naming attribute  usually  requires
           to add an "extensibleObject" value to ldap_entry_objclasses.
    
    
    

    CAVEATS

           As  previously stated, this backend should not be considered a replace-
           ment 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 (brain-dead?) filter of
           the form (!(hasSubordinates=TRUE)) will  give  no  results  instead  of
           returning  all  the  leaf entries, because it actually expands into ...
           AND NOT (1=1).  If you need to find all the leaf  entries,  please  use
           (hasSubordinates=FALSE) instead.
    
           A  directoryString  value  of  the form "__First___Last_" (where under-
           scores mean spaces, ASCII 0x20  char)  corresponds  to  its  prettified
           counterpart  "First_Last"; this is not currently honored by back-sql if
           non-prettified data is written via RDBMS; when non-prettified  data  is
           written  through  back-sql,  the  prettified  values  are actually used
           instead.
    
    
    

    BUGS

           When the ldap_entry_objclasses table is empty, filters on  the  object-
           Class attribute erroneously result in no candidates.  A workaround con-
           sists in adding at least one row to that table, no matter if  valid  or
           not.
    
    
    
    

    FILES

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

    SEE ALSO

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

    OpenLDAP 2.4.40 2014/09/20 SLAPD-SQL(5)

    
    
  • MORE RESOURCE


  • Linux

    The Distributions





    Linux

    The Software





    Linux

    The News



  • MARKETING






  • Toll Free

webmaster@linuxguruz.com
Copyright © 1999 - 2016 by LinuxGuruz