db_user cannot grant privs on databases by default

[Kevin W Monroe]

I'm relating a charm to mariadb. It works fine, and I can get my host/user/password just fine. From my unit, I can also fire up mysql and create a database and user on the remote mariadb unit:

mysql> create database staging;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'staging' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Super. However, I now want to grant my new user privileges on my new database, but I get the following error:

mysql> GRANT ALL PRIVILEGES ON staging.* TO 'staging'@'$db_host';
ERROR 1044 (42000): Access denied for user '$db_user'@'%' to database 'staging'

This is because my $db_user doesn't have Grant_priv for host '%'.

mysql> SELECT host,user,password,Grant_priv FROM mysql.user;

This output would look awful here, so I'm not gonna paste it. Trust me that the Grant_priv column is 'N' for $db_user. I can work around my error with the following:

mysql > UPDATE mysql.user SET Grant_priv='Y' WHERE User='$db_user';
mysql > FLUSH PRIVILEGES;

But I'm curious if this is the right way to do it. Would it be better for the $db_user to have Grant_priv=Y by default, or was this privilege intentionally omitted? If not intentional, I believe this could be fixed in the db-relation-joined hook when $db_user is granted privs (maybe it belongs in the db-admin-relation-x). Change this:

grant all on `%s`.* to `%s` identified by '%s' ...

to:

grant all on `%s`.* to `%s` identified by '%s' with grant option ...

If this is already working as designed, perhaps a callout in the readme would be helpful so people know the $db_user can't grant stuff by default.

[Cory Johns (johnsca)]

If 'with grant option' is not desired as a default, perhaps a 'with-grant' flag could be passed along the db-admin-relation to include that option without having to manually update the table?

[Marco Ceppi (marcoceppi)]

I think having a "flags" key in general on mysql-root interface would be a worthwhile pursuit