[Digikam-users] Mysql/MariaDb database expert needs...

[Henrique Santos Fernandes]

I dont understando that much but i have 2 questions.

Why dont create the index normaly instead of using this if exist procedure?

If the procedure is really needed, you need to create it with digikam user?
You should be able to create the procedure when seting up the databases,
grant privilges and stuff. right?

So user digikam dont need to create the procedure right? the root/admin
could do this..

Well, as i said, i dont know much..

Hope this helps!


Em qua, 18 de nov de 2015 às 11:24, Gilles Caulier <caulier.gilles at gmail.com>
escreveu:

> 2015-11-18 14:18 GMT+01:00 Gilles Caulier <caulier.gilles at gmail.com>:
>
>>
>>
>> 2015-11-18 13:34 GMT+01:00 Richard Mortimer <
>> richm+digikam at oldelvet.org.uk>:
>>
>>> On 18/11/2015 12:02, Henrique Santos Fernandes wrote:
>>> >     MariaDB [(none)]>  show grants for 'digikam'@'localhost';
>>> >
>>>  +----------------------------------------------------------------------------------------------------------------+
>>> >
>>> >     | Grants for digikam at localhost
>>> >
>>>                |
>>> >
>>> >
>>>  +----------------------------------------------------------------------------------------------------------------+
>>> >
>>> >     | GRANT SUPER ON *.* TO 'digikam'@'localhost' IDENTIFIED BY
>>> PASSWORD
>>> >     '*B86D61DED45FEAAB193591C66C302416B0E64CA6' |
>>> >     | GRANT ALL PRIVILEGES ON `digikamcoredb`.* TO 'digikam'@
>>> 'localhost'
>>> >                                                 |
>>> >     | GRANT ALL PRIVILEGES ON `digikamthumbsdb`.* TO
>>> >     'digikam'@'localhost'                                           |
>>> >     | GRANT ALL PRIVILEGES ON `digikamfacedb`.* TO 'digikam'@
>>> 'localhost'
>>> >                                                 |
>>> >
>>>  +----------------------------------------------------------------------------------------------------------------+
>>> >
>>> >     4 rows in set (0.00 sec)
>>> >
>>> >     Gilles
>>> >
>>> >
>>> > I am no expert but it seens that user 'digikam'@'localhost' dont need
>>> a
>>> > password to the databases digikamcoredb, digikamthumbsdb and
>>> digikamfacedb
>>> > It only need password when using things when need super privileges
>>> right?
>>> No. The password is a global connection setting for that user/host
>>> combination and applies to all databases.
>>>
>>> I really am surprised that digikam needs SUPER privileges. Usual setup
>>> would be to set the password using USAGE privilege.
>>>
>>
>> Me too...
>>
>>
>>>
>>> GRANT USAGE ON *.* TO 'digikam'@'localhost' IDENTIFIED BY PASSWORD
>>> '*B86D61DED45FEAAB193591C66C302416B0E64CA6';
>>>
>>
>> Interresting to investiguate. But see below...
>>
>>
>>>
>>> If SUPER really is required I suspect it is because of the stored
>>> procedure that is used to emulate "IF EXISTS" when adding the indexes.
>>> Even then I would suspect that we could come up with a reduced set of
>>> privileges to access the "mysql" meta database.
>>>
>>
>> yes it is. My investigations revelate that we need to create table on
>> server through this commands :
>>
>> CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.*
>> TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
>> CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON
>> digikamthumbsdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH
>> PRIVILEGES;
>> CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.*
>> TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES;
>>
>> ... and to be able to run index creation procedures, we need :
>>
>> GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH PRIVILEGES;
>>
>> ... because in SQL procedure code we have :
>>
>> SQL SECURITY INVOKER
>>
>> If i drop it, "GRANT SUPER ON *.* TO 'digikam'@'localhost';FLUSH
>> PRIVILEGES;" command at init table is not necessary anymore...
>>
>> This is a first step in the right direction, i hope.
>>
>>>
>>>
> As it work fine without "SQL SECURITY INVOKER" in index procedure
> creation, it safe to remove it ?
>
> After all it's about security stuff. This point is important...
>
> Gilles Caulier
>
>
> _______________________________________________
> Digikam-users mailing list
> Digikam-users at kde.org
> https://mail.kde.org/mailman/listinfo/digikam-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151118/11f54aa2/attachment.html>