Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137

    Unanswered: DML privs and roles

    I refuse to believe this:

    Code:
    create user test_user1 identified test_user1
    /
    create user test_user2 identified test_user2
    /
    create role test_role
    /
    grant create session to test_role
    /
    grant test_role to test_user1
    /
    grant connect, resource to test_user2
    /
    
    conn test_user2/test_user2
    create table test_table (col1 number)
    /
    insert into test_table values (1)
    /
    grant select on test_table to test_role
    /
    
    conn test_user1/test_user1
    /
    select col1 from test_table
    /
        COL1
    --------
             1
    
    create procedure test_proc
    is
      v_dummy number;
    begin
      select col1 into v_dummy from test_table
       where col1 = 1;
    end;
    /
    ORA-00942: table or view does not exist
    Can anybody explain that???
    How can it be possible that I need to grant the select to the user instead of a role??

    Thanks for your advices,
    Germán.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    rights granted to a role are not available during the execution of PL/SQL.

    This perturbed me at first also. But if you think about it, when you're setting up a procedure like this, the only user that needs access to test_table is test_user1. This is because when anyone executes the procedure, it will execute with the rights of the owner.

    So, if you
    create test_user3
    create test_role3
    make test_role3 a member of test_role3
    grant EXECUTE on test_proc to test_role3
    (and don't forget grant select on test_table to test_user1)

    then test_user3 (and test_user1 of course) can execute the procedure, and select from the table.

    -Chuck

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is perfectly OK for you to refuse to believe this, but - if you don't change your mind, you won't be able to use your PL/SQL procedures as they disable all roles when they are run. Solution is in granting privileges directly to the user instead of using roles.

    Check this page to read a few more words about this issue.

  4. #4
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    Thanks both!!
    I understand now, and discovered the AUTHID syntax on procedures.

    Littlefoot, thanks for the article...

    Just when you think you know, you find out you know nothing... :P

    Regards!

Posting Permissions

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