Manual Pages for UNIX Darwin command on man slapd-sql

SLAPD-SQL(5) SLAPD-SQL(5)

NAME

slapd-sql - SQL backend to slapd

SYNOPSIS

/etc/openldap/slapd.conf

DESCRIPTION

The primary purpose of this ssllaappdd(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

hhttttpp::////wwwwww..ooppeennllddaapp..oorrgg//ffaaqq//iinnddeexx..ccggii??ffiillee==337788 (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 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 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. CCOONNFFIIGGUURRAATTIIOONN These ssllaappdd..ccoonnff 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 ssllaappdd..ccoonnff(5) manual page. ddbbnnaammee <> The name of the ODBC datasource to use. ddbbhhoosstt <> ddbbuusseerr <> ddbbppaasssswwdd <>

These three options are generally unneeded, because this infor-

mation 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: ddbbhhoosstt is currently ignored). ssuubbttrreeeeccoonndd <>

Specifies a where-clause template used to form a subtree search

condition (dn=".*"). It may differ from one SQL dialect to another (see samples). cchhiillddrreennccoonndd <>

Specifies a where-clause template used to form a children search

condition (dn=".+,"). It may differ from one SQL dialect to another (see samples). ooccqquueerryy <> The default is SSEELLEECCTT iidd,, nnaammee,, kkeeyyttbbll,, kkeeyyccooll,, ccrreeaatteepprroocc,, ddeelleetteepprroocc,, eexxppeeccttrreettuurrnn FFRROOMM llddaappooccmmaappppiinnggss aattqquueerryy <> The default is SSEELLEECCTT nnaammee,, sseelleexxpprr,, ffrroommttbbllss,, jjooiinnwwhheerree,, aaddddpprroocc,, ddeelleetteepprroocc,, ppaarraammoorrddeerr,, eexxppeeccttrreettuurrnn FFRROOMM llddaappaattttrrmmaappppiinnggss WWHHEERREE ooccmmaappiidd==?? iinnsseennttrryyqquueerryy <> The default is IINNSSEERRTT IINNTTOO llddaappeennttrriieess ((ddnn,, ooccmmaappiidd,, ppaarreenntt,, kkeeyyvvaall)) VVAALLUUEESS ((??,, ??,, ??,, ??)) ddeelleennttrryyqquueerryy <> The default is DDEELLEETTEE FFRROOMM llddaappeennttrriieess WWHHEERREE iidd==?? These four options specify SQL query templates for loading schema mapping metainformation, adding and deleting entries to ldapentries, etc. All these and subtreecond 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. uuppppeerrffuunncc <> 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. uuppppeerrnneeeeddssccaasstt {{ yyeess || nnoo }} Set this directive to yyeess if uuppppeerrffuunncc needs an explicit cast

when applied to literal strings. The form CCAASSTT ((<> AASS VVAARR-

CCHHAARR((<>)))) is used, where <> is builtin. This is eexxppeerriimmeennttaall and may change in future releases. ccoonnccaattppaatttteerrnn <> This statement defines the ppaatttteerrnn to be used to concatenate strings. The ppaatttteerrnn MUST contain two question marks, '?', that will be replaced by the two strings that must be concatenated. The default value is CCOONNCCAATT((??,,??)); 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: CCAASSTT((??||||?? AASS VVAARRCCHHAARR((<>)))). On some RDBMSes (IBM db2,

MSSQL) the form ??++?? is known to work. Carefully check the doc-

umentation of your RDBMS or stay with the examples for supported ones. This is eexxppeerriimmeennttaall and may change in future releases. ssttrrccaassttffuunncc <> 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 eexxppeerriimmeennttaall and may change in future releases. hhaassllddaappiinnffooddnnrruu {{ yyeess || nnoo }} Explicitly inform the backend whether the SQL schema has dnru

column (dn in reverse uppercased form) or not. Overrides auto-

matic check (required by PostgreSQL/unixODBC). This is eexxppeerrii-

