Today’s Question:
How do we prevent Oracle SQL Developer from dropping the grants/privileges when modifying a view. While using SQL Developer we modified a view and once saved it removed all of the user access previously set up for the view. This did not previously take place when using…
Ugh. That doesn’t sound good at all. Let’s drill into this to see what’s happening.
This problem was fixed for version 4.0 of SQL Developer. See the update below.
We’ll need a view and some grants to experiment with.
CREATE OR REPLACE FORCE VIEW EMP_DETAILS_VIEW (EMPLOYEE_ID, JOB_ID, MANAGER_ID, DEPARTMENT_ID,LOCATION_ID,COUNTRY_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, DEPARTMENT_NAME. JOB_TITLE, CITY, STATE_PROVINCE, COUNTRY_NAME) AS SELECT e.employee_id , e.job_id , e.manager_id , e.department_id , d.location_id , l.country_id , e.first_name , e.last_name , e.salary , e.commission_pct , d.department_name , j.job_title , l.city , l.state_province , c.country_name , r.region_name FROM employees e , departments d , jobs j , locations l , countries c , regions r WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id AND 1 =1 WITH READ ONLY;
And a GRANT
GRANT SELECT ON EMP_DETAILS_VIEW TO PUBLIC;
Now let’s take a quick look at the View and any existing grants.
Editing the View
You can launch the Create/Alter dialog for your view by right-clicking on the view in the tree and selecting ‘Edit view.’
I’m going to move up some of the columns in the SELECT on the ‘SELECT clause’ page.
You COULD Click OK, or You Could Be Paranoid Like Me…
I like GUIs. I like SQL Developer. But when I’m working on the database, I don’t leave things to chance. I want to inspect what’s going to happen when I click ‘OK’ BEFORE I click OK, OK?
Luckily I can do that here and go to the ‘DDL’ page.
Guess what happens when you drop an object? That’s right, you lose all of object privileges for that object.
What do we do?
Toggle the Radio Switch to ‘CREATE’
This gives me a script that will REPLACE the existing view. The good news is that this will preserve any existing grants and synonyms on the view.
It’s Still Not Safe to Click ‘OK!’
However, if I click ‘OK’ on the Edit dialog, it’s still going to run the DROP as indicated in the radio control, e.g. ‘for current edit.’
I don’t want that to happen. So I’m going to copy the script out of the dialog and run it manually in the worksheet. I know many of you do this anyway or do it via SQL*Plus to adhere to your security and change management policies, but for those of you using SQL Developer in a production environment, please be sure to check the DDL page to see what’s issued on your behalf before running it.
Of course you’re testing this stuff in a proper test environment before running anything in production, right?
Anyways, I’m asking to have the dialog behavior changed so that the default behavior for views is to issue a ‘CREATE OR REPLACE’ in lieu of the ‘DROP…CREATE’ behavior. If you want to throw in your own two cents here to help strengthen my case, then please leave a comment.
Update: January 2, 2014
As of version v4.0, the default behavior whilst editing VIEWS will be to issue CREATE OR REPLACE DDL!
The proof is in the pudding…
5 Comments
You have my vote…
I’d like to add my vote to `create or replace` as the default behavior. The reasoning behind this is because the action the user starts in SQL-Developer is called `edit view`. If it would be called `drop and recreate view` than its appropriate to implement a drop and create solution. But since you call the function `edit view` I think it’s advisable to implement something that is as close as possible to the most likely understanding of the GUI element and most unlikely to have additional side effects (even though the side effect is obvious in hindsight).
Totally agree Thomas, and it should behave in this manner going forward with our next release.
Jeff, I vote for create or replace versus drop and create as the default behavior. I’ve had this happen to me before as well.
It looks as if this will indeed be the new behavior going forward – stay tuned for details 🙂
Thanks for chiming in Tony, appreciate the support!