I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this.
I have a schema with a table in it. I have granted select on that table to a role.
grant select on user1.example_table to example_role;
I then grant that role to a user:
grant example_role to user2;
Then user2 wants to create a view on top of that table:
create or replace view user2.example_view as select * from user1.example_table;
That throws an error however:
ORA-01031: insufficient privileges
Why though? If they have select permission via the role, why can they not then create a view on that object?
I found that I had to grant the object directly to the user before it would work.
grant select on user1.example_table to user2;
Is there anyway not to have to do this? I wanted to use roles, because I have a lot of tables and a lot of users, and don't want to have to maintain a million different grants to individual users.