Results 1 to 5 of 5

Thread: Righhts problem

  1. #1
    Join Date
    Jun 2009
    Posts
    22

    Unanswered: Righhts problem

    I have defined a role as:

    create role my_role;

    Then I granted the select on USER1.Table1 as:

    Grant select on User1.Table1 to my_role:

    Then I granted the role to User2 as:

    grant my_role to User2;

    Now I am able to execute the following query while I am connected with User2:

    select * from User1.Table1;

    All is working fine.

    Now I have writen a procedure in User2 As

    Create or replace procedure User2.someProceduere
    (
    cur_test out sys_refcursor
    )
    Begin
    Opne cur_test
    for 'select * from User1.Table1';
    End;

    While I runt this procedure it produces the error message
    "Table or view does not exists"

    Intrestingly when I give direct rights on User1.Table1 to User2 the procudure statrs working, without any error

    (From direct rights I mean to say when I do not give rights through the roile "my_role"
    instead I write:
    grant select on User1.Table1 to User2

    I want to know the reason? I am confused, because when I give rights through the role the procedure does not work
    and when I give rights directly to the user the procudue starts working.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is expected Oracle behaviour. It has always worked that way, it still does (and probably will). There's nothing you can do about it - privileges acquired via roles simply don't work in PL/SQL.

  3. #3
    Join Date
    Jun 2009
    Posts
    22
    But I wonder why are the roles there and what are the user of these roles then if it doesn not work in PL/SQL?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Then, you might enjoy this article on AskTom: http://asktom.oracle.com/pls/apex/f?...:1065832643319

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

Posting Permissions

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