| problem with execute privilege on sys.truncate_table [message #357520] |
Wed, 05 November 2008 13:15  |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
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,
divroro12
-----------
|
|
|
|
|
|
| Re: problem with execute privilege on sys.truncate_table [message #357523 is a reply to message #357520] |
Wed, 05 November 2008 13:53  |
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
|
Senior Member |
|
|
Michel,
Are you suggesting i create the truncate_table stored procedure as the system user & proceed with the steps i took earlier, because this procedure is owned by sys, & execution privilege is meant to be granted for less privileged users by sys as required.
Regards,
Divine
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
