RE: ORA-01031: Insufficient Privileges (Urgent)

Isabel,

 

Has role_y been granted to the y user?

 

Here is what I would do.

 

Revoke everything from user y.

Connect as user x.  

grant select on vw_qqqq to role_y.

 

You do not need to grant access to the underlying tables or the function that 
the view is using.  That is why we build views - to prevent access to tables.  
Grant everything to the role and grant the role to the user.

 

Also, always perform the grants from the user who owns the objects.  This is 
not a requirement, but it can makes things clearer in your head.  Think of 
ownership of the objects and who can grant access to the objects you own.  You 
wouldn't want your neighbor to lend the keys to your car to someone else.  So 
why do that here?

 

Hope this helps.


Tom


--------------------------------------------------------
This transmission may contain confidential, proprietary, or privileged 
information which is intended solely for use by the individual or entity to 
whom it is addressed.  If you are not the intended recipient, you are hereby 
notified that any disclosure, dissemination, copying or distribution of this 
transmission or its attachments is strictly prohibited.  In addition, 
unauthorized access to this transmission may violate federal or State law, 
including the Electronic Communications Privacy Act of 1985.  If you have 
received this transmission in error, please notify the sender immediately by 
return e-mail and delete the transmission and its attachments.


________________________________


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of crcbedoy@xxxxxxxxxxxxxxxxx
Sent: Thursday, August 31, 2006 10:20 AM
To: Oracle-L@xxxxxxxxxxxxx
Subject: 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: