Oracle – dbms_redefinition Fails with ORA-01749

dbms-redefinitionoraclepermissions

I ran this as a part of redefining a table

DECLARE
  l_num_ers PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'myuser',
    orig_table        => 'mytable',
    int_table         => 'redef',
    copy_indexes      => DBMS_REDEFINITION.cons_orig_params, 
    copy_triggers     => TRUE, 
    copy_constraints  => TRUE, 
    copy_privileges   => TRUE, 
    ignore_errors     => FALSE,
    num_errors        => l_num_ers);
   DBMS_OUTPUT.put_line('l_num_ers=' || l_num_ers);
END;
/

And got the error

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
ORA-06512: at "SYS.DBMS_REDEFINITION", line 752
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1698

So how can I solve this?.
The same code worked on another database where the only difference I've found is that the user myuser did not have select right on the table.

I did

Select PRIVILEGE, GRANTEE from dba_tab_privs where owner = 'MYUSER' and TABLE_NAME = 'MYTABLE';

To find the differences.

Is the solution to revoke my own privilege to the table?

Best Answer

Having SELECT privilege on your own table is unnecessary.

Granting privileges on your own objects added extra rows in the dictionary in earlier versions and it was considered as a bug, fixed in newer releases.

This note decribes this and why redefinition fails becasue of this:

Bug 8984274 - ORA-1749 Error Running DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (Doc ID 1345539.1)

Just revoke the privilege on your own objects.

Related Question