Change public synonyms to private synonyms in Oracle
The following anonymous PL/SQL block will change the public synonyms granted by SCOTT to private synonyms:
. oraenv
sqlplus / as sysdba
grant create any synonym to scott;
grant drop public synonym to scott;
accept pwd hide
conn scott/&pwd@testdb
--@testdb (user: SCOTT)
set serveroutput on size 1000000
declare
type synonym_record is record
(table_name varchar2(30),
synonym_name varchar2(30),
grantee varchar2(30)
);
type synonym_table_type is table of synonym_record
index by pls_integer;
synonym_tab synonym_table_type;
type unq_synonym_type is table of varchar2(30)
index by pls_integer;
unq_synonym unq_synonym_type;
begin
select a.table_name as table_name,a.synonym_name as synonym_name,b.grantee as grantee
bulk collect into synonym_tab
from
(select owner,table_owner,synonym_name,table_name
from all_synonyms where table_owner = 'SCOTT' and owner = 'PUBLIC'
) a,user_tab_privs b
where a.table_name = b.table_name
order by b.grantee;
if synonym_tab.COUNT = 0 then
return;
end if;
-- Create Private Synonyms
for i in synonym_tab.FIRST .. synonym_tab.LAST loop
begin
execute immediate 'create synonym '||synonym_tab(i).grantee||'.'||synonym_tab(i).table_name||' for '||synonym_tab(i).table_name;
exception
when others then
dbms_output.put_line(synonym_tab(i).table_name||' => '||sqlerrm);
end;
end loop;
select distinct a.synonym_name as synonym_name
bulk collect into unq_synonym
from
(select owner,table_owner,synonym_name,table_name
from all_synonyms where table_owner = 'SCOTT' and owner = 'PUBLIC'
) a,user_tab_privs b
where a.table_name = b.table_name;
-- Drop Public Synonyms
for i in unq_synonym.FIRST .. unq_synonym.LAST loop
begin
execute immediate 'drop public synonym '||unq_synonym(i);
exception
when others then
dbms_output.put_line(unq_synonym(i)||' => '||sqlerrm);
end;
end loop;
end;
/
sqlplus / as sysdba
revoke create any synonym from SCOTT;
revoke drop public synonym from SCOTT;
