参考文章http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php。
Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol. In previous versions of the database, access to external services was effectively an on/off switch based on whether a user was granted execute permissions on a specific package or not. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants.
我们的解决方案参考了前面的文章，具体步骤如下：
Check xdb status
set echo on;
Master Note
for Oracle XML Database (XDB) Install / Deinstall (文档 ID 1292089.1)
connect / as sysdba
set pagesize 1000
col comp_name format a36
col version format a12
col status format a8
col owner format a12
col object_name format a35
col name format a25
-- Check status of XDB
select comp_name, version, status
from dba_registry
where comp_id = 'XDB';
(select * from v$option)
-- Check for invalid objects
select owner, object_name, object_type, status
from dba_objects
where status = 'INVALID'
and owner in ('SYS', 'XDB');
spool off;
step1 install
xdb
select tablespace_name from dba_tablespaces;
SQL>
select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME SEGMEN
------------------------------ ------
SYSTEM MANUAL
SYSAUX AUTO
UNDOTBS1 MANUAL
TEMP MANUAL
USERS AUTO
MAXIMO AUTO
RBS AUTO
RBSBIG AUTO
TOOLS AUTO
@?/rdbms/admin/catqm.sql andrew123 USERS TEMP YES
@?/rdbms/admin/utlrp.sql
step2: install the dbms_network_acl_admin package
@ ?/rdbms/admin/catnacl.sql
@ ?/rdbms/admin/dbmsnacl.sql
@ ?/rdbms/admin/prvtnacl.plb
Step3 create acl
Log db as sysdba
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl=> 'NOR754S_SENDMAIL.xml',
description=> 'EMAIL_SEND',
principal=> 'SYS',
is_grant=> TRUE,
privilege=> 'connect',
start_date=> SYSTIMESTAMP,
end_date=> NULL);
COMMIT;
END;
/
Run the following
SQL AS sysdba:
spool
/home/ora11g/NOR754S.sql
select
'BEGIN' ||chr(10)||
' DBMS_NETWORK_ACL_ADMIN.add_privilege (
'||chr(10)||
' acl=>
'||'''NOR754S_SENDMAIL.xml'''||', '||chr(10)||
' principal=>'''||upper(username)||''','||chr(10)||
' is_grant=> TRUE, '||chr(10)||
' privilege=> '||'''connect'','||chr(10)||
' position=> NULL, '||chr(10)||
' start_date=> SYSTIMESTAMP,'||chr(10)||
' end_date=> NULL);'||chr(10)||
' END;'||chr(10)||
'/'||chr(10)
from dba_users
where account_status='OPEN'
and username
not in('SYS');
spool off
!
---then run
sqlplus /as sysdba
@/home/ora11g/NOR754S.sql
---to add other user
Only one ACL can be assigned to a specific
host and port-range combination. Assigning a new ACL to a specific host and
port-range results in the deletion of the previous assignment. You must take
care when making a new assignment that you are not opening ports that were
closed by a previous ACL assignment, or you could be opening yourself to attack.
When wildcard usage causes overlapping assignments, the most specific
assignment will take precedence, so an ACL assigned to 192.168.2.3:80 takes
precedence over once assigned to 192.168.2.* etc. Run the following sql:
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl=> 'NOR754S_SENDMAIL.xml',
host=> '9.xxx.xxx.19', ----邮件服务器IP
lower_port=>24433, -----指定端口号
upper_port=> NULL);
COMMIT;
END;
/
Step4 :add other
user to acl
sqlplus -s / as sysdba < set termout off
set heading off
set pagesize 0
set verify off
set echo off
step5: assign act to network