RE: ORA-01031: Insufficient Privileges (Urgent)

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Thu, 31 Aug 2006 10:28:45 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF6815A9@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of crcbedoy_at_proteccion.com.co Sent: Thursday, August 31, 2006 10:20 AM To: Oracle-L_at_FreeLists.org
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:

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 31 2006 - 09:28:45 CDT