Thursday, April 30, 2009

Grating roles after creating table/views/synonyms in Peoplesoft

When ever there database changes in migrations, Admin need to update the customized grants to the tables /views /synonyms in oracle.

This can be automated with sciprt below :

This script helps to create 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 = 'user1'
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 = 'user1'
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 = 'user1'

No comments: