create or replace view loses grants on the view

Gregory_William

create schema gwtest; create table gwtest.x (a int); create view gwtest.y as select * from gwtest.x; grant usage on schema gwtest to zenpm; grant select on gwtest.y to someuser; grant usage on schema gwtest to someuser; select * from grants where object_schema='gwtest'; grant_id | grantor_id | grantor | privileges_description | object_schema | object_name | object_id | object_type | grantee_id | grantee -------------------+-------------------+---------+-------------------------------------------------+---------------+-------------+-------------------+-------------+-------------------+--------- 45035998801266060 | 45035996273704962 | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES* | gwtest | y | 45035998801266054 | VIEW | 45035996273704962 | dbadmin 45035998801266062 | 45035996273704962 | dbadmin | SELECT | gwtest | y | 45035998801266054 | VIEW | 67553994410572054 | someuser create or replace view gwtest.y as select *, 2 from gwtest.x; select * from grants where object_schema='gwtest'; grant_id | grantor_id | grantor | privileges_description | object_schema | object_name | object_id | object_type | grantee_id | grantee ----------+------------+---------+------------------------+---------------+-------------+-----------+-------------+------------+--------- I'd expect the grants to be maintained when executing create or replace view. This suggests that internally this is implemented as drop view; create view. Vertica doesn't appear to provide an ALTER VIEW variant that allows a view's definition to be changed, so I've used CREATE OR REPLACE VIEW to fit that purpose.

Vladimir__Baran

It's normal. You should keep your grants together with view's script and apply them at the same time. And be careful with "view in view", if your view contains another view inside and you modify and raplace internal veiw, you must replace the first view too otherwise your first view will keep in old version. Vertica stores views in precompiled code.

Sharon_Cutter

I don't think that this should be considered normal. In Oracle the grants would be preserved when CREATE OR REPLACE VIEW is used. This is the behavior users expect. "Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it." Regarding the previous comment about views in views. You don't need to replace the outer view if an inner view is modified. dbadmin=> create view v1 as select 'view 1'; CREATE VIEW dbadmin=> create view v2 as select * from v1; CREATE VIEW dbadmin=> select * from v2; ?column? ---------- view 1 (1 row) dbadmin=> create or replace view v1 as select 'modified v1'; CREATE VIEW dbadmin=> select * from v2; ?column? ------------- modified v1 (1 row)

Vladimir__Baran

Sybase IQ loses all grants like Vertica too. May be UDFs instead of views must be recreated ... now it works correctly in version 6.1.2

Sharon_Cutter

Actually if you change the columns that are selected in the inner view, you do need to recreate the outer view. It depends on what you've changed. So Oracle does preserve grants, and Sybase IQ does not. It seems silly not to preserve them - it's way too easy to casually do a CREATE OR REPLACE VIEW without giving a thought to the grants.