When creating views, why do users need direct object permissions if they already have the same permissions via a role?

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.

From the manual:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510


The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

(Emphasis by me)

So I guess there is no way around this problem.

StackExchange Database Administrators Q#4137, answer score: 7