Home » RDBMS Server » Server Administration » problem with execute privilege on sys.truncate_table (10.2.0.3 running on AIX 5.3)
problem with execute privilege on sys.truncate_table [message #357520] Wed, 05 November 2008 13:15 Go to next message
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 #357521 is a reply to message #357520] Wed, 05 November 2008 13:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"truncate_table" is NOT a standard procedure. Don't put your objects in SYS schema.

- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel
Re: problem with execute privilege on sys.truncate_table [message #357523 is a reply to message #357520] Wed, 05 November 2008 13:53 Go to previous messageGo to next message
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
Re: problem with execute privilege on sys.truncate_table [message #357528 is a reply to message #357523] Wed, 05 November 2008 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use SYSTEM either.
Create your own user and give it the necessary privileges.
I don't know your procedure, so can't say more.

Regards
Michel
Re: problem with execute privilege on sys.truncate_table [message #357530 is a reply to message #357520] Wed, 05 November 2008 15:22 Go to previous message
divroro12
Messages: 105
Registered: March 2008
Location: Laurel, MD USA
Senior Member
Thanks...
Previous Topic: Oracle Restore Point
Next Topic: dbschema (merged)
Goto Forum:
  


Current Time: Sun Dec 11 08:29:30 CST 2016

Total time taken to generate the page: 0.10110 seconds