Problem with execute on sys.truncate_table

[divroro12]

Hi DBA's,

I granted the execute privilege for the sys.truncate_table stored procedure to a database user, but this user is still incable of executing this procedure, even though i get the response "PL/SQL procedure successfully completed".

I then proceeded to create a public synonym on this procedure & granted the user execute privilege on the stored procedure, but the results were still the same, i.e. the PL/SQL procedure executed successfully, but the table wasn't truncated.

Below are the steps I took:

SQL> connect sys / as sysdba
Enter password:
Connected.

SQL> grant execute on truncate_table to INFA_451_DA;

Grant succeeded.

SQL> connect INFA_451_DA
Enter password:
Connected.
SQL> exec SYS.TRUNCATE_TABLE('FACETS45_DA_DEV' ,'NCA_FLDD_DEPENDENT_DETAIL')

PL/SQL procedure successfully completed.

SQL> select count (*) from FACETS45_DA_DEV.NCA_FLDD_DEPENDENT_DETAIL
2 /

COUNT(*)
----------
21274110

SQL> connect sys / as sysdba
Enter password:
Connected.
SQL> revoke execute on sys.truncate_table from INFA_451_DA;

Revoke succeeded.

SQL> show user
USER is "SYS"
SQL> desc truncate_table
PROCEDURE truncate_table
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLEOWNER VARCHAR2 IN
TABLENAME VARCHAR2 IN

SQL> create or replace public synonym truncate_table for truncate_table;

Synonym created.

SQL> grant execute on truncate_table to INFA_451_DA;

Grant succeeded.

SQL> connect INFA_451_DA
Enter password:
Connected.
SQL> clear screen
SQL> show user
USER is "INFA_451_DA"
SQL> exec SYS.TRUNCATE_TABLE('FACETS45_DA_DEV' ,'NCA_FLDD_DEPENDENT_DETAIL')

PL/SQL procedure successfully completed.

SQL> select count (*) from FACETS45_DA_DEV.NCA_FLDD_DEPENDENT_DETAIL
2 /

COUNT(*)
----------
21274110

I'll be very greatful if someone can help me bypass this hitch & get me rolling again.

Thanks,

[vnktummala]

Is INFA_451_DA had the necessary privilege on FACETS45_DA_DEV.NCA_FLDD_DEPENDENT_DETAIL table? I can see INFA_451_DA had SELECT privilege but that is not enfough to truncate the table.

Thanks,

[divroro12]

INFA_451_DA has SELECT, INSERT, UPDATE & DELETE privileges on all objects in the FACETS45_DA_DEV schema

[vnktummala]

Does INFA_451_DA has DROP_ANY_TABLE system privilege? This privilege is required to truncate the tables on other user.

Thanks,

[divroro12]

Does a user actually need the DROP_ANY_TABLE system privilege to truncate a table? I think all a user needs to truncate a table is the DELETE object privilege on that table...

[vnktummala]

Yes, you need it to truncate the tables on other schema.

Thanks,

[vnktummala]

else

create the truncate_table procedure in FACETS45_DA_DEV schema and grant execute to INFA_451_DA.

Thanks,

[divroro12]

OK, thnx vn; that's exactly why this procedure was created to be able to accomplish this task of truncating tables by certain users without the DROP_ANY_TABLE system privilege

[vnktummala]

Is it working?

(Make sure you create the truncate_table procedure in FACETS45_DA_DEV schema and grant execute on FACETS45_DA_DEV.truncate_table to INFA_451_DA. NOT on sys.truncate_table.)

Thanks,