Oracle Wierdnees - View and Role

[rktscientist]

I have the dba role

I have a nice pretty view but I can't select from it - I get ORA-01031: insufficient privileges.

I can get the information using the sql that makes up the view.

I created a 2nd view using the exact same sql and it works for me just fine.

The moment I grant select to a specific role, which the original view has - I get the ORA-01031: insufficient privileges error again.

I am not in this role. I've added a few other different roles and haven't had any issues - it appears to be just this one role and it happens as soon as I give it select on the view - if i revoke the select privileges then the view works again.

Anyone have any ideas why giving select to a role would cause ORA-01031: insufficient privileges?

Thanks

[rktscientist]

Oh and the users in the role can run the underlying sql for the view as well but they also get the ORA-01031 error on the view.

The weird part is that I'm getting it as a dba but only when that role is granted select on the view...


-edited for grammer

[abhaysk]

any object/sys privs through role wont wrok in stored objects..

[rktscientist]

That's not what's weird.

What's weird is:

I'm not in that role

I can add other roles with no issues

The only time it seems like I can't access that view is when I add one specific role.

The people in the role can run the sql that makes up the view

I could understand it if just the people in the role are affected but why am I being affect when I'm not in that role?

Maybe I'm not understanding what you mean by
any object/sys privs through role wont wrok in stored objects..

[davey23uk]

and what do you by 'in a role' that doesnt make sense

[rktscientist]

Doh! - I think I forgot an important item.

I'm not the owner of the view.

There is a view called sch.v_view

I can access this view with no issues, since I'm in the dba role.

There is a role call sch_read - my user id is not in that role.

When I give select permissions to sch_read on sch.v_view
then I can't access that view any more - it gives me who is not in that role and those that are in that role ORA-01031: insufficient privileges

I can give select permissions to other roles and it works fine - I can still select. But the moment I add the sch_read role - it gives ORA-01031: insufficient privileges.

Does this make better sense?