Does recreating a view remove the privileges?

Short answer: No

This blog post is pretty much just to document what seems to have been hard to find online.

At work there was a big rework of a liquibase project due to the perception that “create or replace view” on an existing view would make the granted privileges be dropped. That was of course not what happened.

The issue was that using liquibase the new version of the view was installed. Then the rollback was triggered and it executed the manual version of undoing the create view. Unfortunately the way this was done was by issuing a “drop view”. After that the update via Liquibase was executed again.

This ended up giving people the impression that replacing a view results in privileges being lost. They are not lost and once the conclusion was questioned it was easily proven that the database does not lose privileges on a replaced view, it does however drop them if you drop the view.

Running a few google searches I found some bad information, primarily on some large forums not too trafficked by Oracle or even database experts.

I just wanted to put this out in case it may help someone struggling with this in the future and hopefully someone finds this and not the erroneous forum posts about how databases work. They do not implement replacing a view as drop view and create view thus losing privileges…

The Internet is a funny and scary place.

May 13th, 2019 by