09 Sep 2010 
Support Center » Knowledgebase » MPP ACL's using SQL Template Queries and multiple MySQL DB's
 MPP ACL's using SQL Template Queries and multiple MySQL DB's
Solution In this example, you'll see how we can combine SQL Template Queries and 2 MySQL DB's containing different users for recipient ACL's:

The file /usr/local/MPP/passwords should have MySQL passwords for MySQL users of both connections
mpp_acl_first_pw    PASSWORD_HERE
mpp_acl_second_pw PASSWORD_HERE


<mppd>
<common>
<passwords>/usr/local/MPP/passwords</passwords>
...
</common>
<engines>

   <mysql id="acl_first">
     <connection>
       <host>DB1_HOST</host>
       <port>3306</port>
       <database>DB1</database>
       <unix_socket>/var/lib/mysql/mysqld.sock</unix_socket>
       <defaults_file>/etc/my.cnf</defaults_file>
       <user>mpp</user>
       <password_id>mpp_acl_first_pw</password_id>
   </connection>

    <query id="acl_recipient">

       <template>
           SELECT `id` FROM `accesslists` WHERE `mail`='${escape $recipient}'
       </template>

       <result id="status">
           <if_empty_table>
               <result>acl_recipient</result>
           </if_empty_table>

           <if_filled_table>
               <result>none</result>
           </if_filled_table>
       </result>

       <result id="result">
           <if_empty_table>
               <result>none</result>
           </if_empty_table>

           <if_filled_table>
               <result>none</result>
           </if_filled_table>
       </result>

     </query>

   </mysql>
   <mysql id="acl_second">
     <connection>
       <host>DB2_HOST</host>
       <port>3306</port>
       <database>DB2</database>
       <unix_socket>/var/lib/mysql/mysqld.sock</unix_socket>
       <defaults_file>/etc/my.cnf</defaults_file>
       <user>mpp</user>
       <password_id>mpp_acl_second_pw</password_id>
   </connection>

     <query id="acl_recipient">
       <template>
           SELECT `id` FROM `accesslists` WHERE `mail`='${escape $recipient}'
       </template>
       <result id="status">
           <if_empty_table>
               <result>acl_recipient</result>
           </if_empty_table>

           <if_filled_table>
               <result>none</result>
           </if_filled_table>
       </result>

       <result id="result">
           <if_empty_table>
               <result>none</result>
           </if_empty_table>

           <if_filled_table>
               <result>none</result>
           </if_filled_table>
       </result>

     </query>

     <query id="acl_final">

       <result id="result">
           <if_empty_table>
               <case>
                   <condition>$engines.acl_first.acl_recipient.status $EQ acl_recipient $AND $engines.acl_second.acl_recipient.status $EQ acl_recipient</condition>
                   <result>acl_recipient</result>
               </case>
               <result>none</result>
           </if_empty_table>

           <if_filled_table>
               <result>none</result>
           </if_filled_table>
       </result>

     </query>

   </mysql>

</engines>


<groups>
   <group id="recipients_group">
    <members_addresslist type="recipient">domain1.com, domain2.com</members_addresslist>
    <wbl_engines>acl_first.acl_recipient,acl_second.acl_recipient,acl_second.acl_final</wbl_engines>
   </group>
</group>

</mppd>



Article Details
Article ID: 45
Created On: 03 Mar 2009 11:56 PM

 This answer was helpful  This answer was not helpful

 User Comments Add a Comment
 Back
 Login [Lost Password] 
Email:
Password:
Remember Me:
 
 Search
 Article Options
 Add Comment
 Print Article
 PDF Version
 Email Article
 Add to Favorites
Home | Register | Submit a Ticket | Knowledgebase | Downloads
Language: