Oracle 9i Release 2 database
I need to change synonyms at runtime to point to a new table owner. But I am running into "insufficient privileges" errors.
SQL> select grantee,owner,table_name,privilege from dba_tab_privs
2 where table_name IN ('OE4PNFGETDATA','OEBGGETDATA','OEGETDATA');
GRANTEE OWNER TABLE_NAME PRIVILEGE
------------- --------- ----------------- -----------
OEMUSER OEM2 OE4PNFGETDATA EXECUTE
OEMUSER OEM2 OEBGGETDATA EXECUTE
OEMUSER OEM2 OEGETDATA EXECUTE
OEMUSER OEM1 OEBGGETDATA EXECUTE
OEMUSER OEM1 OEGETDATA EXECUTE
OEMUSER OEM1 OE4PNFGETDATA EXECUTE
SQL> sho user
USER is "OEMUSER"
SQL> select synonym_name,table_owner,table_name from user_synonyms ;
SYNONYM_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------
OE4PNFGETDATA OEM1 OE4PNFGETDATA
OEBGGETDATA OEM1 OEBGGETDATA
OEGETDATA OEM1 OEGETDATA
I need to switch the table owner to OEM2. Using create or replace synonym in a procedure:
CREATE OR REPLACE PROCEDURE oemuser.p_switch_users ( i_nonactive_user IN VARCHAR2
, o_msg OUT VARCHAR2 )
AS
v_synonym_name VARCHAR2(30) ;
v_table_owner VARCHAR2(30) := i_nonactive_user ;
v_table_name VARCHAR2(30) ;
v_sql VARCHAR2(255);
BEGIN
FOR rec IN ( select synonym_name, table_owner, table_name from user_synonyms )
LOOP
v_synonym_name := rec.synonym_name ;
v_table_name := rec.table_name ;
v_sql := 'CREATE OR REPLACE SYNONYM ' || v_synonym_name || ' FOR ' || v_table_owner ||
'.' || v_table_name ;
EXECUTE IMMEDIATE v_sql ;
END LOOP ;
o_msg := 'Completed successfully' ;
EXCEPTION
WHEN others THEN
o_msg := 'Error_Stack...' || Chr(10) ||
DBMS_UTILITY.FORMAT_ERROR_STACK ;
RAISE ;
END p_switch_users ;
/
var o_msg varchar2(512)
var i_user varchar2(10)
exec :i_user := 'OEM2'
exec p_switch_users(:i_user,:o_msg)
ORA-01031: insufficient privileges
ORA-06512: at "OEMUSER.P_SWITCH_USE
RS", line
I haven't tried dropping the synonyms and re-creating them, but I would expect the same error. Any suggestions?