Update to utl_mail Trigger

A year or so ago I posted a trigger for adding the entry to the network ACL when granting privileges on utl_mail (here: https://dbakerber.wordpress.com/2011/06/29/11gr2-network-acl-what-a-nice-feature/ ). For some reason Oracle decided that in Oracle 11, if you grant execute on utl_mail to a user, you must also grant the user access to a network Access Control List if you want the user to be able to actually use utl_mail. I have recently updated the trigger to deal with some design problems, and also written a trigger to remove the access when the permission on utl_mail is revoked. The updated and new trigger are below:

Grant trigger:

/* Formatted on 10/28/2014 7:43:52 AM (QP5 v5.267.14150.38599) */
CREATE OR REPLACE TRIGGER capture_grant_mail
   AFTER GRANT
   ON DATABASE
DECLARE
   smtp_server_name   VARCHAR2 (30) := 'EMPTY_NAME';
   sql_text           ora_name_list_t;
   hosted             NUMBER := 0;
   grantee_list       ora_name_list_t;
   grantee            VARCHAR2 (30);
   stmt               VARCHAR2 (2000);
   n                  NUMBER;
   num_grantees       BINARY_INTEGER;
   added              NUMBER := 0;
   created            NUMBER := 0;
   cluster_database   VARCHAR2 (5) := 'FALSE';
BEGIN
   DBMS_OUTPUT.enable (20000);

   BEGIN
      SELECT VALUE
        INTO smtp_server_name
        FROM v$parameter
       WHERE name = 'smtp_out_server';
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         smtp_server_name := 'EMPTY_NAME';
   END;

   SELECT VALUE
     INTO cluster_database
     FROM v$parameter
    WHERE name = 'cluster_database';

   IF    RTRIM (LTRIM (smtp_server_name)) IS NULL
      OR smtp_server_name = 'EMPTY_NAME'
   THEN
      IF cluster_database = 'TRUE'
      THEN
         smtp_server_name := 'CLUSTER_NAME';
      ELSE
         SELECT host_name INTO smtp_server_name FROM v$instance;
      END IF;
   END IF;

   n := ora_sql_txt (sql_text);

   FOR i IN 1 .. n
   LOOP
      stmt := stmt || sql_text (i);
   END LOOP;

   IF (UPPER (stmt) LIKE '%UTL_MAIL%')
   THEN
      num_grantees := ora_grantee (grantee_list);
      grantee := UPPER (grantee_list (1));

      SELECT COUNT (1)
        INTO created
        FROM sys.dba_network_acls
       WHERE acl LIKE '%mail_service.xml%';

      -- dbms_output.put_line(to_char(created));
      IF created = 0
      THEN
         -- dbms_output.put_line('Creating...');
         DBMS_NETWORK_ACL_ADMIN.create_acl (acl           => 'mail_service.xml',
                                            description   => 'UTL_MAIL',
                                            principal     => grantee,
                                            is_grant      => TRUE,
                                            privilege     => 'connect');

         -- dbms_output.put_line('Created..');

         IF smtp_server_name = 'CLUSTER_NAME'
         THEN
            FOR host_cursor IN (SELECT host_name FROM gv$instance)
            LOOP
               DBMS_NETWORK_ACL_ADMIN.assign_acl (
                  acl    => 'mail_service.xml',
                  HOST   => host_cursor.host_name);
            END LOOP;
         ELSE
            DBMS_NETWORK_ACL_ADMIN.assign_acl (acl    => 'mail_service.xml',
                                               HOST   => smtp_server_name);
         END IF;
      ELSE
         SELECT COUNT (1)
           INTO hosted
           FROM sys.dba_network_acls
          WHERE     acl LIKE '%mail_service.xml%'
                AND (   HOST = smtp_server_name
                     OR (    smtp_server_name = 'CLUSTER_NAME'
                         AND HOST IN (SELECT host_name FROM gv$instance))
                     OR HOST = '*');

         IF hosted = 0
         THEN
            IF smtp_server_name = 'CLUSTER_NAME'
            THEN
               FOR host_cursor IN (SELECT host_name FROM gv$instance)
               LOOP
                  DBMS_NETWORK_ACL_ADMIN.assign_acl (
                     acl    => 'mail_service.xml',
                     HOST   => host_cursor.host_name);
               END LOOP;
            ELSE
               DBMS_NETWORK_ACL_ADMIN.assign_acl (acl    => 'mail_service.xml',
                                                  HOST   => smtp_server_name);
            END IF;
         END IF;
      END IF;

      FOR i IN 1 .. num_grantees
      LOOP
         grantee := UPPER (grantee_list (i));

         SELECT COUNT (1)
           INTO added
           FROM dba_network_acl_privileges
          WHERE     principal = grantee
                AND UPPER (privilege) = 'RESOLVE'
                AND acl LIKE '%mail_service.xml%';

         IF added = 0
         THEN
            DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'mail_service.xml',
                                                  principal   => grantee,
                                                  is_grant    => TRUE,
                                                  PRIVILEGE   => 'resolve');
         END IF;

         SELECT COUNT (1)
           INTO added
           FROM dba_network_acl_privileges
          WHERE     principal = grantee
                AND UPPER (privilege) = 'CONNECT'
                AND acl LIKE '%mail_service.xml%';

         IF added = 0
         THEN
            DBMS_NETWORK_ACL_ADMIN.add_privilege (acl         => 'mail_service.xml',
                                                  principal   => grantee,
                                                  is_grant    => TRUE,
                                                  PRIVILEGE   => 'connect');
         END IF;
      END LOOP;
   END IF;
