privileges required to enable/disable table constraint

[dinocwh]

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?

[pipo]

interesting ... maybe you could :

grant index on mytable to sidusr;

[dinocwh]

"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?

[aarroyob]

For do this you could:

grant alter on on siddba.mytable to sidusr;

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

Regards

Angel

[dinocwh]

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

[jmodic]

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.

[dinocwh]

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.

[dinocwh]

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...