BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'utl_mail.xml',
description => 'Enables mail to be sent',
principal => 'ABC_PROD',
is_grant => TRUE,
privilege => 'connect'
);
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'utl_mail.xml',
host => 'mailgot.it.volvo.com',
lower_port => 25);
commit;
END;
/
Begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_mail.xml',
principal => 'ABC_PROD',
is_grant => TRUE,
privilege => 'connect');
end;
/
---------------------------------------------------------------------------------------
Prod :
HOST LOWER_PORT UPPER_PORT ACL
---------------------------------------- ---------- ---------- --------------------------------------------------
* NETWORK_ACL_85F175090E3FD358E0535EB670994281
localhost /sys/acls/oracle-sysman-ocm-Resolve-Access.xml
SQL> alter session set container=gddvvp02;
Session altered.
SQL> SET LINESIZE 150
COLUMN acl FORMAT A50
COLUMN principal FORMAT A20
COLUMN privilege FORMAT A10
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
ORDER BY acl, principal, privilege;SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8
ACL PRINCIPAL PRIVILEGE IS_GR START_DATE END_DATE
-------------------------------------------------- -------------------- ---------- ----- ----------- -----------
/sys/acls/oracle-sysman-ocm-Resolve-Access.xml ORACLE_OCM resolve true
NETWORK_ACL_85F175090E3FD358E0535EB670994281 GSMADMIN_INTERNAL resolve true
NETWORK_ACL_85F175090E3FD358E0535EB670994281 PUBLIC connect true
NETWORK_ACL_85F175090E3FD358E0535EB670994281 PUBLIC resolve true
oracle@localhost: sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Apr 1 08:30:42 2020
Version 18.8.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.8.0.0.0
SQL> alter session set container=ABCPDB;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
Package created.
Synonym created.
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.
No errors.
SQL> GRANT EXECUTE ON UTL_TCP TO MANGO;
GRANT EXECUTE ON UTL_SMTP TO MANGO;
GRANT EXECUTE ON UTL_MAIL TO MANGO;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
SQL> GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO dbvowner;
GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO dbvowner
*
ERROR at line 1:
ORA-01917: user or role 'DBVOWNER' does not exist
SQL> GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO MANGO;
Grant succeeded.
SQL> SET LINESIZE 150
SQL>
SQL> COLUMN acl FORMAT A50
COLUMN principal FORMAT A20
SQL> SQL> COLUMN privilege FORMAT A10
SELECT acl,
SQL> SQL> 2 principal,
privilege,
is_grant,
3 4 5 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
6 7 8 ORDER BY acl, principal, privilege;
ACL PRINCIPAL PRIVILEGE IS_GR START_DATE END_DATE
-------------------------------------------------- -------------------- ---------- ----- ----------- -----------
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 APEX_190200 connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 APEX_190200 http true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 DVINTRA connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 DVINTRA http true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 DVLUREN connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 DVLUREN http true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 DVWEB connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 DVWEB http true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 EASIER connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 EASIER http true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 GGSYS resolve true
ACL PRINCIPAL PRIVILEGE IS_GR START_DATE END_DATE
-------------------------------------------------- -------------------- ---------- ----- ----------- -----------
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 GSMADMIN_INTERNAL resolve true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 SALJSTOD connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 SALJSTOD http true
14 rows selected.
SQL> Begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect');
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> Begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'resolve');
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => '/sys/acls/oracle-sysman-ocm-Resolve-Access.xml',
description => 'localhost',
principal => 'ORACLE_OCM',
is_grant => TRUE,
privilege => 'resolve');
END;
2 3 4 5 6 7 8 9
10 /
PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
acl => '/sys/acls/oracle-sysman-ocm-Resolve-Access.xml');
END;
GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO PUBLIC;
GRANT EXECUTE ON UTL_TCP TO PUBLIC;
GRANT EXECUTE ON UTL_SMTP TO PUBLIC;
GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO PUBLIC;
GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO DVWEB;
GRANT EXECUTE ON UTL_TCP TO DVWEB;
GRANT EXECUTE ON UTL_SMTP TO DVWEB;
GRANT EXECUTE ON UTL_MAIL TO DVWEB;
GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO DVWEB;
-------------------------------------------------------------------------------------
set linesize 200 pages 9999
col host for a30
col acl for a50
col acl_owner for a15
SELECT host, lower_port, upper_port, acl,
DECODE(
DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, 'TANGO', 'connect'),
1, 'GRANTED', 0, 'DENIED', null) privilege
FROM dba_network_acls
WHERE host IN
(SELECT * FROM
TABLE(DBMS_NETWORK_ACL_UTILITY.DOMAINS('www.us.oracle.com')))
ORDER BY DBMS_NETWORK_ACL_UTLITITY.DOMAIN_LEVEL(host) desc, lower_port,
upper_port;
========================================
SELECT * from dba_network_acls;
=========================================
COLUMN acl FORMAT a50
COLUMN principal FORMAT A30
col PRIVILEGE for a15
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;
========================================
SELECT acl,
host,
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege_aclid(aclid, 'TANGO', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dba_network_acls;
========================================
Open ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'open_acl_file.xml',
description => 'A test of the ACL functionality',
principal => 'TEST',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'open_acl_file.xml',
host => '*',
lower_port => 1,
upper_port => 9999);
COMMIT;
END;
/
==================================================
Assign ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1',
principal => 'TANGO',
is_grant => FALSE,
privilege => 'resolve',
position => NULL,
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
connect
http
http-proxy
resolve
smtp
=================================================
Check Privilege
SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1', 'TANGO', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;
===================================================
BEGIN
-- Create the new ACL, naming it "netacl.xml", with a description.
-- Also, provide one starter privilege, granting user FOO
-- the privilege to connect.
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('netacl.xml',
'Allow usage to the UTL network packages', 'FOO', TRUE, 'connect');
-- Now grant privilege to resolve DNS names for FOO,
-- and then grant connect and resolve to user BAR
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1' ,'TANGO', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1' ,'TANGO', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1' ,'BAR', TRUE, 'resolve');
-- Specify which hosts this ACL applies to,
-- for simplicity, we're saying all (*)
-- You might want to specify certain hosts to lock this down.
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('netacl.xml','*');
END;
/
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE('NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1' ,'TANGO', TRUE, 'connect');
===============================================================
https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_networkacl_adm.htm
Prod :
HOST LOWER_PORT UPPER_PORT ACL
---------------------------------------- ---------- ---------- --------------------------------------------------
* NETWORK_ACL_85F175090E3FD358E0535EB670994281
localhost /sys/acls/oracle-sysman-ocm-Resolve-Access.xml
QA
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb
GRANT CREATE PROCEDURE, DROP ANY PROCEDURE TO dbvowner;
GRANT EXECUTE ON UTL_TCP TO TANGO;
GRANT EXECUTE ON UTL_SMTP TO TANGO;
GRANT EXECUTE ON UTL_MAIL TO TANGO;
GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO TANGO;
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => '/sys/acls/oracle-sysman-ocm-Resolve-Access.xml',
description => 'localhost',
principal => 'ORACLE_OCM',
is_grant => TRUE,
privilege => 'resolve');
END;
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 PUBLIC connect true
NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1 PUBLIC resolve true
SQL> Begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect');
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
SQL> Begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'NETWORK_ACL_96A9E1DDBF6B8B54E0535CB6709913C1',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'resolve');
end;
/ 2 3 4 5 6 7 8
PL/SQL procedure successfully completed.
================================================================
begin
DBMS_NETWORK_ACL_ADMIN.delete_privilege (
acl => 'networkacl.xml',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => 'networkacl.xml'
);
dbms_network_acl_admin.create_acl(acl => 'networkacl.xml',
description => 'Allow Network Connectivity',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
dbms_network_acl_admin.assign_acl(acl => 'networkacl.xml',
host => 'wv4101-vfsindiacms-qa.srv.volvo.com',
lower_port => NULL,
upper_port => NULL);
commit;
end;
commit;
end;
No comments:
Post a Comment