roles authenticated using the stored procedures not working.....
646155Jan 4 2010 — edited Jan 4 2010sir,
see the steps i have gone through
create user user100 identified by 123;
create user user101 identified by 123;
grant create session, create role, create procedure to user100;
grant connect to user101;
alter user user100 quota 10M on users;
grant create table to user100 with admin option;
conn user100/123
create or replace procedure settingrole as
begin
dbms_Session.set_role('dummy');
end;
/
create role dummy identified using user100.settingrole;
grant create table to dummy;
grant execute on settingrole to user101;
conn user101/123
execute user100.settingrole;
now the error i get is ora-06565 cannot execute set role from within stored procedures
ora-065512 at sys.dbms_session line
can u please help me how to make this work