Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: A conditional truncate problem - Need help

    Hi,

    I have a specific requirement, which I am not very sure how to tackle even though I have come up with a few solutions. Can someone please present a good solution to the problem given below:

    I have a table T1 whose owner is 'A'. My requirement is that, other users who are not owners of T1, for example user 'B' will be logging into this DB and accessing this table. These users may need to truncate T1.
    Now, once 'B' fires a truncate on T1, I should have a piece of SQL code(either a trigger or procedure) which should first check whether 'B' has 'select', 'insert', 'delete' privileges on T1. This can be checked from DBA_USER_PRIVS view. If 'B' has these privileges, then this user should be able to go ahead and truncate this table. If 'B' doesn't have these privileges, then an exception should be thrown.
    I know a user can truncate a table from another user's schema only if that user is given the 'Drop any table' privilege. But that can't be done.
    Please note, data has to be truncated and not deleted as its a datawarehouse with huge data.

    Any good way to do this? Either by a proc or a trigger? Any steps,code or pseudocode will be helpful.

    Or, if at all, this truncation by another user cannot be done, is there any alternative way of removing data from this table maintaining good performance? Amount of data in this table is huge.

    Thanks
    Neel

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any good way to do this? Either by a proc or a trigger? Any steps,code or pseudocode will be helpful.
    Have user A write/own a procedure (TRUNC_T1) that does
    EXECUTE IMMEDIATE TRUNCATE TABLE T1;
    then issue SQL below
    GRANT EXECUTE TRUNC_T1 TO B;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2010
    Posts
    6
    Thanks, this worked.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    v$session_longops?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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