In order to consolidate two users, user1 and user2, in the database I
changed the owner of all tables and views that user2 owned to be owned
by user1. I then revoked all remaining privileges from user2 and
dropped the role. During this process I changed both table1 and view1
(that selects from table1) to be owned by user1. After this process,
user1 cannot select from view1 with an error saying permission denied
on table1. user1 can run the query in the definition of the view
without a problem. Granting select on table1 to public fixes this
problem. Running create or replace on the view with the same view
definition fixes this problem. Any idea what could have caused this?
It's almost as if user2 still existed in some settings for the view
and was only cleared out by the create or replace statement.
Thanks,
David