Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: Grants to a view

    I have a user called S_KFC. This user has the role S_USER granted.
    I have a DBA-user called VDS_VALIDATE which owns a view called TJEK01.

    I have granted SELECT to both the user and the role

    The view TJEK01 is based on a table DOKUMENTLINK, which I have also granted SELECT privileges to both the user and the role.

    BUT the user gets the message ORA-01031: Insufficient privileges

    I have a lot of users and views, so I need to find out how to solve the problem.

    Any sugestions?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check post #3 in this thread.

    [EDIT]

    Perhaps it will not answer your question, so - let me try to add a few more words: if this user received an error message while working in PL/SQL, privileges received through roles do not apply in PL/SQL (in SQL they will work fine). Therefore, grant required privileges directly to the user (not through the role).
    Last edited by Littlefoot; 09-11-06 at 05:23.

  3. #3
    Join Date
    Jun 2003
    Posts
    81

    Sql

    I don't see the solution. Let be try to be more specific by describing the SQL:

    connect vds_validate/****@vds;

    CREATE OR REPLACE VIEW TJEK01
    AS
    select * from dokumentlink where dead_link = 'T'
    /

    create or replace public synonym tjek01 for vds_validate.tjek01;
    grant select on tjek01 to S_KFC;
    grant select on tjek01 to S_USER;

    connect s_kfc/****@vds;
    select count(*) from tjek01;

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

    ORA-01031: insufficient privileges

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is the result of (when connected as S_KFC)

    SELECT * FROM dual;

  5. #5
    Join Date
    Jun 2003
    Posts
    81
    connect s_kfc/****@vds;
    select * from dual;

    ---------
    result:

    D
    -
    X

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; obviously, my guess (that S_KFC needs SELECT ANY TABLE privilege) was wrong. Does it need any other privilege? Because, your scenario seems to work correctly on my 8i:
    Code:
    SQL> connect mig/mig@ora8i
    Connected.
    SQL> create view v_zupanije as select * From zupanije;
    
    View created.
    
    SQL> create public synonym ps_zupanije for v_zupanije;
    
    Synonym created.
    
    SQL> grant select on ps_zupanije to mig_jedan;
    
    Grant succeeded.
    
    SQL> connect mig_jedan/mig_jedan@ora8i
    Connected.
    SQL> select count(*) from ps_zupanije;
    
      COUNT(*)
    ----------
            22
    
    SQL>

  7. #7
    Join Date
    Jun 2003
    Posts
    81

    Third user involved

    Actually there is a third user involved VDS. This user owns the table DOKUMENTLINK.

    It seems to be the problem, because I can make a copy of the table in the VDS_VALIDATE scheme, and can afterwards grant the necessary rights with success.

    But how shall I solve that problem?

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps by including the WITH GRANT OPTION while granting privileges from VDS to VDS_VALIDATE?

  9. #9
    Join Date
    Jun 2003
    Posts
    81

    Talking Yes!

    Thank you

    WITH GRANT OPTION solved the problem

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Marvelous!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •