PL/SQL doesnt recognize privileges granted to roles
My procedure has a create database link in it:
v_exe := 'create database link temp_security_monitor ' ||
'connect to ' p_username ' identified by ' p_password ||
' using ' '''' p_tns_string '''';
if v_debug = TRUE
then dbms_output.put_line (v_exe);
end if;
execute immediate v_exe;
This was failing:
Unexpected error-1031 ORA-01031: insufficient privileges
SQL> conn <a href="mailto:sys@db">sys@db</a> as sysdba
Connected.
SQL> grant create database link to system;
Grant succeeded.
Then all was hunky-dory