Send EMAIL using UTL_MAIL in Oracle 10g

The UTL_MAIL package was introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP. In order to use Oracle UTL_MAIL package you need to set a new init.ora parameter “SMTP_OUT_SERVER”, set to your outgoing mailserver.
Follow the simple steps to send an email using UTL_MAIL package
Step 1: Install UTL_MAIL package
To install the UTL_MAIL package, run the below files as user "SYS"
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb
Step 2: Grant permissions
Grants the execute permission on UTL_MAIL privilege to PUBLIC or the user which will use the package. Run the below command as user “SYS”
SQL> GRANT EXECUTE ON UTL_MAIL TO PUBLIC;
-or-
SQL> GRANT EXECUTE ON UTL_MAIL TO ;
Step 3: Set SMTP_OUT_SERVER parameter
SQL> ALTER SYSTEM SET smtp_out_server=’smtp.domain.com’ SCOPE=both;
Step 4: Create procedure to send email
CREATE OR REPLACE PROCEDURE test_email AS
BEGIN
UTL_MAIL.SEND(sender => 'xxx@ esscongroup.com',
recipients => 'xxx@ esscongroup.com',
cc => 'xxx@ esscongroup.com',
bcc => 'xxx@ esscongroup.com',
subject => 'Test Mail',
message => 'Hi, This is just a test mail');
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occured:' ||sqlerrm);
END;
SQL> exec test_email;
Step 5: Send email using UTL_MAIL with attachments
You must set UTL_FILE_DIR to a directory, where the attachment files exists
CREATE OR REPLACE PROCEDURE test_email_attach AS
BEGIN
UTL_MAIL.SEND_ATTACH_VARCHAR2(sender => 'xxx@ esscongroup.com',
recipients => 'xxx@ esscongroup.com',
cc => 'xxx@ esscongroup.com',
bcc => 'xxx@ esscongroup.com',
subject => 'Test Mail',
message => 'Hi, This is just a test mail'
attachment => ‘text’
att_filename => ‘test_attach.txt’);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occured: '
sqlerrm);
END;
Please note that with att_inline you can specify, whether the attachment is viewable inline with the message body or not.
You can also use the below parameters
attachment - A text attachment.
priority - The message priority, the default is NULL.
mime_type - The mime type of the message, default is ‘text/plain; charset=us-ascii‘.
att_inline - Specifies whether the attachment is viewable inline with the message body, default is TRUE.
att_mime_type - The mime type of the attachment, default is ‘text/plain; charset=us-ascii‘.
att_filename - The string specifying a filename containing the attachment, default is NULL.

Restrict DDL Operations on a Schema in Oracle Database

In order to ensure schema consistency, DBAs may want to streamline who can issue Data Definition Language commands. Certain users are not permitted to make structural changes to object definitions, which implies no DDL operations can be performed by certain group of users. In that case DBA can achieve his goal simply by making a trigger on the schema.

For instance we want the user Nathan not to be able to perform any DDL. Then create trigger as below:

SQL> conn nathan/n
Connected.

SQL> create table before_trigger(a number);
Table created.

SQL>conn system/s
Connected.SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_Nathan
3 BEFORE DDL
4 ON Nathan.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /
Trigger created.

SQL> conn nathan/n
Connected.

SQL> create table after_trigger(a number);
create table after_trigger(a number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -30900 is out of
range
ORA-06512: at line 2

Create Read only user for a Schema in Oracle

I will demonstrate the procedure with examples to setup a read only user for a schema in oracle database. In the example I will make chris user with read only permission on sam schema.
Let's start by creating SAM user.
SQL> CREATE USER SAM IDENTIFIED BY S;
User created.

SQL> GRANT DBA TO SAM;
Grant succeeded.

SQL> CONN SAM/S;
Connected.

SQL> CREATE TABLE SAM_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO SAM_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE SAM_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER SAM_TAB2_T AFTER INSERT ON SAM_TAB1
BEGIN
INSERT INTO SAM_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM SAM_TAB2;
View created.


Method 1: Granting Privilege Manually

Step 1: Create Chris User
SQL> CREATE USER CHRIS IDENTIFIED BY C;
User created.

Step 2: Grant only select session and create synonym privilege to Chris user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO CHRIS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_sam.sql
@@/oradata2/select_only_script.sql
SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate an output script /oradata2/select_only_to_sam.sql which will be rerun.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT 'GRANT SELECT ON SAM.' ||TABLE_NAME || ' TO CHRIS;' FROM DBA_TABLES WHERE OWNER='SAM';
SELECT 'GRANT SELECT ON SAM.' ||VIEW_NAME || ' TO CHRIS;' FROM DBA_VIEWS WHERE OWNER='SAM';

Step 5:
Now execute the /oradata2/script.sql which will generate scipt /oradata2/select_only_to_sam.sql.
SQL> @/oradata2/script.sql

GRANT SELECT ON SAM.SAM_TAB1 TO CHRIS;
GRANT SELECT ON SAM.SAM_TAB2 TO CHRIS;

Step 6:
Execute the output script select_only_to_sam.sql which will grant read only permission to chris user no sam's schema.
SQL> @/oradata2/select_only_to_sam.sql

Step 7:
Log on as chris user and create synonym that will access sam's table without any dot(.). Like to access sam_tab2 of sam schema he need to write sam.sam_tab2. But after creating synonym he simply can use sam_tab2 to access chris table and views.
To create synonym do the following,

SQL>CONN CHRIS/C;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_sam.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR SAM.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='SAM';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR SAM.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='SAM';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_sam.sql

Step 8: At this stage you have completed your job. Log on as chris schema and see,
SQL> select * from sam_tab1;
1 2

SQL> show user
USER is "CHRIS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into sam_tab1 values(4,3);
insert into sam_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='SAM';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON SAM.'||tables.table_name||' TO chris';
execute immediate sql_txt;
END LOOP;
END;
/

This is the script for grant select permission for views.

DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='SAM';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON SAM.'||tables.view_name||' TO chris';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on sam schema,
Log on as chris and execute the following procedure.

SQL>CONN CHRIS/C
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='SAM'
UNION SELECT VIEW_NAME name from all_views where owner='SAM' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR SAM.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Method 3: Writing a Trigger

After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to sam just after log in CHRIS user.

create or replace trigger log_on_after_chris
after logon ON CHRIS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = sam';
END;
/