ThatJeffSmith

Preserving Privilege Grants Whilst Editing Views in Oracle SQL Developer

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", "REGION_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.

Grants for my view

Grants for my view

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.

Who wants to type when you can click?

Who wants to type when you can click?

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.

DROP...hmmm, wonder what that does?

DROP…hmmm, wonder what that does?

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’

CREATE gives us a CREATE OR REPLACE instead of a DROP then CREATE

CREATE gives us a CREATE OR REPLACE instead of a DROP then 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…

The default now goes to a CREATE OR REPLACE syntax.

The default now goes to a CREATE OR REPLACE syntax.