77 lines
2.9 KiB
SQL
77 lines
2.9 KiB
SQL
-- Generate emcli (script) commands to set the preferred sysdba credential for databases that have not yet a preferred sysdba credential set
|
|
-- The named credential set depends on which admin group the database belongs to
|
|
-- (change the mapping to fit your needs. If you also created manual groups, duplicates can occur)
|
|
-- No password testing is done, but can be added in the emcli command as parameter
|
|
|
|
select
|
|
'set_preferred_credential(set_name="DBCredsSYSDBA", target_name="'
|
|
|| tgts.target_name
|
|
|| '",target_type="'
|
|
|| tgts.target_type
|
|
|| '",credential_name="'
|
|
|| ( case gdm.composite_target_name
|
|
when 'evol-MIC-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'evol-PRD-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'evol-UAT-Grp' then 'ORADB_DEFEVOACC_SYS'
|
|
when 'evol-TST-Grp' then 'ORADB_DEFEVOTST_SYS'
|
|
when 'evol-DEV-Grp' then 'ORADB_DEFEVODEV_SYS'
|
|
when 'bi-MIC-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'bi-PRD-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'bi-UAT-Grp' then 'ORADB_DEFEVOACC_SYS'
|
|
when 'bi-TST-Grp' then 'ORADB_DEFEVOTST_SYS'
|
|
when 'bi-DEV-Grp' then 'ORADB_DEFEVODEV_SYS'
|
|
when 'dots-MIC-Grp' then 'ORADB_DEFDOTSPRD_SYS'
|
|
when 'dots-PRD-Grp' then 'ORADB_DEFDOTSPRD_SYS'
|
|
when 'dots-UAT-Grp' then 'ORADB_DEFDOTSACC_SYS'
|
|
when 'dots-TST-Grp' then 'ORADB_DEFDOTSTST_SYS'
|
|
when 'dots-DEV-Grp' then 'ORADB_DEFDOTSDEV_SYS'
|
|
when 'infra-PRD-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'infra-TST-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'misc-MIC-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'misc-PRD-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
when 'syn-PRD-Grp' then 'ORADB_DEFEVOPRD_SYS'
|
|
end
|
|
)
|
|
|| '",'
|
|
|| ')'
|
|
-- tgts.target_name,
|
|
-- tgts.target_type,
|
|
-- tgts.type_qualifier3,
|
|
-- nvl(composite_target_name, 'Unassigned') group_name
|
|
from
|
|
mgmt$target tgts
|
|
join mgmt$group_derived_memberships gdm
|
|
on ( tgts.target_guid = gdm.member_target_guid
|
|
)
|
|
where
|
|
tgts.target_type in
|
|
( 'oracle_database', 'oracle_pdb', 'rac_database'
|
|
)
|
|
and target_guid not in
|
|
( select
|
|
tcreds.target_guid
|
|
from
|
|
em_nc_creds creds
|
|
join em_target_creds_e tcreds
|
|
on ( creds.cred_guid = tcreds.cred_guid
|
|
)
|
|
where
|
|
creds.cred_type_name = 'DBCreds'
|
|
and creds.cred_scope = 1
|
|
and tcreds.set_name = 'DBCredsSYSDBA'
|
|
and tcreds.is_default = 0
|
|
)
|
|
and ( composite_target_guid is null
|
|
or composite_target_guid not in
|
|
( select
|
|
composite_target_guid
|
|
from
|
|
mgmt$group_derived_memberships
|
|
where
|
|
member_target_type = 'composite'
|
|
)
|
|
)
|
|
order by
|
|
tgts.target_name
|
|
;
|