This discussion is archived
1 Reply Latest reply: Aug 16, 2013 8:56 AM by Barbara Boehmer RSS

Select view from user

user1764238 Newbie
Currently Being Moderated

Hi,

 

I have two schema lets say Schema A and Schema B.

 

I am connecting to Schema A and trying to select one view of Schema B.

 

But I am getting " ORA-01031: insufficient privileges" error all the time.

 

I checked the Schema A privileges and found all the privileges has been assigned to it. Also If I provided "select any table" role to Schema A, everything is working finr. But I dont want to use this role to be granted on Schema A.

 

Is there any possibilities without using this role I can select view from Schema B?

 

There are two synonyms created with the same name as the view. The synonym is not a public synonym. also the owner for those two synonym is Schema B and a different schema.

 

Pleas ehelp me to sort this issue.

 

Thanks.

  • 1. Re: Select view from user
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    This question has nothing to do with Objects, so perhaps some moderator will move it from Objects to SQL and PL/SQL.

     

    Please see the example below and read the comments within it for a demonstration and explanation of the minimum privileges required.

     

    SCOTT@orcl12c_11gR2> -- user A and privileges:

    SCOTT@orcl12c_11gR2> create user A identified by A

      2  /

     

    User created.

     

    SCOTT@orcl12c_11gR2> grant create session, create synonym to A

      2  /

     

    Grant succeeded.

     

    SCOTT@orcl12c_11gR2> -- user B and privileges:

    SCOTT@orcl12c_11gR2> create user B identified by B

      2  /

     

    User created.

     

    SCOTT@orcl12c_11gR2> alter user B quota unlimited on users -- or smaller

      2  /

     

    User altered.

     

    SCOTT@orcl12c_11gR2> grant create session, create table, create view, create synonym to B

      2  /

     

    Grant succeeded.

     

    SCOTT@orcl12c_11gR2> -- user B creates table, view, synonym, and grants select on view to user A

    SCOTT@orcl12c_11gR2> -- (note that you cannot create a synonym with the same name as the view in the same schema):

    SCOTT@orcl12c_11gR2> connect B/B

    Connected.

    B@orcl12c_11gR2> create table b_tab (b_col  number)

      2  /

     

    Table created.

     

    B@orcl12c_11gR2> insert into b_tab values (1)

      2  /

     

    1 row created.

     

    B@orcl12c_11gR2> create view b_view as select * from b_tab

      2  /

     

    View created.

     

    B@orcl12c_11gR2> create synonym b_view for b_view

      2  /

    create synonym b_view for b_view

    *

    ERROR at line 1:

    ORA-01471: cannot create a synonym with same name as object

     

     

    B@orcl12c_11gR2> create synonym b_syn for b_view

      2  /

     

    Synonym created.

     

    B@orcl12c_11gR2> grant select on b_view to A

      2  /

     

    Grant succeeded.

     

    B@orcl12c_11gR2> -- user A cannot select from the table, with or without the schema,

    B@orcl12c_11gR2> -- user A cannot select from the view or synonym without the schema,

    B@orcl12c_11gR2> -- user A can select from the view or synonym with the schema

    B@orcl12c_11gR2> connect A/A

    Connected.

    A@orcl12c_11gR2> select * from b_tab

      2  /

    select * from b_tab

                  *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

     

    A@orcl12c_11gR2> select * from b.b_tab

      2  /

    select * from b.b_tab

                    *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

     

    A@orcl12c_11gR2> select * from b_view

      2  /

    select * from b_view

                  *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

     

    A@orcl12c_11gR2> select * from b_syn

      2  /

    select * from b_syn

                  *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

     

    A@orcl12c_11gR2> select * from B.b_view

      2  /

     

         B_COL

    ----------

             1

     

    1 row selected.

     

    A@orcl12c_11gR2> select * from B.b_syn

      2  /

     

         B_COL

    ----------

             1

     

    1 row selected.

     

    A@orcl12c_11gR2> -- if user A creates a synonym, then user A can select using the synonyn:

    A@orcl12c_11gR2> create synonym b_view for B.b_view

      2  /

     

    Synonym created.

     

    A@orcl12c_11gR2> select * from b_view

      2  /

     

         B_COL

    ----------

             1

     

    1 row selected.