mmeennttaall and may change in future releases. ffaaiilliiffnnoommaappppiinngg {{ yyeess || nnoo }} When set to yyeess 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 ldapocmappings, an add operation will fail regardless of the ffaaiilliiffnnoommaappppiinngg switch;

see section "METAINFORMATION USED" for details. This is eexxppeerrii-

mmeennttaall and may change in future releases. MMEETTAAIINNFFOORRMMAATTIIOONN UUSSEEDD Almost everything mentioned later is illustrated in examples located in

the sseerrvveerrss//ssllaappdd//bbaacckk-ssqqll//rrddbbmmssddeeppeenndd// 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. Example: Let's suppose that we store information about persons working in our organization in two tables: PERSONS PHONES

----- -------

id integer id integer firstname varchar persid integer references persons(id) lastname varchar phone middlename varchar ... (PHONES contains telephone numbers associated with persons). A person can have several numbers, then PHONES contains several records with corresponding persid, or no numbers (and no records in PHONES with such persid). 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.firstname,' ',persons.lastname) AS cn FROM persons WHERE persons.id=? for telephoneNumber we can use: SELECT phones.phone AS telephoneNumber FROM persons,phones WHERE persons.id=phones.persid 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.persid 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): ldapocmappings (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 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). ldapattrmappings (some columns are not listed for clarity)

------

id=1 ocmapid=1 name="cn" selexpr="CONCAT(persons.firstname,' ',persons.lastname)" fromtbls="persons" joinwhere=NULL ************ id= ocmapid=1 name="telephoneNumber" selexpr="phones.phone" fromtbls="persons,phones" joinwhere="phones.persid=persons.id" This table defines mappings between LDAP attributes and SQL queries that load their values. Note that, unlike LDAP schema, these are not

aattttrriibbuuttee ttyyppeess - 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 ocmapid column with link to ocmappings table. Now we cut the SQL query that loads values for a given attribute into 3

parts. First goes into selexpr 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. ldapentries

------

id=1 dn= ocmapid=... parent= keyval= 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.

Also, several people complained that they don't really need very struc-

tured 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 ldapentries, something like this (by Robin Elfrink): CREATE VIEW ldapentries (id, dn, ocmapid, 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; TTyyppiiccaall SSQQLL bbaacckkeenndd ooppeerraattiioonn 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 ldapobjclasses. Example: for our query with filter (telephoneNumber=123*) we would get the following query generated (which loads candidate IDs) SELECT ldapentries.id,persons.id, 'person' AS objectClass, ldapentries.dn AS dn FROM ldapentries,persons,phones WHERE persons.id=ldapentries.keyval AND ldapentries.objclass=? AND ldapentries.parent=? AND phones.persid=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.persid=persons.id Then, we use testfilter() 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 metainformation (addproc 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 ocmappings and attrmappings, holding statements to execute (like createproc, addproc, delproc 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. CCoommmmoonn tteecchhnniiqquueess ((rreeffeerrrraallss,, mmuullttiiccllaassssiinngg eettcc..)) 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. Fortu-

nately, they are easy to adopt in this scheme. The SQL backend sug-

gests two more tables being added to the schema - ldapentryobject-

classes(entryid,ocname), and ldapreferrals(entryid,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 "object-

class" attribute to each objectclass mapping that loads values from

this table. So, you may, for instance, have a mapping for inetOrgPer-

son, 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 ldapreferrals 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. CCaavveeaattss

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 (braindead) filter of the

form ((!!((hhaassSSuubboorrddiinnaatteess==TTRRUUEE)))) will give no results instead of return-

ing all the leaf entries. If you need to find all the leaf entries, please use ((hhaassSSuubboorrddiinnaatteess==FFAALLSSEE)) instead. EEXXAAMMPPLLEESS

There are example SQL modules in the slapd/back-sql/rdbmsdepend/

directory in the OpenLDAP source tree. FILES /etc/openldap/slapd.conf default slapd configuration file