phantom rows in grants system table

Pietro_La_Torre

Hi, I am recently interacting with "grants" system table for a task involving grant management. It happens that I have a list of grants for a role/user X , lets say 200 instructions (for example GRANT SELECT for 200 objects), and if I look at "grants" system table I see strange things happen. For example, if I execute one by one these instructions, initially I see the new grants correctly and an increasing number of rows associated to grantee X in table "grants".. select * from grants where grantee = 'X'; but after a certain quantity of instructions, the grants already assigned start to disappear and are swapped with new ones... There are 2 strange things: - actually the grant assigned are still saved somewhere, in fact user/role X has privileges even if I don't see the corrisponding grants on the "grants" table any more - if I execute again the whole set of instructions there is no more this strange effect of phantom rows. can someone investigate on this topic? thanks in advance, Pietro

[Deleted User]

Hi Pietro, Hm... This is definitely odd. I'm not familiar with the GRANT mechanism personally, but will ask around. It would be quite helpful if you could post a series of SQL statements that consistently do something weird for you. Thanks, Adam

Pietro_La_Torre

Hi Adam, thank you for your reply. Here there's a list of 50 SQL instructions that I execute while having this problem. The whole set is composed by 500 instructions, mostly composed by GRANT SELECT / GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES: drop ROLE if exists talend_dwh_role CASCADE; CREATE ROLE talend_dwh_role; CREATE USER talend_dwh IDENTIFIED BY 'password'; GRANT ALL ON schema dwh TO talend_dwh_role; GRANT ALL ON schema ext TO talend_dwh_role; GRANT ALL ON schema replica TO talend_dwh_role; GRANT ALL ON schema stg TO talend_dwh_role; GRANT USAGE ON schema ca TO talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.fct_leg to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.stg_ang_supplier to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.tmp_id_macrocustomergroup to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.stg_ang_cobranded to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.stg_ang_airline to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.tmp_dati_cohort to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_margin_driver_configuration to talend_dwh_role; GRANT SELECT on ca.storico_flusso_unificato to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_booking_margin_tracker to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_booking_invoice to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_filter_business_test to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_booking_events to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.tmp_comportamentale_1 to talend_dwh_role; GRANT SELECT on ca.dim_user to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.booking_tocheck to talend_dwh_role; GRANT SELECT on ca.stg_qnt_1 to talend_dwh_role; GRANT SELECT on ca.stg_mobile to talend_dwh_role; GRANT SELECT on ca.flusso_unificato to talend_dwh_role; GRANT SELECT on ca.fct_cl_delivery to talend_dwh_role; GRANT SELECT on ca.stg_last_dw_id_user to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.fct_booking_evolution to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_check_status to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_booking_passenger to talend_dwh_role; GRANT SELECT on ca.stg_cmp_booking_2 to talend_dwh_role; GRANT SELECT on ca.ext_mobile to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.tmp_pf to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.tmp_f to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on stg.cfg_backup to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.fct_pax_option to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.fct_margin_hotel to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on replica.sessions to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_filter_business to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.prova_daily_sales to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_ver_booking_pax_addson to talend_dwh_role; GRANT SELECT on ca.fct_subscription to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.ang_airport to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_booking_accounting to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on ext.ext_xpay_bank_transaction to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.fct_event to talend_dwh_role; GRANT SELECT on ca.cl_email_events to talend_dwh_role; GRANT SELECT on ca.foto_dim_user to talend_dwh_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES on dwh.fct_insurance to talend_dwh_role; I'd like to solve this issue. Thanks, Pietro

[Deleted User]

