Using the "Edit..." action from the popup menu on a view is dangerous.
create or replace view test_view(id_col)
as select null id_col from dual;
grant select on
test_view to <any_role>;
Edit the view, changing f.ex. null to 1: "as select 1 id_col from dual" Select privilege is gone!
Seems like behind the scenes the view is dropped and recreated instead of using "create or replace", which does not interfere with the privileges.
Also, when trying out this example of mine, each time I ran into an error while trying to update the view via this dialog (such as a missing column alias) the view had been dropped.
This edit "feature" should be removed, it is quite dangerous in it's current state.
Edited by: Björn Darri on Oct 26, 2012 4:37 AM
This behavior is still present in version 3.2.20.
Selecting the DDL tree page and setting the SQL Statement radio control to "Create" does change the code in the SQL statement to "CREATE OR REPLACE VIEW", but selecting OK on the Edit View dialog DOES NOT run this code. It still drops the table and recreates it - which causes all privileges to be lost.
The default for editing a view should be to "CREATE OR REPLACE VIEW".
Please consider fixing the Edit View dialog to:
- Default to the "CREATE OR REPLACE VIEW" logic
- When DDL tree page is selected, the code that is displayed in the SQL Statement should be executed when the "OK" button is selected.
I agree with the original posting that using the "Edit..." action from the popup menu on a view is dangerous. It has bitten us quite a few times.