[Spce-user] Hide customer password in Kamailio DB

[Daniel Grotti]

Hi,
if access mysql, go to provisioning db and run "show triggers\G"
command, you should see a trigger like that:

*************************** 45. row ***************************
             Trigger: voip_sub_crepl_trig
               Event: INSERT
               Table: voip_subscribers
           Statement: BEGIN

    DECLARE subscriber_domain varchar(127);

    SELECT domain INTO subscriber_domain FROM voip_domains where id =
NEW.domain_id;

    INSERT INTO kamailio.subscriber (username, domain, uuid, password,
datetime_created, ha1, ha1b)
                     VALUES(NEW.username, subscriber_domain, NEW.uuid,
NEW.password, '0',
                            MD5(CONCAT(NEW.username, ':',
subscriber_domain, ':', NEW.password)),
                            MD5(CONCAT(NEW.username, '@',
subscriber_domain, ':', subscriber_domain, ':', NEW.password)));


    INSERT INTO voip_usr_preferences (subscriber_id, attribute_id, value)
    SELECT NEW.id, p.id, pe.value
    FROM voip_preferences p, voip_preferences_enum pe
    WHERE p.id <=> preference_id AND p.usr_pref=1 AND pe.usr_pref=1 AND
pe.default_val=1 AND pe.value IS NOT NULL;
END
              Timing: AFTER
             Created: NULL
            sql_mode:
             Definer: sipwise at localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci



here you can see if we make an insert into
provisioning.voip_subscribers, this will set off the INSERT into
kamailio.subscriber table, where we set password field in clear text,
and ha1 and ha2.




---
Daniel Grotti
VoIP Engineer


Sipwise GmbH
Europaring F15 | 2345 Brunn am Gebirge, Austria | www.sipwise.com

On 05/05/2015 01:17 PM, Mathys Frédéric wrote:
> Hello Daniel,
> 
> Could you give me more information about this trigger you use to provision the DB? If a hacker takes control of my SIP server (hope it will not happen...) I don't want him to diffuse the clear text password on the internet.  
> 
> Thank you for your help,
> Frederic
> 
> -----Original Message-----
> From: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] On Behalf Of Daniel Grotti
> Sent: Thursday 30 April 2015 19:03
> To: Raúl Alexis Betancor Santana
> Cc: <spce-user at lists.sipwise.com>
> Subject: Re: [Spce-user] Hide customer password in Kamailio DB
> 
> Hi,
> That's work only in you create the user via kamctl command.
> We create the entry via a mysql trigger from provisioning database.
> As far as I know you should change that trigger command.
> 
> 
> Daniel
> On Apr 30, 2015 6:11 PM, Raúl Alexis Betancor Santana <rabs at dimension-virtual.com> wrote:
>>
>> Marc,
>>
>> HA1 and HA1_2 are not 'unencrypted' text ... they are HASH values, generated from the user URI, the REALM and the PASSWORD ... your could not use the HA1 and HA1_2 values for anything than 'check' if the sended (by the SIP UA) credentials are Ok, you could not use them to 'know' the unencrypted password.
>>
>> Best regards
>>
>> ________________________________
>>>
>>> De: "Marc Storck" <mstorck at voipgate.com>
>>> Para: "<spce-user at lists.sipwise.com>" <spce-user at lists.sipwise.com>
>>> Enviados: Jueves, 30 de Abril 2015 12:57:35
>>> Asunto: Re: [Spce-user] Hide customer password in Kamailio DB
>>>
>>> What is the difference from reading the plain text (unencrypted) password or reading the plain text (unencrypted) HA1 and HA1_2 values from DB?
>>>
>>> AFAIK, an attacker, who was able to read either of them from your DB, can use those values to correctly authenticate to the SPCE in any case.
>>>  
>>>>
>>>> On 30 Apr 2015, at 13:45, Mathys Frédéric <frederic.mathys at nagra.com> wrote:
>>>> Hello,
>>>>  
>>>> When creating a new user, by default the password is saved in plaintext in the DB, column “password”. For obvious security reasons, I’d like to remove the password in this column and use only ha1 and ha1b values. To do that, I modified the “auth_db” module configuration :
>>>>  
>>>> /etc/kamailio/proxy/kamailio.cfg
>>>> modparam("auth_db", "use_domain", 1) modparam("auth_db", 
>>>> "calculate_ha1", 0) modparam("auth_db", "password_column", "ha1") 
>>>> modparam("auth_db", "password_column_2", "ha1_2")
>>>>  
>>>> Then, I removed the password for all users in the DB, and everyone seems able to connect with this configuration. My problem is now when I create a new user, the password is automatically saved in plaintext and I don’t want that. So I tried to modify “kamctlrc” by adding the following line :
>>>>  
>>>> /etc/kamailio/proxy/kamctlrc and /etc/kamailio/lb/kamctlrc
>>>> STORE_PLAINTEXT_PW=0
>>>>  
>>>> This has no effect, what should I do to disable that?
>>>>  
>>>> Thank you
>>>>  
>>>> Frederic Mathys
>>>> System Integration & Validation Engineer P Please consider the 
>>>> environment - do you really need to print this email ?
>>>>  
>>>> _______________________________________________
>>>> Spce-user mailing list
>>>> Spce-user at lists.sipwise.com
>>>> https://lists.sipwise.com/listinfo/spce-user
>>>
>>>
>>>
>>> _______________________________________________
>>> Spce-user mailing list
>>> Spce-user at lists.sipwise.com
>>> https://lists.sipwise.com/listinfo/spce-user
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user
>