I wanted to lock other frontends out from making video metadata changes to my mythtv database. There does not seem to be a way to do this easily through mythtv. So with some research and expirimenting I have come up with this solution. This was used on mythtv v.21; MySQL v5.0.60; and Arch Linux 2008.06.

First thing first, backup up your database on the mythtv backend with:
$ mysqldump -u <myth_user> -p –extended-insert –databases <myth_db_name> > mythdatabase.bak
Password: <myth_password>

I created a new database on seperate server just because I new I would screw this up plenty while figuring it out. So if you do it on your live database, MAKE SURE YOU HAVE A BACKUP. YOU HAVE BEEN WARNED! The following will create a new database from your backup (use this only if you want to create the database somewhere else):
mysql -u <root_user> -p <mythdatabase.bak
Password: <root_password>

Now I have a database with all my real world settings and information in it. The next step will be to add a user to it. I will call this new user ‘unpriv’ and want to make their permissions so that they WILL be able to do anything in the mythconverg db they want EXCEPT make changes to video metadata. Basically the default ‘mythuser’ minus the update permissions for the videometadata tables.

Login to mysql as root:
mysql -u root -p

Give the following a read: http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables
According to the information at devshed, we will want to give the ‘unpriv’ user no privileges at the user or database level, but set the privileges at the table level (in the tables_priv table of the mysql database).

STEP 1 -

Create an unprivileged user with the CREATE USER statement (MySQL 5.0.2+) with the following syntax:
CREATE USER user [IDENTIFIED BY [PASSWORD] ‘password’]

Here is mine as an examle:
CREATE USER ‘unpriv’@’192.168.1.102′ IDENTIFIED BY ‘easy2guess’;

STEP 2 -

Grant minimum permissions at the database level to user ‘unpriv’ with the following:
GRANT ALL ON mythconverg.* TO ‘unpriv’@’192.168.1.102′ IDENTIFIED BY ‘easy2guess’;
REVOKE DROP, ALTER, DELETE, UPDATE, INSERT ON mythconverg.* FROM ‘unpriv’@’192.168.1.102′;

That grants all privileges then revokes the ones that we want to keep the ‘unpriv’ user from having (right now anyways), ‘unpriv’ will need more permissions set up at the table level to make personal settings changes. I tried this a couple different ways and found the above GRANT ALL / REVOKE to be the easiest way. The user has to have a few permissions to even start the mythtv frontend. So I left those turned on at the database level, because you will never want to turn those off.

STEP 2a -

Try connecting to the database from mythtv with user ‘unpriv’. I had to manually change the ~/.mythtv/mysql.txt and ~/.mythtv/config.xml files with the new info, mythtv wouldn’t keep it otherwise.

Mythtv seems to quietly accept that it cannot write to the database. If you start it up and try to change themes or edit video metadata, it will look like you made a change, but if you leave that screen and go back to it you will see that the change did not take effect. Perfect. Now we are getting somewhere.

STEP 3 -

Now we need to grant DROP, ALTER, DELETE, UPDATE, INSERT privileges on a table by table basis for user ‘unpriv’ in the ‘mythconverg’ database. This would be an extremely daunting task to manually grant privileges on 88 tables. But thanks to http://archives.neohapsis.com/archives/mysql/2006-q4/0372.html this can be accomplished much easier with a little script.

SELECT CONCAT(‘GRANT DROP, ALTER, DELETE, UPDATE, INSERT ON mythconverg.’, TABLE_NAME, ‘ TO ”unpriv”@”192.168.1.102”;’)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘mythconverg’;

*note that in the above command those are ALL single quotes ( ‘ ).

This will print out the commands to grant DROP, ALTER, DELETE, UPDATE, INSERT privileges to ‘unpriv’ on all 88 tables in ‘mythconverg’. Or you can use the script here and just change the <user>, <host>, and <password> to fit your needs.

Now we want to revoke UPDATE and INSERT privileges from user ‘unpriv’ for the video metadata tables. Find them with:
mysql> SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE ‘videometa%’;

And revoke the privileges with:
REVOKE UPDATE, INSERT ON mythconverg.videometadata FROM ‘unpriv’@’192.168.1.102′;
REVOKE UPDATE, INSERT ON mythconverg.videometadatacast FROM ‘unpriv’@’192.168.1.102′;
REVOKE UPDATE, INSERT ON mythconverg.videometadatacountry FROM ‘unpriv’@’192.168.1.102′;
REVOKE UPDATE, INSERT ON mythconverg.videometadatagenre FROM ‘unpriv’@’192.168.1.102′;

STEP 4 -

Start mythtv. Now you cannot edit any of the video metadata through Setup -> Video Manager. When you try to it will look like it accepts the change, but if you exit the Video Manager and return to it, you will see that the change was not saved to the database.

Perfect! Now if you have a frontend that you do not want to allow to change your video metadata, just setup their mythtv with user ‘unpriv’ instead of your normal mythtv user (typically ‘mythtv’).

By having most of the privileges set at the table level instead of user or database level, you will be able to easily control access to other parts of the database as you see fit. i.e. restricting access to music metadata.

Thanks to help from:
http://archives.neohapsis.com/archives/mysql/2006-q4/0372.html
http://www.devshed.com/c/a/MySQL/The-MySQL-Grant-Tables
http://www.mythtv.org/wiki/index.php/Backup_your_database