10 Replies Latest reply: Nov 17, 2012 5:54 PM by Peter Gjelstrup RSS

    error on creation of a view

    GcG-Italia
      i have a norma view like this

      CREATE FORCE VIEW USERBO.USERPROFILE_VIEW
      (ROW_ID,EMPLOYEE,RESPONSABILITY_ID,RUOLO,E_USERNAME,E_PASSWORD,USERTYPE,USER_ID) AS
      SELECT RESPONSABILITYUSER.row_id,
      client.surname
      || ' '
      || client.NAME,
      RESPONSABILITYUSER.responsability_id,
      'USERWEB',
      upper(client.username) || '-' || client.organization_id,
      client.pwd,
      client.usertype,
      client.ROW_ID
      FROM RESPONSABILITYUSER,
      general.client
      WHERE ((client.row_id = RESPONSABILITYUSER.user_id))
      AND client.status ='ACTIVE'


      the select works fine but the view
      was create with "error"
      but how i can find the essror ??

      i tried "show error" but no error found
      insten when i create the view occur an error of creation and dos not work !!

      Help
        • 1. Re: error on creation of a view
          Solomon Yakobson
          SELECT  LINE,
                  POSITION,
                  TEXT
            FROM  ALL_ERRORS
            WHERE OWNER = 'USERBO'
              AND NAME = 'USERPROFILE_VIEW'
          /
          SY.
          • 2. Re: error on creation of a view
            SomeoneElse
            You can try select * from user_errors where name = 'USERPROFILE_VIEW';
            • 3. Re: error on creation of a view
              GcG-Italia
              LINE POSITION TEXT
              ----- --------- -------------------------------------------------------
              0 0 ORA-01730: numero nomi di colonne richiesto non valido
              0 0 ORA-01031: privilegi insufficienti


              BUT THE SELECT WORKS FINE
              AND ALL 2 USERS ARE DBA !!!

              WHAT I CAN DO ??
              • 4. Re: error on creation of a view
                rp0428
                >
                the select works fine but the view
                was create with "error"
                but how i can find the essror ??
                >
                A common cause of errors is that the user was granted select privilege through a role instead of a direct grant. That won't work with views.

                See CREATE VIEW in the SQL Language doc
                http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm
                >
                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, rather than through a role.
                >
                You are trying to create a view 'USERBO.USERPROFILE_VIEW' in the USERBO schema that selects from the RESPONSABILITYUSER table.

                You don't mention what user you are logged on as but the user creating the view has to have select privileges on the RESPONSABILITYUSER table granted directly to them.

                Does user 'USERBO' have select privileges granted directly to them on the RESPONSABILITYUSER table?
                • 5. Re: error on creation of a view
                  ranit B
                  CREATE FORCE VIEW USERBO.USERPROFILE_VIEW
                  (ROW_ID,EMPLOYEE,RESPONSABILITY_ID,RUOLO,E_USERNAME,E_PASSWORD,USERTYPE,USER_ID) AS
                  SELECT RESPONSABILITYUSER.row_id,
                  client.surname
                  || ' '
                  || client.NAME,
                  RESPONSABILITYUSER.responsability_id,
                  'USERWEB',
                  upper(client.username) || '-' || client.organization_id,
                  client.pwd,
                  client.usertype,
                  client.ROW_ID
                  FROM RESPONSABILITYUSER,
                  general.client
                  WHERE ((client.row_id = RESPONSABILITYUSER.user_id))
                  AND client.status ='ACTIVE'
                  Since the view is being created in schema 'USERBO', please check if you have proper grants on Table- 'CLIENT' in schema- 'GENERAL' for 'USERBO'.

                  HTH
                  Ranit B.
                  • 6. Re: error on creation of a view
                    GcG-Italia
                    the strange is that the select extract from the view and execute from user USERBO
                    works without errors and the two USER (USERBO AND CLIENT) are dba users.

                    I must grant select on all tables ??
                    • 7. Re: error on creation of a view
                      ranit B
                      ...
                      FROM RESPONSABILITYUSER,
                      general.client
                      where ...
                      What about the user - 'GENERAL' ?
                      I can see a table GENERAL.CLIENT.
                      • 8. Re: error on creation of a view
                        sb92075
                        GcG-Italia wrote:
                        the strange is that the select extract from the view and execute from user USERBO
                        works without errors and the two USER (USERBO AND CLIENT) are dba users.

                        I must grant select on all tables ??
                        YES
                        CREATE FORCE VIEW userbo.userprofile_view 
                        (row_id, employee, responsability_id, ruolo, e_username, e_password, usertype, 
                        user_id) 
                        AS 
                          SELECT responsabilityuser.row_id, 
                                 client.surname 
                                 || ' ' 
                                 || client.name, 
                                 responsabilityuser.responsability_id, 
                                 'USERWEB', 
                                 Upper(client.username) 
                                 || '-' 
                                 || client.organization_id, 
                                 client.pwd, 
                                 client.usertype, 
                                 client.row_id 
                          FROM   responsabilityuser, 
                                 general.client 
                          WHERE  (( client.row_id = responsabilityuser.user_id )) 
                                 AND client.status = 'ACTIVE' 
                        • 9. Re: error on creation of a view
                          rp0428
                          >
                          the strange is that the select extract from the view and execute from user USERBO
                          works without errors and the two USER (USERBO AND CLIENT) are dba users.
                          >
                          Why is that strange? The common way to make a user a DBA is to grant them the DBA role.

                          Reread my reply above. Especially this part
                          >
                          the user creating the view has to have select privileges on the RESPONSABILITYUSER table granted directly to them.
                          >
                          To work in a view or pl/sql procedure/function/package privileges must be 'GRANTED DIRECTLY TO THEM'!

                          Repeat after me:

                          DBA is a role, roles are not active in views/procedures/functions/packages: privileges must be 'GRANTED DIRECTLY TO THEM'!
                          DBA is a role, roles are not active in views/procedures/functions/packages: privileges must be 'GRANTED DIRECTLY TO THEM'!
                          DBA is a role, roles are not active in views/procedures/functions/packages: privileges must be 'GRANTED DIRECTLY TO THEM'!
                          DBA is a role, roles are not active in views/procedures/functions/packages: privileges must be 'GRANTED DIRECTLY TO THEM'!
                          DBA is a role, roles are not active in views/procedures/functions/packages: privileges must be 'GRANTED DIRECTLY TO THEM'!

                          Now please post the answer to this question:
                          >
                          I must grant select on all tables ??
                          • 10. Re: error on creation of a view
                            Peter Gjelstrup
                            GcG-Italia wrote:
                            BUT THE SELECT WORKS FINE
                            AND ALL 2 USERS ARE DBA !!!

                            WHAT I CAN DO ??
                            Since both are already DBA (Which they should probably not be), you don't jeopardize much by granting SELECT ANY TABLE to USERBO.

                            Nothing I would usually recommend, but working with cross schema designs always get messy if little thought is given to it to start with.

                            Regards
                            Peter