This discussion is archived
10 Replies Latest reply: Nov 17, 2012 3:54 PM by Peter Gjelstrup RSS

error on creation of a view

GcG-Italia Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    SELECT  LINE,
            POSITION,
            TEXT
      FROM  ALL_ERRORS
      WHERE OWNER = 'USERBO'
        AND NAME = 'USERPROFILE_VIEW'
    /
    SY.
  • 2. Re: error on creation of a view
    SomeoneElse Guru
    Currently Being Moderated
    You can try select * from user_errors where name = 'USERPROFILE_VIEW';
  • 3. Re: error on creation of a view
    GcG-Italia Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    ...
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points