summary
1. create utl_mail package
2. create an ACL and grant privilege
3. add user privilege to ACL
4. assign ACL to network
5. Test configuration
Step 1 : create utl_mail package
CONN sys/password AS SYSDBA
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
SQL> alter system set smtp_out_server = '' scope=spfile;
Step 2 : -- create an ACL and grant privilege
col acl form a50
col host form a30
col principal form a30
set lines 120
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'UTLMAILPKG.xml',
description => 'UTL_MAIL ACL Setup',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
Step 3. -- add user privilege to ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'UTLMAILPKG.xml',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'resolve',
position => NULL,
start_date => NULL,
end_date => NULL);
COMMIT;
END;
/
Step 4 -- assign ACL to network
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'UTLMAILPKG.xml',
host => '*',
lower_port => NULL,
upper_port => NULL);
commit;
end;
/
Step 5 : Test configuration
begin
utl_mail.send(
sender => 'scott',
recipients => 'scott@gmail.com',
message => 'Hello World'
);
commit;
end;
/
- check ACL configuration using v$ view
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('CSX_CMOR_UTLMAILPKG.xml', 'MAXIMO', 'connect'), 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('CSX_CMOR_UTLMAILPKG.xml', 'MAXIMO', 'resolve'),1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
FROM dual;
-- Check ACL’s and privileges by querying dba_network_acls.
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
-- check ACL privileges
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
-- check user ACL access
conn coi_own/coi_own
COLUMN host FORMAT A30
SELECT host, lower_port, upper_port, privilege, status FROM user_network_acl_privileges;
-- Removing ACL and priviliges
Run the following as SYS:
-- unassign ACL
begin
dbms_network_acl_admin.unassign_acl(
acl => 'utl_http.xml',
host => 'www.tiger.com',
lower_port => 80,
upper_port => 80
);
commit;
end;
/
-- Delete Privilege
begin
dbms_network_acl_admin.delete_privilege(
'utl_http.xml', 'SCOTT', NULL, 'connect'
);
commit;
end;
/
-- drop ACL
begin
dbms_network_acl_admin.drop_acl(
'utl_http.xml'
);
commit;
end;
Hope this Help! Rupam
