Create and configure ACL in oracle database, Network Access control list (ACL), smtp_out_server - SMTP outgoing mail server

Create and configure ACL in oracle database, Network Access control list (ACL), smtp_out_server - SMTP outgoing mail server.

Access control lists are created and manipulated using the DBMS_Network_ACL_Admin and DBMS_Network_ACL_Utility packages. ACLs are used to control access by users to external network services and resources from the database through PL/SQL network utility packages including UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL and UTL_INADDR.

Follow the below steps to configure ACL.

o   Activate UTL_MAIL

            Execute below query in SYS user.
            SQL> @?/rdbms/admin/utlmail.sql
                        Package created.
                        Synonym created.
            SQL> @?/rdbms/admin/prvtmail.plb
                        Package created.
                        Package body created.
                        Grant succeeded.
                        Package body created.
                        No errors.
            SQL> 

o   Find out SMTP mail server ip & port and configure the database parameter smtp_out_server. Here smtp_out_server's ip is 172.16.20.18 and port is 25.

Execute below query in SYS user.
            SQL> show parameter smtp
                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        smtp_out_server                      string
            SQL>
            SQL> alter system set smtp_out_server='172.16.20.18:25';

            System altered.

o   Create ACL

            In ACL principals - Users, privileges – right.

           begin
            dbms_network_acl_admin.create_acl (
            acl         => 'utl_mail_test.xml',
            description => 'Allow mail to be send',
            principal   => 'FINN',
            is_grant    =>  TRUE,
            privilege   => 'connect'
            );
            commit;
          end; 
         /

o   Add Privilege

        begin
            dbms_network_acl_admin.add_privilege (
            acl       => 'utl_mail_test.xml',
            principal => 'FINN',
             is_grant  => TRUE,
            privilege => 'connect'
            );
            commit;
        end;
        /

o   Assign ACL

        begin
            dbms_network_acl_admin.assign_acl(
            acl  => 'utl_mail_test.xml',
            host => '172.16.20.18',
             lower_port  => 25,
            upper_port  => NULL
            );
            commit;
        end;
        /

o   If other users need to be added to the ACL list, then run add_ privilege after some changes. Here the other user is ACCT.

                   begin 

                dbms_network_acl_admin.add_privilege (
                acl       => 'utl_mail_test.xml',
                principal => 'ACCT',
                 is_grant  => TRUE,
                privilege => 'connect'
                );
                commit;
            end;
            /

o   If other host need to be assign to the same ACL list, then run assign_acl after some changes. Here the other host is '172.16.124.52' and port 7081.


            begin
                dbms_network_acl_admin.assign_acl(
                acl  => 'utl_mail_test.xml',
                host => '172.16.124.52',
                lower_port  => 7081,
                upper_port  => 7081
                );
                commit;
            end;
            / 

 To Grant Privileges

        grant execute on UTL_TCP to FINN;
        grant execute on UTL_SMTP to FINN;
        grant execute on UTL_MAIL to FINN;
        grant execute on UTL_HTTP to FINN; 
        grant execute on UTL_INADDR to FINN;

        grant execute on UTL_TCP to ACCT;
        grant execute on UTL_SMTP to ACCT;
        grant execute on UTL_MAIL to ACCT;
        grant execute on UTL_HTTP to ACCT; 
        grant execute on UTL_INADDR to ACCT;


o   Test the ACL

        BEGIN
        sys.Utl_Mail.send(sender => 'ora.anup@gmail.com'
        ,recipients => 'ora.anup@gmail.com'
        ,subject => 'Testing Mail'
        ,message => 'This is Testing'
        );
        END;
        /

 

 


o   Some Important Views

The DBA_NETWORK_ACLS view displays information about network and ACL assignments.
            SET linesize 135
            col HOST for a15
            col ACL for a45
            SELECT host, lower_port, upper_port, acl  FROM   dba_network_acls;

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL.
           SET linesize 135
           col ACL for a45
           col PRINCIPAL for a20
           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;
 
The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings.
    SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;

Checking Privileges
          col GRANTEE for a25;
          col TABLE_NAME for a30;
          select grantee , table_name , privilege from dba_tab_privs where table_name = 'UTL_HTTP';
          select host, lower_port, upper_port, acl from dba_network_acls where                                                           ACL='/sys/acls/utl_mail_test.xml';


 Some more articles you might also be interested in :-

    Create and configure ACL in oracle database

    The common errors during ACL configuration

    Remove ACL and Privileges

 


Comments

Post a Comment

Popular posts from this blog

Remove ACL and privileges, Drop ACL in oracle

Image
  Remove ACL and privileges, Drop ACL in oracle See the Network and ACL assignments as below -                                    o    Unassign ACL           begin              dbms_network_acl_admin.unassign_acl(              acl   => 'utl_mail_test.xml',              host => '172.16.20.18',                lower_port   => 25,              upper_port   => NULL              );              commit;          end; ...
Read more

ORA-02050: transaction rolled back, some remote DBs may be in-doubt

ORA-02050: transaction rolled back, some remote DBs may be in-doubt Indicates that a communication error occurred during the two-phase commit. The two phase-commit mechanism is used to ensure data integrity in a distributed transaction. Example of handling the In-Doubt Transactions - in this example we are taking two servers ACCT and FIN.   Got error ORA-02050: transaction rolled back, some remote DBs may be in-doubt at FIN Server Scenario- LOCAL_TRAN_ID and GLOBAL_TRAN_ID are same.   Execute below query in SYS user at FIN server. SQL> SELECT LOCAL_TRAN_ID , STATE FROM dba_2pc_pending ; LOCAL_TRAN_ID  GLOBAL_TRAN_ID         STATE -------------   --------------- ---------------- 3.12 .2 54         --------. 3.12 . 254    collecting   Here local id and Global id is same, so it is the global coordinator. State column is collecting means this ...
Read more

Distributed transaction, Oracle Distributed Transactions, distributed transaction in oracle

Distributed transaction, Oracle Distributed Transactions, distributed transaction in oracle A distributed transaction modifies data related to two or more databases, it contains DML statements than span many nodes. For a distributed transaction to be successful all or none of the database nodes involved in the transaction need to commit or rollback the whole transaction. Note the difference between a distributed and a remote transaction; a remote transaction contains one or more DML statements that are executed on the SAME remote node, consider the examples below:- Example of Distributed transaction: insert into table@remotesite; insert into mytable: --local table commit;   Example of Remote transaction: insert into table@remotesite; commit;   Database links are used to communicate between the databases performing distributed transactions. In a distributed environment, it is fundamental that the global name of a database is unique in the network so that eac...
Read more