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
create table test_table (col1 number)
insert into test_table values (1)
grant select on test_table to test_role
select col1 from test_table
create procedure test_proc
select col1 into v_dummy from test_table
where col1 = 1;
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??
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
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.
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.