Viewing Incoming/7516
Full headers

Major security issue: yes  no

Notes:

Notification:

Date: Fri, 01 Feb 2013 12:56:02 +0000
From: povilas@Daukas.lt
To: openldap-its@OpenLDAP.org
Subject: Mysql backend is readonly with included metadata sql files

Full_Name: Povilas Daukas
Version: any
OS: Ubuntu
URL: ftp://ftp.openldap.org/incoming/
Submission from: (NULL) (78.63.107.40)



Hi, 

While trying to setup mysql as backend to the openldap server i noticed that it
only allowed reading from the database but not updating as the metadata was not
there to handle the updates.  

So I adopted the functions and sql from PostgreSQL to make it read write.  the
file to modify is below.

Hope you and others will find it useful.

Kind regards

Paul


----------------------------------------------------------------------
File:  servers/slapd/back-sql/rdbms_depend/mysql/testdb_metadata.sql
----------------------------------------------------------------------
-- mappings 

-- objectClass mappings: these may be viewed as structuralObjectClass, the ones
that are used to decide how to build an entry
--	id		a unique number identifying the objectClass
--	name		the name of the objectClass; it MUST match the name of an objectClass
that is loaded in slapd's schema
--	keytbl		the name of the table that is referenced for the primary key of an
entry
--	keycol		the name of the column in "keytbl" that contains the primary key of
an entry; the pair "keytbl.keycol" uniquely identifies an entry of objectClass
"id"
--	create_proc	a procedure to create the entry
--	delete_proc	a procedure to delete the entry; it takes "keytbl.keycol" of the
row to be deleted
--	expect_return	a bitmap that marks whether create_proc (1) and delete_proc (2)
return a value or not
INSERT INTO `ldap_oc_mappings`
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
VALUES ('1', 'inetOrgPerson', 'persons', 'id', 'SELECT create_person()', 'DELETE
FROM persons WHERE id=?', 0);
INSERT INTO `ldap_oc_mappings`
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
VALUES ('2', 'document', 'documents', 'id', 'SELECT create_doc()', 'DELETE FROM
documents WHERE id=?', 0);
INSERT INTO `ldap_oc_mappings`
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
VALUES ('3', 'organization', 'institutes', 'id', 'SELECT create_o()', 'DELETE
FROM institutes WHERE id=?', 0);
INSERT INTO `ldap_oc_mappings`
(id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
VALUES ('4', 'referral', 'referrals', 'id', 'SELECT create_referral()', 'DELETE
FROM referrals WHERE id=?', 0);
-- attributeType mappings: describe how an attributeType for a certain
objectClass maps to the SQL data.
--	id		a unique number identifying the attribute	
--	oc_map_id	the value of "ldap_oc_mappings.id" that identifies the objectClass
this attributeType is defined for
--	name		the name of the attributeType; it MUST match the name of an
attributeType that is loaded in slapd's schema
--	sel_expr	the expression that is used to select this attribute (the "select
<sel_expr> from ..." portion)
--	from_tbls	the expression that defines the table(s) this attribute is taken
from (the "select ... from <from_tbls> where ..." portion)
--	join_where	the expression that defines the condition to select this attribute
(the "select ... where <join_where> ..." portion)
--	add_proc	a procedure to insert the attribute; it takes the value of the
attribute that is added, and the "keytbl.keycol" of the entry it is associated
to
--	delete_proc	a procedure to delete the attribute; it takes the value of the
attribute that is added, and the "keytbl.keycol" of the entry it is associated
to
--	param_order	a mask that marks if the "keytbl.keycol" value comes before or
after the value in add_proc (1) and delete_proc (2)
--	expect_return	a mask that marks whether add_proc (1) and delete_proc(2) are
expected to return a value or not
INSERT INTO `ldap_attr_mappings` into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
VALUES ('1', '1', 'cn', 'concat(persons.name,\' \',persons.surname)', null,
'persons', null, 
	'SELECT update_person_cn(?,?)', 'SELECT 1 FROM persons WHERE persons.name=? AND
persons.id=? AND 1=0', '3', '0');
INSERT INTO `ldap_attr_mappings` into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
VALUES ('2', '1', 'telephoneNumber', 'phones.phone', null, 'persons,phones',
'phones.pers_id=persons.id', 
	'SELECT add_phone(?,?)', 'DELETE FROM phones WHERE phone=? AND pers_id=?', '3',
'0');
INSERT INTO `ldap_attr_mappings` into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
VALUES ('3', '1', 'givenName', 'persons.name', null, 'persons', null, 
	'UPDATE persons SET name=? WHERE id=?', 'UPDATE persons SET name=\'\' WHERE
(name=? OR name=\'\') AND id=?', '3', '0');
INSERT INTO `ldap_attr_mappings` into ldap_attr_mappings
(id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
VALUES ('4', '1', 'sn', 'persons.surname', null, 'persons', null, 
	'UPDATE

Followup 1

Date: Fri, 01 Feb 2013 14:19:31 +0100
From: Pierangelo Masarati <masarati@aero.polimi.it>
To: povilas@Daukas.lt
CC: openldap-its@openldap.org
Subject: Re: (ITS#7516) Mysql backend is readonly with included metadata sql
 files

On 02/01/2013 01:56 PM, povilas@Daukas.lt wrote:
> Full_Name: Povilas Daukas
> Version: any
> OS: Ubuntu
> URL: ftp://ftp.openldap.org/incoming/
> Submission from: (NULL) (78.63.107.40)
>
>
>
> Hi,
>
> While trying to setup mysql as backend to the openldap server i noticed
that it
> only allowed reading from the database but not updating as the metadata was
not
> there to handle the updates.
>
> So I adopted the functions and sql from PostgreSQL to make it read write. 
the
> file to modify is below.

Thank you, but please follow guidelines for contributions here 
<http://www.openldap.org/devel/contributing.html> to ease review and 
integration.

Thanks, p.


-- 
Pierangelo Masarati
Associate Professor
Dipartimento di Scienze e Tecnologie Aerospaziali
Politecnico di Milano