We have 100+ Oracle servers with versions 11/12c and Oracle Cloud Control 13c connected to them all.
I was asked to prepare scripts to execute "CIS Oracle 12c benchmark" scripts automatically. Some of them will "harden" the DB, by changing parameters, and some will alert for noncompliance, for example #1.3 - drop default users:
SELECT USERNAME
FROM ALL_USERS
WHERE USERNAME IN ('BI','HR','IX','OE','PM','SCOTT','SH');
But we have SAP application which works with user "HR". So we must put the SAP DB server in the exclude list.
We decided to use Cloud Control 13c (CC13c) jobs mechanism to accomplish this task.
I will create "Control config table" in the CC13c server, which will control the "exclude list".
I have encountered an issue with accessing "Control config table" and target DB server.
Each job execution should be started with checking, if the particular DB server is in the "exclude list" or not for current paragraph.
What's the problem? lets use DB_link! - I said, but the answer was NO! Neither creating ANY DB object in target DB is allowed.
I am considering:
1) "one-time" creation of <XXX> directory in each target server for local/shared location
2) export "Control config table" from CC13c to CSV file
3) copy to each target/shared location
4) load the data from CSV file to memory using UTL_FILE
Any advise will be much appreciated!
1 through 3 sound fine. But in terms of easy loading, I'd just have the config as an xml file, then you can access it directly with SQL, eg
config.xml
==========
<servers>
<server>
<hostid>7369</hostid>
<hostname>host1</hostname>
<os>WINDOWS</os>
</server>
<server>
<hostid>7499</hostid>
<hostname>host2</hostname>
<os>SOLARIS</os>
</server>
<server>
<hostid>7521</hostid>
<hostname>host3</hostname>
<os>LINUX</os>
</server>
</servers>
SQL> with config as
2 ( select xmltype(bfilename('TEMP','confg.xml'), nls_charset_id('UTF8')) xml_data from dual )
3 SELECT xt.*
4 FROM config x,
5 XMLTABLE('/servers/server'
6 PASSING x.xml_data
7 COLUMNS
8 "HOSTID" VARCHAR2(4) PATH 'hostid',
9 "HOSTNAME" VARCHAR2(10) PATH 'hostname',
10 "OS" VARCHAR2(9) PATH 'os'
11 ) xt;
HOST HOSTNAME OS
---- ---------- ---------
7369 host1 WINDOWS
7499 host2 SOLARIS
7521 host3 LINUX