Objects invisible to users when NOT created by dbadmin
Posted: Mon Jul 23, 2018 10:23 pm
I have inherited a database where all objects are owned by dbadmin. I have roles set up to grant privileges out to users, normally this works fine.
However in order to spare myself some work, I wanted to allow developers to create/alter/drop objects they need on our dev instance, through a "developer" role. In this case he created a view. This seemed to work fine, the view was there, owned by the developer's account. He granted SELECT access to the "it_user" role. I could query it from the dbadmin account.
However, none other users could use it, getting "permission denied" errors. I tripled checked that the roles has SELECT privs and the roles were granted to the users as default, but still nothing.
On a whim I recreated the view as dbadmin with the same script that my developer used for his account. Everything works fine now.
Why can't users see the view created by a developer account?
However in order to spare myself some work, I wanted to allow developers to create/alter/drop objects they need on our dev instance, through a "developer" role. In this case he created a view. This seemed to work fine, the view was there, owned by the developer's account. He granted SELECT access to the "it_user" role. I could query it from the dbadmin account.
However, none other users could use it, getting "permission denied" errors. I tripled checked that the roles has SELECT privs and the roles were granted to the users as default, but still nothing.
On a whim I recreated the view as dbadmin with the same script that my developer used for his account. Everything works fine now.
Why can't users see the view created by a developer account?