[mythtv-users] Problem with Ubuntu upgrade

[Jason Chambers]

On 26/10/2010 13:02, Damian wrote:
> Now for damage control ...
> 
> I assume having a password like 'mythtv' is not a good idea. Is there a
> sure fire simple way to change that without breaking anything?

Now we know MySQL is allowing access properly you can change it back to
whatever you like.

Just remember that there are multiple mythtv users 'mythtv'@'localhost'
and 'mythtv'@'%'.  You will need to change both of them and they should
both use the same password for your own sanity.

To do this from the command line:

mysql> SET PASSWORD FOR 'mythtv'@'localhost' = PASSWORD('your preferred
password');

(repeat for the 'mythtv'@'%').

Then test you can still get access with the new passwords:

$ mysql -u mythtv -p mythconverg

$ mysql -u mythtv -h mythbox -p mythconverg

You could also do this through phpmyadmin however not being familiar
with that tool, I'm not sure how it works.

Some tools or command lines suggested by users manipulated the privilege
tables directly (e.g. "UPDATE user ...").  MySQL caches these tables, so
the changes do not take effect until the next time MySQL starts.

The proper permission related commands (SET PASSWORD, GRANT, CREATE
USER) tell MySQL that the caches need refreshing so they take effect
immediately.

This obviously leads to confusion if you don't understand MySQL very
well or cannot see what commands you preferred tool is issuing as it may
appear hit-and-miss whether a change takes effect immediately.

Therefore if any permission related command does not appear to have
worked, simply issue this command to force MySQL to reload is privilege
cache before moving to the next step:

mysql> FLUSH PRIVILEGES;

> Secondly, when I was poking around in phpmyadmin, I granted lots of
> privileges to the mythtv user that weren't there before. My guess is
> that's not a good idea either. How can I safely get things back to where
> they should be?

The privileges each user account has are in two places.

Firstly their are global permissions that they user has *regardless* of
which database they have.  These are the extra columns on the "user"
table (select_priv, update_priv etc.):

mysql> SELECT * FROM user;

Secondly their are *database specific* permissions these are in the "db"
table.

This is the same format as the user table, except it has an extra column
to show which database it applies to.

mysql> SELECT * FROM db;

If you look at these tables you can see what permissions you have granted.

Phpmyadmin must have config screens that map to these tables, but
whether they make a clear distinction between global privileges and
database specific privileges I don't know.

So you may be better off running the queries above to view the
privileges even if you are going to use another tool to actually change
them.  It will certainly help if you need to include the privileges in
any further posts to the list.

(Also ending SQL commands with " \G" instead of ";" will display results
vertically - helping avoid nasty wrapping of wide tables in emails or
small terminal windows).

Technically for mythtv to work it only needs full permission on the
"mythconverg" database (so that it can read and write the data and
upgrade the schema when new MythTV releases are applied).  The global
permissions are not required.

So in theory based on what I have seen in your posts so far you just
need to remove the permissions that mythtv has to all databases:

mysql> REVOKE ALL ON *.* FROM 'mythtv'@'localhost';
mysql> REVOKE ALL ON *.* FROM 'mythtv'@'%';

This should set the permissions to "N" in the "user" tables but leave
the database specific ones in "db" alone;

If you find the 'mythtv'@'%' or 'mythtv'@'%' users are missing
permissions in the "db" tables then the following will correct it:

mysql> GRANT ALL ON mythconverg.* TO 'mythtv'@'localhost';
mysql> GRANT ALL ON mythconverg.* TO 'mythtv'@'%';

> So relieved to be functioning again! Thanks for staying with me!!

Not a problem.  It makes a change to be dealing with a MythTV issue for
a change.  Recently all I seem to see is posts about Asterisk and VoIP
in general ;-)

Regards,
Jason