user12055696 wrote:privileges acquired via ROLE do NOT apply within named PL/SQL procedures.
Any help would be most appreciated.
I am trying to create a stored procedure that will create all my privilege framework. It queries the dictionary and creates dynamic plsql to create grants.
The problem is that when I run this as a script in SQLPLUS it works, but when I run the same code from a the stored procedure it gives that that the table does not exist.
Thanks for your support and insights.
vTable_Name := 'employee';
vStatement_Command := 'GRANT INSERT ON MyShema.' || vTable_Name || ' TO "MyUser"';
EXECUTE IMMEDIATE vStatement_Command;
user12055696 wrote:USER_DBA must start new session after the GRANT has been issued.
Ok. Thanks for your patience.
Lets see if I can explain this:
User_Dba is the dba, which is trying to assign privileges to User_Target on a table owned by User_Owner (using a stored proc)
So User_Owner executes the following statement:
grant select, insert, delete, update on mytable to user_Dba with grant option;
User_Dba then tries to assign a privilege using a SP
grant insert on User_Owner.mytable to user_target
error I get is
Error at line 1
ORA-00942: table or view does not exist
ORA-06512: at "Custom_PRIVILEGE.UTIL", line 91
ORA-06512: at line 4
Why are you still using LONG datatypes? Get rid of that.
create or replace procedure test_grant as vTable_Name VARCHAR2(30); vStatement_Command LONG; BEGIN vTable_Name := 'emp'; vStatement_Command := 'GRANT INSERT ON ' || vTable_Name || ' TO "HR"'; DBMS_OUTPUT.PUT_LINE (vStatement_Command); EXECUTE IMMEDIATE vStatement_Command; END; / SQL> set serveroutput on SQL> exec test_grant; GRANT INSERT ON emp TO "HR" PL/SQL procedure successfully completed. SQL>