[mythtv-users] MariaDB 10.3.22 Database access with Master and Fedora 31

[Bill Meek]

On 7/4/20 6:01 AM, John Pilkington wrote:
> On 02/07/2020 23:36, Bill Meek wrote:
>> On 7/2/20 5:26 PM, John Pilkington wrote:
>>> On 02/07/2020 22:09, Bill Meek wrote:
>>>> On 7/2/20 1:05 PM, John Pilkington wrote:
>>>>> The section below gave me the 'mistake in syntax' response
>>>>>
>>>>> Finally, if you just want to restrict by IP subnet (in this example, the 192.168.1. network):
>>>>>
>>>>>      $ mysql -u root mythconverg
>>>>>      mysql> create user "mythtv"@"192.168.1.%" identified by "mythtv";
>>>>>      mysql> grant all on mythconverg.* to mythtv@"192.168.1.%";
>>>>>      mysql> flush privileges;
>>>>
>>>> The following worked for MySQL v5.7 and v8.0 plus MariaDB at least 10.3.iforget,
>>>> although initially, it was tested with localhost and is in the mc.sql.
>>>>
>>>> But I just tried it with an IP and it worked fine (that is, no syntax errors.)
>>>>
>>> {{{
>>>
>>> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
>>> ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
>>>
>>> }}}
>>>
>>> Thanks Bill.  I may try it with a specific address first, because I want to be able to run other systems independently.  This stuff works for 
>>> me for years and it's scary to have to fiddle with it when it stops.
>>>
>>> I'm expecting that grant and flush lines will be needed too.
>>
>> ^^^ Yes.
>>
>> I expect more of the same as MySQL changes (v8 has already caused issues) and MariaDB
>> doesn't have the same changes, at least not yet.
>>
>> <PedPeeve>Too many places with similar information. Makes it tough to find/update all of them.</PetPeeve>
>>
> 
> Bill:  I haven't tried that ALTER on the Fedora box but did it on a laptop with *buntu PPA 31 fixes (bionic, mythtv-setup.real, mythbackend, 
> mythtfrontend.real) which is fine as a FE/BE.
> 
> The ALTER doesn't work as I expected, though.  Looking at the GRANTS, the % is not recognised as a wildcard.  Perhaps the susbstitution happens 
> later?  133 is the laptop, 68 the F31 box.
> 
> {{{
> mysql> show grants ;
> +-----------------------------------------------------------------+
> | Grants for mythtv at localhost                                     |
> +-----------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'mythtv'@'localhost'                      |
> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
> +-----------------------------------------------------------------+
> 2 rows in set (0.00 sec)
> 
> mysql> show grants for mythtv@'localhost' ;
> +-----------------------------------------------------------------+
> | Grants for mythtv at localhost                                     |
> +-----------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'mythtv'@'localhost'                      |
> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'localhost' |
> +-----------------------------------------------------------------+
> 2 rows in set (0.00 sec)
> 
> mysql> show grants for 'mythtv'@'192.168.1.%' ;
> +-------------------------------------------------------------------+
> | Grants for mythtv at 192.168.1.%                                     |
> +-------------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'mythtv'@'192.168.1.%'                      |
> | GRANT ALL PRIVILEGES ON `mythconverg`.* TO 'mythtv'@'192.168.1.%' |
> +-------------------------------------------------------------------+
> 2 rows in set (0.00 sec)
> 
> mysql> show grants for 'mythtv'@'192.168.1.133' ;
> ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on host '192.168.1.133'
> mysql> show grants for 'mythtv'@'192.168.1.68' ;
> ERROR 1141 (42000): There is no such grant defined for user 'mythtv' on host '192.168.1.68'
> mysql>
> 
> }}}

I <think> you're expecting the last two commands to work, but there are
no such users.

I'd check to see if the wildcard is working with:

   mysql --user=mythtv --host=192.168.1.68 --password mythconverg

For everything except MariaDB versions below v10.3, I expect this to
work, including things other than localhost:

CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO 'mythtv'@'localhost';

Just tested on a Fedora 33 host running 10.4.13-MariaDB.

-- 
Bill