Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24

    Unanswered: alter table disable all triggers give error ORA-01031

    Hi All,

    It might be very logical scenario for you guys and Oracel too but it quite illogical for me, I am afraid.

    Here is the issue:-

    I've two users A and B, A has table t1 and few triggers are also defined on this table. User B has been granted 'ALTER' privilege on A.t1 as:-

    grant alter on A.t1 to B;

    B was able to add new column, modify existing column and enable/diable constraints on A.t1 but when B tries to disable all triggers defined on A.t1 it throws ORA-01031 error:

    SQL> alter table A.t1 disable all triggers;
    alter table A.t1 disable all triggers
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    I know granting 'ALTER ANY TRIGGER' to user B and disabling triggers one by one will resolve this issue but my question is if I've granted alter table priv to the user, why he is unable to alter the table?

    An early explanation/solution will be highly appreciated.

    Cheers
    Last edited by rzsattar; 09-06-08 at 06:54.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    was the grant to the A user issued by the B user?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    Quote Originally Posted by beilstwh
    was the grant to the A user issued by the B user?
    no, it was granted by user A to user B.

  4. #4
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    Just wondering there is none who can throw light on it?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    table data type <> trigger data type.
    You can have triggers which are NOT tied/related to any specific table.

    If User_A had granted access to User_B for the triggers on table T1, the User_B could modify them.

    This is no different than if T1 had a SEQUENCE as PK.
    User_B would not be able to modify this seqence simply because he had been granted access to table T1.

    Why is this confusing you?
    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.

Posting Permissions

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