DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: truncate privs

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    truncate privs

    Hi

    Is it possible to assign truncate privs to another user?

    I tried the following and it doesnt work.

    grant truncate on table A to user A;

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    grant the drop table privelege to user A .Then it will work.

    regards
    Hrishy

  3. #3
    Join Date
    Apr 2002
    Posts
    291
    Nope, u can't.

    SQL> create table dummy_fac as select * from m_fac;

    Table created.

    SQL> grant truncate on dummy_fac to adm2;
    grant truncate on dummy_fac to adm2
    *
    ERROR at line 1:
    ORA-00990: missing or invalid privilege
    PNRDBA

  4. #4
    Join Date
    Jan 2000
    Posts
    387
    Hi

    I do not want to drop and re-create the table. I just want to clear the table. As there are mnay records inside therefore I would like to use truncate instead of delete.

    In the first place, is there a grant truncate in Oracle??

    Thanks!

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    There is no TRUNCATE privilege, in order to do this you will need to grant the DROP ANY TABLE privilege to the user if they don't own the object.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mooks
    In the first place, is there a grant truncate in Oracle??
    No, there is not. The only system-provided method is the one suggested by hrishy (GRANT DROP ANY TABLE TO ...). For a more safe alternative, check
    http://www.dbasupport.com/forums/sho...threadid=20154
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2000
    Posts
    387
    In a procedure, cannot just use the command truncate table right? There will be an error.

    create procedure abc as
    begin
    truncate table a;
    end;
    /
    Last edited by mooks; 10-23-2002 at 05:30 AM.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Nope, you'll have to use dynamic SQL.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    execute immediate '************';

    -nagarjuna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width