Re: ORA-01031: Insufficient Privileges (Urgent)

Hi,
I think you may be missing a "grant select on vw_qqqq to y;" Due to Oracle's definer rights model there should be no need to grant select or execute on the underlying objects to the other user. I ran the following on a 9i database without a problem:

SQL> create user test1 identified by test1 default tablespace users temporary tablespace temp;

User created.

SQL> create user test2 identified by test2 default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to test1, test2;

Grant succeeded.

SQL> conn test1/test1
Connected.

SQL> create table t1 (n1 number);
Table created.

SQL> create table t2 (v1 varchar2(10));
Table created.

SQL> create function f1 return date is
  2 begin
  3 return sysdate;
  4 end;
  5 /

Function created.

SQL> create view v1 as
  2 select n1, v1, f1 from t1, t2;
View created.

SQL> insert into t1 values (1);
1 row created.

SQL> insert into t2 values ('a');
1 row created.

SQL> select * from v1;

        N1 V1 F1
---------- ---------- ---------

         1 a 01-SEP-06
1 row selected.

SQL> grant select on v1 to test2;
Grant succeeded.

SQL> conn test2/test2
Connected.

SQL> select * from test1.v1;

        N1 V1 F1
---------- ---------- ---------

         1 a 01-SEP-06
1 row selected.

SQL> Cheers,

Stu.

On 9/1/06, crcbedoy_at_proteccion.com.co <crcbedoy_at_proteccion.com.co> wrote:
>
>
> 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