[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!