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

[John Pilkington]

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>

}}}