Error occurs when sending email from database 11g triiger

We had 9i database and we migrated to 11g.
one of the problems that occurred was   executing the "demo_mail" package that sends emails.
The problem
Sending mail via UTL_TCP and UTL_SMTP failes
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115 

Cause :

The Database user which execute the demo_mail package is not authorized to open connection and send a mail.

Solution :

suppose that the user executes the package is "Scott" and  smtp_host is "email2.dhaman.org"

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'send_mail.xml',description => 'send_mail ACL',principal => 'scott',is_grant => true,privilege => 'connect');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl =>'send_mail.xml',principal => 'scott',is_grant  => true,privilege => 'resolve')

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'send_mail.xml',host => 'email2.dhaman.org')

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

now re execute the package again.It will work.


If you got the error again , follow these steps

as SYS:

exec DBMS_NETWORK_ACL_ADMIN.DROP_ACL('mailserver_acl.xml');      
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('mailserver_acl.xml','Comment','SILVIO',TRUE,'connect');      
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('mailserver_acl.xml','*');      
commit;

as SILVIO:

select utl_inaddr.get_host_address('z-dina-db') from dual;      
SQL> select utl_inaddr.get_host_address('z-dina-db') from dual;      
select utl_inaddr.get_host_address('z-dina-db') from dual      
       *      
ERROR at line 1:      
ORA-24247: network access denied by access control list (ACL)      
ORA-06512: at "SYS.UTL_INADDR", line 19      
ORA-06512: at "SYS.UTL_INADDR", line 40      
ORA-06512: at line 1

as SYS:      
SELECT ACL as ACL_PATH FROM DBA_NETWORK_ACLS      
        WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

     
select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','connect') from dual;      
-- returns 1      
select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','resolve') from dual;      
-- returns NULL

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('mailserver_acl.xml','SILVIO',TRUE,'resolve');      
commit;

select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','connect') from dual;      
-- returns 1      
select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/mailserver_acl.xml','SILVIO','resolve') from dual;      
-- returns 1

as SILVIO:

SQL>  select utl_inaddr.get_host_address('z-dina-db') from dual;

UTL_INADDR.GET_HOST_ADDRESS('Z-DINA-DB')      
------------------------------------------------------------------------------------------------------------------------------------      
10.223.6.223

 

 

So, only after assigning the 'resolve' privilege in addition to the 'connect' privilege I could successfully use the UTL_INADDR call.

Hope this helps.

 Silviois the user name

Comments

Post a Comment

Popular posts from this blog

Another user has changed the row with primary key oracle.jbo.Key[12 ].

Image
Try these solutions:  make sure that you check "refresh after insert" and refresh after update" for the primary key column in you entity object. in the application module tab Click the Properties tab and select jbo.locking.mode. Double click the “pessimistic”value next to the property to open the field editor and type “optimistic” over the value.Press enter. if error still exist then you must set "refresh after insert" and refresh after update" as true for all attributes in the entity object another way is to execute the View Object after the commit operation to bring back updated data to ViewObject. For this we need to override the  afterCommit()  of ViewObject class as follows:                  public void afterCommit(TransactionEvent){                         executeQuery();                       }              5- another way to solve this error is to overide the lock() method of the EntityImpl class       public void lock() {    
Read more

Working With File Throgh WebUtill

-------------------------------------------------- Required Configuraion : By default the file transfer is disabled in webutil for a security measure. So, you have to enable File transfer configuration in webutil.cfg file. The webutil.cfg file is located in  forms\server. Open The webuil.cfg file and edit the following parameters : transfer.database.enabled=FALSE   Change To TRUE transfer.appsrv.enabled=FALSE       Change to TRUE WebUtil_File contains APIs to manipulate files and directories on the client. These Functions Or APIs are: Copy_File :  Copies a file Returns a Boolean to indicate success.  Syntax :   Webutil_File.Copy_File(SourceFileName in Varchar2 , Destination In Varchar2) Return Boolean  Example   Declare  vCopied Boolean;  Begin   vCopied :=  Webutil_File.Copy_File('C:\temp\mypic.jpg','X:\destination_folder');  If vCopied = true Then    Message('File copied Successfully');  Else   Message('Error in Copying File');  End if; Delete_File:
Read more

Reading From File

-------------------------------------------------- Declare   vfilename varchar2(500);   in_file   Client_Text_IO.File_Type;   linebuf   VARCHAR2(1800);  BEGIN     vfilename := client_get_file_name('c:/temp/', File_Filter=>'Comma Dialimeted Files (*.csv)|*.csv|');      in_file := client_Text_IO.Fopen(vfilename, 'r');       GO_BLOCK('Emp');      FIRST_RECORD;     LOOP     Client_Text_IO.Get_Line(in_file, linebuf);      p_output_line(linebuf);     Client_Text_IO.New_Line;      Next_record;    END LOOP;     FIRST_RECORD; EXCEPTION   WHEN no_data_found THEN     Client_Text_IO.Put_Line('Closing the file...');     Client_Text_IO.Fclose(in_file); END; ------------------------------------------------------- PROCEDURE p_output_line(p_line varchar2) IS  vLINE VARCHAR2(4000); vVALUE VARCHAR2(1000);  vCOMMA_COUNT NUMBER; vREPORT_DATE DATE; BEGIN                      vLINE := p_line;  vCOMMA_COUNT := LENGTH(vLINE)- LENGTH(REPLACE(vLINE,',',''
Read more