(2023-08-12, 11:57)Supay Wrote: (2022-08-10, 15:46)TakedaT Wrote: The one issue so far that has been a bit of a pain, was that other users who share the database could hose the library if their pi was to lose a mount and attempt cleanup (they selected the default 'delete' option when the prompt showed).
Not sure if this has been mentioned in the previous posts, but the way I got around this was to create a read-only mysql user for those users' pis, and grant only SELECT access to all tables in their db, except granting ALL access to both the bookmark and files tables. Been using this for a few weeks now without issues for the most part. They can still have their own watched status and get regular updates when i update from my end. One small issue is if a read-only user attempts to refresh certain items, OSMC will die with a sadface, but thats minimal and I just tell them to stop trying to update.
Has anyone else been plagued with other users hosing their library like this? If so, just wondering how they approached preventing it.
With my children growing and experimenting, this has become an issue for me as well. I'm about to try the same setup, so hopefully it works. Will confirm once done!
@
TakedaT - Been tinkering all day. It kind of works, but not fully. I setup everything as usual, and then added separate users to the mariadb database. Then granted them each permissions to the tables in their databases, as you stated in your post. I'll add the lines I used below.
Quote:GRANT SELECT ON Kodi_U03_Video_121.* TO 'KODI_U03'@'%';
GRANT ALL ON Kodi_U03_Video_121.bookmark TO 'KODI_U03'@'%';
GRANT ALL ON Kodi_U03_Video_121.files TO 'KODI_U03'@'%';
And this is what I see for permissions in the database:
Quote:+---------------------------------------------------------------------------------------------------------+
| Grants for KODI_U03@% |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `KODI_U03`@`%` IDENTIFIED BY PASSWORD 'REDACTED' |
| GRANT SELECT ON `Kodi_U03_Video_121`.* TO `KODI_U03`@`%` |
| GRANT ALL PRIVILEGES ON `Kodi_U03_Video_121`.`files` TO `KODI_U03`@`%` |
| GRANT ALL PRIVILEGES ON `Kodi_U03_Video_121`.`bookmark` TO `KODI_U03`@`%` |
+---------------------------------------------------------------------------------------------------------+
So, this works in that each user is able to have their own working watch statuses but they cannot remove or alter files in the library whatsoever, which is perfect and exactly what I wanted.
I've long used Trakt for keeping a separate watch history copy for each user in case the DB ever goes wrong during an upgrade or I break it. Trakt worked perfectly for my main account under U01, where I have full permissions to the database, and all of my watch statuses came across from Trakt and updated into the new database. However, for the other users with the exact same Trakt setup other than their own account linked, it doesn't update the Kodi database for them. It runs and identifies that there are entries on Kodi that need to be updated from Trakt, and it spends ages processing this all as expected, however at the end not a single status has been updated from Trakt to Kodi. Yet if I select one of the same movies or episodes that I know should have been updated and manually do it, it records the watch history as expected.
I have run a debug and checked the logs, and I can see a massive amount of failures when it is trying to execute the updates to the user database. Common repeat is below:
Quote:error <general>: SQL: [Kodi_U03_Video_121] Undefined MySQL error: Code (1142)
Checking online it seems this may be a permissions issue. I assume Trakt is trying to update the tables in a way that isn't permitted following the restricting of these users, but I don't understand why or how to fix it.
Are you or anyone else able to please advise as to why I can manually update watch statuses either by watching content or through the context menu, but Trakt fails?
