Manage Relational Connections

-- Assign permissions to relational connections for individual groups/owners
SELECT 'assignpermission -o Connection -t Relational -n ',cnx.object_name, USR_GRP_PERM
FROM opb_cnx cnx
JOIN
(
select ' -g "Informatica Administrators" -s SONIC_LDAP -p rwx' USR_GRP_PERM from dual
union
select ' -g "Informatica Developers" -s SONIC_LDAP -p rx' USR_GRP_PERM  from dual
union
select ' -g "Informatica Operators" -s SONIC_LDAP -p rx' USR_GRP_PERM  from dual
union
select ' -g Administrators -p rwx' USR_GRP_PERM  from dual
union
select ' -g "Automated Runners" -p rx' USR_GRP_PERM  from dual
union
select ' -u Administrator -p rwx' USR_GRP_PERM  from dual
union
select ' -g Everyone -p ""' USR_GRP_PERM  from dual
union
select ' -g Developers -p ""' USR_GRP_PERM  from dual)
USR_GRP on 1=1
ORDER BY cnx.object_name,USR_GRP_PERM

-- Change owner of relational connections
SELECT 'changeowner -o Connection -t Relational -n '|| cnx.object_name ||' -u Administrator'
FROM opb_cnx cnx
where NOT(cnx.object_name like 'bsmith%' or
      cnx.object_name like 'steve%'    or 
      cnx.object_name like 'sam%' or
      cnx.object_name like 'ci%')
order by cnx.object_name     

-- Delete connections
SELECT 'deleteconnection -n '|| cnx.object_name ||' -f -s Relational'
FROM opb_cnx cnx
where (cnx.object_name like 'dbconnection%' or
      cnx.object_name like 'DB_Connection_name%'    or 
      cnx.object_name like 'dev%' or
      cnx.object_name like 'ci%')
order by cnx.object_name