ORA-01031: Insufficient Privileges (Urgent)

 Hi all,
 
I am in a tough situation :(, I have searched around,  lots of FAQs, 
newsgroups, etc and my problem continues:(. I have followed all of 
the instructions given to other users with similar problems, but nothing 
seems to work because the error message keeps appearing.
 
My problem is: We have a view, this view is owned by X user and it's 
called by Y user.

connect as y/y;
select * from x.vw_qqqq;
ORA-01031: Insufficient Privileges

Now, this view is making a query to two tables: 
x.table1 
x.table2
It's also invoking the function: x.function1

We have a rol (since this view and other objects owned by this schema are 
going to be invoked by several other users), which is: rol_y, I granted 
the privileges with my username (A, granted with DBA privileges):
 
connect A/A;
GRANT SELECT ON x.table1 TO rol_y;
GRANT SELECT ON x.table2 TO rol_y;
GRANT ALL ON x.function1 TO rol_y;

And then:
connect as y/y;
select * from x.vw_qqqq;
ORA-01031: Insufficient Privileges

I then tried to:
 
connect A/A;
GRANT SELECT ON x.table1 TO y;
GRANT SELECT ON x.table2 TO y;
GRANT ALL ON x.function1 TO y with grant option;
 
I then created the synonyms to x.table1, x.table2 and x.function1 on 
schema y. I tried again and ORA-01031: Insufficient Privileges. 
I tried querying the tables one by one and there are no errors, but when I 
try to execute the function, it gives me the error: Function must 
declared.
After updating the view and canceling the function invoke, user x can 
query the view without any problem. Any help? hope? advice?..
 
S.O.S!!!!:(



Isabel Bedoya Gómez
Centro de Gestión y Control
Tel. 2307500 ext:

Other related posts: