begin
dbms_network_acl_admin.append_host_ace (
host=>'*',
lower_port => 1,
upper_port => 65535,
ace=> sys.xs$ace_type( privilege_list=>sys.XS$NAME_LIST('JDWP') ,
principal_name=>'HMA_TM_PROD',
principal_type=>sys.XS_ACL.PTYPE_DB));
end;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '*',
lower_port => 1,
upper_port => 65535,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'HMA_TM_PROD',
principal_type => xs_acl.ptype_db));
END;
/
*************************************
send test email from database
Create proc
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
/
Add privs
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'send_mailx.xml',
description => 'Purpose of the acl is to send mail',
principal => 'sravan',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'send_mailx.xml',
host => '*',
lower_port => 1,
upper_port => 9999);
COMMIT;
END;
/
Send
BEGIN
send_mail(p_to => 'xxx10563@xxx.local',
p_from => 'xxx-dba-alerts@xxx.local',
p_message => 'This is a test message.',
p_smtp_host => '10.120.xx.xx');
END;
/
dbms_network_acl_admin.append_host_ace (
host=>'*',
lower_port => 1,
upper_port => 65535,
ace=> sys.xs$ace_type( privilege_list=>sys.XS$NAME_LIST('JDWP') ,
principal_name=>'HMA_TM_PROD',
principal_type=>sys.XS_ACL.PTYPE_DB));
end;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.append_host_ace (
host => '*',
lower_port => 1,
upper_port => 65535,
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'HMA_TM_PROD',
principal_type => xs_acl.ptype_db));
END;
/
*************************************
send test email from database
Create proc
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
/
Add privs
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'send_mailx.xml',
description => 'Purpose of the acl is to send mail',
principal => 'sravan',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'send_mailx.xml',
host => '*',
lower_port => 1,
upper_port => 9999);
COMMIT;
END;
/
Send
BEGIN
send_mail(p_to => 'xxx10563@xxx.local',
p_from => 'xxx-dba-alerts@xxx.local',
p_message => 'This is a test message.',
p_smtp_host => '10.120.xx.xx');
END;
/