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

Stephen Worthington stephen_agent at jsw.gen.nz
Sat Jul 4 14:34:38 UTC 2020


On Sat, 4 Jul 2020 12:01:00 +0100, you 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>
>
>}}}

My reading of 192.168.1.133 not showing as having grants is that it is
doing an exact match in the "show grants" command, rather than the
wildcard grant not actually working.  Can you log in to MariaDB as
mythtv from 192.168.1.133 or 192.168.1.68?

mysql -h <mythbackend server> -u mythtv -p

If your network has a nameserver, you might also need to do grants
using the name rather than the IP address.


More information about the mythtv-users mailing list