Hi Pietro, Thanks for the update. Hm... Looking at these statements, it looks like you're setting lots of different permissions. Is it possible that you're issuing multiple GRANT statements per table per user? (Possibly with different permissions.) The GRANTS table doesn't contain one row per GRANT statement. It contains one row per table per user, listing an aggregate of all GRANTs that apply to that user on that table. So issuing additional GRANTs won't grow the table in that case; the information's still there, just as an update to existing records. If that's not your issue: I've written the following Python script to generate a .sql file: (The actual .sql file is too long to post here, I think...) """ print "create role test_role;" print "create schema test;" for i in xrange(500): print "create table test.t%d (i int);" % i print "grant all on schema test to test_role;" # Make sure new GRANTs are added for i in xrange(500): print "grant insert, update, delete, references on test.t%d to test_role;" % i # Make sure additional permissions are merged into the same rows for i in xrange(500): print "grant select on test.t%d to test_role;" % i print "select count(*) from grants where grantee = 'test_role';" print "drop role test_role cascade;" print "drop schema test cascade;" """ When I run the .sql file, the SELECT statement at the bottom returns: """ count ------- 501 (1 row) """ Which is exactly what I would expect. So if you have further issues, I'm not sure how to help... I'd really need some SQL statements that actually reproduce the issue -- not just a sample of commands; an actual reproducer, a complete, standalone .sql script that, if run, doesn't just trigger the problem, but produces output (typically via a SELECT statement at the end, like the above) that is clearly wrong in the way that you're seeing. Without that, I don't immediately recognize this issue so I'm kinda just floundering, trying to understand what's going on. With that, anyone can see exactly what you're talking about on their own machine; makes it much much quicker to debug. If you have an enterprise account and want a quick resolution, you should probably contact our support group; they're set up to answer this sort of question properly.

Pietro_La_Torre

Hi again Adam, thanks for your efficiency. In this case it doesn't happen to add grants to existing ones. Actually my SQL script has only one row for each (schema.table,role) couple. What I forgot to tell you is that in the end of my SQL script I also change the ownership. And that is my problem. In fact, I found out that the rows disappear from grants table if the object owner changes. I made the following example: -- these 3 instructions are useful to restart the example (for now just ignore them) drop table stg.example; drop user example; drop user example2; drop role example_role; -- I create 2 users, a table and a role create user example identified by 'pwd'; create user example2 identified by 'pwd'; create table stg.example(aaa int); CREATE ROLE example_role; --I check that grants table is clear select * from grants where grantee in ('example','example2','example_role'); --only 2 rows (for usage) --I give grant to the role GRANT USAGE on schema stg to example_role; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE stg.example to example_role; select * from grants where grantee in ('example','example2','example_role'); --I see the new grant assigned to the role GRANT example_role TO example2; ALTER USER example2 DEFAULT ROLE example_role; select * from grants where grantee in ('example','example2','example_role'); --I see that example_role can be used to example2 select * from users where user_name = 'example2'; ALTER TABLE stg.example OWNER TO example; select * from grants where grantee in ('example','example2','example_role'); --the grant on the table assigned to the role disappears!! As a consequence neither example2 nor example_role can't see stg.example GRANT example_role TO example; ALTER USER example DEFAULT ROLE example_role; select * from grants where grantee in ('example','example2','example_role'); --I see that example_role can be used by example select * from users where user_name = 'example'; GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE stg.example to example_role; --I try to give again the grant to the role select * from grants where grantee in ('example','example2','example_role'); --this time I see 2 new grants: 1 for the role example_role and 1 for the user example (which is owner) As a conclusion, if I am developing a grant script I should assign owners first and grants later, otherwise when changing ownership I would lose the grants assigned before. Am I missing something? is it correct that by changing the owner the grants are cleared? thanks a lot, Pietro

[Deleted User]

Hi Pietro, I'm not personally a Vertica-permissions guy; someone who is may come along and correct me. But your observations appear to be correct; for now, if you're writing a script, you should assign owners first and other permissions second. At the very least, there's no harm in doing so (as long as it's not too inconvenient). Adam

[Deleted User]

Ah, I see what's going on here. The documentation actually mentions it: If you take a look at the documentation for ALTER TABLE: https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#1295.htm it does specify that any existing GRANTs on the table are revoked when the owner changes. So, yes, you should set the owner first and grant later. The rationale is, only the owner can do grants; if that user is having their granting privileges revoked, then all grants that were given out using said privileges are revoked as well.