В Oracle11g для любого пакета, выполняющего внешние сетевые вызовы (UTL_SMTP, UTL_MAIL) необходимо создать список ACL, включить в него пользователя или роль и предоставить списку привилегию сетевого уровня.
Посмотреть, есть ли какие-либо ACL в базе можно так:
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
Создаем список ACL, включаем в него пользователя и предоставляем списку привилегию сетевого уровня:
begin
dbms_network_acl_admin.create_acl(
acl => 'mail-server.xml'
,description => 'Permission to make connections to mail server'
,principal => 'SCOTT'
,is_grant => TRUE
,privilege => 'connect'
);
dbms_network_acl_admin.assign_acl(
acl => 'mail-server.xml'
,host => 'my-SMTP-servername'
,lower_port => 25
,upper_port => NULL /* открывается только порт 25*/
);
end;
ещё пример:
begin
-- Создание списка ACL
dbms_network_acl_admin.create_acl(
acl => 'oracle-permissions.xml'
,description => 'Network permissions for www.oracle.com'
,principal => 'WEBROLE'
,is_grant => TRUE
,privilege => 'connect'
,start_date => SYSTIMESTAMP
,end_date => NULL
);
-- Назначение привилегий
dbms_network_acl_admin.create_acl(
acl => 'oracle-permissions.xml'
,description => 'Network permissions for www.oracle.com'
,principal => 'WEDROLE'
,is_grant => TRUE
,privilege => 'connect'
,start_date => SYSTIMESTAMP
,end_date => NULL
);
-- Определение допустимых адресов
dbms_network_acl_admin.assign_acl(
acl => 'oracle-permissions.xml'
,host => 'www.oracle.com'
,lower_port =>80
,upper_port => 80
);
end;
Еще пример:
Можно использовать пакет:
create or replace package "pkg_DBA_IR"
AUTHID CURRENT_USER
is
procedure change_assign_acl;
end "pkg_DBA_IR";
create or replace package body "pkg_DBA_IR" is
--
-- Константы:
--
-- Имена баз данных:
C_DB_NAME_PROD constant varchar2(30) := 'TEST';
C_DB_NAME_UAT constant varchar2(30) := 'TESTUAT';
C_DB_NAME_DEV constant varchar2(30) := 'TESTDEV';
-- Имена хостов:
C_HOST_NAME_PROD constant varchar2(30) := 'esbprod.sun.com';
C_HOST_NAME_UAT constant varchar2(30) := 'esbuat.sun.com';
C_HOST_NAME_DEV constant varchar2(30) := 'esbdev.sun.com';
-- Имена параметров:
C_ACL_NAME constant varchar2(30) :='networkacl.xml';
-- Смена параметров ACL:
procedure change_assign_acl
is
begin
if upper(SYS_CONTEXT ('USERENV', 'DB_NAME')) = C_DB_NAME_PROD then
raise_application_error(-20001, 'Run Only in Test Environment');
elsif upper(SYS_CONTEXT ('USERENV', 'DB_NAME')) = C_DB_NAME_UAT then
begin
dbms_network_acl_admin.drop_acl(
acl => C_ACL_NAME);
dbms_output.put_line('ACL dropped…..');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.create_acl(
acl => C_ACL_NAME,
description => 'Permissions to access WebServices',
principal => 'TS',
is_grant => TRUE,
privilege => 'connect');
dbms_output.put_line('ACL created…..');
exception
when others then
dbms_output.put_line('Error creating ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.assign_acl(
acl => C_ACL_NAME,
host => C_HOST_NAME_UAT,
lower_port => NULL,
upper_port => NULL);
dbms_output.put_line('ACL assigned…..');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
commit;
elsif upper(SYS_CONTEXT ('USERENV', 'DB_NAME')) = C_DB_NAME_DEV then
begin
dbms_network_acl_admin.drop_acl(
acl => C_ACL_NAME);
dbms_output.put_line('ACL dropped…..');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.create_acl(
acl => C_ACL_NAME,
description => 'Permissions to access WebServices',
principal => 'TS',
is_grant => TRUE,
privilege => 'connect');
dbms_output.put_line('ACL created…..');
exception
when others then
dbms_output.put_line('Error creating ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.assign_acl(
acl => C_ACL_NAME,
host => C_HOST_NAME_DEV,
lower_port => NULL,
upper_port => NULL);
dbms_output.put_line('ACL assigned…..');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
commit;
else
commit;
end if;
exception
when others then
null;
end change_assign_acl;
end "pkg_DBA_IR";
grant execute on dbms_network_acl_admin TO ts;
begin
TS."pkg_DBA_IR".change_assign_acl;
end;
/
Посмотреть, есть ли какие-либо ACL в базе можно так:
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
Создаем список ACL, включаем в него пользователя и предоставляем списку привилегию сетевого уровня:
begin
dbms_network_acl_admin.create_acl(
acl => 'mail-server.xml'
,description => 'Permission to make connections to mail server'
,principal => 'SCOTT'
,is_grant => TRUE
,privilege => 'connect'
);
dbms_network_acl_admin.assign_acl(
acl => 'mail-server.xml'
,host => 'my-SMTP-servername'
,lower_port => 25
,upper_port => NULL /* открывается только порт 25*/
);
end;
ещё пример:
begin
-- Создание списка ACL
dbms_network_acl_admin.create_acl(
acl => 'oracle-permissions.xml'
,description => 'Network permissions for www.oracle.com'
,principal => 'WEBROLE'
,is_grant => TRUE
,privilege => 'connect'
,start_date => SYSTIMESTAMP
,end_date => NULL
);
-- Назначение привилегий
dbms_network_acl_admin.create_acl(
acl => 'oracle-permissions.xml'
,description => 'Network permissions for www.oracle.com'
,principal => 'WEDROLE'
,is_grant => TRUE
,privilege => 'connect'
,start_date => SYSTIMESTAMP
,end_date => NULL
);
-- Определение допустимых адресов
dbms_network_acl_admin.assign_acl(
acl => 'oracle-permissions.xml'
,host => 'www.oracle.com'
,lower_port =>80
,upper_port => 80
);
end;
Еще пример:
DECLARE l_acl_name VARCHAR2(30) := 'utl_tcp.xml'; l_ftp_server_ip VARCHAR2(20) := '192.168.0.1'; l_ftp_server_name VARCHAR2(20) := 'ftp.oracle.com'; l_username VARCHAR2(30) := 'SCOTT'; BEGIN DBMS_NETWORK_ACL_ADMIN.create_acl ( acl => l_acl_name, description => 'Allow connections using UTL_TCP', principal => l_username, is_grant => TRUE, privilege => 'connect', start_date => SYSTIMESTAMP, end_date => NULL); COMMIT; DBMS_NETWORK_ACL_ADMIN.add_privilege ( acl => l_acl_name, principal => l_username, is_grant => FALSE, privilege => 'connect', position => NULL, start_date => NULL, end_date => NULL); COMMIT; DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => l_acl_name, host => l_ftp_server_ip, lower_port => NULL, upper_port => NULL); DBMS_NETWORK_ACL_ADMIN.assign_acl ( acl => l_acl_name, host => l_ftp_server_name, lower_port => NULL, upper_port => NULL); COMMIT; END; /
Можно использовать пакет:
create or replace package "pkg_DBA_IR"
AUTHID CURRENT_USER
is
procedure change_assign_acl;
end "pkg_DBA_IR";
create or replace package body "pkg_DBA_IR" is
--
-- Константы:
--
-- Имена баз данных:
C_DB_NAME_PROD constant varchar2(30) := 'TEST';
C_DB_NAME_UAT constant varchar2(30) := 'TESTUAT';
C_DB_NAME_DEV constant varchar2(30) := 'TESTDEV';
-- Имена хостов:
C_HOST_NAME_PROD constant varchar2(30) := 'esbprod.sun.com';
C_HOST_NAME_UAT constant varchar2(30) := 'esbuat.sun.com';
C_HOST_NAME_DEV constant varchar2(30) := 'esbdev.sun.com';
-- Имена параметров:
C_ACL_NAME constant varchar2(30) :='networkacl.xml';
-- Смена параметров ACL:
procedure change_assign_acl
is
begin
if upper(SYS_CONTEXT ('USERENV', 'DB_NAME')) = C_DB_NAME_PROD then
raise_application_error(-20001, 'Run Only in Test Environment');
elsif upper(SYS_CONTEXT ('USERENV', 'DB_NAME')) = C_DB_NAME_UAT then
begin
dbms_network_acl_admin.drop_acl(
acl => C_ACL_NAME);
dbms_output.put_line('ACL dropped…..');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.create_acl(
acl => C_ACL_NAME,
description => 'Permissions to access WebServices',
principal => 'TS',
is_grant => TRUE,
privilege => 'connect');
dbms_output.put_line('ACL created…..');
exception
when others then
dbms_output.put_line('Error creating ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.assign_acl(
acl => C_ACL_NAME,
host => C_HOST_NAME_UAT,
lower_port => NULL,
upper_port => NULL);
dbms_output.put_line('ACL assigned…..');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
commit;
elsif upper(SYS_CONTEXT ('USERENV', 'DB_NAME')) = C_DB_NAME_DEV then
begin
dbms_network_acl_admin.drop_acl(
acl => C_ACL_NAME);
dbms_output.put_line('ACL dropped…..');
exception
when others then
dbms_output.put_line('Error dropping ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.create_acl(
acl => C_ACL_NAME,
description => 'Permissions to access WebServices',
principal => 'TS',
is_grant => TRUE,
privilege => 'connect');
dbms_output.put_line('ACL created…..');
exception
when others then
dbms_output.put_line('Error creating ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
begin
dbms_network_acl_admin.assign_acl(
acl => C_ACL_NAME,
host => C_HOST_NAME_DEV,
lower_port => NULL,
upper_port => NULL);
dbms_output.put_line('ACL assigned…..');
exception
when others then
dbms_output.put_line('Error assigning ACL: '||C_ACL_NAME);
dbms_output.put_line(sqlerrm);
end;
commit;
else
commit;
end if;
exception
when others then
null;
end change_assign_acl;
end "pkg_DBA_IR";
grant execute on dbms_network_acl_admin TO ts;
begin
TS."pkg_DBA_IR".change_assign_acl;
end;
/