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

[Gilles Caulier]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20151118/3763f5ec/attachment.html>