1 Reply Latest reply: Aug 16, 2013 10:56 AM by Barbara Boehmer RSS

    Select view from user

    user1764238

      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

          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.