[18:36:46] jynus: we have a database that replicates in fundraising, and ejegg wants to use a view to replace a table [18:37:08] I dropped the table and created the view on the master db [18:37:14] just, FYI, no discusing private info here (publicly logged) [18:37:31] cool, just trying to figure out view privs [18:37:39] jynus: (yup)I haven't used views before, not really familiar with how they work in replication [18:37:58] how? [18:38:08] are you using STATEMENT or row? [18:38:19] iirc we're using mixed [18:38:25] checking [18:38:31] well, for the creation, it doesn't matter [18:38:48] CREATE VIEW is sent to the slave [18:38:48] The view seems to exist on the replicated table, and root can select from it [18:38:55] unless you have disable binary logging [18:38:55] *replicated server [18:39:05] yes, that is expected [18:39:08] jynus: yah, and we can see it has replicated and I can query the view as the root user [18:39:26] but two of us users with select on .* get permissions errors [18:39:37] when we try to select from the view [18:39:59] we can select from the replicated tables in that db just fine [18:40:06] so you need select from the view and the underlying table [18:40:14] *select grants [18:40:49] wait, we need select with grant? [18:41:10] afaict we have that, the view is in the same db as the underlying table, and users have select ,* on that db [18:41:33] actually, you do not need select grants on the table, [18:41:43] the definer needs it [18:41:51] ohhhhh [18:41:54] checking that... [18:41:58] but you need grants on the view [18:42:42] so that is "GRANT SELECT on your_database.your_view TO user@host;" [18:42:43] would ".*" cover views in " or do you need to explicitly grant for "," ? [18:42:57] it should cover all objects [18:43:17] weird, this really should be working [18:43:18] check the show create VIEW output, then [18:43:32] wait [18:43:43] are you using .*? [18:44:32] no, that is ok [18:44:41] here's the sql I ran on master db, if it matters: https://git.wikimedia.org/blob/wikimedia%2Ffundraising%2Ftools%2FDjangoBannerStats.git/a64fe0e373a978d3df0b7f1dd74ac4cc5c78d34e/sql%2F003_donatewiki_counts_should_be_a_view.sql [18:44:41] i.e. "grant select on somedb.* to 'someuser'@'localhost' [18:45:01] I'll check privs for the user in the view definition [18:45:18] so check that, and check the user privs, including the host [18:45:19] hmm, I did name a column after a builtin function... [18:46:07] yeah, to be sure use `count` or better, a different name [18:46:41] will change that to 'total' [18:47:04] check that, I will log in if you do not see anything [18:47:32] the DEFINER= user does not exist on the slave db in question, fixing that now [18:48:12] ah, there you have it [18:48:25] ooh weird, guess replicated views /do/ have some tricky properties [18:48:34] which means you have different users on both servers, and that is dangerous [18:49:07] oh? [18:49:08] jynus: understood, this is a special case where we're replicating a handful of databases to a test/dev server [18:49:09] ejegg, no, it means there differences between servers, and the minimal difference can be a problem [18:49:29] yes, I am not critizising, I am the first one that have those in production databases [18:49:40] but it is easier to tell than to fix :-) [18:49:44] heh [18:50:28] at trick, but I do not know if one that is PCI-complient [18:50:31] *a [18:50:40] pt-show-grants [18:50:56] ^nice tool to sumarize, compare and migrate grants [18:51:50] jynus: i've been managing the prod master/slave with a scripted privileges generator, but this is a separate machine that has its own privs [18:52:29] as I said, you do not have to justify anything :-) [18:52:38] weird ugly corner case :-) [18:52:54] specially administrating mysql [18:53:06] its grant system is not preciselly clear or rational [18:53:17] no, it's really terrible to work with [18:53:43] there is plugins to integrate it with pam, or using any external autentication system [18:54:09] privileges are still something that has to be handled [18:54:35] although things are getting better- mariadb 10.1 has roles [18:55:15] I want to implement LDAP + roles for labs soon-ich [18:57:08] nice [18:57:54] So, Jeff, can you confirm that works? [18:59:29] just got it done, ejegg can you test now? [18:59:52] yep, can select just fine! Thanks jynus and Jeff_Green ! [19:00:00] great, thanks everyone!