END;
/

 

Revoke Trigger:

CREATE OR REPLACE TRIGGER capture_revoke_mail
   AFTER REVOKE
   ON DATABASE
DECLARE
   smtp_server_name   VARCHAR2 (30) := 'EMPTY_NAME';
   empty_list         EXCEPTION;
   PRAGMA EXCEPTION_INIT (empty_list, -24246);
   sql_text           ora_name_list_t;
   hosted             NUMBER := 0;
   grantee_list       ora_name_list_t;
   grantee            VARCHAR2 (30);
   stmt               VARCHAR2 (2000);
   n                  NUMBER;
   num_grantees       BINARY_INTEGER;
   added              NUMBER := 0;
   created            NUMBER := 0;
   cluster_database   VARCHAR2 (5) := 'FALSE';
BEGIN
   -- dbms_output.enable(20000);
   n := ora_sql_txt (sql_text);

   FOR i IN 1 .. n
   LOOP
      stmt := stmt || sql_text (i);
   END LOOP;

   IF (UPPER (stmt) LIKE '%UTL_MAIL%')
   THEN
      DBMS_OUTPUT.put_line (TO_CHAR (n));
      num_grantees := ora_revokee (grantee_list);
      DBMS_OUTPUT.put_line (TO_CHAR (num_grantees));
      grantee := UPPER (grantee_list (1));

      FOR i IN 1 .. num_grantees
      LOOP
         BEGIN
            -- dbms_output.put_line('In loop:');
            grantee := UPPER (grantee_list (i));

            SELECT COUNT (1)
              INTO added
              FROM dba_network_acl_privileges
             WHERE     principal = grantee
                   AND UPPER (privilege) = 'RESOLVE'
                   AND acl LIKE '%mail_service.xml%';

            IF added = 0
            THEN
               DBMS_OUTPUT.put_line (
                     'User does not have resolve privilege, not revoked='
                  || grantee);
            ELSE
               DBMS_NETWORK_ACL_ADMIN.delete_privilege (
                  acl         => 'mail_service.xml',
                  principal   => grantee,
                  is_grant    => TRUE,
                  privilege   => 'resolve');
            END IF;

            SELECT COUNT (1)
              INTO added
              FROM dba_network_acl_privileges
             WHERE     principal = grantee
                   AND UPPER (privilege) = 'CONNECT'
                   AND acl LIKE '%mail_service.xml%';

            IF added = 0
            THEN
               DBMS_OUTPUT.put_line (
                     'User does not have connect privilege, not revoked='
                  || grantee);
            ELSE
               DBMS_NETWORK_ACL_ADMIN.delete_privilege (
                  acl         => 'mail_service.xml',
                  principal   => grantee,
                  is_grant    => TRUE,
                  privilege   => 'connect');
            END IF;
         EXCEPTION
            WHEN empty_list
            THEN
               DBMS_NETWORK_ACL_ADMIN.drop_acl (acl => 'mail_service.xml');
               CONTINUE;
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (SQLERRM);
               RAISE;
         END;
      END LOOP;
   END IF;
END;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: