Database changes requires to update customized grants to the tables
/views /synonyms in oracle.
This script creates public synonyms, reanalyze the tables that got modified with data. Also provide the grants to roles with new objects
I just mentioned the broad steps to follow to automate ;
-- Create roles
select 'create role xxxx;' from dual
-- Create public synonyms
select 'create public synonym ' || table_name || ' for ' || owner || '.' || table_name || ';'
from dba_tables a
where owner = 'yourschemauser'
and not exists (select 'x'
from dba_synonyms b
where b.owner = 'PUBLIC'
and b.synonym_name = a.table_name
and b.table_owner = a.owner
and b.table_name = a.table_name)
union
select 'create public synonym ' || view_name || ' for ' || owner || '.' || view_name || ';'
from dba_views a
where owner = 'yourschemauser'
and not exists (select 'x'
from dba_synonyms b
where b.owner = 'PUBLIC'
and b.synonym_name = a.view_name
and b.table_owner = a.owner
and b.table_name = a.view_name)
-- Analyze tables
-- Grant select to xxxx
select 'grant select on ' || owner || '.' || object_name || ' to xxxx;'
from dba_objects
where owner = 'yourschemauser'
This script creates public synonyms, reanalyze the tables that got modified with data. Also provide the grants to roles with new objects
I just mentioned the broad steps to follow to automate ;
-- Create roles
select 'create role xxxx;' from dual
-- Create public synonyms
select 'create public synonym ' || table_name || ' for ' || owner || '.' || table_name || ';'
from dba_tables a
where owner = 'yourschemauser'
and not exists (select 'x'
from dba_synonyms b
where b.owner = 'PUBLIC'
and b.synonym_name = a.table_name
and b.table_owner = a.owner
and b.table_name = a.table_name)
union
select 'create public synonym ' || view_name || ' for ' || owner || '.' || view_name || ';'
from dba_views a
where owner = 'yourschemauser'
and not exists (select 'x'
from dba_synonyms b
where b.owner = 'PUBLIC'
and b.synonym_name = a.view_name
and b.table_owner = a.owner
and b.table_name = a.view_name)
-- Analyze tables
-- Grant select to xxxx
select 'grant select on ' || owner || '.' || object_name || ' to xxxx;'
from dba_objects
where owner = 'yourschemauser'
