Use of ACLs in setting up an Oracle TDM repository

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

These instructions discuss the use of ACLs in setting up an Oracle TDM repository and also on steps to make it more restrictive if required. 
Since we now support Oracle 11g, there is no way to bypass the use of ACLs. 
ACL were introduced from Oracle 11g to remove a security risk to the database. 

Issue with installing TDM DB repository on Oracle RAC cluster. Running into ACL issues while setting up DB repositories on Oracle RAC cluster.
It would be helpful to have the best practices for DB repository install on Oracle RAC cluster.

Environment

TDM 3.8 Oracle RAC repository

Resolution

When running into ACL issues, you might have to rerun the grants.ddl. 
This file is part of the DB install kit.
This dll will allow the user to drop the ACL and then recreate it.

The syntax is:

sqlplus -L %GT_SYS_USER%/%GT_SYS_PASSWORD%%GT_TNS% @grants.ddl %GT_REP_USER% %GT_REP_USER_UC% >> repository.log

Where
%GT_SYS_USER% is the system user
%GT_SYS_PASSWORD% password
%GT_TNS% TNS alias for target database
% GT_REP_USER%  is the repository user
%GT_REP_USER_UC% is the repo user in Uppercase

Note:  There is no way to bypass the use of ACLs.  ACL were introduced from Oracle 11g to remove a security risk to the database.  
Connect and resolve privileges will be needed for our repository.

The link "Fine grained access to network services 11g -> Assign an ACL to a network" provides detailed information about ACLs. 
Our grants.ddl uses * when assigning the ACL to the host.
This would allow the ACL to resolve network addresses using UTL_INADDR

Note:  It is believed that our product does not use the following APIs  (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP).

For now, it is suggested that these two ACLs resolve1.xml and resolve2.xml be created.

• The first one is assigned to hostname like DEV01-main*
• The second one is assigned to the subnet where the server is hosted like 192.168.*.*

The ACL should be created by the repository owner. 
The IP address would be the machine where the repository is installed. 
The hostname needs to be included as well. 
If uppercase and lowercase hostnames are considered different entities, then you would need to include both upper and lower cases.  

As discussed to get the hostname,  please run:

select SYS_CONTEXT ('USERENV','SERVER_HOST')   from dual

You can also use the following query:

SELECT DISTINCT              upper (instance_name),

                                                upper (host_name),

                                                (              CASE instr (UTL_INADDR.get_host_address (host_name),'%')

                                                                                WHEN 0 then UTL_INADDR.get_host_address (host_name)

                                                                                ELSE substr (UTL_INADDR.get_host_address (host_name), 1, instr (UTL_INADDR.get_host_address (host_name),'%') -1 )

                                                                END ) ip_address

FROM                   gv$instance

 

This will give you the hostname you can run:

SELECT  host_name FROM gv$instance