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

[Gilles Caulier]

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.

But, for an internal server solution, this continue to stop at the first
run with an error message about the index creation procedure which cannot
be executed. I don't know why...

Gilles









>
> I'm trying to find the time to build myself a copy of git against Ubuntu
> 14.04 (trusty) so that I can give this a look. Does anyone know if the
> qt5 library versions included with trusty are recent enough or do I need
> to load a PPA from somewhere? I digikam from the philip5/extra/ubuntu
> trusty PPA installed already.
>
> Regards
>
> Richard
>
> _______________________________________________
> 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/bc823a74/attachment.html>