Sample MySQL schema for OpenLDAP with back-sql

Tags: 

Linux

From How to install OpenLDAP with MySQL as backend data on Debian 6 64-bit. Today I will describe about MySQL schema that I use for LDAP. This will very simple so you can adapt for your own use. I won't explain about basic LDAP because I don't expert in this too.

  • I start with org table for Organization for LDAP
CREATE TABLE IF NOT EXISTS `org` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `o` varchar(255) NOT NULL,
  `dc` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

This is sample data for org table

id o dc
1 example.com example
  • Next will be users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(64) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

These are sample data for users table

id username password name surname email phone
1 test1 test1 TestName1 TestSurname1 test1@email.com 1111
2 test2 test2 TestName2 TestSurname2 test2@email.com 2222
  • Next will be a must ldap_oc_mappings table. This table will be mapping to objectClass
CREATE TABLE IF NOT EXISTS `ldap_oc_mappings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `keytbl` varchar(64) NOT NULL,
  `keycol` varchar(64) NOT NULL,
  `create_proc` varchar(255) DEFAULT NULL,
  `delete_proc` varchar(255) DEFAULT NULL,
  `expect_return` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

These are sample data for ldap_oc_mappings table

id name keytbl keycol create_proc delete_proc expect_return
1 inetOrgPerson users id NULL NULL 0
2 organization org id NULL NULL 0

id = a unique id that you will refer to in other ldap_* tables
name = The value for objectClass
keytbl = the table where entities for the objectClass are held. Ex: inetOrgPerson is for identifying people, so it uses the persons table in the test data.
keycol = the table's primary key column name
create_proc = the SQL code when an LDAP create is called
delete_proc = the SQL code when an LDAP delete is called
expect_return = what to expect when the query is successful (ie not an error)

  • Next is ldap_attr_mappings that is table to mapping attribute of LDAP
CREATE TABLE IF NOT EXISTS `ldap_attr_mappings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `oc_map_id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `sel_expr` varchar(255) NOT NULL,
  `sel_expr_u` varchar(255) DEFAULT NULL,
  `from_tbls` varchar(255) NOT NULL,
  `join_where` varchar(255) DEFAULT NULL,
  `add_proc` varchar(255) DEFAULT NULL,
  `delete_proc` varchar(255) DEFAULT NULL,
  `param_order` tinyint(4) NOT NULL,
  `expect_return` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

These are sample data for ldap_attr_mappings table

id oc_map_id name sel_expr sel_expr_u from_tbls join_where add_proc delete_proc param_order expect_return
1 1 uidNumber users.id NULL users NULL NULL NULL 3 0
2 1 uid users.username NULL users NULL NULL NULL 3 0
3 1 cn users.username NULL users NULL NULL NULL 3 0
4 1 mail users.email NULL users NULL NULL NULL 3 0
5 1 telephoneNumber users.phone NULL users NULL NULL NULL 3 0
6 1 givenName users.name NULL users NULL NULL NULL 3 0
7 1 displayName concat(users.name,' ',users.surname) NULL users NULL NULL NULL 3 0
8 1 sn users.surname NULL users NULL NULL NULL 3 0
9 1 userPassword users.password NULL users users.password IS NOT NULL NULL NULL 3 0
10 2 o org.o NULL org NULL NULL NULL 3 0
11 2 dc org.dc NULL org NULL NULL NULL 3 0

id = a unique id that you will refer to in other ldap_* tables
oc_map_id = refers back to the id of the relevant objectClass in the ldap_oc_mappings table
name = the ldap attribute name
sel_expr = the SELECT xxxx part of the SQL statement
from_tbls = the FROM xxxx part of the SQL statement
join_where = the WHERE ... xx.xx=yy.yy ... part of the SQL statement if applicable. A null is allowed if you are not doing a join.
add_proc = the SQL code when an LDAP create is called
delete_proc = the SQL code when an LDAP delete is called
param_order = (I have no idea, but 3 seems to be the default in the test data.)
expect_return = what to expect when the query is successful (ie not an error)

  • Next is ldap_entries table that explain LDAP Tree
CREATE TABLE IF NOT EXISTS `ldap_entries` (
`id` int(11)
,`dn` longtext
,`oc_map_id` int(0)
,`parent` bigint(11)
,`keyval` bigint(20)
);

id = a unique id
dn = the dn for an entry
oc_map_id = refers back to the id of the relevant objectClass in the ldap_oc_mappings table
parent = what level in the LDAP tree this is located at, starting with 0 (zero)
keyval = refers back to the id of the relevant row of the table the data is contained in. These rows are identified by a number that is a primary key.

These are sample data that we want

id dn oc_map_id parent keyval
1 dc=example,dc=com 2 0 1
2 cn=test1,dc=example,dc=com 1 1 1
3 cn=test2,dc=example,dc=com 1 1 2

From above sample data. You will notice that if you have new user, these table must always updated. So for reduce complex to create new data. We can create VIEW for ldap_entries

CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval) AS
        SELECT id, 'dc=example,dc=com', 2, 0, 1 FROM org
UNION
        SELECT id,
        CONCAT('cn=',username,',dc=example,dc=com'),
        1,
        1,
        id
        FROM users

Download all database schema

  • When you ready to go with all database. You have to restart slapd everytime you change the data in ldap_* table
/etc/init.d/slapd24 restart
  • Test with ldapsearch. So the return data should be like below
ldapsearch -x -D cn=root,dc=example,dc=com -w yourpassword -s sub -b "dc=example,dc=com" "(objectClass=*)"
# extended LDIF
#
# LDAPv3
# base <dc=example,dc=com> with scope subtree
# filter: (objectClass=*)
# requesting: ALL
#
 
# test1, example.com
dn: cn=test1,dc=example,dc=com
objectClass: inetOrgPerson
cn: test1
sn: TestSurname1
uid: test1
mail: test1@email.com
givenName: TestName1
uidNumber: 1
displayName: TestName1 TestSurname1
userPassword:: dGVzdDE=
telephoneNumber: 1111
 
# test2, example.com
dn: cn=test2,dc=example,dc=com
objectClass: inetOrgPerson
cn: test2
sn: TestSurname2
uid: test2
mail: test2@email.com
givenName: TestName2
uidNumber: 2
displayName: TestName2 TestSurname2
userPassword:: dGVzdDI=
telephoneNumber: 2222
 
# example.com
dn: dc=example,dc=com
objectClass: organization
o: example.com
dc: example
 
# search result
search: 2
result: 0 Success
 
# numResponses: 4
# numEntries: 3

Download all database schema

More info: Setting up LDAP with back-sql