admin 2: config connection ACL to dispatcher - noradle/noradle-oracle-server GitHub Wiki
- call dbms_network_acl_admin 11.2 12.1 in trigger of server_control_t to implement it
- check DBA_NETWORK_ACLS 11.2 and DBA_NETWORK_ACL_PRIVILEGES 11.2 to ensure you have the right to connect to noradle-dispatcher
select a.res.getclobval() from resource_view a where equals_path(res, '/sys/acls/noradle.xml') > 0; -- in 11g, not 12c
select * from dba_network_acls;
select * from dba_network_acl_privileges;
HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER
* /sys/acls/noradle.xml 000000008000273A SYS
60.29.143.50 /sys/acls/noradle.xml 000000008000273A SYS
127.0.0.1 /sys/acls/noradle.xml 000000008000273A SYS
ACL ACLID PRINCIPAL PRIVILEGE IS_GRANT INVERT START_DATE END_DATE ACL_OWNER
/sys/acls/noradle.xml 000000008000273A PSP1 connect true false SYS
By default, install noradle-oracle-server sql install script will set PSP db user access right to any address, but if it's too open for security, you can grant PSP db user access right to just the listening addresses of the noradle-dispatchers the db want to connect to, as configured in table server_control_t. I just consider that noradle-oracle-server will do it automatically, as the following 2 methods
- as DML of server_control_t, a trigger will grant/revoke access right to the target address of the record
- as a server process started, [framework.listen][] will grant access right when ACL error is captured
but for manual config, see grant_network.sql
Oracle DB is able to make TCP/IP connection to outside world by UTL_TCP
pl/sql API,
but by default,
oracle(11g and up) forbid to make connection to any address by network ACL rules,
you must use DBMS_NETWORK_ACL_ADMIN
package to create a new ACL to allow access to nodejs(noradle listener).
NodeJS dispatcher server will manage all the connections made by oracle,
and use them as communication path for the nodejs clients.
The configuration script is as the following code:
Be sure to connect as sys or other privileged db users in SQLPlus(or other oracle clients), and execute the code below.
begin
/* view current noradle network ACL configuration with this SQL:
select a.res.getclobval() from resource_view a where equals_path(res, '/sys/acls/noradle.xml') > 0;
*/
/* uncomment this when you want existing ACL "noradle.xml" to be removed first
dbms_network_acl_admin.drop_acl(
acl => 'noradle.xml'
);
*/
dbms_network_acl_admin.create_acl(
acl => 'noradle.xml',
description => 'oracle2nodejs',
principal => 'PSP',
is_grant => true,
privilege => 'connect'
);
/* when ACL "noradle.xml" exists, execute .add_privilege is ok,
for example, when you reinstall psp schema
dbms_network_acl_admin.add_privilege(
acl => 'noradle.xml',
principal => 'PSP',
is_grant => true,
privilege => 'connect'
);
*/
-- for each record in server_control_t, call assign_acl to grant network access right from oracle to nodejs
dbms_network_acl_admin.assign_acl(
acl => 'noradle.xml',
host => '127.0.0.1'
);
-- or call assign_acl to grant network access to all ip address
dbms_network_acl_admin.assign_acl(
acl => 'noradle.xml',
host => '*'
);
commit;
end;
/
Note:
- "install.sql" will setup net ACL by default configuration, you may bypass this step.
- read http://oradoc.noradle.com/appdev.112/e10577/d_networkacl_adm.htm for reference
- "principal" must specify the schema(case sensitive, def to PSP) that hold the noradle core schema.
- "dbms_network_acl_admin.add_privilege" will grant right to other db user that act as NORADLE engine user.
- Notice: normally you will install only one version of NORADLE, so ".add_privilege"can be bypassed.
- "host" in "dbms_network_acl_admin.assign_acl" specify where(dns/ip) the NORADLE dispatcher is.
- if you have multiple NORADLE dispatcher in multiple address, repeat ".assign_acl" with each of the addresses.
After done, oracle background scheduler processes (as Noradle server processes) have the right to make connection to all your nodejs NORADLE dispatcher sever process who listen for oracle connection.
Note: you must be sure that oracle XML-DB is installed, see rem code in install.sql if XML-DB is not installed,
prompt xmldb must be installed already
prompt see and run $ORACLE_HOME/rdbms/admin/catqm.sql
Rem NAME
Rem catqm.sql - CAtalog script for sQl xMl management
Rem
Rem DESCRIPTION
Rem Creates the tables and views needed to run the XDB system
Rem Run this script like this:
Rem catqm.sql <XDB_PASSWD> <TABLESPACE> <TEMP_TABLESPACE> <SECURE_FILES_REPO>
Rem -- XDB_PASSWD: password for XDB user
Rem -- TABLESPACE: tablespace for XDB
Rem -- TEMP_TABLESPACE: temporary tablespace for XDB
Rem -- SECURE_FILES_REPO: if YES and compatibility is at least 11.2,
Rem then XDB repository will be stored as secure files;
Rem otherwise, old LOBS are used. There is no default value for
Rem this parameter, the caller must pass either YES or NO.
@@grant_network.sql
reference: