DDL ALERT LOGGING

There are always new features that may not be as big as for example an active duplicate is but sometimes
are very handy.
One of those in Oracle 11G  is the DDL ALERT LOGGING. Which do exactly what it says.
So if you create ,drop of truncate a table it will be shown in the alert file or in de log.xml which
contains more detail information than the alert file. It come in handy when you want to know the
exacte time you drop a table for the flashback
You must the follwoing parameter to TRUE :  default is FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=both;

SQL> DROP TABLE AAP;

cd /oracle/hone/diag/rdbms/sfd451t/SFD451T/
view log.xml

<msg time='2012-06-26T12:03:29.632+02:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:3961:4222364190' client_id='' type='NOTIFICATION'
 group='schema_ddl' level='16' host_id='srv5011'
 host_addr='10.142.151.23' module='SQL*Plus' pid='14870'>
 <txt>drop table aap
 </txt>
</msg>


You of course can also it in the alert file

Tue Jun 26 12:03:29 2012
drop table aap
Tue Jun 26 12:04:16 2012
Thats nice and handy

ORA-29273: HTTP request failed

I got a call from a devloper that there was an issue on there new Oracle 11gr2 database
It had to do with a http request from within the database. the error was

ORA-29273: HTTP-aanvraag is mislukt.
ORA-06512: in "SYS.UTL_HTTP", regel 1722
ORA-24247: netwerktoegang geweigerd door toegangsbeheerlijst (ACL, access control list)
ORA-06512: in regel 1

The message was clear the user didn't have acces to the utl_http packge.
Which was strange because the user had execute rights on the package.

SQL>select grantee , table_name , privilege
          from dba_tab_privs
          where table_name = 'UTL_HTTP'
          and   grantee = 'OWNER_SY';

GRANTEE                        TABLE_NAME                     PRIVILEGE
------------------------------ ------------------------------ ----------------------------------------
OWNER_SY                        UTL_HTTP                       EXECUTE

So you would think that it should work as in Oracle 10g and below
But no
select substr(utl_http.request('http://example:7778'),1,30) from dual
              *
ERROR at line 1:
ORA-29273: HTTP-aanvraag is mislukt.
ORA-06512: in "SYS.UTL_HTTP", regel 1722
ORA-24247: netwerktoegang geweigerd door toegangsbeheerlijst (ACL, access control list)
ORA-06512: in regel 1

The reason why it doesn't work is the due to the enhanced security level in Oracle 11g.
We need to confugure an access control list (ACL) and grant connect privilige on the acl
to user owner_sy.

First we create an ACL. This has to be done user user sys

SQL> BEGIN
          DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
          acl          => 'utl_http.xml',
         description  => 'http access',
         principal    => 'OWNER_SY',
         is_grant     => TRUE,
        privilege    => 'connect');
       COMMIT;
END;
/

acl
 => Name of the Access Control List. This is a XML file which will be created in /sys/acls directory by default.
Description
 => Description of the ACL.
Principal
 => Name of the user or role (case sensitive) to whom the permissions are being granted or denied.
is_grant
 => TRUE or FALSE, whether to grant access or deny access.
privilege
 => connect or resolve (lowercase always). Will the user be able to connect to the network resource or just could resolve the network address.
start_date
 => Start date (optional) of the access to the user.
end_date
 => End date (optional) of the access to the user.

If you want to grant connect privilige to the just created ACL to an user use the following package
Also under user sys
Here we grant the connect privilige on acl utl_http.xml to user scott

SQL>BEGIN
        DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
        acl          => 'utl_http.xml',               
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'connect',
       position     => null);
      COMMIT;
END;
/

Now of course we have to assign a network host to the access control list.
In this cause host example.
Under user sys

SQL>BEGIN
         DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
         acl          => 'utl_http.xml',               
         host         => 'example.com');
   COMMIT;
END;
/

acl
 => Name of the Access Control List.
host
 => Name of the host.
lower_port
 => Lower port (optional) from the range of ports allowed on this host.
upper_port
=> Upper port (optional) from the range of ports allowed on this host

NOTE:  You can also assign multiple hosts in one ACL. but you can't assign one host to multiple ACL'S

SQL>BEGIN
          DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
          acl          => 'utl_http.xml',               
          host         => '*.example.com');
   COMMIT;
END;
/

We can see if the acl hosts assignment and privililges via two views

SQL> select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS;
ACL                            HOST                 LOWER_PORT UPPER_PORT
------------------------------ -------------------- ---------- ----------
/sys/acls/utl_http.xml         example.com
                          

SQL>select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
ACL                            PRINCIPAL            PRIVILEGE  IS_GRANT
------------------------------ -------------------- ---------- --------
/sys/acls/utl_http.xml         OWNER_SY             connect    true
/sys/acls/utl_http.xml         SCOTT                      connect    true

Now let see if it's work under user owner_sy
SQL> select substr(utl_http.request('http://example.com:7778'),1,30) from dual;
SUBSTR(UTL_HTTP.REQUEST('HTTP:
------------------------------
<!-- saved from url=(0022)http
Now see it it work under user scott

SQL> conn scott/tiger@testdb
SQL> select substr(utl_http.request('http://example.com:7778'),1,30) from dual;
SUBSTR(UTL_HTTP.REQUEST('HTTP:
------------------------------
<!-- saved from url=(0022)http

It works

How to drop an acl

SQL> BEGIN
         DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
         acl         => 'utl_http.xml');
        COMMIT;
        END;
/

How to unassign an host in an acl

SQL>BEGIN
         DBMS_NETWORK_ACL_ADMIN.unassign_acl (
         acl         => 'utl_http.xml',
        host       => '*.example.com'
    );
  COMMIT;
END;
/

Remove privilege from acl

SQL>BEGIN
        DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE (
        acl          => 'utl_http.xml',               
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'connect');
      COMMIT;
END;
/
Thats it.