How to enable UTL_SMTP in database level and create ACL,assigning to a user?
UTL_SMTP:
This package is used to send email's from database over SMTP(Simple mail transfer Protocol).This utility enables e-mail messages to be sent from the database (PL/SQL blocks/procs/packages) to any valid e-mail address.
Generally this is useful in database monitoring since e-mails can be sent to supporting DBAs when certain events occur. Using these services we can prepare alerts for any type of monitoring activities.
Prior to 10g we have to configure UTL_TCP, UTL_SMTP separately. But from 11g with the configuration of UTL_SMTP will invoke UTL_TCP automatically.
So now we shall configure this UTL_SMTP using below steps:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-1: Execute the query in sql prompt and make sure that "Oracle XML Database" component is valid.
SQL> select substr(comp_name,1,40) comp_name, schema, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME SCHEMA STATUS VERSION
---------------------------------------- ---------- ------ -----------
JServer JAVA Virtual Machine SYS VALID 11.2.0.2.0
OLAP Analytic Workspace SYS VALID 11.2.0.2.0
OLAP Catalog OLAPSYS VALID 11.2.0.2.0
Oracle Application Express APEX_04000 VALID 4.0.2.00.0
Oracle Database Catalog Views SYS VALID 11.2.0.2.0
Oracle Database Java Packages SYS VALID 11.2.0.2.0
Oracle Database Packages and Types SYS VALID 11.2.0.2.0
Oracle Enterprise Manager SYSMAN VALID 11.2.0.2.0
Oracle Expression Filter EXFSYS VALID 11.2.0.2.0
Oracle OLAP API SYS VALID 11.2.0.2.0
Oracle Rules Manager EXFSYS VALID 11.2.0.2.0
Oracle Text CTXSYS VALID 11.2.0.2.0
Oracle Workspace Manager WMSYS VALID 11.2.0.2.0
Oracle XDK SYS VALID 11.2.0.2.0
Oracle XML Database XDB VALID 11.2.0.2.0
15 rows selected.
Here in our case it is valid. This means the component "Oracle XML Database" is properly installed and we can configure the mail service by creating ACL (Access control list) services
Note: if it not valid proceed make it valid by using the steps shown in(http://orcl11gdba.blogspot.in/2014/04/making-invalid-oracle-xml-database.html)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-2: Find out our mail server. If we don’t know what it is we can find it in /etc/mail/sendmail.cf file.
Now go to the $ORACLE_HOME/rdbms/admin path and execute the below scripts.
sqlplus /nolog
Connect / as sysdba
@?/rdbms/admin/utlmail.sql
------ The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, BCC, and return receipt. UTL_MAIL is not installed by default.
@?/rdbms/admin/prvtmail.plb
------ The prvtmail.plb package internally creates some more supporting packages/procedures and updates/modifies the UTL_MAIL package
Now grant the access on UTL_MAIL to either public or specific user depending upon your security policy
SQL>grant execute on utl_mail to public;
SQL>grant execute on utl_mail to user_1;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-3: Now we shall be setting the parameter smtp_out_server with our mail server ip/hostname as shown below.
SQL>alter system set smtp_out_server ='xxx.xx.xxx.xx:25';
Here the number '25' indicates port number which is default.
Now we can see the effected settings in dba_network_acls view
SQL>select * from dba_network_acls;
Till now half of our part is done. Rest of part will include creation of acl and privilege provision to the user. Access control lists (ACL) maintains the XML DB repository which maintains access users, permissions, network and resources etc.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-4: Drop the same named access_control list if it already exist
You can then check what ACLs you have already defined for your database using the following query:
SQL>SELECT any_path FROM resource_view WHERE any_path like '%acls%';
ANY_PATH
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sys/acls/bootstrap_acl.xml
/sys/acls/mail_access.xml
/sys/acls/ocl_form_version_generation.xml
/sys/acls/ro_all_acl.xml
/sys/acls/utl_mail.xml
/sys/acls/utl_mail_uat1.xml
/sys/apps/plsql/xs/netaclsc.xml
Since the ACL we want to create is already exists drop the old one.
SQL>
BEGIN
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
acl => 'mail_access.xml');
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-5: Create an access control list:
Now we don’t have the old ACL in our repository so create new one with same name
SQL>
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'mail_access.xml',
description => 'Permissions to access e-mail server.',
principal => 'user_1/public', -----------if you want to create ACL for public use public in principal field or else keep the specific user
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-6: Now the ACL for the user is created and we have to assign this list to smtp(mail server)
SQL>
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'mail_access.xml',
host => 'xxx.xx.xxx.xx',
lower_port => 25,
upper_port => 25
);
COMMIT;
END;
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-7: Now the add privilege to other users also to whom you would like to provide access
SQL>
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'mail_access.xml',
principal => 'user_1',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'mail_access.xml',
principal => 'user_2',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'mail_access.xml',
principal => 'user_3',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
acl => 'mail_access.xml',
principal => 'user_4');
END;
/
Now we can see the ports ,hosts in dba_network_acls
SQL>SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-8: Now the entire setup is done .we can review the ports and the users who got privilege using below query
SQL>
COLUMN host FORMAT A30
COLUMN acl FORMAT A30
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
COLUMN acl FORMAT A30
COLUMN principal FORMAT A30
set lines 200
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;
Note:The DBA_NETWORK_ACLS, DBA_NETWORK_ACL_PRIVILEGES and USER_NETWORK_ACL_PRIVILEGES views display the current ACL settings
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Step-9:Now test the mail service by creating a small proc/plsql block
begin
UTL_MAIL.SEND(sender => 'pavan3417@gmail.com', recipients => 'pavan3417@gmail.com', subject => 'hello', message => 'mail test',mime_type =>'text/html');
end;
Errors i have faced during the above process:
while following the above steps i got an error at step-6 as below:
ORA-04063: package body "XDB.DBMS_RESCONFIG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "XDB.DBMS_RESCONFIG"
this means the package DBMS_RESCONFIG owned by XDB is invalid. in order to make it valid i tried to recompile it by executing
SQL>alter package XDB.DBMS_RESCONFIG compile body;
SQL>@?/rdbms/admin/utlrp.sql
but still the package was invalid and i got the below errors in package:
SQL> show errors
Errors for PACKAGE BODY XDB.DBMS_RESCONFIG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
67/8 PL/SQL: SQL Statement ignored
69/26 PL/SQL: ORA-00932: inconsistent datatypes: expected REF got CHAR
179/8 PL/SQL: SQL Statement ignored
181/26 PL/SQL: ORA-00932: inconsistent datatypes: expected REF got CHAR
In order to make it valid i have to reinstall the 'Oracle XML Database' component but it requires downtime ,so i followed other process:
----->executed the script in 733667.1 doc to find invalids(this is optional)
----->conn xdb/password
@?/rdbms/admin/prvtxrc.plb
this will try to recreate the DBMS_RESCONFIG package
----->conn / as sysdba
@?/rdbms/admin/catmetx
this will modify/alter the package DBMS_RESCONFIG and rebuilds the associated indexes and provides privileges.
@?/rdbms/admin/utlrp
this is to compile invalid objects
select owner,object_type,count(*) from dba_objects where status='INVALID' and owner ='XDB';
compilation of catmetx cleared the error and made the dbms_resconfig valid
Note:The real object views are defined in the catmetx.sql script (this script is invoked by catxdbv.sql which is invoked by catqm.sql)
cheers...!!!!
No comments:
Post a Comment