9 Replies Latest reply: Apr 30, 2013 1:53 AM by jeneesh RSS

    View Creation Error

    EV259
      Hi,
      SQL> SELECT SP.ID,
        2    SP.TENANTID,
        3    CM.COMPANYNAME AS TENANTNAME,
        4    SP.AMOUNTPAID,
        5    SP.PAYMENTDATE,
        6    SP.MODEOFPAYMENTID,
        7    PAYMENTTYPE.DESCRIPTION AS MODEOFPAYMENTTYPEDESC,
        8    SP.BANKNAME,
        9    SP.CHEQUEORCARDNUMBER AS CHEQUEORCARDNO,
       10    SP.ACTIVE,
       11    SP.CREATEDBY,
       12    SP.CREATEDAT,
       13    SP.MODIFIEDBY,
       14    SP.MODIFIEDAT,
       15    SP.ISDELETED,
       16    SP.SAVESTATUS,
       17    SP.ENTRYORIGIN,
       18    SP.APPROVALSTATUS,
       19    SP.ENTITYID,
       20    TS.PACKAGEID,
       21    PM.PACKAGENAME,
       22    PM.FROMDATE,
       23    PM.TODATE,
       24    SP.ROWVER,
       25    SP.TENANTSUBSCRIPTIONID
       26  FROM ADMIN.SUBSCRIPTIONPAYMENT SP
       27  LEFT OUTER JOIN ADMIN.TENANTSUBSCRIPTION TS
       28  ON SP.TENANTID=TS.TENANTID
       29  LEFT OUTER JOIN ADMIN.PACKAGEMASTER PM
       30  ON TS.PACKAGEID=PM.ID
       31  LEFT OUTER JOIN SOLADMIN.COMPANYMASTER CM
       32  ON SP.TENANTID=CM.ID
       33  LEFT OUTER JOIN SOLADMIN.LOOKUPVALUES PAYMENTTYPE
       34  ON SP.MODEOFPAYMENTID = PAYMENTTYPE.ID
       35  LEFT OUTER JOIN soladmin.lookupmaster lm
       36  on lm.id           = paymenttype.lookupid
       37  WHERE (SP.ISDELETED='0' ) ;
      
      no rows selected
      
      SQL> create or replace VIEW ADMIN.BROWSESUBSCRIPTIONPAYMENT as
        2  SELECT SP.ID,
        3    SP.TENANTID,
        4    CM.COMPANYNAME AS TENANTNAME,
        5    SP.AMOUNTPAID,
        6    SP.PAYMENTDATE,
        7    SP.MODEOFPAYMENTID,
        8    PAYMENTTYPE.DESCRIPTION AS MODEOFPAYMENTTYPEDESC,
        9    SP.BANKNAME,
       10    SP.CHEQUEORCARDNUMBER AS CHEQUEORCARDNO,
       11    SP.ACTIVE,
       12    SP.CREATEDBY,
       13    SP.CREATEDAT,
       14    SP.MODIFIEDBY,
       15    SP.MODIFIEDAT,
       16    SP.ISDELETED,
       17    SP.SAVESTATUS,
       18    SP.ENTRYORIGIN,
       19    SP.APPROVALSTATUS,
       20    SP.ENTITYID,
       21    TS.PACKAGEID,
       22    PM.PACKAGENAME,
       23    PM.FROMDATE,
       24    PM.TODATE,
       25    SP.ROWVER,
       26    SP.TENANTSUBSCRIPTIONID
       27  FROM ADMIN.SUBSCRIPTIONPAYMENT SP
       28  LEFT OUTER JOIN ADMIN.TENANTSUBSCRIPTION TS
       29  ON SP.TENANTID=TS.TENANTID
       30  LEFT OUTER JOIN ADMIN.PACKAGEMASTER PM
       31  ON TS.PACKAGEID=PM.ID
       32  LEFT OUTER JOIN SOLADMIN.COMPANYMASTER CM
       33  ON SP.TENANTID=CM.ID
       34  LEFT OUTER JOIN SOLADMIN.LOOKUPVALUES PAYMENTTYPE
       35  ON SP.MODEOFPAYMENTID = PAYMENTTYPE.ID
       36  LEFT OUTER JOIN soladmin.lookupmaster lm
       37  on lm.id           = paymenttype.lookupid
       38  WHERE (SP.ISDELETED='0' ) ;
      LEFT OUTER JOIN SOLADMIN.COMPANYMASTER CM
                               *
      ERROR at line 32:
      ORA-00942: table or view does not exist
      In the above the SELECT is working, but while creating the view giving me an error. what could be the issue, Please help me. All the users are having the DBA role.

      Thanks
        • 1. Re: View Creation Error
          Peter Gjelstrup
          Hi,

          If ADMIN wants to create a view that references a table in SOLADMIN, then he should be granted privileges to do so directly, not through a role.

          Regards
          Peter
          • 2. Re: View Creation Error
            Solomon Yakobson
            From Oracle docs: "The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, <font color=red>rather than through a role</font>".

            Most likely user has select on SOLADMIN.COMPANYMASTER via role. In order to create a view you must grant select on SOLADMIN.COMPANYMASTER to view owner directly, not via role.

            SY.
            • 3. Re: View Creation Error
              EV259
              Thanks Peter and SY for your response.

              Could you also give me the example, to give access to multiple objects at a time. Suppose I have 20 tables in SOLADMIN and want to give the direct access to ADMIN in order to create the views based on the base tables in SOLADMIN schema.

              It was my mistake and corrected now, I thought if both the users having the DBA role, they can do any thing in the database and with the other schema objects too.

              Thanks
              • 4. Re: View Creation Error
                Solomon Yakobson
                968217 wrote:
                Thanks Peter and SY for your response.

                Could you also give me the example, to give access to multiple objects at a time.
                Oracle allows object level direct grants (e.g. SELECT ON schema.table) or database level (sytem privileges, e.g. SELECT ANY TABLE) direct grants. You can't grant privilege on all objects in a schema or on a group of objects.

                SY.
                • 5. Re: View Creation Error
                  EV259
                  SQL> conn sys as sysdba
                  Enter password:
                  Connected.
                  SQL> show user;
                  USER is "SYS"
                  SQL> grant all on SOLADMIN.COMPANYMASTER to admin;
                  
                  Grant succeeded.
                  After issuing the above statement I am succeeded with the view creation. What and all the privileges ADMIN has now on SOLADMIN.COMPANYMASTER.

                  Thanks,
                  • 6. Re: View Creation Error
                    Solomon Yakobson
                    Why are you granting it as SYS? Now only SYS will be able to revoke it. Normally object privilege is granted by object owner.

                    SY.
                    • 7. Re: View Creation Error
                      EV259
                      SQL> grant all on SOLADMIN.COMPANYMASTER to admin;

                      If I issue the above statement by logged in as SOLADMIN, what and all the privileges will have the user ADMIN on SOLADMIN.COMPANYMASTER table.

                      Thanks in advance.

                      Thanks
                      • 8. Re: View Creation Error
                        EV259
                        deleted as not relevant to this
                        • 9. Re: View Creation Error
                          jeneesh
                          968217 wrote:
                          SQL> grant all on SOLADMIN.COMPANYMASTER to admin;

                          If I issue the above statement by logged in as SOLADMIN, what and all the privileges will have the user ADMIN on SOLADMIN.COMPANYMASTER table.

                          Thanks in advance.

                          Thanks
                          SQL> conn
                          Enter user-name: scott
                          Enter password:
                          Connected.
                          SQL> drop table test;
                          
                          Table dropped.
                          
                          SQL> create table test(id number);
                          
                          Table created.
                          
                          SQL> grant all on test to hr;
                          
                          Grant succeeded.
                          
                          SQL> conn
                          Enter user-name: hr
                          Enter password:
                          Connected.
                          
                          SQL> select privilege,grantee
                            2  from all_tab_privs
                            3  where table_schema = 'SCOTT'
                            4  and table_name = 'TEST';
                          
                          PRIVILEGE                                GRANTEE
                          ---------------------------------------- ---------
                          ALTER                                    HR
                          DELETE                                   HR
                          INDEX                                    HR
                          INSERT                                   HR
                          SELECT                                   HR
                          UPDATE                                   HR
                          REFERENCES                               HR
                          ON COMMIT REFRESH                        HR
                          QUERY REWRITE                            HR
                          DEBUG                                    HR
                          FLASHBACK                                HR
                          
                          11 rows selected.