Method 2 - Proxy users

This method makes use of proxy connections, which results in more restrictive permissions for the users, but provides a more secure environment. It does mean that there may be more connections to the database, per process, as a separate connection is required for each unique set of credentials.

It is recommended to create the relevant schemas before starting the MFDBFH-enabled enterprise server region, rather than allowing the region to automatically generate the schemas, as you need to create and grant the appropriate privileges to the schema users to be able to use them as proxies.

Micro Focus suggest performing the following steps:

1. Create the schema users for the region, cross-region and any datastores you intend to use.
   Tip: You can locate the names of the schema users in the creation scripts that are produced using the dbfhadmin -script command (for an example, see Create Oracle Datastores Using Script Files). Within the script, search for the ‘CREATE USER’ statement. In general, they are:

   Datastores	Name of the datastore prefixed with DBFH_; for example, DBFH_VSAM for a datastore named ‘VSAM’
   Region databases	Name of the PAC prefixed with DBRG_; for example, DBRG_TESTDB for a PAC named ‘TESTDB’
   Cross-region databases	Always named DBRG__XREGN_

2. Grant the appropriate permissions for each schema user to allow proxy connections and the creation of tables/procedures within their own schemas. For example, grant the following (replacing <schema-user> with the schema users located above, and <user> with the user you intend to make the proxy connection through):

   ALTER USER <schema-user> GRANT CONNECT THROUGH <user>
   GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TYPE TO <schema-user>
   GRANT SELECT ON SYS.V_$ACTIVE_INSTANCES
   GRANT SELECT ON SYS.V_$INSTANCE
   GRANT SELECT ON SYS.V_$PARAMETER
   GRANT UNLIMITED TABLESPACE TO <schema-user>

   See the lists in Method 1 - Using MFDBFH-specific roles for the minimum required permissions for each user, depending on the role they perform.

3. Populate the relevant region and cross-region schemas. This should be done via the scripts generated with dbfhadmin. You can execute these using sqlplus or dbfhadmin, ensuring you provide the credentials as the proxy user. For example:

   sqlplus dbfhinstall[DBRG__XREGN_]/dbfhinstall@DBFHTEST @datastorescript.sql
   dbfhadmin -createdb -file:createregn.sql -type:region -provider:ora -existdb:DBFHTEST -user:dbfhinstall[DBRG_TESTDB] -password:dbfhinstall

4. In your MFDBFH configuration file, ensure the proxy user is specified in the connection string. The proxy user for each connection should be the name of the schemas identified above. For example:

   <dsn name="ORA.VSAM" type="datastore" dsname="VSAM" optio="none" dbname="DBFHTEST" connect="dbfhdefault[DBFH_VSAM]/dbfhdefault@DBFHTEST"/>
   <dsn name="ORA.CAS.CROSSREGION" type="crossregion.cas" dbname="DBFHTEST" connect="dbfhdefault[DBRG__XREGN_]/dbfhdefault@DBFHTEST"/>
   <dsn name="ORA.CAS.TESTDB" type="region.cas" region="TESTDB" dbname="DBFHTEST" connect="dbfhdefault[DBRG_TESTDB]/dbfhdefault@DBFHTEST"/>

To reduce the number of connections per process, you could specify the proxy user for the region and cross-region databases to be the same user as used for one of your datastores; each other datastore will still needs its own unique proxy user. This can only be done if you populate the region/cross-region databases before you start the enterprise server region, as otherwise the necessary tables are not created. In the above example, this would mean replacing DBRG__XREGN_ and DBRG_TESTDB with DBFH_VSAM, which reduces the number of connections used for proxy users from 3 to 1.