FAQ
Greetings,

I just demonstrated something to myself which took me by surprise and am wondering if I am perhaps missing something. Running Oracle 9.2.0.8.0 EE on RHEL 64-bit. Here is the scenario...

schema X has a view, v1 which selects from table t1 owned by schema Y.
schema Y has granted select on t1 to schema X.
schema X has in turn granted select on view v1 to user Z.

When user Z issues the command

SQL> select * from X.v1;

ORA-01031, insufficient privileges is returned. In order to resolve this schema Y had to

SQL> grant select on v1 to X with grant option;

My question. I didn't expect it would be necessary to issue the grant with grant option. Am I missing something or is there another way to handle this? I don't want to issue the grant to public and I am not particularly happy about user X being able to grant select on view v1 to others. If this is the way it must be so be it but it took me by surprise.

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208

Search Discussions

  • Jared Still at Sep 10, 2008 at 8:25 pm

    On Wed, Sep 10, 2008 at 1:05 PM, William Wagman wrote:

    ORA-01031, insufficient privileges is returned. In order to resolve this
    schema Y had to

    SQL> grant select on v1 to X with grant option;
    It has worked this way for quite a long time.

    SELECT privs on T1 have been granted to X

    Y as the owner of T1, does not intend for X to share this data with Z, or
    anyone else.

    Y has to explicitly allow X to share this data, which is why the 'grant
    option' is needed
    for X to allow Z to see the view V1.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • William Wagman at Sep 10, 2008 at 8:29 pm
    One learns something new every day, I did not know this. Thank you.

    Bill Wagman
    Univ. of California at Davis
    IET Campus Data Center
    wjwagman_at_ucdavis.edu
    (530) 754-6208
    From: Jared Still
    Sent: Wednesday, September 10, 2008 1:26 PM
    To: William Wagman
    Cc: oracle-l_at_freelists.org
    Subject: Re: Question re view privileges

    On Wed, Sep 10, 2008 at 1:05 PM, William Wagman > wrote:
    ORA-01031, insufficient privileges is returned. In order to resolve this schema Y had to

    SQL> grant select on v1 to X with grant option;

    It has worked this way for quite a long time.

    SELECT privs on T1 have been granted to X

    Y as the owner of T1, does not intend for X to share this data with Z, or anyone else.

    Y has to explicitly allow X to share this data, which is why the 'grant option' is needed
    for X to allow Z to see the view V1.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F (LABOR) at Sep 11, 2008 at 12:14 pm
    Bill,



    I follow the basic rule that if a view is needed on data from a specific
    schema, then that schema should own the view. There is no good reason
    for allowing another schema to create views on a base schema and then
    share that view around to other users. As a DBA, you lose control very
    quickly and answering the question from auditors later on of "Who can
    see his data" gets very hard to answer.



    Just my opinion.



    Tom



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of William Wagman
    Sent: Wednesday, September 10, 2008 4:30 PM
    To: Jared Still
    Cc: oracle-l_at_freelists.org
    Subject: RE: Question re view privileges



    One learns something new every day, I did not know this. Thank you.



    Bill Wagman
    Univ. of California at Davis
    IET Campus Data Center
    wjwagman_at_ucdavis.edu
    (530) 754-6208

    From: Jared Still
    Sent: Wednesday, September 10, 2008 1:26 PM
    To: William Wagman
    Cc: oracle-l_at_freelists.org
    Subject: Re: Question re view privileges



    On Wed, Sep 10, 2008 at 1:05 PM, William Wagman
    wrote:

    ORA-01031, insufficient privileges is returned. In order to
    resolve this schema Y had to


    SQL> grant select on v1 to X with grant option;

    It has worked this way for quite a long time.

    SELECT privs on T1 have been granted to X

    Y as the owner of T1, does not intend for X to share this data with Z,
    or anyone else.

    Y has to explicitly allow X to share this data, which is why the 'grant
    option' is needed
    for X to allow Z to see the view V1.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Roman Podshivalov at Sep 11, 2008 at 1:57 pm
    Such situation is being addressed in 10G by:

    SQL> grant select on v1 to u3;
    grant select on v1 to u3

    *

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

    SQL> !oerr ora 1720
    01720, 00000, "grant option does not exist for '%s.%s'"

    // *Cause: A grant was being performed on a view and the grant option was
    // not present for an underlying object.
    // *Action: Obtain the grant option on all underlying objects of the view.

    --romas
    On Wed, Sep 10, 2008 at 4:05 PM, William Wagman wrote:

    Greetings,

    I just demonstrated something to myself which took me by surprise and am
    wondering if I am perhaps missing something. Running Oracle 9.2.0.8.0 EE on
    RHEL 64-bit. Here is the scenario...

    schema X has a view, v1 which selects from table t1 owned by schema Y.
    schema Y has granted select on t1 to schema X.
    schema X has in turn granted select on view v1 to user Z.

    When user Z issues the command

    SQL> select * from X.v1;

    ORA-01031, insufficient privileges is returned. In order to resolve this
    schema Y had to

    SQL> grant select on v1 to X with grant option;

    My question. I didn't expect it would be necessary to issue the grant with
    grant option. Am I missing something or is there another way to handle this?
    I don't want to issue the grant to public and I am not particularly happy
    about user X being able to grant select on view v1 to others. If this is the
    way it must be so be it but it took me by surprise.

    Thanks.

    Bill Wagman
    Univ. of California at Davis
    IET Campus Data Center
    wjwagman_at_ucdavis.edu
    (530) 754-6208

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 10, '08 at 8:05p
activeSep 11, '08 at 1:57p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase