1 2 Previous Next 16 Replies Latest reply: Apr 17, 2012 4:25 AM by 900442 RSS

    Intersting Issue

    user12000301
      Hi,
      DB_VERSION=10.2.0.4
      OS_VERSION=Windows 2008

      I have user TEST with following privileges ;
      CREATE SESSION
      UNLIMITED TABLESPACE
      CREATE SYNONYM
      Also as a USER_A i have created a view "VW_TEST" which refers to 2 tables T1 ,T2 of USER_B. This view has been create successfully.

      Now i wish to grant SELECT Privilege ON this view i.e. USER_A.VW_TEST to TEST user.

      For that I have given required grants to TEST user as follows :
      grant SELECT on USER_B.T1 to TEST;
      grant SELECT on USER_B.T2 to TEST;
      Now as a TEST user when i wish to access this view , I am getting following error :

      SQL>show user
      SQL> "TEST"
      SQL> select * from VW_TEST;
      select * from VW_TEST
                    *
      ERROR at line 1:
      ORA-01031: insufficient privileges
      Did i missed any thing ??
        • 1. Re: Intersting Issue
          Fran
          edited:

          Aman and Kuljeet Pal Singh has the correct answer

          Edited by: Fran on 16-abr-2012 7:57
          • 2. Re: Intersting Issue
            Aman....
            PLease see, running on 11201 , Windows XP,
            SQL>
            SQL>
            SQL> drop user test cascade;
            
            User dropped.
            
            SQL> create user test identified by test;
            
            User created.
            
            SQL> grant create session, CREATE SYNONYM to test;
            
            Grant succeeded.
            
            SQL> conn aman/aman
            Connected.
            SQL> drop table t1 purge;
            drop table t1 purge
                       *
            ERROR at line 1:
            ORA-00942: table or view does not exist
            
            
            SQL> drop table t2 purge;
            drop table t2 purge
                       *
            ERROR at line 1:
            ORA-00942: table or view does not exist
            
            
            SQL> create table t1 (a number);
            
            Table created.
            
            SQL> create table t2 (a number);
            
            Table created.
            
            SQL> create view v1 as select t1.a as a_t1, t2.a as a_t2 from t1, t2;
            
            View created.
            
            
            
            SQL> grant select on t1 to test;
            
            Grant succeeded.
            
            SQL> grant select on t2 to test;
            
            Grant succeeded.
            
            SQL> conn test/test
            Connected.
            SQL> select * from aman.v1;
            select * from aman.v1
                               *
            ERROR at line 1:
            ORA-00942: table or view does not exist
            
            
            SQL> conn aman/aman
            Connected.
            SQL> grant select on v1 to test;
            
            Grant succeeded.
            
            SQL> conn test/test
            Connected.
            SQL> select * from aman.v1;
            
            no rows selected
            
            SQL>
            HTH
            Aman....
            • 3. Re: Intersting Issue
              kuljeet singh -
              Now i wish to grant SELECT Privilege ON this view i.e. USER_A.VW_TEST to TEST user.
              grant SELECT on USER_B.T1 to TEST;
              grant SELECT on USER_B.T2 to TEST;
              grant select permission on view as well.
              • 4. Re: Intersting Issue
                user12000301
                Thanks Aman for your response.

                @Kuljeet .. I am Sorry i did not added that info ..My bad..

                I have already given SELECT privilege on view to TEST user

                SQL>grant SELECT on USER_A.VW_TEST to TEST;

                But still i was getting ORA-01031: insufficient privileges


                Now When i have given SELECT ANY TABLE Privilege to TEST user , I am able to access this View..

                However giving SELECT ANY TABLE system Privilege to TEST user doesn't seems good...
                • 5. Re: Intersting Issue
                  Fran
                  could you show us your users privileges?

                  SELECT privilege ,
                  admin_option "Admin"
                  FROM user_sys_privs;

                  and

                  select *
                  from user_tab_privs;

                  Edited by: Fran on 16-abr-2012 8:31
                  • 6. Re: Intersting Issue
                    Aman....
                    Can you show us a copy/paste from the sql*plus so that we can see what's going on actually? As I have shown, it works and it should work without any system privilege given to the user.

                    Aman....
                    • 7. Re: Intersting Issue
                      Mark Malakanov (user11181920)
                      Now i wish to grant SELECT Privilege ON this view i.e. USER_A.VW_TEST to TEST user.
                      For that I have given required grants to TEST user as follows :
                      grant SELECT on USER_B.T1 to TEST;
                      grant SELECT on USER_B.T2 to TEST;
                      If you want to grant select on the view, so grant it on the view itself, not on the tables that the view refers.
                      • 8. Re: Intersting Issue
                        user12000301
                        Hi Aman,

                        Please note that there's no Error while assgining Privileges to TEST user.

                        As discussed Earlier :

                        1. TEST user has following Privileges:
                        SQL >show user
                        USER is "TEST"
                        SQL >select * from session_privs;
                        
                        PRIVILEGE
                        ----------------------------------------
                        CREATE SESSION
                        UNLIMITED TABLESPACE
                        CREATE SYNONYM
                        2. As a USER_A , I have created A view which refers 2 tables (In FROM calse ) T1,T2 owned by USER_B.

                        3. I have given SELECT grant on USER_B.T1,USER_B.T2 to TEST user :
                        SQL>grant SELECT on USER_B.T1 to TEST;
                        SQL>grant SELECT on USER_B.T2 to TEST;
                        4. More importantly I have given SELECT privilege on the VW_TEST to TEST user
                        SQL>grant SELECT on USER_B.VW_TEST to TEST;
                        Now when i connect as TEST user and try to access USER_B.VW_TEST view , it ends up with
                        SQL> select * from USER_B.VW_TEST;
                        select * from USER_B.VW_TEST
                                      *
                        ERROR at line 1:
                        ORA-01031: insufficient privileges
                        But when i given SELECT ANY TABLE privilege , I am able to access the View... :-(
                        • 9. Re: Intersting Issue
                          Aman....
                          I can't think of anything that why its happening. Can you check the same on some another db of yours that its being repeated or not?

                          Aman....
                          • 10. Re: Intersting Issue
                            900442
                            4. More importantly I have given SELECT privilege on the VW_TEST to TEST user



                            SQL>grant SELECT on USER_B.VW_TEST to TEST;

                            -----------------------------------------------------------------------------------------------------------------------

                            Shouldn't it be USER_A.VW_TEST? as the view is owned by USER_A...
                            • 11. Re: Intersting Issue
                              900442
                              Done the following:

                              Created USER_A,USER_B AND TEST;
                              Created tab1,tab2 as USER_B;
                              Granted select on tab1,tab2 to USER_A with grant option;
                              Created view V1 as USER_A as select from USER_B.tab1,USER_B.tab2;
                              Granted select on view V1 to TEST;

                              connected as USER_A:
                              SQL> grant select on V1 to test;

                              disconnected and connected as TEST:
                              SQL> desc USER_A.V1

                              Name Null? Type
                              ----------------------------------------- -------- ----------------------------
                              A_T1 NUMBER
                              A_T2 CHAR(1)

                              HTH...
                              • 12. Re: Intersting Issue
                                user12000301
                                Correction ,it is USER_A.VW_TEST ...While re writting the issue , i have did this mistake

                                I have given :
                                SQL>grant SELECT on USER_A.VW_TEST to TEST;
                                But no success ..
                                • 13. Re: Intersting Issue
                                  Richard Harrison .
                                  Hi,
                                  The problem is you are indirectly granting permission on another users objects which my default you can't do. You need to make the following grants to the user that owns the view from the user that owns the table.

                                  grant select on USER_B.T1 to USER_A with admin option;
                                  grant select on USER_B.T2 to USER_A with admin option;

                                  Then try the select from test again.

                                  Cheers,
                                  Harry
                                  • 14. Re: Intersting Issue
                                    900442
                                    Hi,
                                    I think with grant option should solve your issue (or as suggested by Harry).

                                    Thanks....
                                    1 2 Previous Next