Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the

ALTER SESSION SET_CURRENT_SCHEMA=vwowner

Roles are transient. You can do a SET ROLE NONE to turn them all off. You can have multiple sessions for the same account with different roles enabled. That isn't compatible with the way that Oracle handles objects (where they are either valid or they are not; they can't be valid for some sessions and not for others).

share|improve this answer
    
Yeah I get it now, thanks. We do we have a "market" schema with all the tables in it and many views. But the users still like to play around writing their own views in their own accounts, hence the question. I suppose I can just grant access to the tables as they request them. –  asgeo1 Jul 27 '11 at 6:51
add comment

The simple explanation is that the software was not designed to allow that. As Gary pointed out, since roles were designed to be able to be turned on and off the view could be valid for some sessions while it isn’t for others. However, the system could be designed to allow roles to work.

What we need is a persistent role or a device similar to a role that is always on and cannot be disabled. This would be a useful addition to Oracle for situations such as views joining data from another schema to data in the current schema. If even a small number of schema are doing this with only a few tables, the time and maintenance savings of a persistent role would be worthwhile.

As a workaround you could create a procedure that grants permissions on a set of tables to a given user and then run that for each user you grant the role to. You might even be able to pass the procedure a role name and have it generate what is required, but in the end it would still be a kludgy solution.

share|improve this answer
    
Good point with messing around with the privileges by turning off a role. –  a_horse_with_no_name Jul 27 '11 at 15:35
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.