Oracle 10g Granted privileges and dynamic sql

Oracle 10g I have a stored procedure that I invoke from a sql*plus session connected as schema ca50633. It executes the following command which fails with an error message about insufficient privileges.

COMMAND := 'TRUNCATE TABLE RT_TEST.STATIC_DATA';      
EXECUTE IMMEDIATE COMMAND;

I have granted ca50633 all privileges on the rt_test.static_data table from the rt_test schema and can truncate the table interactively from sql*plus.

What am I missing?
rmtyeAsked:
Who is Participating?
 
bobbymanochaConnect With a Mentor Commented:
grant all grants object privileges.    You need a system privilege here, and 'grant drop any table to ca50633' will do that for you.

Alternatively, compile the stored procedure in the rt_test schema and grant execute on the procedure to ca50633.
0
 
bobbymanochaCommented:
PL/SQL doesn't recognize privileges granted through roles.  The privilege needs to be granted directly.
 
grant drop any table to ca50633;
0
 
Jinesh KamdarCommented:
GRANT TRUNCATE ON rt_test.static_data TO ca50633;
0
 
rmtyeAuthor Commented:
I granted all on rt_test.static_data to ca50633; but, it didn't help.

Grant truncate returned an error message
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.