ORA-01031 when creating a view

[BarbaraFuhrmann]

Hi,

we run a database that was developed until now by a user called "DEVELOPER". So all the elements belong to this schema. Now the management set the rule that every employee should work with a personel user. Because I want to keep all elements in schema DEVELOPER, I did the following:

- created user FUHRMANN
- granted DBA to FUHRMANN
- build a logon trigger that changes the current schema to DEVELOPER for FUHRMANN
- created public synonyms for every element in the database
- created a role DEVELOPMENT
- granted all rights for every element to role DEVELOPMENT
- granted role DEVELOPMENT to FUHRMANN

When FUHRMANN drops a view, this is done.
When FUHRMANN tries to create the view again an
"ORA-01031 insufficient privileges"
appears.
The documentation says "Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges."

I'm pretty sure that I did not try to change the password nor to install a database.

When FUHRMANN executes the select statement that is to be the view, the data is displayed as aspected.

What is wrong? Does anyone have an idea what I missed out?

Thank you
Barbara

 

[gunjan14]

Hi Barbara,
I guess the object still resides in schema "DEVELOPER". To create a view in FUHRMANN user that is accessing the tables in DEVELOPER schema, you need to grant explicit select privilege on the tables in DEVELOPER schema to FUHRMANN (means it has to be given as the code below shows

<FROM USER DEVELOPER>
[b]
grant select on <table_name> to FUHRMANN;
[/b]

and not through the roles).

Let me know if this resolves your issue

BR,
Gunjan