Re: Privilege - View (ORA-1031)

From: Alberto Dell'Era <alberto.dellera_at_gmail.com>
Date: Tue, 2 Jan 2007 20:38:36 +0100
Message-ID: <4ef2fbf50701021138m6cbb2accj560d5c096a3f611a@mail.gmail.com>

Probably the "grant option" has been revoked (10.2.0.2):

dellera_at_ORACLE10> create table b.t (x int);

Table created.

dellera_at_ORACLE10> grant select on b.t to a;

Grant succeeded.

dellera_at_ORACLE10> create or replace view a.v as select * from b.t;

View created.

dellera_at_ORACLE10> grant select on a.v to c; grant select on a.v to c

                   *

ERROR at line 1:
ORA-01720: grant option does not exist for 'B.T'

Note: it is logical that you need the "grant option", since granting select on a view means granting read privs on (some) info of the table.

dellera_at_ORACLE10> grant select on b.t to a with grant option;

Grant succeeded.

dellera_at_ORACLE10> grant select on a.v to c;

Grant succeeded.

dellera_at_ORACLE10> revoke select on b.t from a;

Revoke succeeded.

dellera_at_ORACLE10> grant select on b.t to a;

Grant succeeded.

dellera_at_ORACLE10> conn c/c_at_oracle10g
Connected.
c_at_ORACLE10> select * from a.v;
select * from a.v

                *

ERROR at line 1:
ORA-01031: insufficient privileges

c_at_ORACLE10> select privilege from user_tab_privs where table_name = 'V';

PRIVILEGE

---

SELECT On 1/2/07, Shivaswamy Raghunath <shivaswamykr_at_gmail.com> wrote:
> Hello.
>
> Version - 10.1.0.5.0.
>
> User C is NOT able to select (ORA-1031) from a View V in Schema A (On which
> C has select privilege), which is based on a table in Schema B.
>
> If user C is granted Select any Table, he is able to select from the view V.
>
> This does not loook logical to me. Have you come across this?
>
> Thanks,
> Shiva
>

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 02 2007 - 13:38:36 CST