privileges required to enable/disable table constraint
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: privileges required to enable/disable table constraint

  1. #1
    Join Date
    Mar 2001
    Posts
    29
    the case is, say, table_name = mytable, owner = siddba
    grant all privileges on siddba.mytable to sidusr; is done
    create public synonym mytable for siddba.mytable; is done

    when sidusr executes
    alter table mytable disable primary key;
    "ORA-00942: table or view does not exist" returns
    alter table siddba.mytable disable primary key;
    "ORA-01418: specified index does not exist" returns

    if alter any index is granted to sidusr
    "ORA-01031: insufficient privileges" returns

    any idea for the goal?
    dino

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    interesting ... maybe you could :

    grant index on mytable to sidusr;

  3. #3
    Join Date
    Mar 2001
    Posts
    29
    "ORA-01031: insufficient privileges"
    but if i connect as system or sys it works... really hard to pick the privilege for this operation
    and... seems that the synonym doesn't work for this ddl command?
    dino

  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    For do this you could:

    grant alter on on siddba.mytable to sidusr;

    After this you will be able to disable constraints ...

    Regards

    Angel

  5. #5
    Join Date
    Mar 2001
    Posts
    29
    no i can't...
    i got "ORA-01418: specified index does not exist" error....
    SQL> grant alter on scott.mytest to dino;
    Grant succeeded.
    SQL> connect dino/dino;
    Connected.
    SQL> alter table scott.mytest disable primary key;
    alter table scott.mytest disable primary key
    *
    ERROR at line 1:
    ORA-01418: specified index does not exist
    SQL> connect / as sysdba
    Connected.
    SQL> grant alter any index to dino;
    Grant succeeded.
    SQL> connect dino/dino
    Connected.
    SQL> alter table scott.mytest disable primary key;
    alter table scott.mytest disable primary key
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    got the same case for grantting index on table to dino
    dino

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Could you specify your database release you are using? I just tested this on my 8.1.7.1.2, had no problem with your test case.
    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
    Mar 2001
    Posts
    29
    815 on an NT platform ... and i also try this with 81701 on a linux box. it doesn't work either

    note that.. i try to use sys and system to alter the table's constraint without problems. just think that it is hard to find out the privilege required to be grant.
    dino

  8. #8
    Join Date
    Mar 2001
    Posts
    29
    ok... i got it... i need to grant the system privileges "create any index" and "drop any index" to dino. seems that the privs are too high for dino. anyway, it works.
    grantting table privilege "index" on table doesn't help either...
    so weird...
    dino

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